Spatial SQL mha PostGIS

Några av landets västligaste vårdcentraler ihop med Topografisk Webbkarta Visning © Lantmäteriet.

Ja, det stämmer att jag håller på att putsa på mina kunskaper i PostGIS. Jag tror att det är klokt att ha kunskap om flera metoder och verktyg för att lösa en uppgift och jag har länge känt behov av ett personligt kunskapslyft inom PostGIS. Eftersom nätverksdisken brukar vara bättre än jag på att komma ihåg SQL-detaljerna, sparar jag de flesta av mina SQL-frågor där. Det är aningen orättvist att den som vill lära sig mer om detta verktyg, men inte kommer åt nätverket, inte får tillgång till samlingen, så jag lägger ut ett axplock här på bloggen. Syftet är inte att utgöra ett utbildningsmaterial eller att vara heltäckande, utan det är endast en sammanställning av ett antal SQL-frågor som har hjälp mig i arbetet. Förhoppningsvis är det någon mer som har nytta av dem. Jag räknar med att återkomma med fler exempel framöver. Nog så, här kommer det.

Lägg till extensions
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION pgrouting;

Version av PostgreSQL, PostGIS och pgRouting
SELECT version();
SELECT postgis_full_version();
SELECT pgr_version();

Lista alla tabeller i schemat public
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Lägg till kommentar om tabell
COMMENT ON TABLE public.adresser IS 'Adresser för Västra Götaland';

Tilldela behörigheter till tabell
GRANT SELECT, INSERT, UPDATE, DELETE ON tabell TO user;

Lägg till kolumn för punktgeometri
ALTER TABLE tabell ADD COLUMN geom geometry(POINT,3006);

Räkna antalet poster i en tabell
SELECT count(*) FROM routes;

Ta reda på referenssystem för en tabell
SELECT Find_SRID('public', 'sjukhus', 'geom');

Ta reda på geometrityp
SELECT ST_GeometryType(geom) 
FROM kommunytor_vg;

Välj unika värden
SELECT DISTINCT ort FROM vardcentraler;

Skapa linjer från polygoner
SELECT id, kommunnamn, ST_Boundary(geom) 
FROM kommunytor_vg;

Buffra 100 m runt vårdcentraler
SELECT id, namn, ST_Buffer(vardcentraler_vg.geom, 100)
 FROM vardcentraler_vg;

Beräkna area
SELECT kommunkod, kommunnamn, ST_Area(geom), geom FROM kommunytor;

Summera area (i km2)
SELECT sum(ST_Area(geom))/1000000 FROM kommunytor_vg;

Beräkna linjelängd och lägg värdet i ett attribut
UPDATE vagnat SET length = ST_Length(geom);

Summera linjelängder för hela tabellen
SELECT sum(ST_Length(geom)) FROM tabell;

Summera linjelängd för linjer i resp. kommun
SELECT kommunytor_vg.kommunnamn, sum(ST_Length(kollektivtrafik_linjeriktningar.geom)/1000) AS total_langd
FROM kollektivtrafik_linjeriktningar, kommunytor_vg 
WHERE st_intersects(kommunytor_vg.geom,kollektivtrafik_linjeriktningar.geom) 
GROUP BY kommunytor_vg.id
ORDER BY kommunnamn;

Hitta alla punkter inom specifik polygon
SELECT tandlakare_folktandvarden.id, tandlakare_folktandvarden.namn, tandlakare_folktandvarden.geom
FROM
 kommunytor_vg JOIN tandlakare_folktandvarden
ON ST_Contains(kommunytor_vg.geom, tandlakare_folktandvarden.geom)
WHERE
 kommunytor_vg.kommunkod=1496;

Räkna vårdcentraler, gruppera på kommun
SELECT kommunnamn, count(*) AS antal_vc FROM vardcentraler_vg, kommunytor_vg 
WHERE st_intersects(kommunytor_vg.geom,vardcentraler_vg.geom) 
GROUP BY kommunytor_vg.id
ORDER BY kommunnamn;

Sök punkter inom 5km från koordinat
SELECT * FROM skolenheter 
WHERE ST_DWithin(geom, ST_SetSRID(ST_Point(319409, 6399807), 3006), 5000);

Så, med detta i åtanke kan du, nästa gång du ser VGR:s logga, tänka på våra vårdcentraler, sjukhus, Folktandvården, GöteborgsOperan, Göteborgs Symfoniker, Film i Väst… och spatial SQL.

En mer komplett sammanställning om funktionerna finner du på http://postgis.net/docs/manual-2.4/reference.html.