Trabalhar com dados geoespaciais no Bigtable

Nesta página, descrevemos como armazenar e consultar dados geoespaciais no Bigtable usando as funções geográficas do GoogleSQL. Os dados geoespaciais incluem representações geométricas da superfície da Terra na forma de pontos, linhas e polígonos. É possível agrupar essas entidades em coleções para rastrear locais, rotas ou outras áreas de interesse.

Operações básicas

Com as funções geográficas dedicadas do GoogleSQL, o Bigtable otimiza a performance do banco de dados para cálculos geoespaciais e tempos de consulta. Nos exemplos de uso básico, usamos uma tabela tourist_points_of_interest com um grupo de colunas chamado poi_data.

	poi_data
chave de linha	nome	local
p1	Torre Eiffel	POINT(2.2945 48.8584)

Gravar dados geográficos

Para gravar dados geoespaciais em colunas do Bigtable, é necessário fornecer representações de string de entidades geográficas nos formatos GeoJSON ou texto conhecido (WKT). O Bigtable armazena os dados subjacentes como bytes brutos. Assim, é possível fornecer qualquer combinação de entidades em uma única coluna (como Points, LineStrings, Polygons, MultiPoints, MultiLineStrings ou MultiPolygons). O tipo de entidade geoespacial fornecida é importante no momento da consulta porque é necessário usar a função de geografia adequada para interpretar os dados.

Por exemplo, para adicionar algumas atrações à tabela tourist_points_of_interest, use a ferramenta CLI cbt da seguinte maneira:

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

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

Consultar dados geográficos

É possível consultar a tabela tourist_points_of_interest com o GoogleSQL no Bigtable Studio e filtrar os resultados com funções geográficas. Por exemplo, para encontrar todas as atrações turísticas em um raio de 1.500 metros do Trocadéro, use a função 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);

O resultado seria semelhante a este:

Captura de tela mostrando a tabela tourist_points_of_interest no Bigtable Studio com resultados de consulta filtrados com a função geográfica ST_DWITHIN.

A coluna location mostra a representação textual original dos dados fornecidos na operação de gravação, mas o Bigtable os armazena como bytes. Neste exemplo, mesmo que Place Charles de Gaulle seja definido como um polígono que se estende além do limite de 1.500 metros, ele é incluído nos resultados da consulta. Isso acontece porque a função ST_DWITHIN retorna "true" se qualquer ponto contido em uma entidade geoespacial estiver dentro da distância esperada.

Uso avançado

As seções a seguir descrevem como usar funções geográficas para cenários complexos, como análise de geocercas. A seção Otimização de consultas explica como usar visualizações materializadas contínuas para melhorar a performance.

Para ilustrar usos avançados de funções geográficas no Bigtable, imagine um cenário em que você é um pesquisador monitorando o comportamento de pinguins-imperadores que vivem no lado sul da Ilha Snow Hill.

Seu equipamento fornece centenas de pings de localização para cada pinguim, então você cria a tabela penguin_movements para armazená-los:

	penguin_details
chave de linha	penguin_id	timestamp	local
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)

Exemplo: encontrar quem cruzou a geocerca

Durante a pesquisa, você observa o comportamento dos pinguins em torno de um local de alimentação específico. As áreas definidas com limites virtuais são conhecidas como geocercas. O local de alimentação pode ser considerado uma área geocercada. Você quer saber se algum pinguim visitou o local de alimentação em 3 de dezembro de 2025. Para responder à pergunta, use a função ST_CONTAINS desta forma:

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

Você vai receber resultados parecidos com este:

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

Exemplo: transformar pontos em rotas

Para visualizar melhor como os pinguins viajam, você decide organizar pings de localização individuais em LineStrings usando a função ST_MAKELINE da seguinte maneira:

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

Com essa consulta, você pode receber insights mais avançados para sua pesquisa. Abra as seções a seguir para ver exemplos de consultas e resultados.

Qual é a distância de percurso diário de cada pinguim?

Para calcular a distância percorrida por cada pinguim todos os dias, use a função 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;

Essa consulta retorna um resultado semelhante a este:

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

Quanto do trajeto diário ocorre dentro da área delimitada por fronteira geográfica virtual?

Para responder a essa pergunta, filtre os resultados com a função ST_INTERSECTION para verificar quais partes da rota do pinguim se cruzam com a área delimitada por geocerca. Em seguida, a função ST_LENGTH pode ajudar a calcular a proporção de movimento dentro das áreas de alimentação em relação à distância total percorrida naquele dia.

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

Essa consulta retorna um resultado semelhante a este:

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

Otimizar consultas com visualizações materializadas contínuas

Consultas geoespaciais em grandes conjuntos de dados podem ser lentas porque exigem a verificação de muitas linhas. O Bigtable não oferece suporte a índices geoespaciais dedicados, mas é possível melhorar o desempenho das consultas com visualizações materializadas contínuas.

Para criar uma chave de linha exclusiva para essas visualizações, transforme o tipo GEOGRAPHY em uma célula S2. O S2 é uma biblioteca que permite realizar trigonometria complexa para geometria esférica. Calcular distâncias com base em coordenadas geográficas pode ser caro do ponto de vista computacional. As células S2 podem representar uma área específica na superfície da Terra como um número inteiro de 64 bits, o que as torna ideais para indexação geoespacial com visualizações materializadas contínuas.

Para definir uma célula S2, chame a função S2_CELLIDFROMPOINT(location, level) e forneça o argumento de granularidade level. Esse argumento é um número entre 0 e 30 e define o tamanho de cada célula: quanto maior o número, menor a célula.

No exemplo da pesquisa de pinguins, você pode criar uma visualização materializada contínua indexada no local e no carimbo de data/hora de cada pinguim:

-- 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 essa visualização para responder à pergunta Quem cruzou o geofence? se torna muito mais eficiente com uma abordagem de duas fases. Primeiro, use o índice cell_id na visualização materializada contínua para encontrar rapidamente linhas candidatas na vizinhança geral e, em seguida, aplique a função ST_CONTAINS precisa no conjunto de dados reduzido:

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

Limitações

O Bigtable não oferece suporte aos seguintes recursos ao trabalhar com dados geográficos:

Geometrias tridimensionais. Isso inclui o sufixo "Z" no formato WKT e a coordenada de altitude no formato GeoJSON.
Sistemas de referência linear. Isso inclui o sufixo "M" no formato WKT.
Objetos de geometria WKT que não sejam primitivos de geometria ou geometria de várias partes. Especificamente, o Bigtable é compatível apenas com Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon e GeometryCollection.
A função ST_CLUSTERDBSCAN não é compatível.

Para restrições específicas dos formatos de entrada GeoJson e WKT, consulte ST_GEOGFROMGEOJSON e ST_GEOGFROMTEXT.

A seguir

Saiba mais sobre as funções geográficas disponíveis. Consulte Visão geral das funções geográficas do GoogleSQL.