Como trabalhar com os dados do BigQuery GIS

Com o BigQuery GIS, é possível analisar dados geográficos no BigQuery. Esses dados são também são conhecidos como dados geoespaciais.

Quando o BigQuery GIS é usado, o SQL padrão se torna compatível com o tipo de dados GEOGRAPHY. O tipo de dados GEOGRAPHY representa um conjunto de pontos na superfície da Terra. O conjunto é composto por pontos, linhas e polígonos com bordas geodésicas no esferoide de referência WGS84.

Para usar o tipo de dados GEOGRAPHY, chame uma das funções geográficas do SQL padrão. A saída das funções geográficas é renderizada como WKT ("well-known text", na sigla em inglês, ou "texto conhecido"). No WKT, o formato usado é longitude primeiro e latitude depois.

Formatos de dados geoespaciais

Pontos únicos na Terra podem ser descritos por apenas um par (longitude, latitude). Para descrever geografias mais complexas, como linhas e polígonos, é possível carregar dados geoespaciais em uma coluna GEOGRAPHY no BigQuery, se os dados estiverem em um dos seguintes formatos compatíveis:

Diferenças entre WKT e GeoJSON

Objetos geométricos versus recursos espaciais e coleções de recursos

Os tipos comuns de objetos ao trabalhar com dados espaciais incluem o seguinte:

  • Uma geometria individual ou um valor de GEOGRAPHY representa uma área de superfície na Terra. É frequentemente descrito com pontos, linhas, polígonos ou uma coleção desses elementos. Uma coleção de geometrias representa a união espacial de todas as formas na coleção.
  • Um recurso espacial representa um objeto espacial lógico. Ele combina geometria com atributos arbitrários adicionais específicos do aplicativo.
  • Uma coleção de recursos espaciais é um conjunto de objetos de recursos.

WKT é um formato de texto para descrever formas geométricas individuais usando pontos, linhas, polígonos com furos opcionais ou uma coleção de pontos, linhas ou polígonos. Por exemplo, um ponto no WKT seria semelhante ao seguinte:

POINT(-121 41)

Para descrever um recurso espacial, o WKT costuma ser incorporado em algum formato de arquivo de contêiner, geralmente um arquivo CSV, ou em uma tabela de banco de dados. Uma linha de arquivo ou de tabela geralmente corresponde ao recurso espacial. O arquivo ou a tabela inteiros correspondem à coleção de recursos.

O WKB é uma versão binária do formato WKT.

O GeoJSON é um formato mais complexo baseado em JSON para geometrias e recursos espaciais. Por exemplo, um ponto no GeoJSON seria semelhante ao seguinte:

{ "type": "Point", "coordinates": [-121,41] }

O GeoJSON é usado para descrever uma das opções a seguir:

  • Um objeto geometry (em inglês) individual. Um único objeto pode ter um formato espacial complexo, descrito como uma união de pontos e linhas, além de polígonos com furos opcionais. Isso é semelhante ao uso do formato WKT.
  • Um objeto Feature (em inglês). Trata-se de um objeto com geometria e propriedades nomeadas arbitrárias extras. Isso é semelhante a uma linha em um arquivo CSV com uma coluna WKT.
  • um objeto de tipo FeatureCollection. Trata-se de um conjunto de objetos parecido com uma tabela de um banco de dados ou com um arquivo CSV com várias linhas e colunas.

O BigQuery GIS só é compatível com objetos geométricos individuais no GeoJSON. No momento, o BigQuery GIS não é compatível com objetos de recursos GeoJSON ou com o formato de arquivo GeoJSON.

Arestas e sistemas de coordenadas

O formato WKT não fornece um sistema de coordenadas. Por isso, o sistema é definido pelo BigQuery GIS. No BigQuery GIS, os pontos WKT são posições na superfície de um esferoide WGS84, expresso como latitude geodésica e longitude. Uma aresta é uma geodésica esférica entre dois endpoints. No GeoJSON, o sistema de coordenadas é composto explicitamente de coordenadas WGS84 com arestas planas.

Para fazer uma conversão entre esses dois tipos de arestas, pontos são adicionados pelo BigQuery GIS à linha conforme necessário, de modo que a sequência de arestas convertida permaneça no máximo a 10 metros da linha original. Este é um processo conhecido como tesselação ou densificação não uniforme. Não é possível controlar diretamente o processo de tesselação.

Para importar regiões geográficas com arestas esféricas, use o WKT como no exemplo a seguir:

SELECT
  *,
  ST_GeogFromText(wkt) AS g
FROM
  mytable

Para importar regiões geográficas com arestas planas, geralmente chamadas de "geometrias", use o GeoJSON como no exemplo a seguir:

SELECT
  *,
  ST_GeogFromGeoJSON(geocol) AS g
FROM
  mytable

Também é possível excluir a coluna GeoJSON original dos resultados:

SELECT
  * EXCEPT(geocol),
  ST_GeogFromGeoJSON(geocol) AS geocol
FROM
  mytable

Não se esqueça de usar o formato adequado. A maioria dos sistemas indica a respectiva compatibilidade com a análise da região geográfica (não da geometria) do WKT ou pressupõe arestas planas, caso em que o GeoJSON precisa ser usado como formato de intercâmbio.

Suas coordenadas precisam ser primeiro longitude e, depois, latitude. Se a região geográfica tiver segmentos ou arestas longos, eles precisarão ser tesselados, uma vez que serão interpretados como geodésicos esféricos pelo BigQuery GIS, o que pode não corresponder ao sistema de coordenadas em que foram originados seus dados.

Orientação do polígono

Em uma esfera, cada polígono tem outro que o complementa. Por exemplo, um polígono que descreva os continentes da Terra terá um complementar que descreva os oceanos. Como os dois polígonos são descritos pelos mesmos anéis delimitadores, são necessárias regras para resolver a ambiguidade entre eles e saber qual é descrito por uma determinada string WKT.

Quando você carrega strings WKB e WKT a partir de arquivos ou pela ingestão de streaming, o BigQuery GIS pressupõe que os polígonos na entrada sejam orientados da seguinte forma: se você transpassar o limite do polígono na ordem dos vértices da entrada, o interior do polígono estará à esquerda. A mesma regra é usada pelo BigQuery GIS ao exportar objetos geography para strings WKT e WKB.

Ao criar objetos de regiões geográficas a partir de uma string WKT com a função ST_GeogFromText, há duas opções para determinar o polígono descrito por essa string:

  1. (Padrão) Interprete a entrada como o polígono com a menor área. Não admita polígonos orientados (oriented = FALSE).

  2. Admita polígonos orientados para permitir a carga de polígonos com uma área maior que um hemisfério (oriented = TRUE).

Essas regras não se aplicam quando você carrega strings GeoJSON. Como essas strings são definidas em um mapa plano, a orientação pode ser determinada sem ambiguidade, mesmo se a entrada não seguir a regra definida em GeoJSON RFC 7946, seção 3.1.6 — Polígono: anéis externos no sentido anti-horário e anéis internos no sentido horário (em inglês).

Como carregar os dados do BigQuery GIS

Ao carregar dados do BigQuery GIS no BigQuery, é possível especificar uma coluna GEOGRAPHY na definição do esquema da tabela. Quando você especifica o tipo dos dados na coluna como GEOGRAPHY, o BigQuery GIS pode detectar se os dados estão no formato WKT ou GeoJSON.

Os objetos geométricos GeoJSON carregados em uma coluna GEOGRAPHY precisam ser formatados como strings de texto, não objetos JSON. Isso é válido mesmo que o objeto esteja sendo carregado de um arquivo JSON delimitado por nova linha.

Se você carregar dados com a detecção automática de esquema, os valores geográficos serão carregados como STRINGs. Atualmente, não é possível detectar colunas geográficas com a detecção automática do esquema.

Para mais informações sobre como carregar dados no BigQuery, veja a Introdução ao carregamento de dados do Cloud Storage.

Como transformar os dados do BigQuery GIS

Se a tabela tiver colunas separadas para longitude e latitude, será possível transformar os valores em regiões geográficas com as funções geográficas do SQL padrão, como ST_GeogPoint. Por exemplo, se você tiver duas colunas DOUBLE para longitude e latitude, use a consulta a seguir para criar uma coluna geográfica.

SELECT
  *,
  ST_GeogPoint(longitude, latitude) AS g
FROM
  mytable

Atualmente, o BigQuery é compatível com a conversão de strings WKT e GeoJSON em tipos geográficos. Shapefiles e muitos outros formatos precisam ser convertidos com ferramentas externas.

Como lidar com dados espaciais formatados incorretamente

Ao carregar dados no BigQuery, talvez você encontre dados inválidos de WKT ou GeoJSON de outras ferramentas, que não podem ser convertidos em uma coluna GEOGRAPHY. Por exemplo, um erro como Edge K has duplicate vertex with edge N mostra que o polígono tem vértices duplicados além do primeiro e do último.

Para evitar problemas de formatação, use uma função que gere uma saída compatível com os padrões. Por exemplo, ao exportar dados do PostGIS, use a função ST_MakeValid para padronizar a saída.

Para localizar ou ignorar os dados formatados incorretamente, use o prefixo da função SAFE para gerar os dados problemáticos. Por exemplo, na consulta a seguir, o prefixo SAFE é usado para recuperar dados espaciais formatados incorretamente.

SELECT
  geojson AS bad_geojson
FROM
  mytable
WHERE
  geojson IS NOT NULL
  AND SAFE.ST_GeogFromGeoJson(geojson) IS NULL

Como particionar e armazenar em cluster os dados do BigQuery GIS

É possível particionar e armazenar em clusters tabelas que contêm colunas GEOGRAPHY. Você pode usar uma coluna GEOGRAPHY como coluna de clustering, mas não uma coluna GEOGRAPHY como coluna de particionamento.

Se você armazenar dados GEOGRAPHY em uma tabela em cluster ou particionada e eles forem filtrados pelas consultas por meio de um predicado espacial, verifique se os dados geográficos são espacialmente compactos. O predicado espacial chama uma função geográfica booleana e tem uma coluna GEOGRAPHY como um dos argumentos. Na amostra a seguir, há um predicado espacial em que a função ST_DWithin é usada:

WHERE ST_DWithin(geo, ST_GeogPoint(longitude, latitude), 100)

Por exemplo, se você tiver uma tabela com colunas para COUNTRY, STATE e ZIP, adicione uma coluna à tabela para armazenar uma versão concatenada dessas colunas. O fragmento de consulta a seguir demonstra isso:

CONCAT(country, '+', IFNULL(state, ''), '+', IFNULL(zip, '')) as loc

Neste exemplo, o IFNULL é usado para eliminar valores ausentes. Depois de criar a coluna concatenada, use-a para armazenar a tabela em cluster.

Como usar JOINs com dados espaciais

JOINs espaciais são junções de duas tabelas com uma função geográfica de predicado na cláusula WHERE. Por exemplo:

#standardSQL
-- how many stations within 1 mile range of each zip code?
SELECT
    zipcode AS zip,
    ST_GeogFromText(ANY_VALUE(zip_codes.zipcode_geom)) AS polygon,
    COUNT(*) AS bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
    `bigquery-public-data.utility_us.zipcode_area` AS zip_codes
WHERE ST_DWithin(
         ST_GeogFromText(zip_codes.zipcode_geom),
         ST_GeogPoint(bike_stations.longitude, bike_stations.latitude),
         1609.34)
GROUP BY zip
ORDER BY bike_stations DESC

As junções espaciais têm melhor desempenho quando os dados geográficos são mantidos. No exemplo acima, os valores geográficos são criados na consulta. É mais eficiente armazenar esses valores em uma tabela do BigQuery.

Por exemplo, com a consulta a seguir, pares longitude-latitude são recuperados e convertidos em pontos geográficos. Ao executar essa consulta, você especifica uma nova tabela de destino para o armazenamento dos resultados:

SELECT
  *,
  ST_GeogPoint(pLongitude, pLatitude) AS p
FROM
  mytable

O BigQuery implementa JOINs espaciais otimizados para operadores INNER JOIN e CROSS JOIN com as seguintes funções de predicado de SQL padrão:

Junções espaciais não são otimizadas nos casos a seguir:

  • Junções LEFT, RIGHT ou FULL OUTER
  • Nos casos que envolvem junções ANTI
  • Quando o predicado espacial é negado

Um valor JOIN que usa o predicado ST_DWithin é otimizado apenas quando o parâmetro de distância é uma expressão constante.

Como exportar dados espaciais

Quando você exporta dados espaciais do BigQuery, os valores da coluna GEOGRAPHY são sempre formatados como strings WKT. Para exportar dados no formato GeoJSON, use a função ST_AsGeoJSON.

Se as ferramentas usadas para analisar os dados exportados não processarem o tipo de dados GEOGRAPHY, converta os valores da coluna em strings. Para isso, basta usar uma função geográfica, como ST_AsText ou ST_AsGeoJSON. O BigQuery GIS adiciona pontos à linha conforme necessário, de modo que a sequência de arestas convertida permaneça no máximo a 10 metros da linha geodésica original.

Por exemplo, na consulta a seguir, a função ST_AsGeoJSON é usada para converter valores GeoJSON em strings.

SELECT
  ST_AsGeoJSON(ST_MakeLine(ST_GeogPoint(1,1), ST_GeogPoint(3,2)))

Os dados resultantes serão semelhantes aos seguintes:

{ "type": "LineString", "coordinates": [ [1, 1], [1.99977145571783, 1.50022838764041], [2.49981908082299, 1.75018082434274], [3, 2] ] }

A linha GeoJSON tem dois pontos extras. Esses pontos são adicionados pelo BigQuery GIS para que a linha GeoJSON siga de perto o mesmo caminho no local que a linha original.

Como trabalhar com regiões geográficas nas bibliotecas de cliente do BigQuery

Atualmente, apenas a biblioteca de cliente do BigQuery para Python é compatível com o tipo de dados GEOGRAPHY. Para outras bibliotecas de cliente, converta os valores GEOGRAPHY em strings usando a função ST_ASTEXT ou ST_ASGEOJSON. Por exemplo, use a função ST_AsText: ST_AsText(ANY_VALUE(zip_regions_geometry.geometry)) AS geometry.

Com a conversão para texto por ST_AsText, apenas um valor é armazenado. Já na conversão para WKT, os dados são anotados como um tipo STRING em vez de GEOGRAPHY.

A seguir

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.