Lavorare con i dati geospaziali

L'analisi geospaziale consente di analizzare i dati geografici in BigQuery. I dati geografici sono noti anche come dati geospaziali.

Di seguito sono riportati i tipi più comuni di oggetti quando si lavora con dati geospaziali:

Una geometria rappresenta un'area della superficie terrestre. Viene spesso descritto utilizzando punti, linee, poligoni o un insieme di punti, linee e poligoni. Una raccolta di geometria è una geometria che rappresenta l'unione spaziale di tutte le forme nella raccolta.
Un elemento spaziale rappresenta un oggetto spaziale logico. Combina una geometria con attributi aggiuntivi specifici dell'applicazione.
Una raccolta di elementi spaziali è un insieme di elementi spaziali.

In BigQuery, il tipo di dati GEOGRAPHY rappresenta un valore di geometria o una raccolta di geometria. Per rappresentare gli elementi spaziali, crea una tabella con una colonna GEOGRAPHY per la geometria e colonne aggiuntive per gli attributi. Ogni riga della tabella è una caratteristica spaziale, mentre l'intera tabella rappresenta una raccolta di elementi spaziali.

Il tipo di dati GEOGRAPHY descrive un insieme di punti sulla superficie terrestre. Un set di punti è un insieme di punti, linee e poligoni sullo sferoide di riferimento WGS84, con bordi geodetici. Puoi utilizzare il tipo di dati GEOGRAPHY chiamando una delle funzioni geografiche di GoogleSQL.

Caricamento dei dati geospaziali in corso...

I singoli punti sulla Terra possono essere descritti solo con la combinazione di longitudine e latitudine. Ad esempio, puoi caricare un file CSV che contiene i valori di longitudine e latitudine e utilizzare la funzione ST_GEOGPOINT per convertirli in valori GEOGRAPHY.

Per aree geografiche più complesse, puoi caricare i seguenti formati di dati geospaziali in una colonna GEOGRAPHY:

Testo noto (WKT)
Programma binario noto (WKB)
GeoJSON

Caricamento di dati WKT o WKB

WKT è un formato di testo per descrivere singole forme geometriche utilizzando punti, linee, poligoni con fori facoltativi o un insieme di punti, linee o poligoni. WKB è la versione binaria del formato WKT. WKB può essere con codifica esadecimale per formati che non supportano dati binari, come JSON.

Ad esempio, quanto segue definisce un punto in WKT:

POINT(-121 41)

Per descrivere una caratteristica spaziale, WKT è solitamente incorporato in un formato file container, come un file CSV o in una tabella di database. Una riga del file o una riga di una tabella di solito corrisponde alla caratteristica spaziale. L'intero file o l'intera tabella corrispondono alla raccolta di caratteristiche. Per caricare i dati WKT in BigQuery, fornisci uno schema che specifichi una colonna GEOGRAPHY per i dati geospaziali.

Ad esempio, potresti avere un file CSV che contiene i seguenti dati:

"POLYGON((-124.49 47.35,-124.49 40.73,-116.49 40.73,-116.49 47.35,-124.49 47.35))",poly1
"POLYGON((-85.6 31.66,-85.6 24.29,-78.22 24.29,-78.22 31.66,-85.6 31.66))",poly2
"POINT(1 2)",point1

Puoi caricare questo file eseguendo il comando load dello strumento a riga di comando bq:

bq load --source_format=CSV \
  --schema="geography:GEOGRAPHY,name:STRING" \
  mydataset.mytable filename1.csv

Per ulteriori informazioni sul caricamento dei dati in BigQuery, consulta Introduzione al caricamento dei dati.

Per inserire flussi di dati WKT in una tabella BigQuery esistente con una colonna GEOGRAPHY, serializza i dati come stringa nella richiesta API.

bq

Esegui il comando insert dello strumento a riga di comando bq:

echo '{"geo": "LINESTRING (-118.4085 33.9416, -73.7781 40.6413)"}' \
    | bq insert my_dataset.geo_table

Python

Prima di provare questo esempio, segui le istruzioni di configurazione di Python disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.

from google.cloud import bigquery
import shapely.geometry
import shapely.wkt

bigquery_client = bigquery.Client()

# This example uses a table containing a column named "geo" with the
# GEOGRAPHY data type.
table_id = "my-project.my_dataset.my_table"

# Use the Shapely library to generate WKT of a line from LAX to
# JFK airports. Alternatively, you may define WKT data directly.
my_geography = shapely.geometry.LineString(
    [(-118.4085, 33.9416), (-73.7781, 40.6413)]
)
rows = [
    # Convert data into a WKT string.
    {"geo": shapely.wkt.dumps(my_geography)},
]

#  table already exists and has a column
# named "geo" with data type GEOGRAPHY.
errors = bigquery_client.insert_rows_json(table_id, rows)
if errors:
    raise RuntimeError(f"row insert failed: {errors}")
else:
    print(f"wrote 1 row to {table_id}")

Per ulteriori informazioni sull'inserimento di flussi di dati in BigQuery, consulta Streaming di dati in BigQuery.

Puoi anche convertire una stringa di testo WKT in un valore GEOGRAPHY utilizzando la funzione ST_GeogFromText.

Caricamento dei dati GeoJSON

GeoJSON è un formato basato su JSON per le geometrie e gli elementi spaziali. Ad esempio, quanto segue definisce un punto in GeoJSON:

{ "type": "Point", "coordinates": [-121,41] }

I dati GeoJSON possono contenere uno dei seguenti tipi di oggetti:

Oggetti geometrici. Un oggetto geometrico è una forma spaziale, definita un insieme di punti, linee e poligoni.
Oggetti delle caratteristiche. Un oggetto feature contiene una geometria più altre coppie nome/valore, il cui significato è specifico per l'applicazione.
Raccolte di caratteristiche. Una raccolta di caratteristiche è un insieme di oggetti feature.

Esistono due modi per caricare i dati GeoJSON in BigQuery:

Carica file GeoJSON delimitati da nuova riga.
Carica singoli oggetti GeoJSON GeoJSON incorporati in altri tipi di file.

Caricamento dei file GeoJSON delimitati da nuova riga

Un file GeoJSON delimitato da nuova riga contiene un elenco di oggetti feature GeoJSON, uno per riga nel file. Un oggetto feature GeoJSON è un oggetto JSON con i seguenti membri:

type. Per gli oggetti feature, il valore deve essere Feature. BigQuery convalida il valore ma non lo include nello schema della tabella.
geometry. Il valore è un oggetto Geometry GeoJSON null. BigQuery converte questo membro in un valore GEOGRAPHY.
properties. Il valore è un oggetto JSON qualsiasi o un valore nullo. Se il valore non è null, BigQuery carica ogni membro dell'oggetto JSON come colonna di tabella separata. Per ulteriori informazioni su come BigQuery analizza i tipi di dati JSON, consulta Dettagli del caricamento dei dati JSON.
id. Facoltativa. Se presente, il valore è una stringa o un numero. BigQuery carica questo valore in una colonna denominata id.

Se l'oggetto feature contiene altri membri non elencati qui, BigQuery li converte direttamente in colonne della tabella.

Puoi caricare un file GeoJSON delimitato da nuova riga utilizzando il comando bq load dello strumento a riga di comando bq, come indicato di seguito:

bq load \
 --source_format=NEWLINE_DELIMITED_JSON \
 --json_extension=GEOJSON \
 --autodetect \
 DATASET.TABLE \
 FILE_PATH_OR_URI

Sostituisci quanto segue:

DATASET è il nome del tuo set di dati.
TABLE è il nome della tabella di destinazione.
FILE_PATH_OR_URI è un percorso a un file locale o a un URI Cloud Storage.

L'esempio precedente abilita il rilevamento automatico dello schema. Per un maggiore controllo sul modo in cui BigQuery converte i valori all'interno dell'oggetto properties, puoi fornire uno schema esplicito. Per maggiori informazioni, consulta Specificare gli schemi. Se fornisci uno schema esplicito, non includere una colonna type di primo livello nella definizione dello schema. Per ogni membro del membro properties, definisci colonne separate, non una singola colonna nidificata.

Come definita da RFC 7946, una struttura completa di dati GeoJSON è un singolo oggetto JSON. Molti sistemi esportano i dati GeoJSON come un singolo oggetto FeatureCollection che contiene tutte le geometrie. Per caricare questo formato in BigQuery, devi convertire il file rimuovendo l'oggetto FeatureCollection di livello principale e suddividendo i singoli oggetti feature in righe separate. Ad esempio, il seguente comando utilizza lo strumento a riga di comando jq per suddividere un file GeoJSON in un formato delimitato da nuova riga:

cat ~/file1.json | jq -c '.features[]' > converted.json

Creazione di una tabella esterna da un file GeoJSON delimitato da nuova riga

Puoi eseguire query su un file GeoJSON delimitato da nuova riga archiviato in Cloud Storage creando una tabella esterna. Per creare la tabella esterna, utilizza l'istruzione DDL CREATE EXTERNAL TABLE. Nella clausola OPTIONS, imposta l'formatopzione su NEWLINE_DELIMITED_JSON e l'opzione json_extension su GEOJSON.

Esempio:

CREATE EXTERNAL TABLE mydataset.table1 OPTIONS (
  format="NEWLINE_DELIMITED_JSON",
  json_extension = 'GEOJSON',
  uris = ['gs://mybucket/geofile.json']
);

Caricamento dei dati relativi alla geometria di GeoJSON

L'analisi geospaziale supporta il caricamento di singoli oggetti geometrici GeoJSON che vengono incorporati come stringhe di testo in altri tipi di file. Ad esempio, puoi caricare un file CSV in cui una delle colonne contiene un oggetto geometrico GeoJSON.

Per caricare questo tipo di dati GeoJSON in BigQuery, fornisci uno schema che specifichi una colonna GEOGRAPHY per i dati GeoJSON. Devi fornire manualmente lo schema. Altrimenti, se il rilevamento automatico è abilitato, BigQuery carica i dati come valore STRING.

L'analisi geospaziale non supporta il caricamento di oggetti di funzionalità o raccolte di caratteristiche GeoJSON tramite questo approccio. Se devi caricare oggetti feature, considera di usare file GeoJSON delimitati da nuova riga.

Per inserire flussi di dati GeoJSON in una tabella BigQuery esistente con una colonna GEOGRAPHY, serializza i dati come stringa nella richiesta API.

bq

Esegui il comando insert dello strumento a riga di comando bq:

echo '{"geo": "{\"type\": \"LineString\", \"coordinates\": [[-118.4085, 33.9416], [-73.7781, 40.6413]]}"}' \
  | bq insert my_dataset.geo_table

Python

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.

import geojson
from google.cloud import bigquery

bigquery_client = bigquery.Client()

# This example uses a table containing a column named "geo" with the
# GEOGRAPHY data type.
table_id = "my-project.my_dataset.my_table"

# Use the python-geojson library to generate GeoJSON of a line from LAX to
# JFK airports. Alternatively, you may define GeoJSON data directly, but it
# must be converted to a string before loading it into BigQuery.
my_geography = geojson.LineString([(-118.4085, 33.9416), (-73.7781, 40.6413)])
rows = [
    # Convert GeoJSON data into a string.
    {"geo": geojson.dumps(my_geography)}
]

#  table already exists and has a column
# named "geo" with data type GEOGRAPHY.
errors = bigquery_client.insert_rows_json(table_id, rows)
if errors:
    raise RuntimeError(f"row insert failed: {errors}")
else:
    print(f"wrote 1 row to {table_id}")

Puoi anche convertire un oggetto geometrico di GeoJSON in un valore GEOGRAPHY utilizzando la funzione ST_GEOGFROMGEOJSON. Ad esempio, puoi memorizzare le geometrie come valori STRING e poi eseguire una query che chiami ST_GEOGFROMGEOJSON.

Coordina i sistemi e i bordi

Nell'analisi geospaziale, i punti sono posizioni sulla superficie di uno sferoide WGS84, espresse come longitudine e latitudine geodetica. Un bordo è una geodetica sferica tra due endpoint. ovvero i bordi sono il percorso più breve sulla superficie di una sfera.

Il formato WKT non fornisce un sistema di coordinate. Durante il caricamento dei dati WKT, l'analisi geospaziale presuppone che i dati utilizzino le coordinate WGS84 con bordi sferici. Assicurati che i dati di origine corrispondano a quel sistema di coordinate, a meno che le aree geografiche non siano abbastanza piccole da ignorare la differenza tra bordi sferici e planari.

GeoJSON utilizza esplicitamente le coordinate WGS84 con bordi planari. Durante il caricamento dei dati GeoJSON, l'analisi geospaziale converte i bordi planari in bordi sferici. L'analisi geospaziale aggiunge ulteriori punti alla linea, se necessario, in modo che la sequenza convertita di bordi rimanga entro 10 metri dalla linea originale. Questo processo è noto come tessellazione o densificazione non uniforme. Non è possibile controllare direttamente il processo di tassellazione.

Per caricare le aree geografiche con bordi sferici, utilizza WKT. Per caricare aree geografiche con bordi piani, spesso chiamati geometrie, è più semplice utilizzare GeoJSON. Tuttavia, se i dati geometrici sono già in formato WKT, un'altra opzione è caricare i dati come tipo STRING e utilizzare la funzione ST_GEOGFROMTEXT per convertirli in valori GEOGRAPHY. Imposta il parametro planar su TRUE per interpretare i dati come planari.

Quando scegli un formato di interscambio, assicurati di comprendere il sistema di coordinate utilizzato dai dati di origine. La maggior parte dei sistemi supporta esplicitamente l'analisi geografica (invece che la geometria) da WKT oppure presuppone bordi planari.

Le coordinate devono essere prima la longitudine e poi la latitudine. Se l'area geografica ha segmenti o bordi lunghi, questi devono essere tassellati, poiché l'analisi geospaziale li interpreta come geodetiche sferiche, che potrebbero non corrispondere al sistema di coordinate in cui hanno avuto origine i dati.

Orientamento del poligono

In una sfera, ogni poligono ha un poligono complementare. Ad esempio, un poligono che descrive i continenti della Terra avrebbe un poligono complementare che descrive gli oceani della Terra. Poiché i due poligoni sono descritti dagli stessi anelli di confine, sono necessarie regole per risolvere l'ambiguità in base alla quale una determinata stringa WKT descrive i due poligoni.

Quando carichi stringhe WKT e WKB da file o utilizzando l'importazione di flussi di dati, l'analisi geospaziale presuppone che i poligoni nell'input siano orientati nel seguente modo: se attraversi i confini del poligono nell'ordine dei vertici di input, l'interno del poligono si trova a sinistra. L'analisi geospaziale utilizza la stessa regola durante l'esportazione di oggetti geografici in stringhe WKT e WKB.

Se utilizzi la funzione ST_GeogFromText per convertire una stringa WKT in un valore GEOGRAPHY, il parametro oriented specifica in che modo la funzione determina il poligono:

FALSE: interpreta l'input come il poligono con l'area più piccola. Questo è il comportamento predefinito.
TRUE: utilizza la regola per l'orientamento a sinistra descritta in precedenza. Questa opzione consente di caricare poligoni con un'area più grande di un emisfero.

Poiché le stringhe GeoJSON sono definite su una mappa planare, l'orientamento può essere determinato senza ambiguità, anche se l'input non segue la regola di orientamento definita nella specifica del formato GeoJSON, RFC 7946.

Gestione di dati spaziali non correttamente formattati

Quando carichi dati spaziali da altri strumenti in BigQuery, potresti riscontrare errori di conversione a causa di dati WKT o GeoJSON non validi. Ad esempio, un errore come Edge K has duplicate vertex with edge N indica che il poligono ha vertici duplicati (oltre al primo e all'ultimo).

Per evitare problemi di formattazione, puoi utilizzare una funzione che generi un output conforme agli standard. Ad esempio, quando esporti dati da PostGIS, puoi utilizzare la funzione ST_MakeValid di PostGIS per standardizzare l'output. In alternativa, importa i dati come testo e convertili chiamando ST_GEOGFROMTEXT o ST_GEOGFROMGEOJSON con il parametro make_valid. Quando make_valid è TRUE, queste funzioni tentano di correggere poligoni non validi.

Per trovare o ignorare i dati formattati in modo errato, utilizza il prefisso della funzione SAFE per restituire i dati problematici. Ad esempio, la seguente query utilizza il prefisso SAFE per recuperare dati spaziali non correttamente formattati.

SELECT
  geojson AS bad_geojson
FROM
  mytable
WHERE
  geojson IS NOT NULL
  AND SAFE.ST_GeogFromGeoJson(geojson) IS NULL

Vincoli

L'analisi geospaziale non supporta le seguenti funzionalità nei formati geospaziali:

Geometrie tridimensionali. Ciò include il suffisso "Z" in formato WKT e la coordinata dell'altitudine nel formato GeoJSON.
Sistemi di riferimento lineari. È incluso il suffisso "M" in formato WKT.
Oggetti geometrici WKT diversi dalle primitive geometriche o dalle geometrie multiparte. In particolare, l'analisi geospaziale supporta solo Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon e GeometryCollection.

Consulta ST_GeogFromGeoJson e ST_GeogFromText per i vincoli specifici dei formati di input GeoJson e WKT.

Carica i dati geospaziali di Google Earth Engine

Google Earth Engine è una piattaforma di dati geospaziali che compila e analizza informazioni ricavate da immagini di osservazione satellitari e della Terra utilizzando dati raster, in cui i dati sono organizzati su una griglia di celle che rappresentano informazioni su immagini digitali. Sebbene BigQuery funzioni principalmente con dati vettoriali tabulari, gli utenti possono utilizzare i loro dati BigQuery insieme ai dati raster di Earth Engine per incorporare set di dati vettoriali e raster nei loro flussi di lavoro.

Per informazioni sull'esportazione dei dati di Earth Engine in BigQuery, consulta Esportazione in BigQuery.

Trasformazione dei dati geospaziali

Se la tabella contiene colonne separate per longitudine e latitudine, puoi trasformare i valori in aree geografiche utilizzando le funzioni geografiche di GoogleSQL come ST_GeogPoint. Ad esempio, se hai due colonne DOUBLE per la longitudine e la latitudine, puoi creare una colonna relativa all'area geografica con la seguente query:

SELECT
  *,
  ST_GeogPoint(longitude, latitude) AS g
FROM
  mytable

BigQuery può convertire le stringhe WKT e GeoJSON in tipi di area geografica. Se i dati sono in un altro formato, ad esempio i shapefile, utilizza uno strumento esterno per convertirli in un formato file di input supportato, ad esempio un file CSV, con GEOGRAPHY colonne codificate come stringhe WKT o GeoJSON.

Partizionamento e clustering dei dati geospaziali

Puoi partizionare e clusterare tabelle che contengono GEOGRAPHY colonne. Puoi utilizzare una colonna GEOGRAPHY come colonna di clustering, ma non puoi utilizzare una colonna GEOGRAPHY come colonna di partizionamento.

Se archivi dati GEOGRAPHY in una tabella e le query filtrano i dati utilizzando un predicato spaziale, assicurati che la tabella sia raggruppata in base alla colonna GEOGRAPHY. Questo in genere migliora le prestazioni delle query e potrebbe ridurre i costi. Un predicato spaziale chiama una funzione di geografia booleana e ha una colonna GEOGRAPHY come uno degli argomenti. L'esempio seguente mostra un predicato spaziale che utilizza la funzione ST_DWithin:

WHERE ST_DWithin(geo, ST_GeogPoint(longitude, latitude), 100)

Utilizzo dei JOIN con i dati spaziali

I JOIN spaziali sono unioni di due tabelle con una funzione geografica di predicato nella clausola WHERE. Ad esempio:

-- how many stations within 1 mile range of each zip code?
SELECT
    zip_code AS zip,
    ANY_VALUE(zip_code_geom) AS polygon,
    COUNT(*) AS bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
    `bigquery-public-data.geo_us_boundaries.zip_codes` AS zip_codes
WHERE ST_DWithin(
         zip_codes.zip_code_geom,
         ST_GeogPoint(bike_stations.longitude, bike_stations.latitude),
         1609.34)
GROUP BY zip
ORDER BY bike_stations DESC

I join spaziali funzionano meglio quando i dati geografici sono resi persistenti. L'esempio riportato sopra crea i valori geografici nella query. È più efficace archiviare i valori dell'area geografica in una tabella BigQuery.

Ad esempio, la seguente query recupera le coppie di longitudine e latitudine e le converte in punti geografici. Quando esegui questa query, specifichi una nuova tabella di destinazione per archiviare i risultati della query:

SELECT
  *,
  ST_GeogPoint(pLongitude, pLatitude) AS p
FROM
  mytable

BigQuery implementa i JOIN spaziali ottimizzati per gli operatori INNER JOIN e CROSS JOIN con le seguenti funzioni di predicato GoogleSQL:

I join spaziali non sono ottimizzati:

Per le join LEFT, RIGHT o FULL OUTER
Nei casi che coinvolgono i join ANTI
Quando il predicato spaziale viene negato

Un JOIN che utilizza il predicato ST_DWithin viene ottimizzato solo quando il parametro di distanza è un'espressione costante.

Esportazione dei dati spaziali

Quando esporti i dati spaziali da BigQuery, i valori delle colonne GEOGRAPHY vengono sempre formattati come stringhe WKT. Per esportare i dati in formato GeoJSON, utilizza la funzione ST_AsGeoJSON.

Se gli strumenti che utilizzi per analizzare i dati esportati non comprendono il tipo di dati GEOGRAPHY, puoi convertire i valori della colonna in stringhe utilizzando una funzione geografica come ST_AsText o ST_AsGeoJSON. L'analisi geospaziale aggiunge ulteriori punti alla linea, ove necessario, in modo che la sequenza convertita di bordi rimanga entro 10 metri dalla linea geodetica originale.

Ad esempio, la seguente query utilizza ST_AsGeoJSON per convertire i valori GeoJSON in stringhe.

SELECT
  ST_AsGeoJSON(ST_MakeLine(ST_GeogPoint(1,1), ST_GeogPoint(3,2)))

I dati risultanti sarebbero i seguenti:

{ "type": "LineString", "coordinates": [ [1, 1], [1.99977145571783, 1.50022838764041], [2.49981908082299, 1.75018082434274], [3, 2] ] }

La riga GeoJSON ha due punti aggiuntivi. L'analisi geospaziale aggiunge questi punti in modo che la linea GeoJSON segua da vicino lo stesso percorso sul suolo della linea originale.

Passaggi successivi

Per iniziare a utilizzare l'analisi geospaziale, consulta Guida introduttiva all'analisi geospaziale per analisti di dati.
Per scoprire di più sulle opzioni di visualizzazione per le analisi geospaziali, consulta Visualizzare i dati geospaziali.
Per la documentazione sulle funzioni GoogleSQL nell'analisi geospaziale, consulta Funzioni geografiche in GoogleSQL.