Trabaja con datos geoespaciales en Bigtable

En esta página, se describe cómo almacenar y consultar datos geoespaciales en Bigtable con las funciones geográficas de GoogleSQL. Los datos geoespaciales incluyen representaciones geométricas de la superficie de la Tierra en forma de puntos, líneas y polígonos. Puedes agrupar estas entidades en colecciones para hacer un seguimiento de ubicaciones, rutas o cualquier otra área de interés.

Operaciones básicas

Las funciones geográficas de GoogleSQL dedicadas permiten que Bigtable optimice el rendimiento de tu base de datos para los cálculos geoespaciales y los tiempos de consulta. En los ejemplos de uso básico, usamos una tabla tourist_points_of_interest con una familia de columnas llamada poi_data.

	poi_data
clave de fila	nombre	ubicación
p1	Torre Eiffel	POINT(2.2945 48.8584)

Escribe datos geográficos

Para escribir datos geoespaciales en columnas de Bigtable, debes proporcionar representaciones de cadenas de entidades geográficas en los formatos GeoJSON o texto conocido (WKT). Bigtable almacena los datos subyacentes como bytes sin procesar, por lo que puedes proporcionar cualquier combinación de entidades en una sola columna (como Points, LineStrings, Polygons, MultiPoints, MultiLineStrings, o MultiPolygons). El tipo de entidad geoespacial que proporciones es importante en el momento de la consulta, ya que debes usar la función geográfica adecuada para interpretar los datos.

Por ejemplo, para agregar algunas atracciones a la tabla tourist_points_of_interest, puedes usar la herramienta de CLI cbt de la siguiente manera:

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

Consulta datos geográficos

Puedes consultar la tabla tourist_points_of_interest con GoogleSQL en Bigtable Studio y filtrar los resultados con funciones geográficas. Por ejemplo, para encontrar todas las atracciones turísticas en un radio de 1,500 metros de Trocadéro, puedes usar la ST_DWITHIN función:

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

El resultado sería similar al siguiente:

Captura de pantalla que muestra la tabla tourist_points_of_interest en Bigtable Studio con los resultados de la consulta filtrados con la función geográfica ST_DWITHIN.

La columna location muestra la representación textual original de los datos proporcionados en la operación de escritura, pero Bigtable la almacena como bytes. En este ejemplo, aunque Place Charles de Gaulle se define como un polígono que se extiende más allá del límite de 1,500 metros, se incluye en los resultados de la consulta. Esto se debe a que la función ST_DWITHIN muestra el valor verdadero si cualquier punto contenido en una entidad geoespacial está dentro de la distancia esperada.

Uso avanzado

En las siguientes secciones, se describe cómo usar funciones geográficas para situaciones complejas, como el análisis de geocercas. En la sección Optimización de consultas , se explica cómo usar vistas materializadas continuas para mejorar el rendimiento.

Para ilustrar los usos avanzados de las funciones geográficas en Bigtable, imagina una situación en la que eres un investigador que supervisa el comportamiento de los pingüinos emperadores que viven en el lado sur de la isla Snow Hill.

Tu equipo proporciona cientos de pings de ubicación para cada pingüino, por lo que creas la tabla penguin_movements para almacenarlos:

	penguin_details
clave de fila	penguin_id	timestamp	ubicación
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)

Ejemplo: Encuentra quién cruzó la geocerca

Durante la investigación, observas el comportamiento de los pingüinos alrededor de un área de alimentación específica. Las áreas definidas con límites virtuales se conocen comúnmente como geocercas. El área de alimentación se puede considerar un área con geocerca. Quieres saber si algún pingüino visitó el área de alimentación el 3 de diciembre de 2025. Para responder la pregunta, usa la ST_CONTAINS función de la siguiente manera:

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

Obtendrás resultados similares a los siguientes:

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

Ejemplo: Transforma puntos en rutas

Para visualizar mejor cómo viajan los pingüinos, decides organizar los pings de ubicación individuales en LineStrings con la ST_MAKELINE función de la siguiente manera:

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 esta consulta, puedes obtener estadísticas más avanzadas para tu investigación. Expande las siguientes secciones para ver ejemplos de consultas y resultados.

¿Cuál es la distancia de viaje diaria de cada pingüino?

Para calcular la distancia que recorre cada pingüino todos los días, puedes usar la ST_LENGTH función.

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;

Esta consulta muestra un resultado similar al siguiente:

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

¿Qué parte de la ruta diaria ocurre dentro del área con geocerca?

Para responder esta pregunta, filtra los resultados con la ST_INTERSECTION función para verificar qué partes de la ruta del pingüino se cruzan con el área con geocerca. Luego, la función ST_LENGTH puede ayudar a calcular la proporción de movimiento dentro de las áreas de alimentación con respecto a la distancia total recorrida ese día.

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

Esta consulta muestra un resultado similar al siguiente:

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

Optimiza las consultas con vistas materializadas continuas

Las consultas geoespaciales en conjuntos de datos grandes pueden ser lentas porque pueden requerir el análisis de muchas filas. Bigtable no admite índices geoespaciales dedicados, pero puedes mejorar el rendimiento de las consultas con vistas materializadas continuas.

Para crear una clave de fila única para esas vistas, puedes transformar el GEOGRAPHY tipo en una celda S2. S2 es una biblioteca que te permite realizar trigonometría compleja para la geometría de esferas. Calcular distancias basadas en coordenadas geográficas puede ser costoso desde el punto de vista computacional. Las celdas S2 pueden representar un área específica en la superficie de la Tierra como un número entero de 64 bits, lo que las hace ideales para la indexación geoespacial con vistas materializadas continuas.

Para definir una celda S2, llama a la función S2_CELLIDFROMPOINT(location, level) y proporciona el argumento level de granularidad. Este argumento es un número entre 0 y 30 y define el tamaño de cada celda: cuanto mayor sea el número, más pequeña será la celda.

En el ejemplo de investigación de pingüinos, puedes crear una vista materializada continua indexada en la ubicación y la marca de tiempo de cada pingüino:

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

Consultar esta vista para responder la pregunta ¿Quién cruzó la geocerca? se vuelve mucho más eficiente con un enfoque de dos fases. Primero, usas el índice cell_id en la vista materializada continua para encontrar rápidamente las filas candidatas dentro de la vecindad general y, luego, aplicas la función ST_CONTAINS precisa en el conjunto de datos reducido:

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

Limitaciones

Bigtable no admite las siguientes funciones cuando se trabaja con datos geográficos:

Geometrías tridimensionales. Esto incluye el sufijo "Z" en el formato WKT y la coordenada de altitud en el formato GeoJSON.
Sistemas de referencia lineal. Esto incluye el sufijo "M" en el formato WKT.
Objetos de geometría WKT aparte de las primitivas de geometría y las geometrías multiparte. En particular, Bigtable solo admite Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon y GeometryCollection.
No se admite la función ST_CLUSTERDBSCAN.

Para conocer las restricciones específicas de los formatos de entrada GeoJson y WKT, consulta ST_GEOGFROMGEOJSON y ST_GEOGFROMTEXT.

Próximos pasos

Obtén más información sobre las funciones geográficas disponibles. Consulta Descripción general de las funciones geográficas de GoogleSQL.