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.
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:
- Carregue arquivos GeoJSON delimitados por nova linha.
- Carregue objetos geométricos GeoJSON individuais incorporados em outros tipos de arquivo.
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 serFeature
. O BigQuery valida o valor, mas não o inclui no esquema da tabela.geometry
O valor é um objeto GeoJSONGeometry
ounull
. O BigQuery converte esse membro em um valorGEOGRAPHY
.properties
O valor é qualquer objeto JSON ou nulo. Se o valor não fornull
, 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 chamadaid
.
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.
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
. 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
- Para começar a usar a análise geoespacial, consulte o artigo Primeiros passos com análises geoespaciais para analistas de dados.
- Para saber mais sobre as opções de visualização para análise geoespacial, consulte o artigo Como visualizar dados geoespaciais.
- Consulte a documentação sobre funções do GoogleSQL na análise geoespacial em Funções geográficas no GoogleSQL.