Trabalhar com dados geoespaciais

A análise geoespacial permite-lhe analisar dados geográficos no BigQuery. Os dados geográficos também são conhecidos como dados geoespaciais.

Os tipos comuns de objetos quando trabalha com dados geoespaciais incluem o seguinte:

  • Uma geometria representa uma área de superfície na Terra. É frequentemente descrita através de pontos, linhas, polígonos ou uma coleção de pontos, linhas e polígonos. Uma coleção de geometrias é uma geometria que representa a união espacial de todas as formas na coleção.
  • Uma funcionalidade espacial representa um objeto espacial lógico. Combina uma geometria com atributos adicionais específicos da aplicação.
  • Uma coleção de elementos espaciais é um conjunto de elementos espaciais.

No BigQuery, o tipo de dados GEOGRAPHY representa um valor de geometria ou uma coleção de geometrias. Para representar elementos espaciais, crie uma tabela com uma coluna GEOGRAPHY para a geometria, além de colunas adicionais para os atributos. Cada linha da tabela é uma funcionalidade espacial e a tabela completa representa uma coleção de funcionalidades 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 arestas geodésicas. Pode usar o tipo de dados GEOGRAPHY chamando uma das funções geográficas do GoogleSQL.

A carregar dados geoespaciais

Os pontos únicos na Terra podem ser descritos apenas por um par de longitude e latitude. Por exemplo, pode carregar um ficheiro CSV que contenha valores de longitude e latitude e, em seguida, usar a função ST_GEOGPOINT para os converter em valores GEOGRAPHY.

Para geografias mais complexas, pode carregar os seguintes formatos de dados geoespaciais numa coluna GEOGRAPHY:

  • Texto conhecido (WKT)
  • Well-known binary (WKB)
  • GeoJSON
  • GeoParquet

Carregar dados WKT ou WKB

O WKT é um formato de texto para descrever formas geométricas individuais através de pontos, linhas, polígonos com orifícios opcionais ou uma coleção de pontos, linhas ou polígonos. WKB é a versão binária do formato WKT. O WKB pode ser codificado em hexadecimal para formatos que não suportam dados binários, como o JSON.

Por exemplo, o seguinte define um ponto em WKT:

POINT(-121 41)

Para descrever uma funcionalidade espacial, o WKT é normalmente incorporado num formato de ficheiro de contentor, como um ficheiro CSV, ou numa tabela de base de dados. Uma linha de ficheiro ou uma linha de tabela corresponde normalmente ao elemento espacial. O ficheiro inteiro ou a tabela inteira corresponde à coleção de elementos. Para carregar dados WKT para o BigQuery, forneça um esquema que especifique uma coluna GEOGRAPHY para os dados geoespaciais.

Por exemplo, pode ter um ficheiro CSV que contenha 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

Pode carregar este ficheiro executando o loadcomando da ferramenta de linhas 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 o artigo Introdução ao carregamento de dados.

Para transmitir dados WKT para uma tabela do BigQuery existente com uma coluna GEOGRAPHY, serialize os dados como uma string no pedido API.

bq

Execute o insertcomando da ferramenta de linhas de comando bq:

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

Python

Antes de experimentar este exemplo, siga as Pythoninstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Python BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure 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 o streaming de dados no BigQuery, consulte o artigo Streaming de dados para o BigQuery.

Também pode converter uma string de texto WKT num valor GEOGRAPHY usando a função ST_GEOGFROMTEXT.

A carregar dados GeoJSON

O formato GeoJSON é um formato baseado em JSON para geometrias e funcionalidades espaciais. Por exemplo, a seguinte definição define um ponto em GeoJSON:

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

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

  • Objetos de geometria. Um objeto de geometria é uma forma espacial, descrita como uma união de pontos, linhas e polígonos com orifícios opcionais.
  • Objetos de funcionalidades. Um objeto de funcionalidade contém uma geometria, além de pares de nome/valor adicionais, cujo significado é específico da aplicação.
  • Coleções de elementos. Uma coleção de elementos é um conjunto de objetos de elementos.

Existem duas formas de carregar dados GeoJSON para o BigQuery:

Carregar ficheiros GeoJSON delimitados por newline

Um ficheiro GeoJSON delimitado por newline contém uma lista de objetos de elementos GeoJSON, um por linha no ficheiro. Um objeto de funcionalidade GeoJSON é um objeto JSON com os seguintes membros:

  • type. Para objetos de funcionalidades, o valor tem de 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 este membro num valor GEOGRAPHY.

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

  • id. Opcional. Se estiver presente, o valor é uma string ou um número. O BigQuery carrega este valor numa coluna denominada id.

Se o objeto de funcionalidade contiver outros membros que não estão listados aqui, o BigQuery converte esses membros diretamente em colunas da tabela.

Pode carregar um ficheiro GeoJSON delimitado por novas linhas através do comando bq load da ferramenta de linhas de comando bq, da seguinte forma:

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

Substitua o seguinte:

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

O exemplo anterior ativa a deteção automática de esquemas. Para ter mais controlo sobre a forma como o BigQuery converte os valores no objeto properties, pode fornecer um esquema explícito. Para mais informações, consulte o artigo Especifique esquemas. Se 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 e não uma única coluna aninhada.

Conforme definido pela 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 este formato no BigQuery, tem de converter o ficheiro removendo o objeto FeatureCollection ao nível da raiz e dividindo os objetos de funcionalidades individuais em linhas separadas. Por exemplo, o seguinte comando usa a ferramenta de linha de comandos jq para dividir um ficheiro GeoJSON num formato delimitado por newline:

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

Criar uma tabela externa a partir de um ficheiro GeoJSON delimitado por newline

Pode consultar um ficheiro GeoJSON delimitado por newline armazenado no Cloud Storage criando uma tabela externa. Para criar a tabela externa, use a declaraçã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']
);

A carregar dados de geometria GeoJSON

A estatística geoespacial suporta o carregamento de objetos de geometria GeoJSON individuais que estão incorporados como strings de texto noutros tipos de ficheiros. Por exemplo, pode carregar um ficheiro CSV em que uma das colunas contém um objeto de geometria GeoJSON.

Para carregar este tipo de dados GeoJSON para o BigQuery, forneça um esquema que especifique uma coluna GEOGRAPHY para os dados GeoJSON. Tem de fornecer o esquema manualmente. Caso contrário, se a deteção automática estiver ativada, o BigQuery carrega os dados como um valor STRING.

A análise geoespacial não suporta o carregamento de objetos de elementos GeoJSON nem coleções de elementos através desta abordagem. Se precisar de carregar objetos de elementos, considere usar ficheiros GeoJSON delimitados por newline.

Para transmitir dados GeoJSON para uma tabela do BigQuery existente com uma coluna GEOGRAPHY, serializar os dados como uma string no pedido API.

bq

Execute o insertcomando da ferramenta de linhas 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 experimentar este exemplo, siga as Pythoninstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Python BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure 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 pode converter um objeto de geometria GeoJSON num valor GEOGRAPHY usando a função ST_GEOGFROMGEOJSON. Por exemplo, pode armazenar as geometrias como valores STRING e, em seguida, executar uma consulta que chame ST_GEOGFROMGEOJSON.

Carregar ficheiros GeoParquet

O GeoParquet é uma especificação que adiciona tipos geoespaciais ao formato de ficheiro Parquet. O GeoParquet inclui metadados que fornecem semântica definida aos dados geoespaciais contidos, evitando os problemas de interpretação que ocorrem com outros formatos de dados geoespaciais.

Ao carregar ficheiros Parquet, o BigQuery verifica a existência de metadados GeoParquet. Se existirem metadados GeoParquet, o BigQuery carrega todas as colunas que descrevem numa coluna GEOGRAPHY correspondente por predefinição. Para mais informações sobre o carregamento de ficheiros Parquet, consulte o artigo Carregar dados Parquet.

Criar uma tabela externa a partir de dados GeoParquet

Tabelas externas que referenciam ficheiros GeoParquet mapeiam colunas relevantes para o GEOGRAPHY tipo.

As estatísticas disponíveis no ficheiro GeoParquet (bbox, covering) não são usadas para acelerar as consultas numa tabela externa.

Sistemas de coordenadas e limites

Nas estatísticas geoespaciais, os pontos são posições na superfície de um esferoide WGS84, expressas como longitude e latitude geodésica. Um limite é uma geodésica esférica entre dois pontos finais. (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 assume que os dados usam coordenadas WGS84 com arestas esféricas. Certifique-se de que os dados de origem correspondem a esse sistema de coordenadas, a menos que as geografias sejam suficientemente pequenas para que a diferença entre as arestas esféricas e planas possa ser ignorada.

O GeoJSON usa explicitamente coordenadas WGS84 com arestas planas. Ao carregar dados GeoJSON, a análise geoespacial converte arestas planas em arestas esféricas. A análise geoespacial adiciona pontos adicionais à linha, conforme necessário, para que a sequência convertida de arestas permaneça a 10 metros da linha original. Este processo é conhecido como tesselatura ou densificação não uniforme. Não pode controlar diretamente o processo de triangulação.

Para carregar geografias com limites esféricos, use o formato WKT. Para carregar geografias com arestas planas, frequentemente denominadas geometrias, é mais simples usar o formato GeoJSON. No entanto, se os dados de geometria já estiverem no formato WKT, outra opção é carregar os dados como um tipo STRING e, em seguida, usar a função ST_GEOGFROMTEXT para converter em valores GEOGRAPHY. Defina o parâmetro planar como TRUE para interpretar os dados como planares.

Os ficheiros GeoParquet incluem metadados sobre o sistema de coordenadas e os limites que foram usados para criar os dados. Quando lê ficheiros GeoParquet com arestas planas, a análise geoespacial converte as arestas planas em arestas esféricas. Os ficheiros GeoParquet com sistemas de coordenadas diferentes de WGS84 são rejeitados.

Ao escolher um formato de intercâmbio, certifique-se de que compreende o sistema de coordenadas usado pelos dados de origem. A maioria dos sistemas suporta explicitamente a análise da geografia (em oposição à geometria) a partir de WKT ou, caso contrário, assume arestas planas.

As coordenadas devem ser primeiro a longitude e, depois, a latitude. Se a geografia tiver segmentos ou arestas longos, estes têm de ser divididos em mosaicos, porque a análise geoespacial interpreta-os como geodésicas esféricas, que podem não corresponder ao sistema de coordenadas de origem dos seus dados.

Orientação do polígono

Numa esfera, todos os polígonos têm um polígono complementar. Por exemplo, um polígono que descreve os continentes da Terra teria um polígono complementar que descreve os oceanos da Terra. Uma vez que os dois polígonos são descritos pelos mesmos anéis de limite, são necessárias regras para resolver a ambiguidade sobre qual dos dois polígonos é descrito por uma determinada string WKT.

Quando carrega strings WKT e WKB a partir de ficheiros ou através da ingestão de streaming, a análise geoespacial assume que os polígonos na entrada estão orientados da seguinte forma: se percorrer o limite do polígono pela ordem dos vértices de entrada, o interior do polígono fica à esquerda. A estatística geoespacial usa a mesma regra quando exporta objetos geográficos para strings WKT e WKB.

Se usar a função ST_GEOGFROMTEXT para converter uma string WKT num valor GEOGRAPHY, o parâmetro oriented especifica como a função determina o polígono:

  • FALSE: interprete a entrada como o polígono com a área mais pequena. Este é o comportamento predefinido.

  • TRUE: use a regra de orientação da mão esquerda descrita anteriormente. Esta opção permite-lhe carregar polígonos com uma área superior a um hemisfério.

Uma vez que as strings GeoJSON são definidas num 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 do formato GeoJSON, RFC 7946.

Tratar dados espaciais formatados incorretamente

Quando carrega dados espaciais de outras ferramentas para o BigQuery, pode encontrar erros de conversão devido a dados WKT ou GeoJSON inválidos. 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, pode usar uma função que gere uma saída em conformidade com as normas. Por exemplo, quando exporta dados do PostGIS, pode usar a função ST_MakeValid do PostGIS para padronizar o resultado. Em alternativa, importe os dados como texto e, em seguida, converta-os chamando ST_GEOGFROMTEXT ou ST_GEOGFROMGEOJSON com o parâmetro make_valid. Quando make_valid é TRUE, estas funções tentam reparar polígonos inválidos.

Para encontrar ou ignorar os dados formatados incorretamente, use o prefixo da função SAFE para gerar os dados problemáticos. Por exemplo, a seguinte consulta usa o prefixo SAFE para obter 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 suporta as seguintes funcionalidades em formatos geoespaciais:

  • Geometrias tridimensionais. Isto inclui o sufixo "Z" no formato WKT e a coordenada de altitude no formato GeoJSON.
  • Sistemas de referência lineares. Isto inclui o sufixo "M" no formato WKT.
  • Objetos geométricos WKT que não sejam primitivos geométricos nem geometrias multipartes. Em particular, a análise geoespacial suporta apenas Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon e GeometryCollection.

Consulte ST_GEOGFROMGEOJSON e ST_GEOGFROMTEXT para ver restrições específicas dos formatos de entrada GeoJSON e WKT.

Integre dados raster geoespaciais com o Google Earth Engine

As estatísticas geoespaciais são frequentemente apresentadas como dados baseados em grelhas ou rasterizados. Os dados raster organizam dados regionalmente contínuos, como imagens de satélite, previsões meteorológicas e cobertura do solo, numa grelha de píxeis. Embora o BigQuery seja especializado principalmente em dados vetoriais tabulares, que representam elementos com limites e pontos, pode integrar dados rasterizados nas suas análises do BigQuery através da função ST_REGIONSTATS. Esta função usa o Earth Engine, a plataforma de análise raster da Google, para fazer cálculos e agregações em dados raster para uma análise geoespacial melhorada. Para mais informações, consulte o artigo Trabalhe com dados raster.

Para obter informações sobre a exportação de dados do Earth Engine para o BigQuery, consulte o artigo Exportar para o BigQuery. Para mais informações sobre as integrações entre o Earth Engine e o BigQuery, consulte a integração do BigQuery na documentação do Earth Engine.

Transformar dados geoespaciais

Se a sua tabela contiver colunas separadas para longitude e latitude, pode transformar os valores em geografias através das funções de geografia do GoogleSQL, como ST_GEOGPOINT. Por exemplo, se tiver duas colunas DOUBLE para a longitude e a latitude, pode criar uma coluna de geografia com a seguinte consulta:

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

O BigQuery pode converter strings WKT e GeoJSON em tipos de geografia. Se os seus dados estiverem noutro formato, como Shapefiles, use uma ferramenta externa para converter os dados num formato de ficheiro de entrada suportado, como um ficheiro CSV, com GEOGRAPHYcolunas codificadas como strings WKT ou GeoJSON.

Criação de partições e clusters de dados geoespaciais

Pode dividir em partições e agrupar tabelas que contenham colunas GEOGRAPHY. Pode usar uma coluna GEOGRAPHY como coluna de agrupamento, mas não pode usar uma coluna GEOGRAPHY como coluna de partição.

Se armazenar dados GEOGRAPHY numa tabela e as suas consultas filtrarem dados através de um predicado espacial, certifique-se de que a tabela está agrupada pela coluna GEOGRAPHY. Normalmente, isto melhora o desempenho das consultas e pode reduzir o custo. Um predicado espacial chama uma função geográfica booleana e tem uma coluna GEOGRAPHY como um dos argumentos. O exemplo seguinte mostra um predicado espacial que usa a função ST_DWITHIN:

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

Usar JOINs com dados espaciais

As 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 um melhor desempenho quando os dados de geografia são mantidos. O exemplo acima cria os valores geográficos na consulta. É mais eficaz armazenar os valores geográficos numa tabela do BigQuery.

Por exemplo, a seguinte consulta obtém pares de longitude e latitude e converte-os em pontos geográficos. Quando executa esta consulta, especifica uma nova tabela de destino para armazenar os resultados da consulta:

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 do GoogleSQL:

As junções espaciais não estão otimizadas:

  • Para LEFT, RIGHT ou FULL OUTER
  • Em casos que envolvam anti-junções
  • Quando o predicado espacial é negado

Uma JOIN que usa o predicado ST_DWITHIN só é otimizada quando o parâmetro de distância é uma expressão constante.

Exportar dados espaciais

Quando exporta dados espaciais do BigQuery, os valores das colunas são sempre formatados como strings WKT.GEOGRAPHY Para exportar dados no formato GeoJSON, use a função ST_ASGEOJSON.

Se as ferramentas que está a usar para analisar os dados exportados não compreenderem o tipo de dados GEOGRAPHY, pode converter os valores das colunas em strings através de uma função geográfica, como ST_ASTEXT ou ST_ASGEOJSON. As estatísticas geoespaciais adicionam pontos adicionais à linha quando necessário para que a sequência convertida de arestas permaneça a 10 metros da linha geodésica original.

Por exemplo, a seguinte consulta usa ST_ASGEOJSON para converter valores GeoJSON em strings.

SELECT
  ST_ASGEOJSON(ST_MAKELINE(ST_GEOGPOINT(1,1), ST_GEOGPOINT(3,2)))

Os dados resultantes teriam o seguinte aspeto:

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

A linha GeoJSON tem dois pontos adicionais. A análise geoespacial adiciona estes pontos para que a linha GeoJSON siga de perto o mesmo caminho no terreno que a linha original.

O que se segue?