Lavorare con i dati geospaziali
L'analisi geospaziale consente di analizzare i dati geografici in BigQuery. I dati geografici sono chiamati anche dati geospaziali.
I tipi comuni di oggetti quando si lavora con dati geospaziali includono:
- Una geometria rappresenta un'area della superficie terrestre. Viene spesso descritta 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 della raccolta.
- Una caratteristica spaziale rappresenta un oggetto spaziale logico. Combina una geometria con attributi aggiuntivi specifici dell'applicazione.
- Una raccolta di caratteristiche spaziali è un insieme di caratteristiche spaziali.
In BigQuery, il tipo di dati GEOGRAPHY
rappresenta un valore o una raccolta di geometria. Per rappresentare le caratteristiche spaziali, crea una tabella con una colonna GEOGRAPHY
per la geometria e altre colonne per gli attributi. Ogni riga della tabella è una caratteristica
spaziale e l'intera tabella rappresenta una raccolta di caratteristiche spaziali.
Il tipo di dati GEOGRAPHY
descrive un insieme di punti sulla superficie terrestre. Un insieme 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
I singoli punti sulla Terra possono essere descritti semplicemente con una coppia di longitudine e latitudine.
Ad esempio, puoi caricare un file CSV che contiene valori di longitudine e latitudine
e poi 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)
- WKB (Ben noto) binario.
- GeoJSON
Caricamento di dati WKT o WKB
WKT è un formato di testo che consente di 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 codificato esadecimale per i formati che non supportano i dati binari, come JSON.
Ad esempio, quanto segue definisce un punto in WKT:
POINT(-121 41)
Per descrivere una caratteristica spaziale, WKT è in genere incorporato in un formato file container, ad esempio un file CSV o in una tabella di database. Una riga di file o una riga di tabella di solito
corrisponde alla caratteristica spaziale. L'intero file o l'intera tabella
corrisponde 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 contenente 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 lo strumento a riga di comando bq load
:
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 trasmettere dati WKT in modalità flusso a 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 per la configurazione di Python nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per maggiori informazioni, consulta la documentazione di riferimento dell'API Python di BigQuery.
Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Per ulteriori informazioni sui flussi di dati in BigQuery, consulta Flusso 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 geometrie e caratteristiche spaziali. Ad esempio, di seguito definisce un punto in GeoJSON:
{ "type": "Point", "coordinates": [-121,41] }
I dati GeoJSON possono contenere i seguenti tipi di oggetti:
- Oggetti geometrici. Un oggetto di geometria è una forma spaziale, descritta come unione di punti, linee e poligoni con fori opzionali.
- Oggetti caratteristica. Un oggetto caratteristica contiene una geometria più coppie nome/valore aggiuntive, il cui significato è specifico per l'applicazione.
- Raccolte di funzionalità. Una raccolta di caratteristiche è un insieme di oggetti di caratteristiche.
Esistono due modi per caricare dati GeoJSON in BigQuery:
- Carica file GeoJSON delimitato da nuova riga.
- Carica singoli oggetti di geometria GeoJSON incorporati in altri tipi di file.
Caricamento di file GeoJSON delimitato da nuova riga
Un file GeoJSON delimitato da nuova riga contiene un elenco di oggetti GeoJSON, uno per riga nel file. Un oggetto caratteristica GeoJSON è un oggetto JSON con i seguenti membri:
type
. Per gli oggetti delle caratteristiche, il valore deve essereFeature
. BigQuery convalida il valore, ma non lo include nello schema della tabella.geometry
. Il valore è un oggetto GeoJSONGeometry
onull
. BigQuery converte questo membro in un valoreGEOGRAPHY
.properties
. Il valore è qualsiasi oggetto JSON o 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 sul caricamento dei dati JSON.id
. Facoltativo. Se presente, il valore è una stringa o un numero. BigQuery carica questo valore in una colonna denominataid
.
Se l'oggetto feature contiene altri membri non elencati qui, BigQuery li converte direttamente in colonne di tabella.
Puoi caricare un file GeoJSON delimitato da nuova riga utilizzando il comando bq
load
dello strumento a riga di comando bq, come segue:
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 di un file locale o un URI di Cloud Storage.
L'esempio precedente abilita il rilevamento automatico dello schema. Per un maggiore controllo su come BigQuery converte i valori all'interno dell'oggetto properties
, puoi fornire invece uno schema esplicito. Per ulteriori informazioni, consulta la sezione 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.
Secondo quanto definito nel documento RFC 7946,
una struttura di dati GeoJSON completa è 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
a livello di directory principale e suddividendo i singoli oggetti delle caratteristiche in righe separate. Ad esempio, il seguente comando utilizza lo strumento a riga di comando jq
per suddividere un file GeoJSON in 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'opzione format
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 geometrici di GeoJSON
L'analisi geospaziale supporta il caricamento di singoli oggetti di geometria GeoJSON 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 di geometria GeoJSON.
Per caricare questo tipo di dati GeoJSON in BigQuery, fornisci uno schema che specifichi una colonna GEOGRAPHY
per i dati GeoJSON. Devi fornire lo schema manualmente. Altrimenti, se il rilevamento automatico è abilitato, BigQuery carica i dati come valore STRING
.
L'analisi geospaziale non supporta il caricamento di oggetti di caratteristiche o raccolte di caratteristiche di GeoJSON utilizzando questo approccio. Se devi caricare oggetti di caratteristiche, valuta la possibilità di usare file GeoJSON delimitato da nuova riga.
Per trasmettere 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
Prima di provare questo esempio, segui le istruzioni per la configurazione di Python nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per maggiori informazioni, consulta la documentazione di riferimento dell'API Python di BigQuery.
Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Puoi anche convertire un oggetto geometrico GeoJSON in un valore GEOGRAPHY
utilizzando
la funzione ST_GEOGFROMGEOJSON
. Ad esempio, puoi archiviare le geometrie come valori STRING
ed eseguire una query che chiama ST_GEOGFROMGEOJSON
.
Coordina sistemi e confini
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 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 sufficientemente piccole da poter 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 puoi controllare direttamente il processo di tassellazione.
Per caricare aree geografiche con bordi sferici, utilizza WKT. Per caricare aree geografiche con bordi planari, spesso chiamati geometrie, è più semplice utilizzare GeoJSON. Tuttavia,
se i dati geometrici sono già in formato WKT, un'altra opzione è caricarli
come tipo STRING
e poi 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 dell'area geografica (anziché la geometria) da WKT, oppure ipotizza bordi planari.
Le coordinate devono essere per prima la longitudine e poi la latitudine. Se l'area geografica ha segmenti o bordi lunghi, questi devono essere tassellati, perché l'analisi geospaziale li interpreta come geodetiche sferiche, che potrebbero non corrispondere al sistema di coordinate in cui hanno avuto origine i dati.
Orientamento poligono
In una sfera, ogni poligono ha un poligono complementare. Ad esempio, un poligono che descrive i continenti della Terra avrà un poligono complementare che descrive gli oceani terrestri. Poiché i due poligoni sono descritti dagli stessi anelli di confine, sono necessarie delle regole per risolvere l'ambiguità attorno a cui i due poligoni vengono descritti da una determinata stringa WKT.
Quando carichi stringhe WKT e WKB dai file o utilizzando l'importazione di flussi di dati, l'analisi geospaziale presuppone che i poligoni nell'input siano orientati come segue: Se attraversi il confine del poligono seguendo l'ordine dei vertici di input, l'interno del poligono è a sinistra. L'analisi geospaziale utilizza la stessa regola quando esporta oggetti geografici in stringhe WKT e WKB.
Se usi 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 un poligono con l'area più piccola. Questo è il comportamento predefinito.TRUE
: utilizza la regola di orientamento verso 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 genera
un output conforme agli standard. Ad esempio, quando esporti dati da PostGIS, puoi utilizzare la funzione PostGIS ST_MakeValid
per standardizzare l'output.
In alternativa, importa i dati come testo e poi convertili chiamando
ST_GEOGFROMTEXT
o ST_GEOGFROMGEOJSON
con il parametro make_valid
. Quando make_valid
è TRUE
, queste funzioni
tentano di riparare i poligoni non validi.
Per trovare o ignorare i dati formattati in modo errato, utilizza il prefisso della funzione SAFE
per generare 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. Sono inclusi il suffisso "Z" in formato WKT e le coordinate di altitudine nel formato GeoJSON.
- Sistemi di riferimento lineari. È incluso il suffisso "M" in formato WKT.
- Oggetti di geometria WKT diversi dalle primitive di geometria o dalle geometrie multiparte. In particolare, l'analisi geospaziale supporta solo punti, multipunto, LineString, MultiLineString, Polygon, MultiPolygon e GeometryCollection.
Consulta
ST_GeogFromGeoJson
e
ST_GeogFromText
per i vincoli specifici dei formati di input GeoJson e WKT.
Caricare i dati geospaziali di Google Earth Engine
Google Earth Engine è una piattaforma di dati geospaziali che compila e analizza informazioni provenienti da immagini satellitari e di osservazione terrestre utilizzando dati raster, in cui i dati sono organizzati su una griglia di celle che rappresentano informazioni su immagini digitali. Mentre BigQuery funziona principalmente con dati tabulari, gli utenti possono utilizzare i propri 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, vedi 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, ad esempio ST_GeogPoint
.
Ad esempio, se disponi di due colonne DOUBLE
per longitudine e latitudine, puoi
creare una colonna per l'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 geografici.
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
colonne GEOGRAPHY
codificate come stringhe WKT o GeoJSON.
Partizionamento e clustering dei dati geospaziali
Puoi eseguire il partizionamento e il cluster delle 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 organizzata in cluster in base alla colonna GEOGRAPHY
.
In genere questo migliora le prestazioni delle query e potrebbe ridurre i costi. Un predicato spaziale chiama una funzione geografica 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 di JOIN con dati spaziali
I JOIN spaziali sono unioni di due tabelle con una funzione geografica dei predicati 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 hanno un rendimento migliore quando i dati geografici sono persistenti. L'esempio precedente crea i valori geografici nella query. Consente di archiviare i valori geografici 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 in cui 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 di GoogleSQL:
I join spaziali non sono ottimizzati:
- Per i join LEFT, RIGHT o FULL OUTER
- Nei casi che coinvolgono l'adesione di ANTI
- Quando il predicato spaziale viene negato
Un JOIN che utilizza il predicato ST_DWithin
viene ottimizzato solo quando il parametro della distanza è un'espressione costante.
Esportazione dei dati spaziali
Quando esporti dati spaziali da BigQuery, i valori della colonna 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 delle colonne 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 saranno i seguenti:
{ "type": "LineString", "coordinates": [ [1, 1], [1.99977145571783, 1.50022838764041], [2.49981908082299, 1.75018082434274], [3, 2] ] }
La linea GeoJSON ha due punti aggiuntivi. L'analisi geospaziale aggiunge questi punti in modo che la linea GeoJSON segua molto sul suolo lo stesso percorso della linea originale.
Passaggi successivi
- Per iniziare a utilizzare l'analisi geospaziale, consulta Introduzione all'analisi geospaziale per analisti di dati.
- Per scoprire di più sulle opzioni di visualizzazione per l'analisi geospaziale, consulta Visualizzazione di dati geospaziali.
- Per la documentazione sulle funzioni GoogleSQL nell'analisi geospaziale, consulta Funzioni geografiche in GoogleSQL.