Databases? Niet erg interessant.
zo zou een gemiddelde persoon die werkt met GIS of datavisualisaties denken. Ik moet toegeven dat databases niet het meest sexy ding in de wereld zijn (sorry DBA ‘ s), maar als je beweert (of streeft) om analytics of visualisatie met (ruimtelijke) data op een meer serieuze manier te doen, moet je ze zeker niet negeren. Ik hoop dat deze blogpost u een idee kan geven wat voor voordelen een efficiënt gebruik van ruimtelijke databases u zou kunnen bieden.
Hype termen komen en gaan in het en er was een grote hype rond big data nog slechts een paar jaar geleden, maar dat is nu langzaam aan het verdwijnen. Nou, data is nog steeds groot en eigenlijk is het groter dan ooit. Bestandsgroottes groeien en in” data science ” en Geowetenschappen moeten mensen omgaan met gegevens die gemakkelijk in het bereik van gigabytes kunnen zijn. Hoe groter de gegevens, hoe meer aandacht we moeten besteden aan de manier waarop we deze opslaan en analyseren.
daar komt een database in beeld.
in softwareontwikkeling is het werken met databases een must. Maar voor mensen in andere subdomeinen van de informatica (zoals GIS) zijn de voordelen van een database misschien niet altijd zo duidelijk. Natuurlijk hebben mensen de neiging om de tools die ze het meest vertrouwd zijn te gebruiken, hoewel het niet de meest efficiënte manier zou zijn om doelen te bereiken. Maar soms kan een stap uit je comfortzone je grote voordelen opleveren. Ik ben mezelf langzaam bewust van het potentieel dat ligt in ruimtelijke SQL.
deze blogpost is voornamelijk bedoeld voor mensen die werken met geospatiale data, maar heeft PostGIS nog niet aangeraakt, of misschien zelfs nog nooit van gehoord. Ik ga niet door hoe PostgreSQL/PostGIS te installeren, maar probeer je eerder een overzicht te geven van wat het is en waar het goed voor is.
mijn workflow en voorbeelden zijn voornamelijk gericht op QGIS + PostGIS combinatie, maar u moet er rekening mee houden dat u ook alleen met PostGIS, uw eigen code of met enkele andere GIS clients kunt werken.
Post … wat?
tijdens mijn GIS-studies had ik al meerdere malen de zin “PostGIS is a spatial extension of Postgres” gehoord. Het betekende niet dat ik enig idee had wat dat betekent. Ik had geen idee wat Postgres is, laat staan een ruimtelijke uitbreiding.
laten we proberen het zo eenvoudig mogelijk af te remmen.
sommige mensen haten me voor deze vergelijking, maar ik neem het risico: als je nog nooit met databases hebt gewerkt, kun je databasetabellen zien als massieve Excel sheets. Maar een enorme intelligente Excel-blad van waaruit u in een milliseconde kunt achterhalen welke waarde in de derde kolom op rijnummer 433 285 staat. En in plaats van functies binnen het blad naar een enkele cel te schrijven, schrijf je ze naar je SQL command window. Dus een plek om gegevens op te slaan en van waaruit je het efficiënt kunt krijgen.
PostGIS is een open source, vrij beschikbare ruimtelijke database extender voor het PostgreSQL Database Management System (a.k.a DBMS). Dus PostgreSQL (ook bekend als Postgres) is de database en PostGIS is als een add-on aan die database. De nieuwste versie van PostGIS komt nu verpakt met PostgreSQL.
In een notendop PostGIS voegt ruimtelijke functies zoals afstand, oppervlakte, vereniging, kruising en speciale geometrie gegevenstypen toe aan PostgreSQL.Ruimtelijke databases slaan ruimtelijke objecten op en manipuleren ze net als elk ander object in de database.
in een normale database slaat u gegevens op van verschillende typen (numeriek, tekst, tijdstempels, afbeeldingen…) en indien nodig kunt u dat opvragen (ophalen) om vragen te beantwoorden met uw gegevens. De vragen kunnen gaan over ‘hoeveel mensen zijn ingelogd op uw website’ of ‘hoeveel transacties zijn gemaakt in een online winkel’. Ruimtelijke functies kunnen in plaats daarvan vragen beantwoorden als ‘hoe dichtbij is de dichtstbijzijnde winkel’,’ is dit punt binnen dit gebied ‘of’wat is de grootte van dit land’.
de gegevens worden dus opgeslagen in rijen en kolommen. Omdat PostGIS een ruimtelijke database is, hebben de gegevens ook een geometriekolom met gegevens in een specifiek coördinatenstelsel gedefinieerd door spatial reference identifier (srid). Maar vergeet niet dat hoewel je PostGIS voornamelijk gebruikt voor ruimtelijke gegevens, het ook mogelijk is om niet-ruimtelijke gegevens daarin op te slaan, omdat het nog steeds alle functionaliteiten van een normale PostgreSQL database heeft!
de uitstekende grenzeloze PostGIS intro intro, introduceert drie kernbegrippen die ruimtelijke gegevens associëren met een database. Gecombineerd bieden deze een flexibele structuur voor geoptimaliseerde prestaties en analyse.
- ruimtelijke gegevenstypen zoals punt, lijn en veelhoek. Vertrouwd bij de meeste werken met ruimtelijke gegevens;
- Multi-dimensionale ruimtelijke indexering wordt gebruikt voor efficiënte verwerking van ruimtelijke bewerkingen;
- ruimtelijke functies, gesteld in SQL, zijn voor het bevragen van ruimtelijke eigenschappen en relaties.
SQL, of “Structured Query Language”, is een middel om vragen te stellen over en gegevens bij te werken in relationele databases. Een SELECT query (die u gebruikt om de vragen te stellen) is over het algemeen een commando met de volgende vorm
SELECT some_columns FROM some_data_source WHERE some_condition;
PostGIS specifieke functies zijn meestal in de vorm van ST_functionName.
u schrijft deze commando ‘ s op de commandoregel na het inloggen in uw database of in uw database GUI tool (bijvoorbeeld pgAdmin of QGIS DB Manager). Dus ja, SQL vereist dat je echt iets schrijft. Het rechtsklikken kan in het algemeen onderschat worden, maar voor iemand die geen code schrijft, is SQL een goede eerste stap om je eigen commando ‘ s en misschien later code te schrijven.
naast PostGIS zijn er ook andere ruimtelijke databases. SQL Server Spatial, ESRI ArcSDE, Oracle Spatial en GeoMesa zijn een paar andere opties voor het beheren en analyseren van ruimtelijke gegevens. Maar PostGIS zou meer functionaliteiten hebben en over het algemeen betere prestaties. Ook de andere genoemde (behalve GeoMesa) zijn niet open source.
als dit nieuw voor u is, kunt u nu in de war raken: dus het is een plek om data op te slaan en je moet informatie op een complexe manier naar buiten brengen door rare dingen op de commandoregel te schrijven? Wacht erop. Er zijn ook enkele echte voordelen die PostGIS je kan bieden als je je er echt toe verbindt.
ik vroeg een aantal ideeën voor de blog post van Twitter en kreeg veel goede feedback. Van daaruit kreeg ik het idee om dit in twee delen te splitsen. In het eerste deel zal ik kijken naar de voordelen die PostGIS kan brengen aan uw dagelijkse werk. In het tweede deel zal ik me meer richten op ruimtelijke SQL.
PostGIS kan u in staat stellen een nieuwe manier van werken aan te nemen. Deze nieuwe manier kan gemakkelijker reproduceerbaar zijn, u kunt versiebeheer gemakkelijker gebruiken en het kan multi-user workflows inschakelen.
bestanden hebben vaak speciale software nodig om te lezen en te schrijven. SQL is een abstractie voor willekeurige toegang tot gegevens en analyse. Zonder die abstractie, zul je ofwel een specifieke software nodig hebben om de bewerkingen uit te voeren of moet je alle toegang en analyse code zelf schrijven.
door uw analyse in SQL te doen in plaats van alleen willekeurige bewerkingen uit te voeren voor bestanden met een aantal willekeurige tools met willekeurige parameters, kunt u uw resultaten Gemakkelijker delen en reproduceren. Misschien heb je die ene” master Shapefile ” ergens, waar je verschillende ruimtelijke joins en clipbewerkingen hebt gemaakt naar een Shapefile om dat te laten zijn zoals het hoort te zijn. Wat als dat verdwijnt?
Johnnie schreef een goed voorbeeld op Twitter over hoe hij per ongeluk al zijn gegevens verwijderde, maar ze met minimale inspanning kon reproduceren met de SQL scripts die hij in GIT had opgeslagen.
mensen die werken met softwareontwikkeling zijn waarschijnlijk (of hopelijk) bekend met versiebeheer. Ik ga daar niet dieper op ingaan in deze blogpost, maar je bent in staat om (en dat zou je moeten doen) je SQL scripts in een versiebeheersysteem te hebben, zoals GIT. Zie het als een kookboek dat je in je boekenplank houdt en constant bijwerkt om altijd de beste recepten te vinden voor smakelijke data-analyse. Alleen dat je weer een nieuw exemplaar van dit exacte kookboek van Amazon kunt kopen als je huis afbrandt.
een database kan u ook helpen uw ruimtelijke gegevens beter in orde te houden. Niemand van ons is echt perfect en waarschijnlijk zult u nog steeds tabellen maken zoals temp_1, final_final, maar nog steeds biedt een database betere mogelijkheid voor u om uw gegevensstructuur te standaardiseren dan alleen bestanden (bijvoorbeeld door de gegevenstypen in uw tabellen te standaardiseren).
en hoe zit het met die grote datasets? Met een ruimtelijke database wordt werken met grote datasets mogelijk. Niet alleen makkelijker, maar soms is het bijna onmogelijk om te werken aan grotere datasets zonder een database. Heeft u ooit geprobeerd om 2 GB csv-bestand te openen? Of heb je geprobeerd een GeoJSON van 800 mb te bewerken? Wist je dat Shapefiles een limiet hebben? Natuurlijk kunt u een aantal van deze problemen aanpakken door gebruik te maken van Geopackage of andere bestandsformaten, maar over het algemeen is PostGIS de optimale tool voor het verwerken van grote (geospatiale) data.
een zeer mooie eigenschap van databases is dat u gemakkelijker processen kunt automatiseren die u normaal handmatig doet. Door bijvoorbeeld de functie PostgreSQL NOTIFY te gebruiken, kunt u uw QGIS-kaarten automatisch bijwerken. Ook als u werkt met ETL tools (bijvoorbeeld FME) om uw werk te automatiseren, lezen/schrijven van/naar PostGIS tabellen is veel gemakkelijker dan met bestanden.
als je niet zoals Ik bent (Ik doe dit op dit moment alleen en voor de lol), heb je misschien iets dat een team heet. Ook bekend als collega ‘ s. Zij hebben misschien de behoefte om toegang te krijgen tot dezelfde gegevens als u. Het gebruik van een database in uw workflow maakt parallel werken volledig op een ander niveau dan alleen het hebben van bestanden op een gedeelde schijf.
een belangrijke reden hiervoor is dat gelijktijdige gebruikers corruptie kunnen veroorzaken. Hoewel het mogelijk is om extra code te schrijven om ervoor te zorgen dat meerdere schrijft naar hetzelfde bestand de gegevens niet beschadigen, tegen de tijd dat u het probleem hebt opgelost en ook het bijbehorende prestatieprobleem hebt opgelost, zou u het betere deel van een databasesysteem hebben geschreven.
natuurlijk zijn er zowel voors als tegens bij het aannemen van een nieuwe workflow. Net als het in orde houden van uw bestanden, aan het einde van de dag, ook het onderhouden van een database kan een hoop werk zijn. Bijvoorbeeld het updaten van uw PostGIS naar een nieuwe versie kan een echte pijn, zoals werd opgemerkt op Twitter. Met grote macht komt grote verantwoordelijkheid.
maar laten we meer praten over dat vermogen deel.
deel 2: De magische wereld van ruimtelijke SQL
ruimtelijke SQL kan uw verwerking echt versnellen (indien verstandig gebruikt). Hieronder is een vergelijking tussen hetzelfde proces doen met een Shapefile en QGIS verwerking en vervolgens in PostGIS met ST_GeneratePoints.
voor deze vergelijking had ik postcodegegevens van Finland en de bevolking in elk postcodegebied. Ik had dit zowel als een Shapefile als een tabel in mijn lokale database. Ik creëerde willekeurige punten in elke veelhoek om de populatie weer te geven. Ik gebruikte de QGIS processing (willekeurige punten binnen veelhoek van Vector Processing) voor het Shapefile en in PostGIS was de SQL echt zo eenvoudig als dit:
SELECT ST_GeneratePoints(geom, he_vakiy) from paavo.paavo
zoals u kunt zien uit de grafiek eerder, het duurde PostGIS minder dan 10 % van de tijd om dezelfde analyse te doen in vergelijking met QGIS en een Shapefile. Als u een GIS analist en doen processen zoals deze elke dag, dat kan bespaart u heel veel tijd in een jaar.
naast een snellere verwerking kunt u genieten van de uitgebreide selectie ruimtelijke functies die PostGIS te bieden heeft. Welke functies het nuttigst voor u zijn, hangt volledig af van de use case. Naast de Voronoi-analyse en meer traditionele GIS-analyse (buffer, overlay, intersect, clip enz..) u kunt meer geavanceerde dingen doen:
- Routing. Met pgRouting en weggegevens kunt u optimale routes vinden en verschillende netwerkanalyses uitvoeren;
- veelhoekskelet. Deze functie stelt u in staat om de mediale as van een veelhoek on the fly te bouwen;
- geometrie onderverdeling. Het verdelen van uw geometrieën voor verdere verwerking kan uw processen aanzienlijk versnellen;
- Clustering. Vind clusters en patronen uit uw gegevens. Met de AI hype op zijn hoogtepunt, zou de K-means voor sommigen nog interessanter kunnen zijn dan voorheen…
waar heb je dingen als polygoon skelet voor nodig? Misschien een goede vraag voor de meeste, maar dat een keer wanneer uw ruimtelijke analyse nodig heeft, zult u zeer blij zijn dat iemand het harde werk (=wiskunde) voor u heeft gedaan. Het combineren van verschillende ruimtelijke functies samen en het gebruik van de Postgres ingebouwde functies met hen zal u toelaten om geavanceerde ruimtelijke analyse te doen in uw database.
ingewikkelde en interessante vragen (ruimtelijke joins, aggregaties, enz.) die in één regel van SQL in de database kunnen worden uitgedrukt, vereisen veel rekenkracht en dat is iets dat PostGIS je biedt. Het beantwoorden van dezelfde vragen met je eigen code kan honderden regels gespecialiseerde code vereisen om te beantwoorden bij het programmeren tegen bestanden.
PostGIS voor DataViz
in veel van de visualisaties die ik in mijn portfolio heb, heeft PostGIS een soort van rol gespeeld in het visualisatieproces. In mijn workflow verwerk ik meestal de gegevens vooraf en doe vervolgens de werkelijke visualisatie in QGIS.
laat een voorbeeld van een van deze processen zien.
animatie over treinen en voronois hierboven geeft een speels voorbeeld van de macht van PostGIS. Ik had een paar miljoen trein GPS punten in mijn lokale database en ik had al animaties gemaakt met de punten gewoon bewegen. Maar ik wilde testen hoe een animatie met Voronoi lijnen eruit zou zien.
ten eerste omdat ik verschillende GPS-punten had voor elke trein per minuut, wilde ik ze groeperen zodat ik één representatief punt had voor elke minuut per trein. Ik had eerst handmatig een tabel gemaakt voor de resulterende punten. Ik schreef de volgende query
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
als we de query in stukjes afbreken kunnen we de volgende stukjes van de puzzel zien:
- u kunt enkele van de normale elementen van een SQL-query zien (invoegen in, selecteren, als, van, waar, en groeperen door)
- geom, trainno en time zijn kolomnamen in mijn weektabel in het schema genaamd treinen
- de subquery a geeft alle GPS-punten terug die binnen het gevraagde tijdsbestek zijn gevolgd.
- omdat ik alle GPS-punten binnen één minuut heb geselecteerd, kan ik voor elke trein meerdere punten krijgen. Ik wilde er maar één, zodat de Voronoi-lijnen er verstandiger uit zouden zien. Daarom gebruik ik ST_Collect om de punten samen te groeperen en er een multipoint geometrie van te maken. ST_Centroid vervangt de multipoint geometrie door een enkel punt gelegen op het centroid (subquery b) en de gegevens worden gegroepeerd op treinnummers.
om hetzelfde meerdere keren te doen, had ik een eenvoudig Python-script om dezelfde query een paar honderd keer door te lusenwaar ik de begin-en eindtijden als parameters had. Na het succesvol vinden van een representatief punt voor elke minuut, Ik liep gewoon de volgende opdracht (in 11,5 seconden):
SELECT t, ST_VoronoiLines(geom) from trains.voronoipoints
toen voegde ik het resultaat toe aan QGIS en visualiseerde het met Time Manager. Dit is misschien een beetje hacky manier om het resultaat te bereiken en een meer ervaren SQL-gebruiker zou het volledig hebben gedaan met een enkele SQL-opdracht, maar ik ben nog steeds vrij blij met het resultaat. Hoewel het misschien zinloos is.
uiteindelijk vrij eenvoudig, maar het resultaat lijkt op hogere wiskunde (en dat is het!), omdat al het harde werk wordt gedaan door PostGIS. Ook omdat ik de Voronoi-analyse voor slechts één punt per trein kon maken, was de verwerkingstijd slechts seconden voor honderdduizenden punten.
vaak neemt de verwerkingstijd van uw query ‘ s exponentieel toe naarmate de data groter wordt. Daarom moet je slim zijn met je vragen.
als vuistregel geldt dat hoe meer gegevens een query moet ophalen en hoe meer bewerkingen de database moet uitvoeren (ordenen, groeperen, enz.), het langzamer en dus minder efficiënt wordt. Een efficiënte SQL query haalt alleen de rijen en kolommen die het echt nodig heeft. SQL kan werken als een logische puzzel, waarbij je echt grondig moet nadenken over wat je wilt bereiken.
ik moet ook opmerken dat het aanpassen van de prestaties van uw vragen een hellend vlak is en u kunt verdwalen in de wereld van eindeloze optimalisatie. Het vinden van de balans tussen een “optimale query” en een optimale query is echt belangrijk. Vooral als je niet het bouwen van een applicatie voor een miljoen gebruikers, een paar milliseconden hier of daar zal waarschijnlijk niet uw boot rock.
hoe aan de slag?
ik durf te zeggen dat het leren van SQL nog voordeliger is voor een gemiddelde GIS gebruiker dan het leren van JavaScript, Python of R. SQL syntaxis heeft slechts kleine veranderingen in de loop der jaren en SQL vaardigheden zijn zeer goed overdraagbaar.
ik heb ontdekt dat de leercurve in SQL niet echt steil is om de basis te doen, maar het kan enige tijd duren om echt de voordelen te zien die het kan brengen voor uw ruimtelijke analyse. Maar ik moedig aan om geduldig te zijn en ingewikkelder analyses te proberen en te streven naar snellere verwerking. Uiteindelijk zul je het verschil zien.
Als u eerst SQL-basisbegrippen leert, leert u hoe u gegevens uit een enkele tabel kunt opvragen met behulp van basisgegevensselectietechnieken zoals het selecteren van kolommen, het sorteren van resultaatset en het filteren van rijen. Dan, leert u over de geavanceerde query ‘ s, zoals het samenvoegen van meerdere tabellen, het gebruik van set operaties, en de bouw van een subquery. Tot slot leert u hoe u databasetabellen kunt beheren, zoals het maken van een nieuwe tabel of het wijzigen van de structuur van een bestaande tabel.
maar er zijn ook hulpmiddelen om u te helpen!
QGIS heeft een geweldig hulpmiddel genaamd DB Manager. Het biedt een vergelijkbare GUI voor uw database, maar op een veel meer gecomprimeerde manier en binnen QGIS. U kunt wijzigen en tabellen toevoegen, indexen toevoegen en doen veel van de basisbewerkingen in een rechts-klikbare manier.
u moet ook pgAdmin controleren, het meest populaire beheer-en ontwikkelplatform voor PostgreSQL. Er zijn meerdere manieren om uw gegevens in PostGIS te krijgen (bijvoorbeeld ogr2ogr, shp2pgsql). In het algemeen moedig ik aan om verschillende tools en methoden uit te proberen om met de gegevens te werken.
Ik heb een paar kleine experimenten gedaan in het combineren van Python en PostGIS. Werken met Python (of R) en PostGIS samen kan echt uw gegevensverwerking en automatisering naar het volgende niveau. Gewoon het combineren van fundamentele scripting mogelijkheden van Python en verbinding maken met PostGIS met behulp van psycopg2 zijn goede manieren om te beginnen.
wilt u aan de slag met PostGIS?
- download de installatieprogramma ‘ s en installeer PostGIS op uw lokale machine. Volg de instructies in de tutorials;
- Laad daar enkele gegevens in. Begin met een enkel Shapefile met behulp van QGIS DB Manager of chech bijvoorbeeld deze tutorial over hoe natuurlijke aarde gegevens naar PostGIS te krijgen;
- begin te spelen met SQL. Begin met de basis (selecteren, filteren en wijzigen van de gegevens) en langzaam zult u zien wat voor soort voordelen het zou kunnen brengen in uw workflow.
conclusies
als uw manier van werken momenteel inefficiënt is, zal het veranderen van uw tools uw uitkomst niet verbeteren of het proces minder pijnlijk maken. U moet de manier waarop u denkt over databeheer veranderen. Er zijn tal van manieren om databases inefficiënt te gebruiken. Geloof me, Ik heb ze gezien en zelfs geprobeerd een paar.
ook dingen veranderen omwille van verandering, heeft geen zin. Als uw dagelijkse werk is gewoon het plotten van een paar punten op een kaart zo nu en dan, kunt u heel doen dat met Shapefiles en csv-bestanden ook in de toekomst. Misschien zelfs efficiënter op die manier.
maar.
als u een aantal serieuze ruimtelijke analyses wilt uitvoeren, uw processen wilt automatiseren of uw manier van werken met spatale gegevens naar een hoger niveau wilt tillen, kan ik ten zeerste aanbevelen om vertrouwd te raken met PostGIS en vooral met ruimtelijke SQL. SQL leren kan ook leuk zijn. Serieus.
Last but definitely not least. Zoals Tom opmerkte: het gebruik van PostGIS geeft je geohipster cred!
een ding dat ik slechts kort noemde was dat PostGIS open source en vrij beschikbaar is. Dit betekent dat mensen die met een klein of geen budget werken (zoals ik) geen toegangsdrempel hebben. Commerciële ruimtelijke databases kunnen enorm duur zijn. Grote dank gaat uit naar alle actieve ontwikkelaars die aan het project werken!
Bedankt voor het lezen! Kijk op mijn website voor meer informatie over mij of geef me een reactie op Twitter.
wilt u meer weten? Bronnen voor deze blogpost en verdere PostGIS lezen
RTFD. De PostGIS documentatie is echt goed.Postgis goeroe Paul Ramsey heeft verschillende presentaties over het onderwerp vanuit verschillende invalshoeken op zijn site
Great materials from Boundless on the introduction to PostGIS.Anita Graser heeft een geweldige serie blog posts geschreven over het omgaan met bewegingsgegevens in PostGIS.
bekijk de PostGIS-boeken van Regina Obe
ik gebruikte deze Boston GIS-handleiding toen ik PostGIS voor het eerst lokaal installeerde
Extra voor mensen die DataViz doen: een interessant experiment over het opslaan van kleuren als 3D-punten in PostGIS