Spatial SQL #2

Spatial SQL

”Lite mindre prosa, lite fler tillämpningar” skrev jag nyligen. Här kommer därför inlägg #2 om vad jag lär mig inom spatial SQL. #1 hittar du här. Precis som förra gången är det en sammanställning av exempel som jag använder mig av och inte ett heltäckande utbildningsmaterial. Kanske är det fler som har nytta av denna sammanställning. I dagsläget är fokus på vektordata och enklare statistik, så antalet tillämpningar inom ex. raster och punktmoln högst begränsat.

Lista egenskaper för alla tabeller i schemat
SELECT *
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name;

Lista egenskaper för alla geometrikolumner
SELECT * FROM public.geometry_columns
WHERE f_table_schema = 'public'
ORDER BY f_table_name;

Lista alla adressplatser på en gata
SELECT adromrade, adrplats, xkoord, ykoord FROM adresser_90a
WHERE adromrade = 'Broplatsen'
AND postort = 'Mölndal'
ORDER BY adrplats;

Skapa convex hull för en kommun
SELECT kommunnamn, ST_ConvexHull(geom)
FROM kommunytor_vg
WHERE id = 1;

Skapa extent för kommuner
SELECT id, kommunnamn, ST_Extent(geom) As extent
FROM kommunytor_vg
GROUP BY kommunnamn, id;

Beräkna antalet noder för varje polygon
SELECT kommunnamn, ST_NPoints(geom) As punkter
FROM kommunytor_vg
ORDER BY punkter DESC;

Transformera till WGS 84
SELECT id, namn, ST_Transform(geom,4326) As geom
FROM vardcentraler_vg;

Läs ut objekt som GeoJSON i WGS 84
SELECT id, namn, ST_asGeoJSON(ST_Transform(geom,4326)) As geom
FROM vardcentraler_vg;

Hitta korta linjer
SELECT id, linje, beskrivning, ST_Length(geom) As length FROM kollektivtrafik_linjeriktningar
WHERE ST_Length(geom) < 500;

Beräkna avstånd mellan två objekt i samma tabell
SELECT ST_Distance(a.geom, b.geom)
FROM sjukhus a, sjukhus b
WHERE a.namn='Alingsås lasarett' AND b.namn='Kungälvs sjukhus';

Beräkna avstånd mellan ett objekt och samtliga poster i en annan tabell
SELECT s.namn As Sjukhus, v.namn As vårdcentral, Round(ST_Distance(s.geom, v.geom)) As avstånd
FROM
 (SELECT namn, geom FROM public.sjukhus WHERE namn = 'Brinkåsen') As s
 CROSS JOIN
 (SELECT namn, geom FROM public.vardcentraler_vg) As v
ORDER BY avstånd;

Summera befolkningsmängd inom 1 km respektive sjukhus
SELECT sj.namn, sum(b.totbef)
FROM
 befolkning_250x250m As b INNER JOIN sjukhus As sj ON ST_DWithin(b.geom,sj.geom,1000)
GROUP BY sj.namn
ORDER BY sj.namn;

Beräkna kollektivtrafiknätets längd på Tjörn
SELECT sum(ST_Length(kollektivtrafik_linjeriktningar.geom))
 FROM kollektivtrafik_linjeriktningar, kommunytor_vg
 WHERE ST_Intersects(kollektivtrafik_linjeriktningar.geom,kommunytor_vg.geom) AND kommunytor_vg.kommunnamn = 'Tjörn';

Beräkna befolkningsmängd i alla kommuner
SELECT kommunnamn, sum(totbef)
FROM public.befolkning_250x250m, kommunytor_vg
WHERE ST_Intersects(befolkning_250x250m.geom,kommunytor_vg.geom)
GROUP BY kommunnamn
ORDER BY kommunnamn;

Beräkna avstånd mellan två kommuner
SELECT a.kommunnamn As komm_a, b.kommunnamn As komm_b, ST_Distance(a.geom, b.geom) As dist_m
FROM kommunytor_vg As a CROSS JOIN kommunytor_vg As b
WHERE a.kommunnamn = 'Tjörn' and b.kommunnamn = 'Alingsås';

Hitta punkter inom 500 m från kollektivtrafikens linjenät
SELECT s.id, s.snamn
FROM
 skolenheter As s INNER JOIN kollektivtrafik_linjeriktningar As kl ON ST_DWithin(s.geom,kl.geom,500)
GROUP BY s.id
ORDER BY s.snamn;

Hitta 5 närmaste punkter
SELECT k.id, k.kommunnamn, ST_Distance(k.geom, s.geom) As avstand, k.geom
FROM kommunytor_vg As k INNER JOIN sjukhus As s ON ST_DWithin(s.geom, k.geom, 50000)
WHERE s.id = 19
ORDER BY avstand
LIMIT 5;

Alternativt

SELECT id, snamn, geom
FROM skolenheter
ORDER BY ST_SetSRID(ST_Point(322176, 6400599), 3006) <-> geom LIMIT 5;

Där tar det stopp för denna gång. Fler exempel lär följa.