NTNU kurs i postgis / cartodb

NTNU - postgis-kurs

Introduksjon
-------
* NORKART
 * https://vimeo.com/92617228
* Webinar: 
 * http://www.norkart.no/kurs/gratis-webinar-laer-alt-du-vil-vite-om-fme-og-open-source-27/
 * http://www.norkart.no/kurs/gratis-webinar-cartodb-og-mediakart-3/
* Sommerjobb i Trondheim
 * Utvikler - JS, HTML, PostGIS, linux/devop

CartoDB-JS-kode
---------------
* https://github.com/Norkart/NTNU-kurs-PostGIS

Datasett
--------
* https://www.geonorge.no/
* https://kartkatalog.miljodirektoratet.no/
* http://www.npd.no/no/Kart/Faktakart/
 * http://factpages.npd.no/ReportServer?/FactPages/geography/geography_all&rs:Command=Render&rc:Toolbar=false&rc:Parameters=f&IpAddress=1&CultureCode=en
* http://www.ngu.no/emne/datasett-og-nedlasting
* https://www.vegvesen.no/vegkart/vegkart/#kartlag:geodata/hva:(~)/hvor:()/@600000,7225000,3
* http://www.ssb.no/natur-og-miljo/geodata
* http://gis3.nve.no/kartkatalog/metadatahg_datasett.html
* http://data.kartverket.no/download/
* http://overpass-turbo.eu/
* https://www.openstreetmap.org/
* http://www.naturalearthdata.com/

Kurs
----
* Romlige databaser
 * objektdatabase
 * lagrer geometri og kan gjøre spørringer på denne.
 
* Laste inn data via ogr2ogr,QGIS, CSV-WKT, INSERT-sql
* Spørringer.
 * where. boolsk. like
 * geometrispørringer
  * st_within, st_intersects, st_dwithin
  * st_buffer, st_length
  * knn <->
  * astext, fromtext, geojson
* Indekser
 * gist - et r-tree
 * && vs st_intersects
 * bbox

* JOINS

NTNU - postgis-kurs.sql

-- http://postgis.org/docs/ST_Area.html
-- http://postgis.org/docs/ST_Intersects.html
-- http://www.postgresql.org/docs/9.5/static/functions-aggregate.html
-- http://www.w3schools.com/sql/sql_groupby.asp
select 
k.the_geom,k.the_geom_webmercator,k.cartodb_id, 
count(t.cartodb_id) antall,
count(t.cartodb_id)/st_area(k.the_geom::geography) t_tetthet
FROM
fylker k JOIN toaletter t
ON st_intersects(k.the_geom,t.the_geom) 
GROUP BY k.the_geom,k.the_geom_webmercator,k.cartodb_id 

-- http://postgis.net/docs/ST_Distance.html
select t.the_geom, t.the_geom_webmercator,t.cartodb_id
FROM toaletter t, buslinjer_trondheim b
WHERE st_distance(t.the_geom::geography, b.the_geom::geography) < 100

-- alternativ: http://postgis.net/docs/ST_DWithin.html
select t.the_geom, t.the_geom_webmercator,t.cartodb_id
FROM toaletter t, buslinjer_trondheim b
WHERE st_dwithin(t.the_geom::geography, b.the_geom::geography, 100)

-- Tidspunkt og timestamp: http://www.postgresql.org/docs/9.5/static/functions-formatting.html
SELECT *,
to_timestamp(ulykkesdat || ulykkestid,'YYYYMMDDHH24Mi') ulykkestimestamp
FROM trafikkulykker2015

--  JSON i SQL - bryter alt dere lærer om normaliserte databaser
SELECT *,
((select string_agg((value->>'reltags')::json->>'name',' | ') from 
json_array_elements(relations::json) b)) asd
FROM buslinjer_trondheim b
where 
((select string_agg((value->>'reltags')::json->>'name',' | ') from 
json_array_elements(relations::json) b)) SIMILAR TO '%66%|%71%'


-- Nærmeste toaletter til hver busslinje - KNN (ikke optimal)
select 
(select t.cartodb_id from toaletter t ORDER by t.the_geom <-> b.the_geom limit 1) cartodb_id,
(select t.the_geom from toaletter t ORDER by t.the_geom <-> b.the_geom limit 1) the_geom,
(select t.the_geom_webmercator from toaletter t ORDER by t.the_geom <-> b.the_geom limit 1) the_geom_webmercator
from buslinjer_trondheim b

NTNU kurs i postgis og cartodb

* http://overpass-turbo.eu/s/fCH
* data.kartverket.no / https://alexanno-test.cartodb.com/datasets 

 
SQL
----
* Geometrier
 * st_astext, st_makepoint, st_geomfromtext, st_geomfromgeojson
 * st_extent, st_box, st_simplify
 * st_area, st_length, st_centroid
* PostGIS - functions: http://postgis.net/docs/PostGIS_Special_Functions_Index.html#PostGIS_TypeFunctionMatrix
* JOINS
 * <>, =, like
 * Spatial joins (intersects, &&, within, dwithin )
* KNN <->
* group by : sum, avg, min, max
 * st_collect, st_union etc http://postgis.net/docs/PostGIS_Special_Functions_Index.html#PostGIS_Aggregate_Functions

webinar-notater.sql

-- http://overpass-turbo.eu/
-- https://gist.github.com/atlefren/60234fb68d8299136c05
-- http://www.norkart.no/produkt/datavarehus/


-- toalett og kommunekart
SELECT 
f.cartodb_id,
f.the_geom_webmercator,
f.the_geom,
count(k.cartodb_id) antall
FROM kommuner f join toaletter k on (k.the_geom && f.the_geom) group by f.cartodb_id,f.the_geom,f.the_geom_webmercator

-- toaletter og kommuner - bedre
SELECT 
f.cartodb_id,
f.the_geom_webmercator,
f.the_geom,
f.navn,
(select count(t.cartodb_id) from toaletter t where st_intersects(t.the_geom, f.the_geom)) antall 
FROM kommuner f

-- bryggerier per kommune med left join
SELECT 
f.cartodb_id,
f.the_geom_webmercator,
f.the_geom,
f.navn, 
count(b.cartodb_id) antall_brygg 
FROM kommuner f left join bryggerier b on (f.the_geom && b.the_geom) group by f.cartodb_id,
f.the_geom_webmercator,
f.the_geom,
f.navn

-- nærmeste toalett til alle bryggerier i norge
SELECT 
f.cartodb_id,
f.the_geom_webmercator,
f.the_geom,
f.name,
(select st_astext(the_geom) from toaletter t order by t.the_geom <-> f.the_geom limit 1) toalett 
FROM bryggerier f

-- omsetninger innenfor naturvernområde

SELECT 
f.cartodb_id,
f.the_geom_webmercator,
f.the_geom,
f.omsetningsbeloep,
f.tekstfelt,
b.faktaark
FROM omsetninger f join naturvernomrader b on (f.the_geom && b.the_geom)


--omsetninger med bryggeriavstand og naturvernområde og innenfor 10km av bryggeri
SELECT 
f.cartodb_id,
f.the_geom_webmercator,
f.the_geom,
f.omsetningsbeloep,
f.tekstfelt,
(select faktaark from naturvernomrader n where n.the_geom && f.the_geom) naturvernomrade,
(select st_distance(t.the_geom,f.the_geom) from bryggerier t order by t.the_geom <-> f.the_geom limit 1) bryggeriavstand_sphere,
(select st_distance(st_transform(t.the_geom,32633),st_transform(f.the_geom,32633)) from bryggerier t order by t.the_geom <-> f.the_geom limit 1) bryggeriavstand_utm
FROM omsetninger f 
where (select st_distance(st_transform(t.the_geom,32633),st_transform(f.the_geom,32633)) from bryggerier t order by t.the_geom <-> f.the_geom limit 1) < 10000

-- antall kulturminner per kommune - lage datasett fra spørring:
SELECT 
f.cartodb_id,
f.the_geom_webmercator,
f.the_geom,
f.navn,
count(1) antall_kulturminner 
FROM kommuner f left join kulturminner b on (f.the_geom && b.the_geom) group by f.cartodb_id,
f.the_geom_webmercator,
f.the_geom,
f.navn