Guida a PostGIS: Esempi di query di base sui dati geospaziali

I dati geospaziali stanno diventando sempre più importanti in molti campi, dalla pianificazione urbana alle scienze ambientali. In questo articolo illustreremo le query PostgreSQL PostGIS di base per lavorare con questo tipo di dati.

I dati geospaziali, che contengono informazioni sui luoghi della Terra, richiedono strumenti specializzati per essere utilizzati in modo efficace. PostGIS è una potente estensione di PostgreSQL che trasforma un database Postgres in un sistema informativo geografico (GIS) completo. Con PostGIS è possibile memorizzare oggetti geografici, eseguire query spaziali e analisi avanzate direttamente in SQL. Questo lo rende uno strumento essenziale per chiunque lavori con dati basati sulla posizione.

L'installazione di PostGIS consente di eseguire operazioni spaziali che di solito richiedono un software GIS specializzato. Se dovete calcolare distanze, misurare aree o analizzare relazioni spaziali, PostGIS vi fornisce gli strumenti necessari per gestire in modo efficiente dati spaziali complessi.

Se volete approfondire le vostre conoscenze su PostGIS, prendete in considerazione l'iscrizione al nostro corso PostGIS. Il corso vi porterà dalle basi del GIS alle tecniche avanzate di PostGIS. Una volta completato, capirete come sfruttare la potenza di PostGIS nei vostri progetti.

In questa guida vi presenteremo le basi di PostGIS. Inizieremo con semplici query spaziali e passeremo a tecniche avanzate come l'analisi di prossimità e l'analisi di rete. Per saperne di più sui dati geospaziali, consultate il nostro articolo Introduzione ai database spaziali e ai dati geospaziali.

Il nostro esempio di dati geospaziali

In questo articolo lavoreremo con un set di dati spaziali provenienti da San Francisco, California. Utilizzeremo diverse tabelle:

• sf_tram_stops: Questa tabella contiene le posizioni delle fermate del tram in tutta la città; memorizza anche le coordinate della posizione e la possibilità di effettuare trasferimenti tra le linee a quella fermata. Ha le seguenti colonne:
  • id - Un identificatore unico per ogni fermata.
  • coordinates - Le coordinate della fermata.
  • transfer_possible - Se un passeggero può cambiare tram a questa fermata.
    
• sf_planning_districts: Questa tabella definisce i confini dei distretti di pianificazione di San Francisco; questa informazione è essenziale per le analisi urbane. Presenta le seguenti colonne:
  • id - Un identificatore unico per ogni distretto di pianificazione.
  • name - Il nome del distretto.
  • boundaries - I confini del distretto (come coordinate di posizione).
• sf_bicycle_routes: Questa tabella contiene informazioni sui percorsi ciclabili della città; ne avrete bisogno per analizzare l'infrastruttura ciclabile di SanFran. Ha le seguenti colonne:
  • id - Un identificatore unico per ogni percorso ciclabile.
  • course - Il percorso del percorso.
  • condition_rating - La valutazione del percorso (in base alle sue condizioni).
• sf_restaurants: Questa tabella elenca le informazioni sui ristoranti di San Francisco, compresi i nomi, l'ubicazione, le valutazioni e i tipi di cucina. Ha le seguenti colonne:
  • id - Un identificativo unico per ogni ristorante.
  • name - Il nome del ristorante.
  • food_type - Il tipo di cucina servita in questo ristorante.
  • rating - La valutazione del ristorante.
  • coordinates - Le coordinate del ristorante.
• sf_sights: Questa tabella contiene informazioni su punti di riferimento e punti di interesse (POI) all'interno della città. Presenta le seguenti colonne:
  • id - Un identificatore unico per ogni punto di interesse.
  • name - Il nome del punto di interesse.
  • coordinates - La posizione del PDI.
• sf_atms: Questa tabella contiene i dettagli dei bancomat di SanFran, compresa l'ubicazione di ciascun bancomat e la società che lo gestisce. Ha le seguenti colonne:
  • id - Un identificativo unico per ogni bancomat.
  • company - La società che gestisce il bancomat.
  • coordinates - L'ubicazione del bancomat.

Questi dati servono come base per le query spaziali che esploreremo. Queste query verranno utilizzate per analizzare la vicinanza tra punti di riferimento e servizi, per calcolare le aree all'interno dei distretti di pianificazione e molto altro ancora. Ogni query dimostrerà come PostGIS aiuti a estrarre informazioni significative dai dati spaziali.

Query spaziali di base con PostGIS

Una volta che i vostri dati geospaziali sono archiviati in PostgreSQL con PostGIS, potete iniziare a interrogarli! Le query spaziali consentono di selezionare, filtrare e manipolare i dati in base alla loro posizione, forma e relazioni spaziali. Cominciamo quindi a lavorare con i dati geospaziali.

Visualizzazione dei dati geospaziali

Per prima cosa, vediamo come appaiono i dati geospaziali. Qui selezioniamo gli ID delle fermate del tram e le coordinate delle fermate in cui i passeggeri possono trasferirsi:

SELECT
  id, 
  coordinates
FROM sf_tram_stops
WHERE transfer_possible = true;

Ecco come appare il risultato:

Come si può vedere, i dati geospaziali nella colonna delle coordinate non sono realmente leggibili. Per poterli utilizzare davvero, è necessario un software specializzato per tracciarli sulla mappa. Fortunatamente, il nostro corso PostGIS è dotato di una mappa integrata; ora è possibile vedere la posizione delle fermate del tram:

Convertire i dati geospaziali in testo

Forse volete vedere le coordinate della posizione delle fermate del tram di cui sopra senza una mappa. È possibile utilizzare la seguente query per convertire i dati in coordinate leggibili dall'uomo:

SELECT
  id, 
  ST_AsText(coordinates),
  ST_Y(coordinates),
  ST_X(coordinates)
FROM sf_tram_stops
WHERE transfer_possible = true;

Questa query utilizza la funzione PostGIS ST_AsText per ottenere le coordinate in un formato leggibile. Utilizza ST_Y e ST_X per estrarre i valori di latitudine e longitudine. Ecco un risultato parziale:

Relazioni spaziali

PostGIS offre diverse funzioni per esplorare le relazioni spaziali degli oggetti geospaziali. Esaminiamole rapidamente.

ST_Intersezione

Questa funzione restituisce la parte condivisa (cioè l'intersezione) di due geometrie. La seguente query mostra tutti i percorsi ciclabili (o le loro parti) all'interno dei confini del quartiere Downtown:

SELECT
  sfbr.id,
  ST_Intersection(sfpd.boundaries, sfbr.course)
FROM sf_bicycle_routes sfbr
JOIN sf_planning_districts sfpd
  ON ST_Intersects(sfpd.boundaries, sfbr.course)
WHERE sfpd.name = 'Downtown';

La funzione ST_Intersection trova l'area in cui due forme si sovrappongono - in questo caso, dove le piste ciclabili attraversano i confini del quartiere "Downtown". La funzione ST_Intersects controlla se le piste ciclabili e i confini del quartiere si toccano o si incrociano, assicurandosi di includere solo quelle che lo fanno.

Ecco un risultato parziale sotto forma di testo:

E come mappa:

ST_Contains

La funzione ST_Contains controlla se una geometria ne contiene completamente un'altra. Per elencare i distretti di pianificazione che contengono bancomat della società Crown Financial, è possibile eseguire questa query:

SELECT DISTINCT sfn.name
FROM sf_planning_districts sfn
JOIN sf_atms sfa
  ON ST_Contains(sfn.boundaries, sfa.coordinates)
WHERE sfa.company = 'Crown Financial Inc.';

Ecco il risultato:

ST_Within

La funzione ST_Within controlla anche se una geometria si trova interamente all'interno di un'altra. Questa query individua i ristoranti con una valutazione superiore a 4,0 all'interno del distretto di Nord-Est:

SELECT 
  sep.name,
  sep.coordinates
FROM sf_planning_districts spd
JOIN sf_restaurants sep
  ON ST_Within(sep.coordinates, spd.boundaries)
WHERE rating > 4.0
  AND spd.name = 'Northeast';

Questa query restituisce i nomi e le coordinate dei ristoranti, rendendoli facilmente visualizzabili su una mappa.

Calcolo di distanze e aree

Il calcolo delle distanze e delle aree è un altro aspetto fondamentale del lavoro con i dati geospaziali. Questi calcoli possono aiutare a rispondere a domande come "Quanto dista questo ATM da un particolare punto di vista?" o "Qual è l'area di questo distretto di pianificazione?".

Calcolo della distanza

La funzione ST_Distance restituisce la distanza tra due oggetti geospaziali. Richiede due argomenti geometrici: ST_Distance(geometryA, geometryB).

Per visualizzare tutti i bancomat situati nel raggio di 300 metri dal Fisherman's Wharf, è possibile utilizzare la seguente query:

SELECT   
  sa.id,
  sa.coordinates,
  ST_Distance(
    ST_Transform(sa.coordinates, 26910), 
    ST_Transform(ss.coordinates, 26910)) AS distance
FROM sf_sights ss
JOIN sf_atms sa
  ON ST_Distance(
     ST_Transform(sa.coordinates, 26910), 
     ST_Transform(ss.coordinates, 26910)) < 300
WHERE ss.name = 'Fisherman''s Wharf';

In questa query, la funzione ST_Transform converte le coordinate date in un sistema di coordinate specifico adatto ai calcoli spaziali. Il sistema di coordinate garantisce che tutte le operazioni spaziali (come il calcolo delle distanze) siano precise e pertinenti alle esigenze specifiche dell'area geografica e del progetto.

In questa ricerca si utilizza il sistema di coordinate definito dal SRID (Spatial Reference System Identifier) 26910, che corrisponde al sistema NAD83 / UTM zona 10N. Questo sistema è comunemente utilizzato per il calcolo accurato delle distanze nell'area di San Francisco.

Come trovare il SRID giusto? Provate a consultare uno dei seguenti siti web:

• Sito web di riferimento spaziale: Utilizzare la scheda EPSG.
• io: Digitare il nome del Paese, il codice o il nome del sistema di coordinate per cercare e ottenere tutti i possibili sistemi di riferimento di coordinate.

Trasformando le coordinate, la query assicura che il calcolo della distanza tra l'ATM e il Fisherman's Wharf sia preciso. Ecco il risultato:

Calcolo dell'area

Per calcolare l'area di un distretto di pianificazione, utilizzare la funzione ST_Area. Questa funzione prende un argomento geometrico (ST_Area(geometry)) e restituisce l'area della forma risultante.

Ecco la query. Si noti che stiamo usando nuovamente SRID = 26910 come sistema di coordinate:

SELECT
  ST_Area(ST_Transform(boundaries, 26910))
FROM sf_planning_districts
WHERE name = 'Buena Vista';

E questo è il risultato:

Con SRID = 26910, l'unità di misura predefinita è il metro. Pertanto, il risultato di ST_Area è presentato in metri quadrati.

Analisi spaziale avanzata

Man mano che si acquisisce familiarità con PostGIS, è possibile utilizzare le sue funzionalità avanzate per eseguire analisi spaziali approfondite. Queste tecniche consentono di esplorare le relazioni spaziali, di eseguire analisi di prossimità e persino di condurre analisi di rete, rendendo i dati geospaziali molto più fruibili.

Buffering e analisi di prossimità

Il buffering crea zone intorno alle caratteristiche spaziali. È particolarmente utile nell'analisi di prossimità, quando è necessario determinare la vicinanza di alcuni elementi. Ad esempio, se si vogliono identificare tutti i ristoranti entro un raggio di 3000 metri dal "Palazzo delle Belle Arti", si può usare la funzione ST_DWithin:

SELECT   
  sep.name,
  sep.rating,
  sep.food_type,
  sep.coordinates,
  ST_Distance(
    ST_Transform(sep.coordinates, 26910), 
    ST_Transform(ss.coordinates, 26910)) AS distance
FROM sf_sights ss
JOIN sf_restaurants sep
  ON ST_DWithin(
     ST_Transform(sep.coordinates, 26910), 
     ST_Transform(ss.coordinates, 26910), 
     3000)
WHERE ss.name = 'Palace of Fine Arts';

Ecco i risultati in forma di tabella e mappa:

Operazioni di sovrapposizione

Nella cartografia, le operazioni di sovrapposizione ci permettono di vedere come diversi insiemi di informazioni geografiche si combinano o interagiscono. Immaginate di avere due mappe, una che mostra tutti i parchi di una città e un'altra che mostra le aree soggette a inondazioni. Sovrapponendo una mappa all'altra, è possibile scoprire quali parchi sono a rischio di inondazione. Questo processo ci aiuta a capire e a visualizzare i punti in cui le diverse caratteristiche geografiche coincidono, rendendo più efficaci la pianificazione, le analisi e il processo decisionale.

La seguente query utilizza la funzione ST_Union per unire i confini di tutti i distretti di pianificazione "ricreativi" in una forma geometrica unificata:

SELECT
  ST_Union(boundaries) 
FROM sf_planning_districts 
WHERE type = 'recreational';

Analogamente, si può usare ST_Intersection per ottenere lo stesso risultato.

Analisi della rete

L'analisi di rete consiste nell'esaminare i percorsi, identificare i percorsi più brevi e migliorare l'efficienza degli spostamenti all'interno di una rete. È fondamentale per comprendere e ottimizzare i sistemi di trasporto e la connettività. PostGIS, se abbinato a pgRouting, offre strumenti robusti per questo tipo di analisi.

L'estensione pgRouting fornisce algoritmi avanzati per l'ottimizzazione dei percorsi. Uno dei più noti è la funzione pgr_dijkstra, che calcola il percorso più breve o più veloce tra due punti considerando fattori quali il tipo di strada, le condizioni del traffico e altre variabili. Questa capacità è preziosa per i sistemi di navigazione, la pianificazione logistica, la gestione dei trasporti e altro ancora. Sfruttando queste estensioni, gli utenti possono migliorare la pianificazione dei percorsi, ridurre i tempi di viaggio e migliorare l'efficienza complessiva della rete.

Continuare a esplorare i dati spaziali con PostGIS

PostGIS, grazie alla sua perfetta integrazione con PostgreSQL, fornisce una potente piattaforma per la gestione e l'analisi dei dati geospaziali. In questo articolo abbiamo trattato le basi delle query spaziali, ci siamo avventurati in analisi spaziali avanzate e abbiamo dimostrato come PostGIS possa aiutarvi a rispondere a domande geografiche complesse con precisione ed efficienza.

Imparare PostGIS può trasformare il vostro approccio ai dati spaziali. Sarà più facile estrarre informazioni preziose, ottimizzare i flussi di lavoro e prendere decisioni basate sui dati. Che siate coinvolti nella pianificazione urbana, nell'analisi ambientale, nelle reti di trasporto o in qualsiasi altro campo legato ai GIS, PostGIS vi fornisce gli strumenti necessari per gestire efficacemente i dati geospaziali.

Esplorando le possibilità di PostGIS, scoprirete che migliora la vostra capacità di lavorare con i dati geospaziali e apre nuove strade per l'innovazione e la scoperta. La combinazione delle solide capacità di database di PostgreSQL e delle funzioni geospaziali di PostGIS consente di affrontare con sicurezza anche le sfide spaziali più complesse.

Prima di andare via, vorrei menzionare ancora una volta il nostro corso completo su PostGIS: è davvero un'ottima scelta per fare pratica nel mondo reale con i dati spaziali. Vi guiderà attraverso ogni aspetto di PostGIS, dai fondamenti alle tecniche avanzate. Prima che ve ne rendiate conto, sarete in grado di affrontare con sicurezza progetti di dati geospaziali.

PostGIS è più di una semplice estensione di PostgreSQL. È una porta d'accesso per liberare tutto il potenziale dei dati geospaziali all'interno dei vostri sistemi di database. Padroneggiando le funzioni di PostGIS, potrete trasformare i dati spaziali in informazioni utili per il successo dei vostri progetti e della vostra azienda.