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.