Perché dovrebbe interessarti di PostGIS? – Una delicata introduzione ai database spaziali

Database? Non molto interessante.

Così potrebbe pensare una persona media che lavora con GIS o visualizzazioni di dati. Devo ammettere che i database non sono la cosa più sexy del mondo (mi dispiace DBA), ma se stai sostenendo (o mirando) di fare analisi o visualizzazione con dati (spaziali) in modo più serio, sicuramente non dovresti ignorarli. Spero che questo post sul blog possa darti un’idea del tipo di vantaggi che l’uso efficiente dei database spaziali potrebbe offrirti.

Hype termini vanno e vengono in ESSO e c’era un grande hype intorno big data ancora solo pochi anni fa, ma che ora sta lentamente svanendo. Bene, i dati sono ancora grandi e in realtà è più grande che mai. Le dimensioni dei file crescono e nella” scienza dei dati ” e nelle geoscienze le persone devono occuparsi di dati che possono facilmente essere nell’intervallo di gigabyte. Più grandi sono i dati, più attenzione dobbiamo prestare al modo in cui li archiviamo e li analizziamo.

Ecco dove un database entra nell’immagine.

Nello sviluppo del software lavorare con i database è un must. Ma per le persone in altri sottodomini dell’informatica (come GIS) i vantaggi di un database potrebbero non essere sempre così evidenti. Naturalmente le persone tendono ad utilizzare gli strumenti più familiari a loro anche se non sarebbe il modo più efficiente per raggiungere gli obiettivi. Ma a volte uscire dalla tua zona di comfort può davvero portarti grandi benefici. Sono stato io stesso lentamente realizzando il potenziale che si trova in SQL spaziale.

Questo post del blog è principalmente per le persone che lavorano con i dati geospaziali, ma non hanno toccato PostGIS, o forse non hanno nemmeno sentito parlare di esso. Non ho intenzione di esaminare come installare PostgreSQL/PostGIS, ma piuttosto provare a darti una panoramica di cosa sia e a cosa serve.

Il mio flusso di lavoro e gli esempi si concentrano principalmente sulla combinazione QGIS + PostGIS, ma dovresti notare che puoi lavorare solo con PostGIS, il tuo codice o con altri client GIS.

Posta what cosa?

Già durante i miei studi GIS avevo sentito più volte la frase “PostGIS è un’estensione spaziale di Postgres”. Non significava che avessi idea di cosa significasse. Non avevo idea di cosa sia Postgres, per non parlare di un’estensione spaziale.

Proviamo a frenarlo nel modo più semplice possibile.

Alcune persone potrebbero odiarmi per questo confronto, ma correrò il rischio: se non hai mai lavorato con i database, puoi pensare alle tabelle del database come a enormi fogli Excel. Ma un enorme foglio Excel intelligente da cui è possibile in un millisecondo scoprire quale valore è sulla terza colonna sul numero di riga 433 285. E invece di scrivere funzioni all’interno del foglio in una singola cella, le scrivi nella tua finestra di comando SQL. Quindi un posto dove archiviare i dati e da dove è possibile estrarli in modo efficiente.

PostGIS è un extender di database spaziale open source e liberamente disponibile per il sistema di gestione database PostgreSQL (noto anche come DBMS). Quindi PostgreSQL (alias Postgres) è il database e PostGIS è come un componente aggiuntivo a quel database. L’ultima versione di PostGIS viene ora fornito con PostgreSQL.

In poche parole PostGIS aggiunge funzioni spaziali come distanza, area, unione, intersezione e tipi di dati geometrici speciali a PostgreSQL.I database spaziali memorizzano e manipolano oggetti spaziali come qualsiasi altro oggetto nel database.

Quindi in un normale database si memorizzano dati di diversi tipi (numerici, testo, timestamp, immagini images) e quando necessario è possibile interrogarlo (fetch) per rispondere alle domande con i propri dati. Le domande possono riguardare “quante persone hanno effettuato l’accesso al tuo sito web” o “quante transazioni sono state effettuate in un negozio online”. Le funzioni spaziali possono invece rispondere a domande come “quanto è vicino il negozio più vicino”, “è questo punto all’interno di questa area” o “qual è la dimensione di questo paese”.

Quindi i dati vengono memorizzati in righe e colonne. Poiché PostGIS è un database spaziale, i dati hanno anche una colonna di geometria con i dati in un sistema di coordinate specifico definito da Spatial Reference Identifier (SRID). Ma ricorda che sebbene tu stia usando PostGIS principalmente per i dati spaziali, è anche possibile memorizzare dati non spaziali, poiché ha ancora tutte le funzionalità di un normale database PostgreSQL!

L’eccellente intro di Boundless PostGIS introduce tre concetti fondamentali che associano i dati spaziali a un database. Combinati questi forniscono una struttura flessibile per prestazioni e analisi ottimizzate.

1. Tipi di dati territoriali quali punto, linea e poligono. Familiare alla maggior parte del lavoro con i dati spaziali;
2. L’indicizzazione spaziale multidimensionale viene utilizzata per l’elaborazione efficiente delle operazioni spaziali;
3. Le funzioni spaziali, poste in SQL, servono per interrogare proprietà e relazioni spaziali.

SQL, o “Structured Query Language”, è un mezzo per porre domande e aggiornare i dati nei database relazionali. Una query select (che si utilizza per porre le domande) è generalmente un comando del seguente modulo

SELECT some_columns FROM some_data_source WHERE some_condition;

Le funzioni specifiche di PostGIS sono di solito sotto forma di ST_functionName.

Si scrivono questi comandi sulla riga di comando dopo aver effettuato l’accesso al database o nello strumento GUI del database (ad esempio pgAdmin o QGIS DB Manager). Quindi sì, SQL richiede di scrivere davvero qualcosa. Fare clic con il tasto destro potrebbe essere sottovalutato in generale, ma per qualcuno che non scrive alcun codice, SQL è un buon primo passo per scrivere i propri comandi e forse il codice successivo.

Ci sono anche altri database spaziali oltre a PostGIS. SQL Server Spatial, ESRI ArcSDE, Oracle Spatial e GeoMesa sono alcune altre opzioni per la gestione e l’analisi dei dati spaziali. Ma PostGIS si dice che abbia più funzionalità e prestazioni generalmente migliori. Anche gli altri menzionati (eccetto GeoMesa) non sono open source.

Se sei nuovo a questo, ora potresti essere confuso: quindi è un posto dove archiviare i dati e devi ottenere informazioni in modo complesso scrivendo alcune cose strane sulla riga di comando? Aspetta. Ci sono anche alcuni vantaggi reali che PostGIS può offrirti se ti impegni davvero.

Ho chiesto alcune idee per il post del blog da Twitter e ho ottenuto un sacco di buoni feedback. Da lì ho avuto l’idea di dividere questo in due parti. Nella prima parte esaminerò i benefici che PostGIS può portare al tuo lavoro quotidiano. Nella seconda parte mi concentrerò maggiormente su SQL spaziale.

PostGIS può consentire di adottare un nuovo modo di lavorare. Questo nuovo modo può essere più facilmente riproducibile, è possibile iniziare a utilizzare il controllo di versione più facilmente e può abilitare flussi di lavoro multiutente.

I file spesso richiedono un software speciale per leggere e scrivere. SQL è un’astrazione per l’accesso e l’analisi dei dati casuali. Senza questa astrazione, avrai bisogno di un software specifico per eseguire le operazioni o dovrai scrivere tu stesso tutto il codice di accesso e analisi.

Fare la tua analisi in SQL piuttosto che fare operazioni casuali per i file con alcuni strumenti casuali con parametri casuali, consente di condividere e riprodurre i risultati più facilmente. Potresti avere quel “master Shapefile” attualmente da qualche parte, dove hai effettuato diversi join spaziali e operazioni di clip su uno Shapefile per ottenere che sia come dovrebbe essere. E se sparisse?

Johnnie ha scritto un buon esempio su Twitter su come ha cancellato accidentalmente tutti i suoi dati, ma è stato in grado di riprodurli con il minimo sforzo con gli script SQL che ha salvato su GIT.

Le persone che lavorano con lo sviluppo del software hanno probabilmente (o si spera) familiarità con il controllo di versione. Non ho intenzione di approfondire questo in questo post del blog, ma sei in grado di (e dovresti) avere i tuoi script SQL in un sistema di controllo della versione, come GIT. Pensate a come un libro di cucina che si tiene nella vostra libreria e aggiornare costantemente per trovare sempre le migliori ricette per l’analisi dei dati gustosi. Solo che è possibile acquistare una nuova copia di questo libro di cucina esatto da Amazon di nuovo se la vostra casa brucia.

Un database può anche aiutarti a mantenere i tuoi dati spaziali in ordine migliore. Nessuno di noi è davvero perfetto e probabilmente creerai ancora tabelle come temp_1, final_final, ma un database offre comunque migliori opportunità per standardizzare la struttura dei dati rispetto ai soli file (ad esempio standardizzando i tipi di dati nelle tue tabelle).

E che dire di quei grandi set di dati? Con un database spaziale che lavora con grandi set di dati diventa possibile. Non solo più facile, ma a volte è quasi impossibile lavorare su set di dati più grandi senza un database. Hai mai provato ad aprire un file csv da 2 gb? O provato a fare un po ‘ di geoprocessing per un GeoJSON da 800 mb? Sapevi almeno che gli Shapefile hanno un limite di dimensioni? Naturalmente è possibile affrontare alcuni di questi problemi utilizzando Geopackage o altri formati di file, ma in generale PostGIS è lo strumento ottimale per la gestione di grandi dati (geospaziali).

Una caratteristica molto bella con i database è che puoi automatizzare più facilmente i processi che normalmente fai manualmente. Ad esempio, utilizzando la funzione di notifica PostgreSQL, è possibile aggiornare automaticamente le mappe QGIS. Anche se stai lavorando con strumenti ETL (ad esempio FME) per automatizzare il tuo lavoro, leggere/scrivere da/verso le tabelle PostGIS è molto più semplice che con i file.

Se non sei come me (attualmente faccio questa roba da solo e per divertimento), potresti avere una cosa chiamata squadra. Conosciuto anche come colleghi di lavoro. Potrebbero avere la necessità di accedere agli stessi dati come te. L’utilizzo di un database nel flusso di lavoro consente di lavorare in parallelo su un livello completamente diverso rispetto alla semplice presenza di file su un’unità condivisa.

Un motivo principale per questo è che gli utenti simultanei possono causare corruzione. Mentre è possibile scrivere codice aggiuntivo per garantire che più scritture nello stesso file non corrompano i dati, nel momento in cui hai risolto il problema e risolto anche il problema di prestazioni associato, avresti scritto la parte migliore di un sistema di database.

Naturalmente ci sono sia pro che contro nell’adozione di un nuovo flusso di lavoro. Proprio come mantenere i file in ordine, alla fine della giornata, anche il mantenimento di un database può essere un sacco di lavoro. Ad esempio, aggiornare il tuo PostGIS a una nuova versione può essere un vero dolore, come è stato sottolineato su Twitter. Con un grande potere viene una grande responsabilità.

Ma parliamo di più su quella parte di potenza.

Parte 2: Il magico mondo di spatial SQL

Spatial SQL può davvero accelerare l’elaborazione (se usato con saggezza). Di seguito è riportato un confronto tra l’esecuzione dello stesso processo con uno Shapefile e l’elaborazione QGIS e quindi in PostGIS con ST_GeneratePoints.

Per questo confronto ho avuto i dati del codice postale dalla Finlandia e la popolazione in ogni area del codice postale. Ho avuto questo sia come Shapefile che come tabella nel mio database locale. Ho creato punti casuali all’interno di ogni poligono per rappresentare la popolazione. Ho usato l’elaborazione QGIS (punti casuali all’interno del poligono dall’elaborazione vettoriale) per lo Shapefile e in PostGIS l’SQL era davvero semplice come questo:

SELECT ST_GeneratePoints(geom, he_vakiy) from paavo.paavo

Come puoi vedere dal grafico precedente, PostGIS ha impiegato meno del 10% del tempo per fare la stessa analisi rispetto a QGIS e uno Shapefile. Se sei un analista GIS e fai processi come questo ogni giorno, questo può farti risparmiare un sacco di tempo in un anno.

Oltre a un’elaborazione più rapida, puoi goderti la vasta selezione di funzioni spaziali che PostGIS ha da offrire. Quali funzioni sono più utili per te dipendono totalmente dal caso d’uso. Oltre all’analisi Voronoi e all’analisi GIS più tradizionale (buffer, overlay, intersect, clip ecc..) puoi fare cose più avanzate:

• Routing. Con pgRouting e dati stradali è possibile trovare percorsi ottimali e fare diverse analisi di rete;
• Scheletrizzazione poligonale. Questa funzione consente di costruire l’asse mediale di un poligono al volo;
• Suddivisione della geometria. Dividere le geometrie per ulteriori elaborazioni può accelerare significativamente i processi;
• Clustering. Trova cluster e modelli dai tuoi dati. Con l’hype AI al culmine, il k-means potrebbe essere ancora più interessante per alcuni rispetto a prima…

A cosa ti servono cose come la scheletrizzazione poligonale? Potrebbe essere una domanda valida per la maggior parte, ma quella volta in cui la tua analisi spaziale ne ha bisogno, sarai estremamente felice che qualcuno abbia fatto il duro lavoro (=matematica) per te. La combinazione di diverse funzioni spaziali insieme e l’utilizzo delle funzioni integrate di Postgres ti permetteranno di eseguire analisi spaziali avanzate nel tuo database.

Domande complicate e interessanti (join spaziali, aggregazioni, ecc.) che sono esprimibili in una riga di SQL nel database richiedono molta potenza computazionale e questo è qualcosa che PostGIS ti offre. Rispondere alle stesse domande con il proprio codice, potrebbe richiedere centinaia di righe di codice specializzato per rispondere durante la programmazione contro i file.

PostGIS per dataviz

In molte delle visualizzazioni che ho nel mio portfolio, PostGIS ha svolto una sorta di ruolo nel processo di visualizzazione. Nel mio flusso di lavoro più spesso pre-elaborare i dati e poi fare la visualizzazione effettiva in QGIS.

Vediamo un esempio di uno di questi processi.

Animazione su treni e voronois sopra dare un esempio giocoso del potere di PostGIS. Avevo qualche milione di punti GPS del treno nel mio database locale e avevo già creato animazioni con i punti appena in movimento. Ma volevo testare come sarebbe stata un’animazione con le linee di Voronoi.

Innanzitutto perché avevo diversi punti GPS per ogni treno al minuto, volevo raggrupparli in modo da avere un punto rappresentativo per ogni minuto per treno. Avevo prima creato una tabella manualmente per i punti risultanti. Ho scritto la seguente 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

Se freniamo la query in pezzi possiamo vedere i seguenti pezzi del puzzle:

• Puoi vedere alcuni degli elementi normali di una query SQL (INSERT INTO, SELECT, AS, FROM, WHERE, AND, GROUP BY)
• geom, trainno e time sono nomi di colonne nella mia tabella settimanale nello schema chiamato trains
• La sottoquery a restituisce tutti i punti GPS che sono stati tracciati entro il periodo di tempo richiesto.
• Poiché seleziono tutti i punti GPS tracciati in un minuto, potrei ottenere diversi punti per ogni treno. Ne volevo solo uno, in modo che le linee voronoi sembrassero più sensate. Ecco perché uso ST_Collect per raggruppare i punti e creare una geometria multipunto da essi. ST_Centroid sostituisce la geometria multipunto con un singolo punto situato al centroide (sottoquery b) e i dati sono raggruppati per numeri di treno.

Per fare la stessa cosa più volte, ho avuto un semplice script Python per eseguire il loop sulla stessa query per alcune centinaia di volte in cui avevo i tempi di inizio e fine come parametri. Dopo aver trovato con successo un punto rappresentativo per ogni minuto, ho appena eseguito il seguente comando (in 11,5 secondi):

SELECT t, ST_VoronoiLines(geom) from trains.voronoipoints

Quindi ho aggiunto il risultato a QGIS e l’ho visualizzato con Time Manager. Questo potrebbe essere un modo un po ‘ hacky per ottenere il risultato e un utente SQL più esperto potrebbe averlo fatto completamente con un singolo comando SQL, ma sono ancora abbastanza soddisfatto del risultato. Anche se potrebbe essere inutile.

Alla fine abbastanza semplice, ma il risultato sembra matematica di livello superiore (ed è!), come tutto il duro lavoro è fatto da PostGIS. Anche perché sono stato in grado di fare l’analisi Voronoi per un solo punto per treno, il tempo di elaborazione è stato solo secondi per centinaia di migliaia di punti.

Spesso il tempo di elaborazione delle query cresce esponenzialmente man mano che crescono le quantità di dati. Questo è il motivo per cui devi essere intelligente con le tue domande.

Come regola generale, più dati una query deve recuperare e più operazioni il database deve fare (ordinamento, raggruppamento ecc.), diventa più lento e quindi meno efficiente. Una query SQL efficiente recupera solo le righe e le colonne di cui ha veramente bisogno. SQL può funzionare come un puzzle logico, in cui devi davvero pensare a fondo a cosa vuoi ottenere.

Devo anche notare che modificare le prestazioni delle tue query è un pendio scivoloso e puoi perderti nel mondo dell’ottimizzazione infinita. Trovare l’equilibrio tra una “query ottimale” e una query ottimale è davvero importante. Soprattutto se non si sta costruendo un’applicazione per un milione di utenti, pochi millisecondi qui o là probabilmente non rock vostra barca.

Come iniziare?

Oserei dire che l’apprendimento di SQL è ancora più vantaggioso per un utente GIS medio rispetto all’apprendimento di JavaScript, Python o R. La sintassi SQL ha avuto solo piccoli cambiamenti nel corso degli anni e le abilità SQL sono molto ben trasferibili.

Ho scoperto che la curva di apprendimento in SQL non è davvero ripida per fare le basi, ma potrebbe volerci del tempo per vedere davvero i benefici che può portare alla tua analisi spaziale. Ma incoraggio ad essere paziente e provare analisi più complicate e mirare a un’elaborazione più rapida. Alla fine si vedrà la differenza.

In primo luogo quando si sta imparando nozioni di base di SQL imparerete come interrogare i dati da una singola tabella utilizzando tecniche di selezione dei dati di base come la selezione di colonne, ordinamento set di risultati, e le righe di filtraggio. Quindi, imparerai a conoscere le query avanzate come l’unione di più tabelle, l’utilizzo di operazioni impostate e la costruzione di una sottoquery. Infine, imparerai come gestire le tabelle del database come la creazione di una nuova tabella o la modifica della struttura di una tabella esistente.

Ma ci sono anche anche strumenti per aiutarti!

QGIS ha un ottimo strumento chiamato DB Manager. Offre una GUI simile per il tuo database, ma in modo molto più compresso e all’interno di QGIS. È possibile modificare e aggiungere tabelle, aggiungere indici e fare molte delle operazioni di base in modo cliccabile con il tasto destro del mouse.

Dovresti anche controllare pgAdmin, che è la piattaforma di amministrazione e sviluppo più popolare per PostgreSQL. Ci sono diversi modi per ottenere i tuoi dati in PostGIS (ad esempio ogr2ogr, shp2pgsql). In generale incoraggio a provare diversi strumenti e metodi di lavoro con i dati.

Ho fatto alcuni piccoli esperimenti nella combinazione di Python e PostGIS. Lavorare insieme con Python (o R) e PostGIS può davvero portare l’elaborazione e l’automazione dei dati al livello successivo. Basta combinare le funzionalità di scripting di base di Python e connettersi a PostGIS usando psycopg2 sono buoni modi per iniziare.

Ti senti come si desidera iniziare con PostGIS?

1. Basta scaricare i programmi di installazione e installare PostGIS sul computer locale. Seguire le istruzioni nei tutorial;
2. Caricare alcuni dati in là. Inizia con un singolo Shapefile usando QGIS DB Manager o chech, ad esempio questo tutorial su come ottenere i dati naturali della Terra in PostGIS;
3. Inizia a giocare con SQL. Inizia con le basi (selezione, filtraggio e modifica dei dati) e lentamente vedrai che tipo di benefici potrebbe portare al tuo flusso di lavoro.

Conclusioni

Se il tuo modo di lavorare è attualmente inefficiente, basta cambiare i tuoi strumenti non renderà il tuo risultato migliore o il processo meno doloroso. È necessario cambiare il modo di pensare alla gestione dei dati. Esistono numerosi modi per utilizzare i database in modo inefficiente. Fidati di me, li ho visti e anche provato alcuni.

Anche cambiare le cose solo per il gusto del cambiamento, non ha senso. Se il tuo lavoro quotidiano sta solo tracciando alcuni punti su una mappa di tanto in tanto, puoi farlo con Shapefile e file csv anche in futuro. Potrebbe anche essere più efficiente in questo modo.

MA.

Se vuoi fare analisi spaziali serie, automatizzare i tuoi processi o in qualsiasi modo spostare il tuo modo di lavorare con i dati spatal al livello successivo, ti consiglio vivamente di familiarizzare con PostGIS e in particolare spatal SQL. Imparare SQL può anche essere divertente. Seriamente.

Ultimo ma sicuramente non meno importante. Come ha sottolineato Tom: l’uso di PostGIS ti dà cred geohipster!

Una cosa che ho menzionato solo brevemente è che PostGIS è open source e liberamente disponibile. Ciò significa che le persone che lavorano con budget piccoli o no (come me) non hanno barriere di ingresso. I database spaziali commerciali possono essere estremamente costosi. Un grande ringraziamento va a tutti gli sviluppatori attivi che lavorano al progetto!

Grazie per la lettura! Controllare il mio sito web per ulteriori informazioni su di me o mi buttare un commento su Twitter.

Vuoi saperne di più? Fonti per questo post del blog e ulteriori PostGIS lettura

RTFD. La documentazione PostGIS è davvero buona.

PostGIS guru Paul Ramsey ha diverse presentazioni sul tema da diversi punti di vista sul suo sito

Grandi materiali da Boundless sull’introduzione a PostGIS.

Anita Graser ha scritto una serie formidabile di post sul blog sulla gestione dei dati di movimento in PostGIS.

Controlla i libri PostGIS da Regina Obe

Ho usato questo tutorial Boston GIS quando ho installato PostGIS localmente

Extra per le persone che fanno dataviz: un esperimento interessante sulla memorizzazione dei colori come punti 3D in PostGIS