Lavorare con i dati geospaziali in Bigtable

Questa pagina descrive come archiviare ed eseguire query sui dati geospaziali in Bigtable utilizzando le funzioni geografiche GoogleSQL. I dati geospaziali includono rappresentazioni geometriche della superficie terrestre sotto forma di punti, linee e poligoni. Puoi raggruppare queste entità in raccolte per monitorare luoghi, percorsi o altre aree di interesse.

Operazioni di base

Le funzioni geografiche GoogleSQL dedicate consentono a Bigtable di ottimizzare le prestazioni del database per i calcoli spaziali e i tempi di query. Negli esempi di utilizzo di base, utilizziamo una tabella tourist_points_of_interest con una famiglia di colonne denominata poi_data.

	poi_data
chiave di riga	nome	località
p1	Torre Eiffel	POINT(2.2945 48.8584)

Scrivere dati geografici

Per scrivere dati geospaziali nelle colonne Bigtable, devi fornire rappresentazioni di stringhe delle entità geografiche nei formati GeoJSON o Well-Known Text (WKT). Bigtable archivia i dati sottostanti come byte non elaborati, quindi puoi fornire qualsiasi combinazione di entità in una singola colonna (ad esempio Points, LineStrings, Polygons, MultiPoints, MultiLineStrings o MultiPolygons). Il tipo di entità geospaziale che fornisci è importante al momento della query perché devi utilizzare la funzione geografica appropriata per interpretare i dati.

Ad esempio, per aggiungere alcune attrazioni alla tabella tourist_points_of_interest, puoi utilizzare lo strumento CLI cbt nel seguente modo:

Con WKT:

cbt set tourist_points_of_interest p2 poi_data:name='Louvre Museum'
cbt set tourist_points_of_interest p2 poi_data:location='POINT(2.3376 48.8611)'

cbt set tourist_points_of_interest p3 poi_data:name='Place Charles de Gaulle'
cbt set tourist_points_of_interest p3 poi_data:location='POLYGON(2.2941 48.8733, 2.2941 48.8742, 2.2957 48.8742, 2.2958 48.8732, 2.2941 48.8733)'

Con GeoJSON:

cbt set tourist_points_of_interest p2 poi_data:name='Louvre Museum'
cbt set tourist_points_of_interest p2 poi_data:location='{"type": "Point", "coordinates": [2.3376, 48.8611]}'

cbt set tourist_points_of_interest p3 poi_data:name='Place Charles de Gaulle'
cbt set tourist_points_of_interest p3 poi_data:location='{"type": "Polygon", "coordinates": [[ [2.2941, 48.8733], [2.2941, 48.8742], [2.2957, 48.8742], [2.2958, 48.8732], [2.2941, 48.8733] ]] }'

Eseguire query sui dati geografici

Puoi eseguire query sulla tabella tourist_points_of_interest con GoogleSQL in Bigtable Studio e filtrare i risultati con le funzioni geografiche. Ad esempio, per trovare tutte le attrazioni turistiche entro 1500 metri dal Trocadéro, puoi utilizzare la funzione ST_DWITHIN:

SELECT _key, poi_data['name'], poi_data['location']
FROM points_of_interest
WHERE ST_DWITHIN(CAST(poi_data['location'] AS STRING), ST_GEOGPOINT(2.2874, 48.86322), 1500);

Il risultato sarebbe simile al seguente:

Screenshot che mostra la tabella tourist_points_of_interest in Bigtable Studio
con i risultati della query filtrati con la funzione geografica ST_DWITHIN.

La colonna location mostra la rappresentazione testuale originale dei dati forniti nell'operazione di scrittura, ma Bigtable li archivia come byte. In questo esempio, anche se Place Charles de Gaulle è definito come un poligono che si estende oltre il limite di 1500 metri, è incluso nei risultati della query. Questo perché la funzione ST_DWITHIN restituisce true se qualsiasi punto contenuto in un'entità geospaziale si trova entro la distanza prevista.

Utilizzo avanzato

Le sezioni seguenti descrivono come utilizzare le funzioni geografiche per scenari complessi come l'analisi del geofencing. La sezione Ottimizzazione delle query spiega come utilizzare le viste materializzate continue per migliorare le prestazioni.

Per illustrare gli utilizzi avanzati delle funzioni geografiche in Bigtable, immagina uno scenario in cui sei un ricercatore che monitora il comportamento dei pinguini imperatore che vivono sul lato sud dell'isola Snow Hill.

La tua attrezzatura fornisce centinaia di ping di posizione per ogni pinguino, quindi crei la tabella penguin_movements per archiviarli:

	penguin_details
chiave di riga	penguin_id	timestamp	località
ping#123	pen_01	2025-12-06 08:15:22+00	POINT(-57.51 -64.42)
ping#124	pen_01	2025-12-06 10:22:05+00	POINT(-57.55 -64.43)
ping#125	pen_01	2025-12-07 12:35:45+00	POINT(-57.58 -64.41)
ping#126	pen_02	2025-12-12 06:05:11+00	POINT(-57.49 -64.39)

Esempio: trovare chi ha attraversato il recinto virtuale

Durante la ricerca, osservi il comportamento dei pinguini in una specifica area di alimentazione. Le aree definite con confini virtuali sono comunemente note come geofence. Il terreno di alimentazione può essere considerato un'area recintata. Vuoi scoprire se dei pinguini hanno visitato l'area di alimentazione il 3 dicembre 2025. Per rispondere alla domanda, utilizza la funzione ST_CONTAINS, come segue:

SELECT
  penguin_details['penguin_id'],
  penguin_details['location'],
  penguin_details['timestamp']
FROM
  penguin_movements
WHERE
  penguin_details['timestamp'] >= '2025-12-03 00:00:00 UTC'
  AND penguin_details['timestamp'] < '2025-12-04 00:00:00 UTC'
  -- The feeding ground boundary is defined with a WKT POLYGON entity
  -- Polygon's last point must be equal to its first point to close the loop
  AND ST_CONTAINS(
        ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'),
        ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING))
      )

Ottieni risultati simili ai seguenti:

+------------+----------------------+------------------------+
| penguin_id |       location       |       timestamp        |
+------------+----------------------+------------------------+
| pen_01     | POINT(-57.15 -64.53) | 2025-12-03 08:15:22+00 |
| pen_02     | POINT(-57.18 -64.54) | 2025-12-03 14:30:05+00 |
| pen_10     | POINT(-57.10 -64.52) | 2025-12-03 11:45:10+00 |
+------------+----------------------+------------------------+

Esempio: trasforma i punti in percorsi

Per visualizzare meglio gli spostamenti dei pinguini, decidi di organizzare i singoli ping di posizione in LineStrings utilizzando la funzione ST_MAKELINE nel seguente modo:

SELECT
  penguin_id,
  DATE(timestamp, 'UTC') AS route_date,
  ST_MAKELINE(ARRAY_AGG(location_cast_to_geog)) AS route
FROM (
  -- Sub-expression to sort all location pings by timestamp
  -- This way you make sure individual points can form a realistic route
  SELECT
    penguin_details['penguin_id'] AS penguin_id,
    -- Extract and cast timestamp once
    CAST(CAST(penguin_details['timestamp'] AS STRING) AS TIMESTAMP) AS timestamp,
    -- Extract and cast location once
    ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)) AS location_cast_to_geog
  FROM
    penguin_movements
  ORDER BY
    -- Pre-sorts location pings for the ARRAY_AGG function
    penguin_id, timestamp ASC
)
GROUP BY
  penguin_id,
  route_date

Con questa query, puoi ottenere informazioni più avanzate per la tua ricerca. Espandi le seguenti sezioni per visualizzare query ed esempi di risultati.

Qual è la distanza di percorrenza giornaliera per ogni pinguino?

Per calcolare la distanza percorsa ogni giorno da ciascun pinguino, puoi utilizzare la funzione ST_LENGTH.

SELECT
  penguin_id,
  route_date,
  ST_LENGTH(route) AS daily_distance_meters
FROM (
  -- Subquery to aggregate points into daily routes
  SELECT
    penguin_id,
    DATE(timestamp, 'UTC') AS route_date,
    ST_MAKELINE(ARRAY_AGG(location_cast_to_geog)) AS route
  FROM (
    -- Sub-expression to sort all location pings by timestamp
    -- This way you make sure individual points can form a realistic route
    SELECT
      penguin_details['penguin_id'] AS penguin_id,
      -- Extract and cast timestamp once
      CAST(CAST(penguin_details['timestamp'] AS STRING) AS TIMESTAMP) AS timestamp,
      -- Extract and cast location once
      ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)) AS location_cast_to_geog
    FROM
      penguin_movements
    ORDER BY
      -- Pre-sorts location pings for the ARRAY_AGG function
      penguin_id, timestamp ASC
  )
  GROUP BY
    penguin_id,
    route_date
) AS DailyRoutes;

Questa query restituisce un risultato simile al seguente:

+------------+------------+-----------------------+
| penguin_id | route_date | daily_distance_meters |
+------------+------------+-----------------------+
| pen_01     | 2025-12-02 | 15420.7               |
| pen_03     | 2025-12-03 | 22105.1               |
| pen_32     | 2025-12-03 | 9850.3                |
+------------+------------+-----------------------+

Quanta parte del percorso giornaliero si svolge all'interno dell'area virtualmente recintata?

Per rispondere a questa domanda, devi filtrare i risultati con la funzione ST_INTERSECTION per verificare quali parti del percorso del pinguino si intersecano con l'area recintata. La funzione ST_LENGTH può quindi aiutarti a calcolare il rapporto tra il movimento all'interno delle aree di alimentazione e la distanza totale percorsa quel giorno.

SELECT
  penguin_id,
  route_date,
  ST_LENGTH(ST_INTERSECTION(route, ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'))) / ST_LENGTH(route) AS proportion_in_ground
FROM (
  -- Subquery to aggregate points into daily routes
  SELECT
    penguin_id,
    DATE(timestamp, 'UTC') AS route_date,
    ST_MAKELINE(ARRAY_AGG(location_cast_to_geog)) AS route
  FROM (
    -- Sub-expression to sort all location pings by timestamp
    -- This way you make sure individual points can form a realistic route
    SELECT
      penguin_details['penguin_id'] AS penguin_id,
      -- Extract and cast timestamp once
      CAST(CAST(penguin_details['timestamp'] AS STRING) AS TIMESTAMP) AS timestamp,
      -- Extract and cast location once
      ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)) AS location_cast_to_geog
    FROM
      penguin_movements
    ORDER BY
      -- Pre-sorts location pings for the ARRAY_AGG function
      penguin_id, timestamp ASC
  )
  GROUP BY
    penguin_id,
    route_date
) AS DailyRoutes
WHERE
  ST_INTERSECTS(route, ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'))

Questa query restituisce un risultato simile al seguente:

+------------+------------+----------------------+
| penguin_id | route_date | proportion_in_ground |
+------------+------------+----------------------+
| pen_01     | 2025-12-03 | 0.652                |
| pen_03     | 2025-12-03 | 0.918                |
| pen_32     | 2025-12-04 | 0.231                |
+------------+------------+----------------------+

Ottimizzare le query con le viste materializzate continue

Le query geospaziali su set di dati di grandi dimensioni possono essere lente perché potrebbero richiedere la scansione di molte righe. Bigtable non supporta indici geospaziali dedicati, ma puoi migliorare le prestazioni delle query con le viste materializzate continue.

Per creare una chiave di riga univoca per queste visualizzazioni, puoi trasformare il tipo GEOGRAPHY in una cella S2. S2 è una libreria che ti consente di eseguire calcoli trigonometrici complessi per la geometria della sfera. Il calcolo delle distanze in base alle coordinate geografiche può essere costoso dal punto di vista computazionale. Le celle S2 possono rappresentare un'area specifica sulla superficie terrestre come un numero intero a 64 bit, il che le rende ideali per l'indicizzazione geospaziale con viste materializzate continue.

Per definire una cella S2, chiama la funzione S2_CELLIDFROMPOINT(location, level) e fornisci l'argomento level per la granularità. Questo argomento è un numero compreso tra 0 e 30 e definisce la dimensione di ogni cella: più alto è il numero, più piccola è la cella.

Nell'esempio di ricerca sui pinguini, puoi creare una vista materializzata continua indicizzata in base alla posizione e al timestamp di ogni pinguino:

-- Query used to create the continuous materialized view
SELECT
  -- Create S2 cells for each penguin's location.
  -- Note that the `level` value is the same for each location so that
  -- every cell is the same size. This ensures consistency for your data.
  S2_CELLIDFROMPOINT(ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)), level => 16) AS s2_cell_id,
  penguin_details['timestamp'] AS observation_time,
  penguin_details['penguin_id'] AS penguin_id,
  penguin_details['location'] AS location
FROM
  penguin_movements

L'esecuzione di query su questa visualizzazione per rispondere alla domanda Chi ha attraversato il recinto virtuale? diventa molto più efficiente con un approccio in due fasi. Per prima cosa, utilizza l'indice cell_id nella vista materializzata continua per trovare rapidamente le righe candidate nelle vicinanze generali, quindi applica la funzione ST_CONTAINS accurata al set di dati ridotto:

SELECT
  idx.penguin_id,
  idx.location,
  idx.observation_time
FROM
  penguin_s2_time_index AS idx
WHERE
  -- Part one: use an approximate spatial filter and timestamp filter
  -- for fast scans on continuous materialized view keys.
  -- Use the S2_COVERINGCELLIDS function to create an array of S2 cells
  -- that cover the feeding ground polygon. The `level` argument must be the
  -- same value as the one you used to create the continuous materialized view.
  idx.s2_cell_id IN UNNEST(S2_COVERINGCELLIDS(
    ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'),
    min_level => 16,
    max_level => 16,
    max_cells => 500
  ))
  AND idx.observation_time >= '2025-12-03 00:00:00 UTC'
  AND idx.observation_time < '2025-12-04 00:00:00 UTC'
  -- Part two: use ST_CONTAINS() on the returned set to ensure precision.
  -- S2 cells are squares, so they don't equal arbitrary geofence polygons.
  -- You still need to check if a specific point is contained within the area,
  -- but the filter applies to a smaller data set and is much faster.
  AND ST_CONTAINS(ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'), idx.location);

Limitazioni

Bigtable non supporta le seguenti funzionalità quando utilizzi dati geografici:

Geometrie tridimensionali. Ciò include il suffisso "Z" nel formato WKT e la coordinata di altitudine nel formato GeoJSON.
Sistemi di riferimento lineari. Ciò include il suffisso "M" nel formato WKT.
Oggetti di geometria WKT diversi da primitive geometriche o geometrie multipart. In particolare, Bigtable supporta solo Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon e GeometryCollection.
La funzione ST_CLUSTERDBSCAN non è supportata.

Per i vincoli specifici per i formati di input GeoJSON e WKT, vedi ST_GEOGFROMGEOJSON e ST_GEOGFROMTEXT.

Passaggi successivi

Scopri di più sulle funzioni geografiche disponibili. Consulta la panoramica delle funzioni geografiche GoogleSQL.