de ce ar trebui să vă pese de PostGIS? – O introducere blândă în bazele de date spațiale

baze de date? Nu foarte interesant.

la fel ar putea gândi o persoană obișnuită care lucrează cu GIS sau vizualizări de date. Trebuie să recunosc că bazele de date nu sunt cel mai sexy lucru din lume (îmi pare rău DBA), dar dacă pretindeți (sau intenționați) să faceți analize sau vizualizare cu date (spațiale) într-o manieră mai serioasă, cu siguranță nu ar trebui să le ignorați. Sper că această postare pe blog vă poate oferi o idee ce fel de beneficii v-ar putea oferi utilizarea eficientă a bazelor de date spațiale.

Termeni Hype vin și pleacă în ea și a existat un hype mare în jurul big data încă în urmă cu doar câțiva ani, dar care acum se estompează încet. Ei bine, datele sunt încă mari și de fapt sunt mai mari ca niciodată. Dimensiunile fișierelor cresc și în” știința datelor ” și geoștiințele oamenii trebuie să se ocupe de date care pot fi cu ușurință în gama de Gigabyte. Cu cât datele sunt mai mari, trebuie să acordăm mai multă atenție modului în care le stocăm și le analizăm.

asta e în cazul în care o bază de date vine la imagine.

în dezvoltarea de software lucrul cu bazele de date este o necesitate. Dar pentru oamenii din alte subdomenii ale informaticii (cum ar fi GIS), beneficiile unei baze de date ar putea să nu fie întotdeauna atât de evidente. Desigur, oamenii tind să folosească instrumentele cele mai familiare pentru ei, deși nu ar fi cel mai eficient mod de a atinge obiectivele. Dar, uneori, ieșirea din zona dvs. de confort vă poate aduce cu adevărat beneficii mari. Am fost eu însumi realizând încet potențialul care se află în SQL spațial.

această postare pe blog este destinată în principal persoanelor care lucrează cu date geospațiale, dar nu au atins PostGIS sau poate nici măcar nu au auzit de ea. Nu voi trece prin modul de instalare a PostgreSQL/PostGIS, ci mai degrabă încerc să vă ofer o imagine de ansamblu asupra a ceea ce este și la ce este bun.

Fluxul meu de lucru și exemplele se concentrează în principal pe combinația QGIS + PostGIS, dar trebuie să rețineți că puteți lucra numai cu PostGIS, propriul cod sau cu alți clienți GIS.

Postează … ce?

deja în timpul studiilor mele GIS am auzit expresia ‘PostGIS este o extensie spațială a Postgres’ de mai multe ori. Nu înseamnă că am avut nici o idee despre ce înseamnă asta. Nu am avut nici o idee despre ceea ce este Postgres, să nu mai vorbim de o extensie spațială.

să încercăm să-l frâneze în jos cât mai simplu posibil.

unii oameni s-ar putea să mă urască pentru această comparație, dar îmi asum riscul: dacă nu ați lucrat niciodată cu baze de date, vă puteți gândi la tabelele de baze de date ca foi Excel masive. Dar o foaie masivă inteligentă Excel de unde puteți afla într-o milisecundă ce valoare este pe a treia coloană de pe numărul rândului 433 285. Și în loc să scrieți funcții în interiorul foii într-o singură celulă, le scrieți în fereastra de comandă SQL. Deci, un loc pentru a stoca date și de unde le puteți scoate eficient.

PostGIS este un open source, disponibil gratuit extender bază de date spațială pentru sistemul de management al bazelor de date PostgreSQL (alias SGBD). Deci PostgreSQL (alias Postgres) este baza de date și PostGIS este ca un add-on la acea bază de date. Cea mai recentă versiune de lansare a PostGIS vine acum ambalate cu PostgreSQL.

pe scurt PostGIS adaugă funcții spațiale, cum ar fi distanța, zona, Uniune, intersecție, și tipuri de date de geometrie de specialitate la PostgreSQL.Bazele de date spațiale stochează și manipulează obiecte spațiale ca orice alt obiect din Baza de date.

deci, într-o bază de date normală stocați date de diferite tipuri (numerice, text, marcajele de timp, imagini…) și atunci când este necesar, puteți interoga (fetch), care să răspundă la întrebări cu datele. Întrebările pot fi despre ‘câte persoane s-au conectat la site-ul dvs. web’ sau ‘câte tranzacții au fost făcute într-un magazin online’. Funcțiile spațiale pot răspunde în schimb la întrebări precum’ cât de aproape este cel mai apropiat magazin’,’ este acest punct în interiorul acestei zone ‘sau’care este dimensiunea acestei țări’.

deci, datele sunt stocate în rânduri și coloane. Deoarece PostGIS este o bază de date spațială, datele au, de asemenea, o coloană de geometrie cu date într-un sistem de coordonate specific definit de identificatorul de referință spațială (SRID). Dar amintiți-vă că, deși ați folosi PostGIS în principal pentru date spațiale, este de asemenea posibil să stocați date non-spațiale acolo, deoarece are încă toate funcționalitățile unei baze de date PostgreSQL normale!

excelentul Intro PostGIS fără limite, introduce trei concepte de bază care asociază datele spațiale cu o bază de date. Combinate acestea oferă o structură flexibilă pentru performanță optimizată și analiză.

1. tipuri de date spațiale, cum ar fi punctul, linia și poligonul. Familiar celor mai mulți care lucrează cu date spațiale;
2. indexarea spațială multidimensională este utilizată pentru procesarea eficientă a operațiilor spațiale;
3. funcțiile spațiale, prezentate în SQL, sunt pentru interogarea proprietăților și relațiilor spațiale.

SQL, sau „limbaj de interogare structurat”, este un mijloc de a pune întrebări și de a actualiza datele din bazele de date relaționale. O interogare de selectare (pe care o utilizați pentru a pune întrebările) este, în general, o comandă a formularului următor

SELECT some_columns FROM some_data_source WHERE some_condition;

funcțiile specifice PostGIS sunt de obicei sub forma ST_functionName.

scrieți aceste comenzi pe linia de comandă după ce vă conectați la baza de date sau în instrumentul GUI al bazei de date (de exemplu, Pgadmin sau QGIS DB Manager). Deci da, SQL vă cere să scrieți cu adevărat ceva. Clic-dreapta ar putea fi subestimat în general, dar pentru cineva care nu scrie nici un cod, SQL este un prim pas bun pentru a scrie propriile comenzi și poate Cod mai târziu.

există și alte baze de date spațiale în afară de PostGIS. SQL Server Spatial, ESRI ArcSDE, Oracle Spatial și GeoMesa sunt câteva alte opțiuni pentru gestionarea și analizarea datelor spațiale. Dar se spune că PostGIS are mai multe funcționalități și, în general, performanțe mai bune. De asemenea, celelalte menționate (cu excepția GeoMesa) nu sunt open source.

dacă sunteți nou la acest lucru, acum ar putea fi confuz: deci, este un loc pentru a stoca date și trebuie să obțineți informații într-un mod complex scriind câteva lucruri ciudate pe linia de comandă? Așteaptă. Există, de asemenea, câteva beneficii reale pe care PostGIS vi le poate oferi dacă vă angajați cu adevărat.

am cerut câteva idei pentru postarea de pe blog de pe Twitter și am primit o mulțime de feedback bun. De acolo am avut ideea de a împărți acest lucru în două părți. În prima parte voi analiza beneficiile pe care PostGIS le poate aduce muncii dvs. zilnice. În partea a doua mă voi concentra mai mult pe SQL spațial.

PostGIS vă poate permite să adoptați un nou mod de lucru. Acest nou mod poate fi mai ușor reproductibil, puteți începe să utilizați controlul versiunii mai ușor și poate activa fluxuri de lucru multi-utilizator.

fișierele necesită adesea software special pentru citire și scriere. SQL este o abstracție pentru accesul și analiza aleatorie a datelor. Fără această abstractizare, veți avea nevoie fie de un software specific pentru a face operațiunile, fie de a scrie singur tot codul de acces și analiză.

efectuarea analizei în SQL, mai degrabă decât efectuarea de operații aleatorii pentru fișiere cu unele instrumente aleatorii cu parametri aleatorii, vă permite să partajați și să reproduceți rezultatele mai ușor. S-ar putea să aveți acel „master Shapefile” în prezent undeva, unde ați făcut mai multe îmbinări spațiale și operații de clip la un fișier Shapefile pentru a face ca acesta să fie așa cum ar trebui să fie. Dacă dispare?

Johnnie a scris un exemplu bun pe Twitter despre cum și-a șters din greșeală toate datele, dar a reușit să le reproducă cu un efort minim cu scripturile SQL pe care le-a salvat în GIT.

persoanele care lucrează cu dezvoltarea de software sunt probabil (sau sperăm) familiarizați cu controlul versiunii. Nu am de gând să merg mai adânc în faptul că, în acest post pe blog, dar sunteți în stare să (și ar trebui să) au script-uri SQL într-un sistem de control al versiunii, cum ar fi GIT. Gândiți-vă la ea ca la o carte de bucate pe care o păstrați în raftul dvs. și actualizați constant pentru a găsi întotdeauna cele mai bune rețete pentru analiza gustoasă a datelor. Doar că puteți cumpăra din nou o nouă copie a acestei cărți de bucate exacte de la Amazon dacă casa dvs. arde.

o bază de date vă poate ajuta, de asemenea, să păstrați datele spațiale într-o ordine mai bună. Niciunul dintre noi nu este cu adevărat perfect și probabil că veți crea în continuare tabele precum temp_1, final_final, dar totuși o bază de date vă oferă o oportunitate mai bună de a vă standardiza structura de date decât fișierele (de exemplu, prin standardizarea tipurilor de date din tabelele dvs.).

și ce zici de aceste seturi de date mari? Cu o bază de date spațială, lucrul cu seturi de date mari devine posibil. Nu numai mai ușor, dar uneori este aproape imposibil să lucrați pe seturi de date mai mari fără o bază de date. Ați încercat vreodată să deschideți fișierul csv de 2 gb? Sau a încercat să facă unele geoprocesare pentru un 800 MB GeoJSON? Știați chiar că fișierele shape au o limită de dimensiune? Desigur, puteți aborda unele dintre aceste probleme utilizând Geopackage sau alte formate de fișiere, dar în general PostGIS este instrumentul optim pentru manipularea datelor mari (geospațiale).

o caracteristică foarte frumoasă cu bazele de date este că puteți automatiza mai ușor procesele pe care le faceți în mod normal manual. De exemplu, utilizând funcția de notificare PostgreSQL, puteți actualiza automat hărțile QGIS. De asemenea, dacă lucrați cu Instrumente ETL (de exemplu, FME) pentru a vă automatiza munca, citirea/scrierea din/în tabelele PostGIS este mult mai ușoară decât în cazul fișierelor.

dacă nu sunteți ca mine (în prezent fac aceste lucruri pe cont propriu și pentru Distracție), s-ar putea să aveți un lucru numit echipă. De asemenea, cunoscut sub numele de co-lucrătorilor. Este posibil să aibă nevoie să acceseze aceleași date ca și dvs. Utilizarea unei baze de date în fluxul de lucru permite paralel de lucru complet la un nivel diferit decât având doar fișiere pe o unitate partajată.

un motiv principal pentru aceasta este faptul că utilizatorii simultani pot provoca corupție. Deși este posibil să scrieți cod suplimentar pentru a vă asigura că mai multe scrieri în același fișier nu corupe datele, până când ați rezolvat problema și ați rezolvat și problema de performanță asociată, ați fi scris cea mai bună parte a unui sistem de baze de date.

desigur, există atât argumente pro și contra în adoptarea unui nou flux de lucru. La fel ca păstrarea fișierelor în ordine, la sfârșitul zilei, menținerea unei baze de date poate fi multă muncă. De exemplu, actualizarea PostGIS-ului la o nouă versiune poate fi o adevărată durere, așa cum a fost subliniat pe Twitter. Cu o mare putere vine o mare responsabilitate.

dar să vorbim mai mult despre acea parte a puterii.

Partea 2: Lumea magică a SQL spațial

SQL spațial poate accelera cu adevărat procesarea (atunci când este utilizată cu înțelepciune). Mai jos este o comparație între efectuarea aceluiași proces cu un fișier Shapefile și procesarea QGIS și apoi în PostGIS cu ST_GeneratePoints.

pentru această comparație am avut date de cod poștal din Finlanda și populația din fiecare zonă de cod poștal. Am avut acest lucru atât ca un fișier shape și un tabel în baza mea de date locală. Am creat puncte aleatorii în interiorul fiecărui poligon pentru a reprezenta populația. Am folosit procesarea QGIS (Random points inside polygon from Vector Processing) pentru fișierul Shapefile și în PostGIS SQL a fost într-adevăr la fel de simplu ca acesta:

SELECT ST_GeneratePoints(geom, he_vakiy) from paavo.paavo

după cum puteți vedea din grafic mai devreme, a fost nevoie de PostGIS mai puțin de 10% din timp pentru a face aceeași analiză în comparație cu QGIS și un fișier shape. Dacă sunteți analist GIS și faceți astfel de procese în fiecare zi, acest lucru vă poate economisi destul de mult timp într-un an.

pe lângă procesarea mai rapidă, vă puteți bucura de vasta selecție de funcții spațiale pe care PostGIS le poate oferi. Ce funcții vă sunt cele mai utile depind în totalitate de cazul de utilizare. Pe lângă analiza Voronoi și analiza GIS mai tradițională (tampon, suprapunere, intersect, clip etc..) puteți face lucruri mai avansate:

• rutare. Cu pgRouting și date rutiere puteți găsi rute optime și de a face diferite analize de rețea;
• poligon skeletonization. Această funcție vă permite să construiască axa mediană a unui poligon pe zbor;
• Geometrie subdiviziune. Împărțirea geometriilor pentru prelucrare ulterioară vă poate accelera semnificativ procesele;
• Clustering. Găsiți clustere și modele din datele dvs. Cu hype-ul AI la vârf, mijloacele k ar putea fi și mai interesante pentru unii decât înainte…

pentru ce ai nevoie de lucruri precum scheletarea poligonului? Ar putea fi o întrebare validă pentru majoritatea, dar că o dată când analiza spațială are nevoie de ea, vei fi extrem de încântat că cineva a făcut munca grea (=matematică) pentru tine. Combinarea diferitelor funcții spațiale împreună și utilizarea funcțiilor Postgres încorporate cu acestea vă va permite să faceți analize spațiale avansate în baza de date.

întrebări complicate și interesante (se alătură spațiale, agregări, etc), care sunt expresibile într-o singură linie de SQL în baza de date necesită o mulțime de putere de calcul și că este ceva care PostGIS vă oferă. Răspunzând la aceleași întrebări cu propriul cod, ar putea dura sute de linii de cod specializat pentru a răspunde la programarea împotriva fișierelor.

PostGIS pentru dataviz

în multe dintre vizualizările pe care le am în portofoliu, PostGIS a jucat un fel de rol în procesul de vizualizare. În fluxul meu de lucru cel mai adesea pre-procesez datele și apoi fac vizualizarea reală în QGIS.

să vedem un exemplu al unuia dintre aceste procese.

animația despre trenuri și voronois de mai sus oferă un exemplu jucăuș de putere a PostGIS. Am avut câteva milioane de puncte GPS de tren în baza mea de date locală și am creat deja animații cu punctele doar în mișcare. Dar am vrut să testez cum ar arăta o animație cu linii Voronoi.

mai întâi pentru că aveam mai multe puncte GPS pentru fiecare tren pe minut, am vrut să le grupez astfel încât să am un punct reprezentativ pentru fiecare minut pe tren. Am creat mai întâi un tabel manual pentru punctele rezultate. Am scris următoarea interogare

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

dacă frânăm interogarea în bucăți, putem vedea următoarele piese ale puzzle-ului:

• puteți vedea unele dintre elementele normale ale unei interogări SQL (INSERT in, SELECT, AS, FROM, WHERE, and, GROUP BY)
• geom, trainno și time sunt nume de coloane în tabelul săptămâna mea în schema numită trains
• subquery a returnează toate punctele GPS care au fost urmărite în intervalul de timp solicitat.
• deoarece selectez toate punctele GPS urmărite într-un minut, s-ar putea să obțin mai multe puncte pentru fiecare tren. Am vrut doar una, pentru ca liniile voronoi să pară mai sensibile. De aceea folosesc ST_Collect pentru a grupa punctele împreună și pentru a crea o geometrie multipunct din ele. ST_Centroid înlocuiește geometria multipunct cu un singur punct situat la centroid (subquery b), iar datele sunt grupate pe numere de tren.

pentru a face același lucru de mai multe ori, am avut un script Python simplu la bucla peste aceeași interogare pentru câteva sute de ori în cazul în care am avut de început și de sfârșit ori ca parametri. După ce am găsit cu succes un punct reprezentativ pentru fiecare minut, am rulat următoarea comandă (în 11,5 secunde):

SELECT t, ST_VoronoiLines(geom) from trains.voronoipoints

apoi am adăugat rezultatul la QGIS și l-am vizualizat cu Time Manager. Acest lucru ar putea fi un mod pic hacky pentru a obține rezultatul și un utilizator SQL mai experimentat ar fi făcut-o complet cu o singură comandă SQL, dar eu sunt încă destul de fericit cu rezultatul. Deși ar putea fi inutil.

în cele din urmă destul de simplu, dar rezultatul arata ca matematica nivel superior (și este!), deoarece toată munca grea este făcută de PostGIS. De asemenea, pentru că am reușit să fac analiza Voronoi pentru un singur punct pe tren, timpul de procesare a fost de doar câteva secunde pentru sute de mii de puncte.

adesea timpul de procesare a interogărilor dvs. crește exponențial pe măsură ce cantitățile de date cresc. Acesta este motivul pentru care trebuie să fii inteligent cu întrebările tale.

ca regulă generală, cu cât o interogare trebuie să preia mai multe date și mai multe operații pe care baza de date trebuie să le facă (ordonare, grupare etc.), devine mai lentă și, prin urmare, mai puțin eficientă. O interogare SQL eficientă preia doar rândurile și coloanele de care are nevoie cu adevărat. SQL poate lucra ca un puzzle logic, în cazul în care într-adevăr trebuie să se gândească bine ceea ce doriți să realizeze.

de asemenea, trebuie să menționez că modificarea performanței interogărilor dvs. este o pantă alunecoasă și vă puteți pierde în lumea optimizării nesfârșite. Găsirea echilibrului între o” interogare optimă ” și o interogare optimă este cu adevărat importantă. Mai ales dacă nu construiți o aplicație pentru un milion de utilizatori, câteva milisecunde aici sau acolo nu vă vor zgudui probabil barca.

cum să începeți?

îndrăznesc să spun că învățarea SQL este chiar mai benefică pentru un utilizator GIS mediu decât învățarea JavaScript, Python sau R. sintaxa SQL a avut doar modificări minore de-a lungul anilor, iar abilitățile SQL sunt foarte bine transferabile.

am constatat că curba de învățare în SQL nu este foarte abruptă pentru a face elementele de bază, dar s-ar putea să vă ia ceva timp pentru a vedea cu adevărat beneficiile pe care le poate aduce analizei spațiale. Dar încurajez să am răbdare și să încerc analize mai complicate și să urmăresc o procesare mai rapidă. În cele din urmă veți vedea diferența.

mai întâi când învățați elementele de bază SQL, veți învăța cum să interogați datele dintr-un singur tabel folosind tehnici de bază de selecție a datelor, cum ar fi selectarea coloanelor, sortarea setului de rezultate și filtrarea rândurilor. Apoi, veți afla despre interogările avansate, cum ar fi alăturarea mai multor tabele, utilizarea operațiilor setate și construirea unei Subinterogări. În cele din urmă, veți învăța cum să gestionați tabelele bazei de date, cum ar fi crearea unui nou tabel sau modificarea structurii unui tabel existent.

dar există, de asemenea, de asemenea, instrumente pentru a vă ajuta!

QGIS are un instrument excelent numit DB Manager. Acesta oferă o interfață grafică similară pentru baza dvs. de date, dar într-un mod mult mai comprimat și în interiorul QGIS. Puteți modifica și adăuga tabele, adăuga indexuri și de a face o mulțime de operațiunile de bază într-un mod cu clic dreapta.

ar trebui să verificați și pgAdmin, care este cea mai populară platformă de administrare și dezvoltare pentru PostgreSQL. Există mai multe modalități de a obține datele dvs. în PostGIS (de exemplu, ogr2ogr, shp2pgsql). În general, încurajez să încerc diferite instrumente și metode de lucru cu datele.

am făcut câteva mici experimente în combinarea Python și PostGIS. Lucrul cu Python (sau R) și PostGIS împreună vă poate duce cu adevărat procesarea și automatizarea datelor la nivelul următor. Doar combinarea capabilităților de script de bază ale Python și conectarea la PostGIS folosind psycopg2 sunt modalități bune de a începe.

simți că vrei să începi cu PostGIS?

1. trebuie doar să descărcați instalatorii și să instalați PostGIS pe mașina dvs. locală. Urmați instrucțiunile din tutoriale;
2. încărcați câteva date acolo. Începeți cu un singur fișier shape folosind QGIS DB Manager sau chech, de exemplu, acest tutorial despre cum să obțineți date naturale de pământ la PostGIS;
3. începeți să jucați cu SQL. Începeți cu elementele de bază (selectarea, filtrarea și modificarea datelor) și încet veți vedea ce fel de beneficii ar putea aduce fluxului dvs. de lucru.

concluzii

dacă modul dvs. de lucru este în prezent ineficient, doar schimbarea instrumentelor dvs. nu vă va face rezultatul mai bun sau procesul mai puțin dureros. Trebuie să schimbați modul în care vă gândiți la gestionarea datelor. Există numeroase modalități de utilizare ineficientă a bazelor de date. Crede-mă, le-am văzut și chiar am încercat câteva.

de asemenea, schimbarea lucrurilor doar de dragul schimbării, nu are sens. Dacă munca dvs. zilnică este doar trasarea câtorva puncte pe o hartă din când în când, puteți face acest lucru cu fișierele Shapefiles și fișierele csv, de asemenea, în viitor. Ar putea fi chiar mai eficient în acest fel.

dar.

dacă doriți să faceți niște analize spațiale serioase, să vă automatizați procesele sau să vă mutați în orice mod modul de lucru cu datele spatal la nivelul următor, vă pot recomanda cu tărie să vă familiarizați cu PostGIS și în special cu SQL spațial. Învățarea SQL poate fi, de asemenea, distractivă. Serios.

nu în ultimul rând. După cum a subliniat Tom: utilizarea PostGIS vă oferă credibilitate geohipster!

un lucru pe care l-am menționat doar pe scurt a fost că PostGIS este open source și disponibil gratuit. Aceasta înseamnă că persoanele care lucrează cu buget mic sau deloc (ca mine) nu au nicio barieră de intrare. Bazele de date spațiale comerciale pot fi extrem de costisitoare. Mulțumiri mari merge la toate dezvoltatorii activi care lucrează la proiect!

Vă mulțumim pentru lectură! Verificați site-ul meu pentru mai multe informații despre mine sau arunca-mi un comentariu pe Twitter.

vrei să afli mai multe? Surse pentru acest post pe blog și PostGIS suplimentare de lectură

RTFD. Documentația PostGIS este foarte bună.

PostGIS guru Paul Ramsey are mai multe prezentări pe această temă din punct de vedere diferit pe site-ul său

materiale grozave de la nemărginit la introducerea în PostGIS.

Anita Graser a scris o serie teribilă de postări pe blog despre manipularea datelor de mișcare în PostGIS.

consultați cărțile PostGIS de la Regina Obe

am folosit acest tutorial Boston GIS când am instalat pentru prima dată PostGIS local

Extra pentru persoanele care fac dataviz: un experiment interesant despre stocarea culorilor ca puncte 3D în PostGIS