Como trabalhar com dados geoespaciais

Com a análise geoespacial, você pode analisar dados geográficos no BigQuery. Os dados geográficos também são conhecidos como dados geoespaciais.

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

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

No BigQuery, o tipo de dados GEOGRAPHY representa um valor de geometria ou uma coleção de geometria. Para representar recursos espaciais, crie uma tabela com uma coluna GEOGRAPHY para a geometria e colunas adicionais para os atributos. Cada linha da tabela é um recurso espacial e a tabela inteira representa uma coleção de recursos espaciais.

O tipo de dados GEOGRAPHY descreve um conjunto de pontos na superfície da Terra. Um conjunto de pontos é um conjunto de pontos, linhas e polígonos no esferoide de referência WGS84, com bordas geodésicas. Para usar o tipo de dados GEOGRAPHY, chame uma das funções geográficas do GoogleSQL.

Como carregar dados geoespaciais

Pontos únicos na Terra podem ser descritos por apenas um par (longitude, latitude). Por exemplo, é possível carregar um arquivo CSV que contenha valores de longitude e latitude e usar a função ST_GEOGPOINT para convertê-los em valores GEOGRAPHY.

Para regiões geográficas mais complexas, é possível carregar os seguintes formatos de dados geoespaciais em uma coluna GEOGRAPHY:

  • Texto conhecido (WKT)
  • Binário conhecido (WKB)
  • GeoJSON

Como carregar dados WKT ou WKB

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. O WKB é uma versão binária do formato WKT. O WKB pode ser codificado em hexadecimal para formatos não compatíveis com dados binários, como JSON.

Por exemplo, o exemplo a seguir define um ponto no WKT:

POINT(-121 41)

Para descrever um recurso espacial, o WKT costuma ser incorporado em um formato de arquivo de contêiner, como 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. Para carregar dados WKT no BigQuery, forneça um esquema que especifique uma coluna GEOGRAPHY para os dados geoespaciais.

Por exemplo, você pode ter um arquivo CSV com os seguintes dados:

"POLYGON((-124.49 47.35,-124.49 40.73,-116.49 40.73,-116.49 47.35,-124.49 47.35))",poly1
"POLYGON((-85.6 31.66,-85.6 24.29,-78.22 24.29,-78.22 31.66,-85.6 31.66))",poly2
"POINT(1 2)",point1

É possível carregar este arquivo executando o comando load da ferramenta de linha de comando bq:

bq load --source_format=CSV \
  --schema="geography:GEOGRAPHY,name:STRING" \
  mydataset.mytable filename1.csv

Para mais informações sobre o carregamento de dados no BigQuery, consulte Introdução ao carregamento de dados.

Para fazer streaming de dados do WKT para uma tabela do BigQuery com uma coluna GEOGRAPHY, serialize os dados como uma string na solicitação de API.

bq

Execute o comando insert da ferramenta de linha de comando bq:

echo '{"geo": "LINESTRING (-118.4085 33.9416, -73.7781 40.6413)"}' \
    | bq insert my_dataset.geo_table

Python

Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente. 

from google.cloud import bigquery
import shapely.geometry
import shapely.wkt

bigquery_client = bigquery.Client()

# This example uses a table containing a column named "geo" with the
# GEOGRAPHY data type.
table_id = "my-project.my_dataset.my_table"

# Use the Shapely library to generate WKT of a line from LAX to
# JFK airports. Alternatively, you may define WKT data directly.
my_geography = shapely.geometry.LineString(
    [(-118.4085, 33.9416), (-73.7781, 40.6413)]
)
rows = [
    # Convert data into a WKT string.
    {"geo": shapely.wkt.dumps(my_geography)},
]

#  table already exists and has a column
# named "geo" with data type GEOGRAPHY.
errors = bigquery_client.insert_rows_json(table_id, rows)
if errors:
    raise RuntimeError(f"row insert failed: {errors}")
else:
    print(f"wrote 1 row to {table_id}")

Para mais informações sobre streaming de dados no BigQuery, consulte Como fazer streaming de dados no BigQuery.

Também é possível converter uma string de texto WKT em um valor GEOGRAPHY usando a função ST_GeogFromText.

Como carregar dados GeoJSON

GeoJSON é um formato baseado em JSON para geometrias e elementos espaciais. Por exemplo, a instrução a seguir define um ponto no GeoJSON:

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

Os dados GeoJSON podem conter qualquer um dos seguintes tipos de objeto:

  • Objetos de geometria. Um objeto geométrico é uma forma espacial, descrita como uma união de pontos, linhas e polígonos com furos opcionais.
  • Objetos de recursos. Um objeto de recurso contém uma geometria e outros pares de nome/valor, cujo significado é específico do aplicativo.
  • Coleções de recursos. Uma coleção de recursos é um conjunto de objetos de recursos.

Há duas maneiras de carregar dados GeoJSON no BigQuery:

Como carregar arquivos GeoJSON delimitados por nova linha

Um arquivo GeoJSON delimitado por nova linha contém uma lista de objetos de recurso GeoJSON, um por linha no arquivo. Um objeto de recurso GeoJSON é um objeto JSON com estes membros:

  • type. Para objetos de recurso, o valor precisa ser Feature. O BigQuery valida o valor, mas não o inclui no esquema da tabela.

  • geometry O valor é um objeto GeoJSON Geometry ou null. O BigQuery converte esse membro em um valor GEOGRAPHY.

  • properties O valor é qualquer objeto JSON ou nulo. Se o valor não for null, o BigQuery carregará cada membro do objeto JSON como uma coluna de tabela separada. Para mais informações sobre como o BigQuery analisa tipos de dados JSON, consulte Detalhes do carregamento de dados JSON.

  • id: opcional. Se presente, o valor é uma string ou um número. O BigQuery carrega esse valor em uma coluna chamada id.

Se o objeto de recurso contiver outros membros não listados aqui, o BigQuery converterá esses membros diretamente em colunas da tabela.

É possível carregar um arquivo GeoJSON delimitado por nova linha usando o comando bq load da ferramenta de linha de comando bq desta forma:

bq load \
 --source_format=NEWLINE_DELIMITED_JSON \
 --json_extension=GEOJSON \
 --autodetect \
 DATASET.TABLE \
 FILE_PATH_OR_URI

Substitua:

  • DATASET é o nome do conjunto de dados.
  • TABLE é o nome da tabela de destino.
  • FILE_PATH_OR_URI é um caminho para um arquivo local ou um URI do Cloud Storage.

O exemplo anterior ativa a detecção automática de esquema. Para ter mais controle sobre como o BigQuery converte os valores dentro do objeto properties, forneça um esquema explícito. Para mais informações, consulte Especificar esquemas. Se você fornecer um esquema explícito, não inclua uma coluna type de nível superior na definição do esquema. Para cada membro do membro properties, defina colunas separadas, não uma única coluna aninhada.

Conforme definido por RFC 7946, uma estrutura de dados GeoJSON completa é um único objeto JSON. Muitos sistemas exportam dados GeoJSON como um único objeto FeatureCollection que contém todas as geometrias. Para carregar esse formato no BigQuery, você precisa converter o arquivo removendo o objeto FeatureCollection no nível raiz e dividindo os objetos de recurso individuais em linhas separadas. Por exemplo, o comando a seguir usa a ferramenta de linha de comando jq para dividir um arquivo GeoJSON em formato delimitado por nova linha:

cat ~/file1.json | jq -c '.features[]' > converted.json

Como criar uma tabela externa a partir de um arquivo GeoJSON delimitado por nova linha

Consulte um arquivo GeoJSON delimitado por nova linha armazenado no Cloud Storage criando uma tabela externa. Para criar a tabela externa, use a instrução DDL CREATE EXTERNAL TABLE. Na cláusula OPTIONS, defina a opção format como NEWLINE_DELIMITED_JSON e a opção json_extension como GEOJSON.

Exemplo:

CREATE EXTERNAL TABLE mydataset.table1 OPTIONS (
  format="NEWLINE_DELIMITED_JSON",
  json_extension = 'GEOJSON',
  uris = ['gs://mybucket/geofile.json']
);

Como carregar dados de geometria GeoJSON

A análise geoespacial é compatível com o carregamento de objetos geométricos GeoJSON individuais incorporados como strings de texto em outros tipos de arquivos. Por exemplo, é possível carregar um arquivo CSV em que uma das colunas contém um objeto de geometria GeoJSON.

Para carregar esse tipo de dados GeoJSON no BigQuery, forneça um esquema que especifique uma coluna GEOGRAPHY para os dados GeoJSON. É necessário fornecer o esquema manualmente. Se a detecção automática estiver ativada, o BigQuery carregará os dados como um valor STRING.

A análise geoespacial não é compatível com o carregamento de objetos de recurso ou coleções de recursos GeoJSON usando essa abordagem. Se você precisar carregar objetos de recurso, use arquivos GeoJSON delimitados por nova linha.

Para fazer o streaming de dados GeoJSON para uma tabela do BigQuery com uma coluna GEOGRAPHY, serialize os dados como uma string na solicitação de API.

bq

Execute o comando insert da ferramenta de linha de comando bq:

echo '{"geo": "{\"type\": \"LineString\", \"coordinates\": [[-118.4085, 33.9416], [-73.7781, 40.6413]]}"}' \
  | bq insert my_dataset.geo_table

Python

Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente. 

import geojson
from google.cloud import bigquery

bigquery_client = bigquery.Client()

# This example uses a table containing a column named "geo" with the
# GEOGRAPHY data type.
table_id = "my-project.my_dataset.my_table"

# Use the python-geojson library to generate GeoJSON of a line from LAX to
# JFK airports. Alternatively, you may define GeoJSON data directly, but it
# must be converted to a string before loading it into BigQuery.
my_geography = geojson.LineString([(-118.4085, 33.9416), (-73.7781, 40.6413)])
rows = [
    # Convert GeoJSON data into a string.
    {"geo": geojson.dumps(my_geography)}
]

#  table already exists and has a column
# named "geo" with data type GEOGRAPHY.
errors = bigquery_client.insert_rows_json(table_id, rows)
if errors:
    raise RuntimeError(f"row insert failed: {errors}")
else:
    print(f"wrote 1 row to {table_id}")

Também é possível converter um objeto de geometria GeoJSON em um valor GEOGRAPHY usando a função ST_GEOGFROMGEOJSON. Por exemplo, é possível armazenar as geometrias como valores STRING e executar uma consulta que chame ST_GEOGFROMGEOJSON.

Arestas e sistemas de coordenadas

Na análise geoespacial, os pontos 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 pontos de extremidade. Ou seja, as arestas são o caminho mais curto na superfície de uma esfera.

O formato WKT não fornece um sistema de coordenadas. Ao carregar dados WKT, a análise geoespacial pressupõe que os dados usem coordenadas WGS84 com arestas esféricas. Verifique se os dados de origem correspondem a esse sistema de coordenadas, a menos que as geografias sejam pequenas o suficiente para que a diferença entre as extremidades esféricas e planas possa ser ignorada.

O GeoJSON usa explicitamente as coordenadas WGS84 com arestas planas. Ao carregar dados GeoJSON, a análise geoespacial converte arestas planas para bordas esféricas. A análise geoespacial 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 original. Esse processo é conhecido como tesselação ou densificação não uniforme. Não é possível controlar diretamente o processo de tesselação.

Para carregar regiões geográficas com arestas esféricas, use o WKT. Para carregar regiões geográficas com arestas planas, geralmente chamadas de geometrias, é mais simples usar o GeoJSON. No entanto, se os dados de geometria já estiverem no formato WKT, outra opção será carregar os dados como um tipo STRING e usar a função ST_GEOGFROMTEXT a ser convertida em valores GEOGRAPHY. Defina o parâmetro planar como TRUE para interpretar os dados como planos.

Ao escolher um formato de alternância, entenda o sistema de coordenadas usado pelos seus dados de origem. A maioria dos sistemas é explicitamente compatível com a análise da região geográfica (em oposição à geometria) do WKT, ou também requer arestas planas.

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 a análise geoespacial os interpreta como geodésicos esféricos, 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 pelo processamento de streaming, oa análise geoespacial 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 pela análise geoespacial ao exportar objetos geográficos para strings WKT e WKB.

Se você usar a função ST_GeogFromText para converter uma string WKT em um valor GEOGRAPHY, o parâmetro oriented especificará como a função determina o polígono:

  • FALSE: interprete a entrada como o polígono com área menor. Este é o comportamento padrão.

  • TRUE: usa a regra de orientação à esquerda descrita anteriormente. Esta opção permite carregar polígonos com uma área maior que um hemisfério.

Como strings GeoJSON são definidas em um mapa plano, a orientação pode ser determinada sem ambiguidade, mesmo que a entrada não siga a regra de orientação definida na especificação de formato GeoJSON, RFC 7946.

Como processar dados espaciais formatados incorretamente

Ao carregar dados espaciais de outras ferramentas no BigQuery, talvez você encontre erros de conversão causados por dados inválidos no WKT ou GeoJSON. Por exemplo, um erro como Edge K has duplicate vertex with edge N indica 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, quando você exporta dados do PostGIS, pode usar a função ST_MakeValid do PostGIS para padronizar a saída. Outra opção é importar seus dados como texto e convertê-los chamando ST_GEOGFROMTEXT ou ST_GEOGFROMGEOJSON com o parâmetro make_valid. Quando make_valid é TRUE, essas funções tentam reparar polígonos inválidos.

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

Restrições

A análise geoespacial não é compatível com os seguintes recursos em formatos geoespaciais:

  • 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, a análise geoespacial é compatível apenas com Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon e GeometryCollection.

Consulte ST_GeogFromGeoJson e ST_GeogFromText para ver as restrições específicas dos formatos de entrada GeoJson e WKT.

Carregar dados geoespaciais do Google Earth Engine

O Google Earth Engine é uma plataforma de dados geoespaciais que compila e analisa insights de imagens de observação por satélite e da Terra usando dados de varredura. Os dados são organizados em uma grade de células que representam informações em imagens digitais. Embora o BigQuery funcione principalmente com dados vetoriais tabulares, os usuários podem usar os dados dele em com os dados de varredura do Earth Engine para incorporar conjuntos de dados vetoriais e de varredura aos fluxos de trabalho deles.

Para informações sobre como exportar dados do Earth Engine para o BigQuery, consulte Como exportar para o BigQuery.

Como transformar dados geoespaciais

Se a tabela tiver colunas separadas para longitude e latitude, é possível transformar os valores em geografias usando as funções geográficas do GoogleSQL, 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

O BigQuery pode converter strings WKT e GeoJSON em tipos de geografia. Se seus dados estiverem em outro formato, como Shapefiles, use uma ferramenta externa para converter os dados em um formato de arquivo de entrada compatível, como um arquivo CSV, com colunas GEOGRAPHY codificadas como strings WKT ou GeoJSON.

Como particionar e armazenar em cluster os dados geoespaciais

É possível particionar e armazenar em clusters tabelas que contêm colunas GEOGRAPHY. É possível 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 e suas consultas filtrarem dados usando um predicado espacial, verifique se a tabela está em cluster pela coluna GEOGRAPHY. Isso costuma melhorar o desempenho da consulta e reduzir o custo. O predicado espacial chama uma função geográfica booleana e tem uma coluna GEOGRAPHY como um dos argumentos. A amostra a seguir apresenta um predicado espacial que usa a função ST_DWithin:

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

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:

-- how many stations within 1 mile range of each zip code?
SELECT
    zip_code AS zip,
    ANY_VALUE(zip_code_geom) AS polygon,
    COUNT(*) AS bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
    `bigquery-public-data.geo_us_boundaries.zip_codes` AS zip_codes
WHERE ST_DWithin(
         zip_codes.zip_code_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 os operadores INNER JOIN e CROSS JOIN com as seguintes funções de predicado do GoogleSQL:

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 JOIN que usa o predicado ST_DWithin é otimizado somente 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 usando uma função geográfica, como ST_AsText ou ST_AsGeoJSON. A análise geoespacial 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, a consulta a seguir usa ST_AsGeoJSON para converter valores de 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 pela análise geoespacial para que a linha GeoJSON siga de perto o mesmo caminho no local que a linha original.

A seguir