varför ska du bry dig om PostGIS? – En mild introduktion till rumsliga databaser

databaser? Inte särskilt intressant.

så kan en genomsnittlig person som arbetar med GIS eller datavisualiseringar tänka. Jag måste erkänna att databaser inte är den sexigaste saken i världen (ledsen DBA), men om du hävdar (eller syftar) att göra analys eller visualisering med (rumsliga) data på ett mer allvarligt sätt, bör du definitivt inte ignorera dem. Jag hoppas att detta blogginlägg kan ge dig en uppfattning om vilken typ av fördelar effektiv användning av rumsliga databaser kan erbjuda dig.

Hype-termer kommer och går i det och det fanns en stor hype kring big data fortfarande för några år sedan, men det försvinner nu långsamt. Tja, data är fortfarande stora och faktiskt är det större än någonsin. Filstorlekar växer och i” datavetenskap ” och geovetenskap måste människor hantera data som lätt kan ligga inom Gigabyte. Ju större data, mer uppmärksamhet vi behöver betala för hur vi lagrar och analyserar det.

det är där en databas kommer in i bilden.

i mjukvaruutveckling arbetar med databaser är ett måste. Men för människor i andra underdomäner inom datavetenskap (som GIS) kan fördelarna med en databas inte alltid vara så uppenbara. Naturligtvis tenderar människor att använda de verktyg som är mest kända för dem, även om det inte skulle vara det mest effektiva sättet att uppnå mål. Men ibland kan du gå ut ur din komfortzon verkligen ge dig stora fördelar. Jag har själv långsamt insett potentialen som ligger i rumslig SQL.

detta blogginlägg är främst för personer som arbetar med geospatial data, men har inte rört PostGIS, eller kanske inte ens hört talas om det. Jag kommer inte att gå igenom hur man installerar PostgreSQL/PostGIS, utan snarare försöka ge dig en översikt över vad det är och vad är det bra för.

mitt arbetsflöde och exempel fokuserar främst på QGIS + PostGIS-kombination, men du bör notera att du också kan arbeta med endast PostGIS, din egen kod eller med några andra GIS-klienter.

inlägg … vad?

redan under mina GIS-studier hade jag hört frasen ’PostGIS är en rumslig förlängning av Postgres’ flera gånger. Det betydde inte att jag hade någon aning om vad det betyder. Jag hade ingen aning om vad som är Postgres, än mindre en rumslig förlängning.

Låt oss försöka bromsa ner det så enkelt som möjligt.

vissa människor kanske hatar mig för denna jämförelse men jag tar risken: om du aldrig har arbetat med databaser kan du tänka på databastabeller som massiva Excel-ark. Men ett massivt intelligent Excel-ark där du på millisekund kan ta reda på vilket värde som finns i den tredje kolumnen på radnummer 433 285. Och istället för att skriva funktioner inuti arket till en enda cell skriver du dem till ditt SQL-kommandofönster. Så en plats att lagra data och varifrån du kan få ut det effektivt.

PostGIS är en öppen källkod, fritt tillgänglig rumslig databas extender för PostgreSQL databashanteringssystem (aka DBMS). Så PostgreSQL (aka Postgres) är databasen och PostGIS är som ett tillägg till den databasen. Den senaste versionen av PostGIS kommer nu förpackad med PostgreSQL.

i ett nötskal lägger PostGIS till rumsliga funktioner som avstånd, område, union, korsning och specialgeometri datatyper till PostgreSQL.Rumsliga databaser lagra och manipulera rumsliga objekt som alla andra objekt i databasen.

så i en normal databas lagrar du data av olika typer (numerisk, text, tidsstämplar, bilder…) och vid behov kan du fråga (hämta) det för att svara på frågor med dina data. Frågorna kan handla om ’hur många som är inloggade på din webbplats’ eller ’hur många transaktioner som har gjorts i en webbutik’. Rumsliga funktioner kan istället svara på frågor som ’hur nära är närmaste butik’, ’är denna punkt inom detta område’eller’ vad är storleken på detta land’.

så data lagras i rader och kolumner. Eftersom PostGIS är en rumslig databas har data också en geometrikolumn med data i ett specifikt koordinatsystem definierat av spatial reference identifier (srid). Men kom ihåg att även om du skulle använda PostGIS främst för rumsliga data, är det också möjligt att lagra icke-rumsliga data där, eftersom det fortfarande har alla funktioner i en vanlig PostgreSQL-databas!

den utmärkta gränslösa PostGIS intro, introducerar tre kärnkoncept som associerar rumsliga data med en databas. Kombinerade dessa ger en flexibel struktur för optimerad prestanda och analys.

1. rumsliga datatyper som punkt, linje och polygon. Bekant för de flesta som arbetar med rumsliga data;
2. multidimensionell rumslig indexering används för effektiv bearbetning av rumsliga operationer;
3. rumsliga funktioner, poserade i SQL, är för att fråga om rumsliga egenskaper och relationer.

SQL, eller ”Structured Query Language”, är ett sätt att ställa frågor om och uppdatera data i relationsdatabaser. En select-fråga (som du använder för att ställa frågorna) är i allmänhet ett kommando av följande formulär

SELECT some_columns FROM some_data_source WHERE some_condition;

PostGIS-specifika funktioner är vanligtvis i form av ST_functionName.

du skriver dessa kommandon på kommandoraden efter att du loggat in i din databas eller i ditt databas GUI-verktyg (t.ex. pgAdmin eller QGIS DB Manager). Så ja, SQL kräver att du verkligen skriver något. Högerklicka kan underskattas i allmänhet, men för någon som inte skriver någon kod är SQL ett bra första steg för att skriva egna kommandon och kanske senare kod.

det finns också andra rumsliga databaser förutom PostGIS. SQL Server Spatial, ESRI ArcSDE, Oracle Spatial och GeoMesa är några andra alternativ för hantering och analys av rumsliga data. Men PostGIS sägs ha fler funktioner och generellt bättre prestanda. Även de andra som nämns (utom GeoMesa) är inte öppen källkod.

om du är ny på detta, nu kan du vara förvirrad: så det är en plats att lagra data och du måste få ut information på ett komplicerat sätt genom att skriva några konstiga saker på kommandoraden? Vänta på det. Det finns också några verkliga fördelar som PostGIS kan erbjuda dig om du verkligen förbinder dig till det.

jag frågade några ideer för blogginlägget från Twitter och fick mycket bra feedback. Därifrån fick jag tanken på att dela upp detta i två delar. I den första delen kommer jag att titta på de fördelar som PostGIS kan ge ditt dagliga arbete. I den andra delen kommer jag att fokusera mer på rumslig SQL.

PostGIS kan göra det möjligt för dig att anta ett nytt sätt att arbeta. Det här nya sättet kan lättare reproduceras, du kan börja använda versionskontroll lättare och det kan aktivera arbetsflöden för flera användare.

filer kräver ofta speciell programvara för att läsa och skriva. SQL är en abstraktion för slumpmässig dataåtkomst och analys. Utan den abstraktionen behöver du antingen en specifik programvara för att göra operationerna eller behöver skriva all åtkomst-och analyskod själv.

gör din analys i SQL snarare än att bara göra slumpmässiga operationer för filer med några slumpmässiga verktyg med slumpmässiga parametrar, låter dig dela och reproducera dina resultat lättare. Du kanske har den där ”master Shapefile” för närvarande någonstans, där du har gjort flera rumsliga kopplingar och klippoperationer till en Shapefile för att få det att vara som det ska vara. Tänk om det försvinner?

Johnnie skrev ett bra exempel på Twitter om hur han av misstag raderade alla sina data, men kunde reproducera dem med minimal ansträngning med SQL-skript som han sparade till GIT.

personer som arbetar med mjukvaruutveckling är förmodligen (eller förhoppningsvis) bekanta med versionskontroll. Jag kommer inte att gå djupare in på det i det här blogginlägget, men du kan (och du borde) ha dina SQL-skript i ett versionskontrollsystem, som GIT. Tänk på det som en kokbok som du förvarar i din bokhylla och ständigt uppdaterar för att alltid hitta de bästa recepten för välsmakande dataanalys. Bara att du kan köpa en ny kopia av denna exakta kokbok från Amazon igen om ditt hus brinner ner.

en databas kan också hjälpa dig att hålla dina rumsliga data i bättre ordning. Ingen av oss är verkligen perfekt och förmodligen kommer du fortfarande att skapa tabeller som temp_1, final_final, men fortfarande erbjuder en databas bättre möjligheter för dig att standardisera din datastruktur än bara filer (t.ex. genom att standardisera datatyperna i dina tabeller).

och hur är det med de stora datamängderna? Med en rumslig databas som arbetar med stora datamängder blir möjligt. Inte bara lättare, men ibland är det nästan omöjligt att arbeta med större datamängder utan en databas. Har du någonsin försökt öppna 2 gb csv-fil? Eller försökte göra lite geoprocessing för en 800 mb GeoJSON? Visste du ens att Shapefiles har en storleksgräns? Naturligtvis kan du ta itu med några av dessa problem genom att använda Geopackage eller några andra filformat, men i allmänhet är PostGIS det optimala verktyget för hantering av stora (geospatiala) data.

en mycket trevlig funktion med databaser är att du lättare kan automatisera processer som du normalt gör manuellt. Till exempel genom att använda PostgreSQL NOTIFY-funktionen kan du uppdatera dina QGIS-kartor automatiskt. FME) för att automatisera ditt arbete är det mycket lättare att läsa/skriva från/till PostGIS-tabeller än med filer.

om du inte är som jag (jag gör för närvarande det här på egen hand och för skojs skull), kanske du har en sak som kallas ett lag. Även känd som medarbetare. De kan ha behov av att komma åt samma data som du. Att använda en databas i ditt arbetsflöde möjliggör parallellt arbete helt på en annan nivå än att bara ha filer på en delad enhet.

en huvudorsak till detta är att samtidiga användare kan orsaka korruption. Även om det är möjligt att skriva extra kod för att säkerställa att flera skrivningar till samma fil inte korrumperar data, när du har löst problemet och också löst det tillhörande prestandaproblemet, skulle du ha skrivit den bättre delen av ett databassystem.

naturligtvis finns det både för-och nackdelar med att anta ett nytt arbetsflöde. Precis som att hålla dina filer i ordning, i slutet av dagen, kan det också vara mycket arbete att upprätthålla en databas. Till exempel att uppdatera dina PostGIS till en ny version kan vara en verklig smärta, som det påpekades på Twitter. Med stor makt kommer stort ansvar.

men låt oss prata mer om den kraftdelen.

del 2: den magiska världen av rumslig SQL

rumslig SQL kan verkligen påskynda din bearbetning (när den används klokt). Nedan följer en jämförelse mellan att göra samma process med en Shapefile och QGIS-bearbetning och sedan i PostGIS med ST_GeneratePoints.

för denna jämförelse hade jag postnummeruppgifter från Finland och befolkningen i varje postnummerområde. Jag hade detta både som en Shapefile och en tabell i min lokala databas. Jag skapade slumpmässiga punkter inuti varje polygon för att representera befolkningen. Jag använde QGIS-bearbetningen (slumpmässiga punkter inuti polygon från Vektorbehandling) för shapefilen och i PostGIS var SQL verkligen så enkelt som det här:

SELECT ST_GeneratePoints(geom, he_vakiy) from paavo.paavo

som du kan se från diagrammet tidigare tog det PostGIS mindre än 10% av tiden att göra samma analys jämfört med QGIS och en Shapefile. Om du är en GIS-analytiker och gör processer som detta varje dag, kan det spara dig ganska mycket tid på ett år.

förutom snabbare bearbetning kan du njuta av det stora urvalet av rumsliga funktioner PostGIS har att erbjuda. Vilka funktioner som är mest användbara för dig beror helt på användningsfallet. Förutom Voronoi-analysen och mer traditionell GIS-analys (buffert, överlägg, skär, klipp etc..) du kan göra mer avancerade saker:

• Routing. Med pgrouting och vägdata kan du hitta optimala rutter och göra olika nätverksanalyser;
• Polygon skeletonization. Denna funktion gör att du kan bygga medialaxeln för en polygon i farten;
• geometri underavdelning. Att dela dina geometrier för vidare bearbetning kan påskynda dina processer avsevärt;
• Clustering. Hitta kluster och mönster från dina data. Med AI hype på topp, k-medel kan vara ännu mer intressant för vissa än tidigare…

vad behöver du saker som polygonskeletonisering för? Kan vara en giltig fråga för de flesta, men den gången när din rumsliga analys behöver det, kommer du att vara mycket glad över att någon har gjort det hårda arbetet (=matte) för dig. Genom att kombinera olika rumsliga funktioner tillsammans och använda Postgres inbyggda funktioner med dem kan du göra avancerad rumslig analys i din databas.

komplicerade och intressanta frågor (rumsliga kopplingar, aggregeringar etc.) som kan uttryckas i en rad SQL i databasen kräver mycket beräkningskraft och det är något som PostGIS erbjuder dig. Att svara på samma frågor med din egen kod kan ta hundratals rader med specialkod att svara när du programmerar mot filer.

PostGIS för dataviz

i många av de visualiseringar jag har i min portfölj har PostGIS spelat någon form av roll i visualiseringsprocessen. I mitt arbetsflöde förbehandlar jag oftast data och gör sedan den faktiska visualiseringen i QGIS.

Låt oss se ett exempel på en av dessa processer.

animering om tåg och voronois ovan ger ett lekfullt exempel på PostGIS kraft. Jag hade några miljoner tåg GPS-punkter i min lokala databas och jag hade redan skapat animationer med punkterna bara flytta. Men jag ville testa hur en animering med Voronoi-linjer skulle se ut.

först eftersom jag hade flera GPS-poäng för varje tåg per minut, ville jag gruppera dem så att jag skulle ha en representativ punkt för varje minut per tåg. Jag hade först skapat en tabell manuellt för de resulterande punkterna. Jag skrev följande fråga

INSERT INTO trains.voronoipoints 
SELECT '2018–01–15 09:00:00' AS t, 
geom 
FROM (SELECT St_centroid(St_collect(geom)) AS geom, 
trainno 
FROM (SELECT geom, 
trainno 
FROM trains.week 
WHERE time > '2018–01–15 09:00:00' 
AND time < '2018–01–15 09:01:00') AS a 
GROUP BY trainno) AS b

om vi bromsar ner frågan i bitar kan vi se följande pusselbitar:

• du kan se några av de normala elementen i en SQL-fråga (INSERT INTO, SELECT, as, FROM, WHERE, and, GROUP BY)
• geom, trainno och time är kolumnnamn i min veckatabell i schemat trains
• subquery a returnerar alla GPS-punkter som har spårats inom den begärda tidsramen.
• eftersom jag väljer alla GPS-punkter som spåras inom en minut kan jag få flera poäng för varje tåg. Jag ville bara ha en, så att voronoi-linjerna skulle se mer förnuftiga ut. Därför använder jag ST_Collect för att gruppera punkterna tillsammans och skapa en multipunktsgeometri från dem. ST_Centroid ersätter multipunktsgeometrin med en enda punkt belägen vid centroid (subquery b) och data grupperas efter tågnummer.

för att göra samma sak flera gånger hade jag ett enkelt Python-skript för att slinga över samma Fråga för några hundra gånger där jag hade start-och sluttider som parametrar. Efter att ha lyckats hitta en representativ punkt för varje minut körde jag bara följande kommando (på 11,5 sekunder):

SELECT t, ST_VoronoiLines(geom) from trains.voronoipoints

sedan lade jag till resultatet i QGIS och visualiserade det med Time Manager. Det här kan vara lite hackigt sätt att uppnå resultatet och en mer erfaren SQL-användare kan ha gjort det helt med ett enda SQL-kommando, men jag är fortfarande ganska nöjd med resultatet. Även om det kan vara meningslöst.

så småningom ganska enkelt, men resultatet ser ut som högre nivå matematik (och det är!), eftersom allt hårt arbete görs av PostGIS. Också för att jag kunde göra Voronoi-analysen för endast en poäng per tåg var behandlingstiden bara sekunder för hundratusentals poäng.

ofta växer behandlingstiden för dina frågor exponentiellt när datamängderna växer. Det är därför du måste vara smart med dina frågor.

som tumregel blir ju mer data en fråga måste hämta och fler operationer databasen måste göra (beställning, gruppering etc), Det blir långsammare och därmed mindre effektivt. En effektiv SQL-fråga hämtar bara de rader och kolumner som den verkligen behöver. SQL kan fungera som ett logiskt pussel, där du verkligen måste tänka noggrant vad du vill uppnå.

jag måste också notera att tweaking prestanda för dina frågor är en hal sluttning och du kan gå vilse i världen av oändlig optimering. Att hitta balansen mellan en” optimal fråga ” och en optimal fråga är verkligen viktigt. Speciellt om du inte bygger en applikation för en miljon användare, kommer några millisekunder här eller där förmodligen inte att rocka din båt.

hur kommer man igång?

jag vågar säga att lärande SQL är ännu mer fördelaktigt för en genomsnittlig GIS-användare än att lära sig JavaScript, Python eller R. SQL-syntax har bara haft mindre förändringar genom åren och SQL-färdigheter är mycket väl överförbara.

jag har funnit att inlärningskurvan i SQL inte är riktigt brant för att göra grunderna, men det kan ta lite tid att verkligen se fördelarna som det kan ge till din rumsliga analys. Men jag uppmuntrar att ha tålamod och prova mer komplicerade analyser och sträva efter snabbare bearbetning. Så småningom kommer du att se skillnaden.

först när du lär dig SQL-grunderna lär du dig att fråga data från en enda tabell med hjälp av grundläggande datavalstekniker som att välja kolumner, Sortera resultatuppsättning och filtrera rader. Sedan kommer du att lära dig om de avancerade frågorna som att gå med i flera tabeller, använda inställda operationer och bygga en underfråga. Slutligen lär du dig att hantera databastabeller som att skapa en ny tabell eller ändra en befintlig tabells struktur.

men det finns också verktyg för att hjälpa dig!

QGIS har ett bra verktyg som heter DB Manager. Det erbjuder en liknande GUI för din databas, men på ett mycket mer komprimerat sätt och inuti QGIS. Du kan ändra och lägga till tabeller, lägga till index och göra mycket av de grundläggande operationerna på ett högerklickbart sätt.

du bör också kolla pgAdmin, som är den mest populära administrations-och utvecklingsplattformen för PostgreSQL. Det finns flera sätt att få dina data till PostGIS (t.ex. ogr2ogr, shp2pgsql). I allmänhet uppmuntrar jag att prova olika verktyg och metoder för att arbeta med data.

jag har gjort några små experiment i att kombinera Python och PostGIS. Att arbeta med Python (eller R) och PostGIS tillsammans kan verkligen ta din databehandling och automatisering till nästa nivå. Att bara kombinera grundläggande skriptfunktioner i Python och ansluta till PostGIS med psycopg2 är bra sätt att komma igång.

känner du att du vill komma igång med PostGIS?

1. ladda bara ner installatörerna och installera PostGIS på din lokala maskin. Följ instruktionerna i handledningarna;
2. ladda lite data där inne. Börja med en enda Shapefile med QGIS DB Manager eller chech, till exempel denna handledning om hur man får Naturliga Jorddata till PostGIS;
3. börja spela med SQL. Börja med grunderna (välja, filtrera och ändra data) och långsamt ser du vilken typ av fördelar det kan ge ditt arbetsflöde.

slutsatser

om ditt sätt att arbeta för närvarande är ineffektivt, kommer bara att ändra dina verktyg inte att göra ditt resultat bättre eller processen mindre smärtsam. Du måste ändra hur du tänker på datahantering. Det finns många sätt att använda databaser ineffektivt. Lita på mig, Jag har sett dem och till och med försökt några.

att ändra saker bara för förändringens skull är inte meningsfullt. Om ditt dagliga arbete bara plottar några punkter på en karta då och då kan du mycket göra det med Shapefiles och csv-filer också i framtiden. Kan till och med vara effektivare på det sättet.

men.

om du vill göra några seriösa rumsliga analyser, automatisera dina processer eller på något sätt flytta ditt sätt att arbeta med spatal data till nästa nivå, kan jag starkt rekommendera att bli bekant med PostGIS och särskilt spatial SQL. Att lära sig SQL kan också vara kul. Allvar.

sist men definitivt inte minst. Som Tom påpekade: att använda PostGIS ger dig geohipster cred!

en sak som jag bara nämnde kortfattat var att PostGIS är öppen källkod och fritt tillgänglig. Det betyder att personer som arbetar med liten eller ingen budget (som jag) inte har något hinder för inträde. Kommersiella rumsliga databaser kan vara enormt dyra. Stort tack till alla aktiva utvecklare som arbetar med projektet!

Tack för att du läste! Kolla min hemsida för mer information om mig eller kasta mig en kommentar på Twitter.

vill du lära dig mer? Källor för detta blogginlägg och ytterligare PostGIS läsning

RTFD. PostGIS-dokumentationen är riktigt bra.

PostGIS guru Paul Ramsey har flera presentationer om ämnet från olika synvinklar på sin webbplats

stora material från gränslös på inledningen till PostGIS.

Anita Graser har skrivit en fantastisk serie blogginlägg om hantering av rörelsedata i PostGIS.

kolla in PostGIS-böckerna från Regina Obe

jag använde denna Boston GIS-handledning när jag först installerade PostGIS lokalt

Extra för personer som gör dataviz: ett intressant experiment om att lagra färger som 3D-punkter i PostGIS