Tenkte jeg skulle komme med en oppdateringspost om masteroppgaven min. Mer spesifikt tenkte jeg å skrive litt om romlige databaser, og da PostGIS spesielt. Hva er PostGIS? Jo, en “spatial database extension for PostgreSQL”, der PostgreSQL er en gammel traver i DBMS-verdenen, opensource må vite. Hva gjør så PostGIS? Den legger til romlig støtte i databasene dine. Det betyr at du kan jobbe med romlige konsepter som punkter, linjer og flater, og kjøre spørringer mot disse.
Det jeg tenkte å gjøre var å kort beskrive hvordan man bruker disse romlige strukturene, og ta noen eksempler på spørringer mot disse. Jeg forutsetter at du har en viss fortåelse for SQL generelt.
Spatial enabeling
Hva skiller en romlig database fra en “vanlig” database? I tilfellet PostGIS er dette to ting: to tabeller og en rekke funksjoner. Tabellene er: geometry_columns og spatial_ref_sys. Disse holder rede på henholdsvis hvilke kolonner i tabellene dine som er romlige og hvilke referansesystemer som brukes. Du slipper imidlertid å bekymre deg for disse, bare vit at de er der. Funksjonene er listet på PostGIS sine nettsider, og noen av dem skal jeg gå gjennom her.
En første romlig tabell
Tenk deg at du skal lage en tabell over bilder, med tilknyttet posisjon. Da lager du først en standard tabell for bildene (noe forenklet her):
CREATE TABLE images(
id int NOT NULL,
filename varchar(255),
PRIMARY KEY(id)
);
Men hvor er den romlige delen? Den er ikke med enda. Den opprettes med AddGeometryColumn(), og har syntaksen:
AddGeometryColumn(<schema_name>, <table_name>, <column_name>, <srid>, <type>, <dimension>)
Her burde det meste være selvforklarende, men det er et par ting som kanskje trenger en oppklaring. Disse er srid og type. SRID er Spatial Reference IDentifier, en måte å forklare hvilket referansesystem vi bruker. Vi skal ikke gå nærmere inn på dette her, annet enn å nevne at spatialreference.org vedlikeholder en liste over SRIDer, og at SRIDen for lengde/breddegrad-par (som du får fra GPSen din, og fra Google Maps etc er 4326.
Type referer til geometrityper, disse er det flere å velge på, f.eks POINT, LINESTRING, POLYGON etc. For en oversikt, se Wikipedia.
Vi skulle lagre georefererte bilder. Da snakker vi punkter, og vi synes lat/lon er fint (det er det man finner i EXIF f.eks.) Da kan vi legge til et lokasjonsfelt i bildetabellen vår:
SELECT AddGeometryColumn('images','the_geom',4326,'POINT',2);
(the_geom blir navnet på kolonnen vår, og 2 sier at vi kun bruker 2 dimensjoner).
Greit nok, da er vi så langt? Hvordan legger vi så inn informasjon? Vha INSERT såklart. Jeg antar at jeg har et bilde tatt på lokasjonen (63.4168827977837, 10.4198884963989), med filnavn bilde.jpg. Jeg har altså posisjonen i klartekst, såkalt WKT, denne må konverteres til et format PostGIS forstår, og ikke overraskende finnes funksjonen ST_GeomFromText(), med syntaksen: ST_GeomFromText(text,[<srid>]).
Der srid igjen er referansesystemet. Vi kan da lage en INSERT setning, eller, det er en ting til vi må huske på: hvordan WKT-formatet fungerer. Vi er jo vant med å skrive bredde,lengdegrader som (lat,lon), men i tilfellet WKT-POINT er det litt annet, her er det POINT(lon lat), dvs “gal” rekkefølge og ikke komma. Husker du på det fra dag 1 sparer du deg mye trøbbel. Dermed:
INSERT INTO images(id,filename,the_geom) VALUES (1,'bilde.jpg',ST_GeomFromText('POINT(10.4198884963989 63.4168827977837)',4326));
Da skal det være lagret. La oss så hente det ut:
SELECT * FROM images;
Dette gir:
1;"bilde.jpg";"0101000020E6100000F1FFFF9FFBD624407C5F5F6A5CB54F40"
Ikke spesielt leselig nei? Men det er fordi vi nå får den interne representasjonen. For å få lokasjonen som WKT kan vi bruke ST_AsText(), slik;
SELECT id,filename,ST_AsText(the_geom) FROM images;
Som gir:
1;"bilde.jpg";"POINT(10.4198884963989 63.4168827977837)"
Dette er et mere leselig format, som også min php-klasse for punkter kan lese. Det samme prinsippet bruker man altså for linjer, punkter etc. også. Du tar nok poenget ganske fort.
Men så langt kunne vi jo like gjerne laget to kolonner, en “lat” og en “lon” og fått samme resultat. Hva er så vitsen? Jo, romlige spørringer.
Romlige spørringer
Den store styrken i en romlig database er romlige spørringer. Dvs. SQl-spørringer som benytter seg av den romlige informasjonen vi har lagret i databasen. Dermed kan vi besvare spørsmål som “hvor lang er denne linjen”, “hva er avstanden mellom disse to bildene”, eller “vis meg alle bilder innenfor en radius av 1 km av huset mitt”, eller “vis meg alle bilder innenfor en grense på 100 meter langs linjen fra A til B.
Jeg skal ta et eksempel på en slik spørring her, for å vise noen av mulighetene. Vi antar at vi har images-tabellen vi lagde over, og en tabell poi der huset vårt ligger lagret med id=1 og en punkt-geometri the_geom. Antar vidre at images-tabellen vår nå har en god del bilder, slik at vi kan finne noen spennende i nærheten av hjemme.
Funksjonen ST_DWithin(geometry, geometry, float) virker fin til dette formålet, men det er en ting vi må være obs på: Referansesystem! PostGIS er ikke overvettes fornuftig av seg, og siden vi lagrer punktene våre som lat,lon, dvs grader vil alle enheter være i grader. Det er ikke så spennende å vite hvilke bilder som er innenfor x grader av huset ditt, vi vil ha meter. En fiks på dette er å bruke et annet referansesystem, helst da et som bruker meter (og ikke grader) som enhet. Her i Norge kan vi da bruke UTM, spesifikt sone 32N (eller 33N, det kommer ann på hvor du bor, jeg går ikke nærmere inn på dette her). SRIDen for UTM sone 32N er 32632 og funksjonen for å transformere koordinater er ST_Transform().
Dermed kan vi finne alle bilder innenfor en radius av 1 km fra huset vårt på følgende vis:
SELECT images.filename FROM images, poi WHERE poi.id=1 AND ST_DWithin(transform(images.the_geom,32632),transform(poi.the_geom,32632),1000);
Og med det tror jeg vi runder av, det er ikke verdens mest avanserte spørringer jeg har gått gjennom, men det gir en innsikt i hva som er spesielt med romlige databaser, og det kan jo være at det inspirerer til vidre eksperimentasjon.
Tar også imot tilbakemeldinger om du finner feil, mangler eller uklarheter. Jeg har selv testet spørringene, så de skal fungere, men man vet jo aldri. Det kan godt være det finne smartere måter å gjøre ting på også, la meg gjerne få vite det om så er tilfelle!