Utiliser des données géospatiales dans Bigtable

Cette page explique comment stocker et interroger des données géospatiales dans Bigtable à l'aide des fonctions de géographie GoogleSQL. Les données géospatiales incluent des représentations géométriques de la surface de la Terre sous la forme de points, de lignes et de polygones. Vous pouvez regrouper ces entités dans des collections pour suivre des lieux, des itinéraires ou d'autres zones d'intérêt.

Opérations de base

Les fonctions de géographie GoogleSQL dédiées permettent à Bigtable d'optimiser les performances de votre base de données pour les calculs et les temps de requête géospatiaux. Dans les exemples d'utilisation de base, nous utilisons une table tourist_points_of_interest avec une famille de colonnes appelée poi_data.

	poi_data
clé de ligne	nom	emplacement
p1	Tour Eiffel	POINT(2.2945 48.8584)

Écrire des données géographiques

Pour écrire des données géospatiales dans des colonnes Bigtable, vous devez fournir des représentations sous forme de chaîne des entités géographiques aux formats GeoJSON ou Well-Known Text (WKT). Bigtable stocke les données sous-jacentes sous forme d'octets bruts. Vous pouvez donc fournir n'importe quelle combinaison d'entités dans une même colonne (par exemple, Points, LineStrings, Polygons, MultiPoints, MultiLineStrings ou MultiPolygons). Le type d'entité géospatiale que vous fournissez est important au moment de la requête, car vous devez utiliser la fonction géographique appropriée pour interpréter les données.

Par exemple, pour ajouter des attractions à la table tourist_points_of_interest, vous pouvez utiliser l'outil CLI cbt comme suit :

Avec 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)'

Avec 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] ]] }'

Interroger les données géographiques

Vous pouvez interroger la table tourist_points_of_interest avec GoogleSQL dans Bigtable Studio et filtrer les résultats avec des fonctions de géographie. Par exemple, pour trouver toutes les attractions touristiques situées à moins de 1 500 mètres du Trocadéro, vous pouvez utiliser la fonction 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);

Le résultat devrait ressembler à ceci :

Capture d'écran montrant la table tourist_points_of_interest dans Bigtable Studio
avec les résultats de la requête filtrés avec la fonction géographique ST_DWITHIN.

La colonne location affiche la représentation textuelle d'origine des données fournies dans l'opération d'écriture, mais Bigtable les stocke sous forme d'octets. Dans cet exemple, même si Place Charles de Gaulle est défini comme un polygone qui s'étend au-delà de la limite de 1 500 mètres, il est inclus dans les résultats de la requête. En effet, la fonction ST_DWITHIN renvoie "true" si un point contenu dans une entité géospatiale se trouve à la distance attendue.

Utilisation avancée

Les sections suivantes décrivent comment utiliser les fonctions géographiques pour des scénarios complexes tels que l'analyse du géorepérage. La section Optimisation des requêtes explique comment utiliser les vues matérialisées continues pour améliorer les performances.

Pour illustrer les utilisations avancées des fonctions géographiques dans Bigtable, imaginez un scénario dans lequel vous êtes un chercheur qui surveille le comportement des manchots empereurs vivant sur le côté sud de l'île Snow Hill.

Votre équipement fournit des centaines de pings de localisation pour chaque pingouin. Vous créez donc la table penguin_movements pour les stocker :

	penguin_details
clé de ligne	penguin_id	timestamp	emplacement
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)

Exemple : Trouver qui a franchi la zone géographique

Au cours de la recherche, vous observez le comportement des pingouins autour d'un lieu d'alimentation spécifique. Les zones définies par des limites virtuelles sont communément appelées géorepères. La zone d'alimentation peut être considérée comme une zone géorepérée. Vous souhaitez savoir si des manchots ont visité la zone d'alimentation le 3 décembre 2025. Pour répondre à la question, vous utilisez la fonction ST_CONTAINS, comme suit :

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))
      )

Vous obtenez des résultats semblables à ceux-ci :

+------------+----------------------+------------------------+
| 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 |
+------------+----------------------+------------------------+

Exemple : Transformer des points en itinéraires

Pour mieux visualiser les déplacements des manchots, vous décidez d'organiser les pings de localisation individuels en LineStrings à l'aide de la fonction ST_MAKELINE comme suit :

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

Cette requête vous permet d'obtenir des insights plus avancés pour vos recherches. Développez les sections suivantes pour voir des exemples de requêtes et de résultats.

Quelle est la distance parcourue chaque jour par chaque manchot ?

Pour calculer la distance parcourue par chaque pingouin chaque jour, vous pouvez utiliser la fonction 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;

Cette requête renvoie un résultat semblable à celui-ci :

+------------+------------+-----------------------+
| 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                |
+------------+------------+-----------------------+

Quelle part de l'itinéraire quotidien se trouve dans la zone de géorepérage ?

Pour répondre à cette question, vous devez filtrer les résultats avec la fonction ST_INTERSECTION afin de vérifier quelles parties de l'itinéraire du pingouin croisent la zone géorepérée. La fonction ST_LENGTH peut ensuite aider à calculer le ratio de déplacement dans les zones d'alimentation par rapport à la distance totale parcourue ce jour-là.

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))'))

Cette requête renvoie un résultat semblable à celui-ci :

+------------+------------+----------------------+
| 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                |
+------------+------------+----------------------+

Optimiser les requêtes avec les vues matérialisées continues

Les requêtes géospatiales sur de grands ensembles de données peuvent être lentes, car elles peuvent nécessiter l'analyse de nombreuses lignes. Bigtable n'est pas compatible avec les index géospatiaux dédiés, mais vous pouvez améliorer les performances de vos requêtes grâce aux vues matérialisées continues.

Pour créer une clé de ligne unique pour ces vues, vous pouvez transformer le type GEOGRAPHY en cellule S2. S2 est une bibliothèque qui vous permet d'effectuer des calculs trigonométriques complexes pour la géométrie sphérique. Le calcul des distances en fonction des coordonnées géographiques peut s'avérer coûteux en ressources de calcul. Les cellules S2 peuvent représenter une zone spécifique à la surface de la Terre sous la forme d'un entier de 64 bits, ce qui les rend idéales pour l'indexation géospatiale avec des vues matérialisées continues.

Pour définir une cellule S2, vous devez appeler la fonction S2_CELLIDFROMPOINT(location, level) et fournir l'argument de précision level. Cet argument est un nombre compris entre 0 et 30. Il définit la taille de chaque cellule : plus le nombre est élevé, plus la cellule est petite.

Dans l'exemple de recherche sur les manchots, vous pouvez créer une vue matérialisée continue indexée sur l'emplacement et le code temporel de chaque manchot :

-- 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'interrogation de cette vue pour répondre à la question Qui a franchi la zone géographique ? devient beaucoup plus efficace avec une approche en deux phases. Vous utilisez d'abord l'index cell_id dans la vue matérialisée continue pour trouver rapidement les lignes candidates à proximité générale, puis vous appliquez la fonction précise ST_CONTAINS sur l'ensemble de données réduit :

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);

Limites

Bigtable n'est pas compatible avec les fonctionnalités suivantes lorsque vous travaillez avec des données géographiques :

Les géométries tridimensionnelles. Cela inclut le suffixe "Z" au format WKT et les coordonnées d'altitude au format GeoJSON.
Les systèmes de référence linéaires. Cela inclut le suffixe "M" au format WKT.
Les objets de géométrie WKT autres que les primitives de géométrie ou les géométries en plusieurs parties. Plus précisément, Bigtable n'accepte que Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon et GeometryCollection.
La fonction ST_CLUSTERDBSCAN n'est pas acceptée.

Pour connaître les contraintes spécifiques aux formats d'entrée GeoJson et WKT, consultez ST_GEOGFROMGEOJSON et ST_GEOGFROMTEXT.

Étape suivante

En savoir plus sur les fonctions de géographie disponibles Consultez la présentation des fonctions de géographie GoogleSQL.