Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
NTNU kurs i postgis / cartodb
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
-- 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
* 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
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.