Lavorare con i dati geospaziali

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

I tipi comuni di oggetti quando si lavora con dati geospaziali includono:

  • 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.
  • Una caratteristica spaziale rappresenta un oggetto spaziale logico. Combina un con altri attributi specifici dell'applicazione.
  • Una raccolta di caratteristiche spaziali è un insieme di caratteristiche spaziali.

In BigQuery, GEOGRAPHY il tipo di dati rappresenta un valore o una raccolta di geometria. Per rappresentare elementi spaziali, crea una tabella con una colonna GEOGRAPHY per il parametro Geometria più colonne aggiuntive per gli attributi. Ogni riga della tabella è uno spazio e l'intera tabella rappresenta una raccolta di caratteristiche spaziali.

Il tipo di dati GEOGRAPHY descrive un insieme di punti sulla superficie terrestre. R insieme di punti è un insieme di punti, linee e poligoni sul WGS84 sferoide di riferimento, con bordi geodetici. Puoi utilizzare il tipo di dati GEOGRAPHY chiamando uno dei team SQL funzioni geografiche.

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 contenente valori di longitudine e latitudine e quindi utilizza 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 per descrivere singole forme geometriche utilizzando punti, linee poligoni con fori opzionali o un insieme di punti, linee o poligoni. WKB è la versione binaria del formato WKT. WKB può essere codificato in formato esadecimale per i formati 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 file container come 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 specifica 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 saperne di più sul caricamento dei dati in BigQuery, consulta Introduzione al caricamento dei dati.

Per creare un flusso di dati WKT in una tabella BigQuery esistente con un 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 nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Python BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura 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 sui flussi di dati in BigQuery, vedi Flusso di dati in BigQuery.

Puoi anche convertire una stringa di testo WKT in un valore GEOGRAPHY utilizzando il metodo ST_GeogFromText personalizzata.

Caricamento dei dati GeoJSON

GeoJSON è un formato basato su JSON per geometrie e caratteristiche spaziali. Ad esempio: quanto segue 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 un'unione di punti, linee e poligoni con fori opzionali.
  • Oggetti caratteristica. Un oggetto caratteristica contiene una geometria più coppie nome/valore, 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:

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 feature GeoJSON è un oggetto JSON con il membri seguenti:

  • type. Per gli oggetti caratteristiche, il valore deve essere Feature. BigQuery convalida il valore, ma non lo include nel schema della tabella.

  • geometry. Il valore è un oggetto GeoJSON Geometry o null. BigQuery converte questo membro in un valore GEOGRAPHY.

  • properties. Il valore è qualsiasi oggetto JSON o nullo. Se il valore non è null, BigQuery carica ogni membro dell'oggetto JSON come una colonna della tabella separata. Per ulteriori informazioni su come BigQuery analizza i tipi di dati JSON, consulta Dettagli sul 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 caratteristica contiene altri membri non elencati qui, allora BigQuery converte questi membri direttamente in colonne delle tabelle.

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 di un URI Cloud Storage.

L'esempio precedente abilita rilevamento automatico dello schema. Per un maggiore controllo in che modo BigQuery converte i valori all'interno dell'oggetto properties puoi fornire uno schema esplicito. Per ulteriori informazioni, vedi Specifica 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 e colonne separate, non una singola colonna nidificata.

Come definito da RFC 7946, Una struttura di dati GeoJSON completa è un singolo oggetto JSON. Molti sistemi esportano Dati GeoJSON come un singolo oggetto FeatureCollection che contiene tutti le geometrie. Per caricare questo formato in BigQuery, devi convertire rimuovendo l'oggetto FeatureCollection a livello di directory principale e dividendo il i singoli oggetti delle caratteristiche in linee separate. Ad esempio, usa lo strumento a riga di comando jq per suddividere un file GeoJSON in una nuova riga formato delimitato:

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 il cluster utilizza la classe CREATE EXTERNAL TABLE l'istruzione DDL. Nella clausola OPTIONS, imposta l'opzione format su NEWLINE_DELIMITED_JSON e l'opzione json_extension per 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 che 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 un schema che specifica una colonna GEOGRAPHY per Dati GeoJSON. Devi fornire lo schema manualmente. Altrimenti, se il rilevamento automatico viene attivato, BigQuery carica i dati come valore STRING.

L'analisi geospaziale non supporta il caricamento di caratteristiche o oggetti GeoJSON raccolte che utilizzano questo approccio. Se devi caricare oggetti caratteristiche, prendi in considerazione l'utilizzo di file GeoJSON delimitato da nuova riga.

Per trasmettere dati GeoJSON in una tabella BigQuery esistente con un 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 nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Python BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura 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 di geometria GeoJSON in un valore GEOGRAPHY utilizzando il ST_GEOGFROMGEOJSON personalizzata. Ad esempio, puoi archiviare le geometrie come valori STRING e poi esegui 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 forma geodetica sferica tra due endpoint. (ovvero, i bordi sono il percorso più breve sulla superficie una sfera.)

Il formato WKT non fornisce un sistema di coordinate. Quando carichi i 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 i valori aree geografiche sono talmente piccole che la differenza tra sferici e planari possono essere ignorati.

GeoJSON utilizza esplicitamente le coordinate WGS84 con bordi planari. Durante il caricamento 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 rimane entro 10 metri dalla linea originale. Questo 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 le 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 è caricare il file dati come tipo STRING, quindi usa ST_GEOGFROMTEXT per convertire 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 utilizzati dai dati di origine. La maggior parte dei sistemi supporta esplicitamente l'analisi l'area geografica (in contrapposizione alla geometria) da WKT oppure presuppongono bordi planari.

Le coordinate devono essere per prima la longitudine e poi la latitudine. Se l'area geografica ha segmenti o bordi lunghi, allora devono essere tassellati, perché l'analisi geospaziale le interpreta come geodetiche sferiche, corrispondono al sistema di coordinate da 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 avrebbe un poligono complementare che descrive gli oceani della Terra. Poiché i due poligoni sono descritti gli stessi anelli di confine, sono necessarie delle regole per risolvere l'ambiguità dei due poligoni viene descritto 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 nell'ordine di input. vertici, l'interno del poligono è a sinistra. Utilizzi dell'analisi geospaziale la stessa regola quando esporti oggetti geografici in stringhe WKT e WKB.

Se utilizzi la ST_GeogFromText per convertire una stringa WKT in un valore GEOGRAPHY, il valore 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 determinato senza ambiguità, anche se l'input non segue l'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, potrebbero verificarsi errori di conversione a causa di dati WKT o GeoJSON non validi. Per Ad esempio, un errore del tipo 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 conforme agli standard. Ad esempio, quando esporti i dati da PostGIS, puoi utilizzare la funzione ST_MakeValid di PostGIS per standardizzare l'output. In alternativa, importa i dati come testo e poi convertili richiamando ST_GEOGFROMTEXT oppure ST_GEOGFROMGEOJSON con il parametro make_valid. Quando make_valid è TRUE, queste funzioni tentare di riparare poligoni non validi.

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

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 campi geospaziali formati:

  • Geometrie tridimensionali. È inclusa la "Z" in WKT e le coordinate di altitudine nel formato GeoJSON.
  • Sistemi di riferimento lineari. È inclusa la "M" in formato WKT.
  • Oggetti di geometria WKT diversi dalle primitive di geometria o dalle geometrie multiparte. In particolare, l'analisi geospaziale supporta solo 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 le 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 le immagini digitali. Mentre BigQuery funziona principalmente con dati vettoriali tabulari; gli utenti possono usare dei propri dati BigQuery insieme ai dati raster Earth Engine di incorporare set di dati sia vettoriali che raster nei propri per i 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 per trasformare i valori in aree geografiche utilizzando SQL funzioni geografiche ad esempio ST_GeogPoint. Ad esempio, se hai due colonne DOUBLE per longitudine e latitudine, puoi creare una colonna di 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 convertire i dati in un formato file di input supportato, come un file CSV, con GEOGRAPHY colonne codificate come stringhe WKT o GeoJSON.

Partizionamento e clustering dei dati geospaziali

Puoi eseguire il partizionamento tabelle cluster che contengono GEOGRAPHY colonne. Puoi usare una colonna GEOGRAPHY come colonna di clustering, ma non puoi utilizza una colonna GEOGRAPHY come colonna di partizionamento.

Se archivi dati di GEOGRAPHY in una tabella e le query filtrano i dati utilizzando una predicato spaziale, assicurati che la tabella sia raggruppata in cluster in base alla colonna GEOGRAPHY. In genere questo migliora le prestazioni delle query e potrebbe ridurre i costi. Uno spazio il predicato chiama una funzione geografica booleana e ha una colonna GEOGRAPHY come una degli argomenti. L'esempio seguente mostra un predicato spaziale che utilizza il parametro 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 in la 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 sopra crea i valori geografici nella query. Consente di archiviare i valori geografici di una tabella BigQuery.

Ad esempio, la seguente query recupera le coppie di longitudine, latitudine e le converte in punti geografici. Quando esegui questa query, specifichi un nuovo 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 INNER JOIN e Operatori CROSS JOIN con le seguenti funzioni dei predicati 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, colonna GEOGRAPHY vengono sempre formattati come stringhe WKT. Per esportare i dati in formato GeoJSON, utilizza la ST_AsGeoJSON personalizzata.

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

Ad esempio, la seguente query utilizza ST_AsGeoJSON per convertire i valori GeoJSON alle 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. Aggiunte di analisi geospaziali questi punti in modo che la linea GeoJSON segua significativamente lo stesso percorso suolo come linea originale.

Passaggi successivi