Trabaja con datos geoespaciales

Las estadísticas geoespaciales te permiten analizar datos geográficos en BigQuery. Los datos geográficos también se conocen como datos geoespaciales.

Los tipos comunes de objetos cuando trabajan con datos geoespaciales incluyen los siguientes:

  • Una geometría representa un área de la superficie de la Tierra. A menudo, se describe mediante puntos, líneas, polígonos, o un conjunto de puntos, líneas y polígonos. Una colección de geometría es una geometría que representa la unión espacial de todas las formas de la colección.
  • Una característica espacial representa un objeto espacial lógico. Combina una geometría con atributos adicionales que son específicos de una aplicación.
  • Una colección de atributos espaciales es un conjunto de atributos espaciales.

En BigQuery, el tipo de datos GEOGRAPHY representa un valor de geometría o una colección de geometría. A fin de representar atributos espaciales, crea una tabla con una columna GEOGRAPHY para la geometría y columnas adicionales para los atributos. Cada fila de la tabla es un atributo espacial, y toda la tabla representa una colección de características espaciales.

El tipo de datos GEOGRAPHY describe un conjunto de puntos de la superficie de la Tierra. Un conjunto de puntos es un conjunto de puntos, líneas y polígonos en el esferoide de referencia WGS84, con bordes geodésicos. Puedes usar el tipo de datos GEOGRAPHY si llamas a una de las funciones geográficas de GoogleSQL.

Cómo cargar datos geoespaciales

Los puntos individuales en la Tierra pueden describirse solo a través de un par longitud, latitud. Por ejemplo, puedes cargar un archivo CSV que contenga valores de longitud y latitud y, luego, usar la función ST_GEOGPOINT para convertirlos en valores GEOGRAPHY.

Para geografías más complejas, puedes cargar los siguientes formatos de datos geoespaciales en una columna GEOGRAPHY:

  • Texto conocido (WKT)
  • Objeto binario conocido (WKB)
  • GeoJSON

Carga datos WKT o WKB

WKT es un formato de texto para describir formas geométricas individuales mediante puntos, líneas, polígonos con agujeros opcionales o una colección de todos ellos. WKB es la versión binaria del formato WKT. WKB puede codificarse en hexadecimal para formatos que no admiten datos binarios, como JSON.

Por ejemplo, lo siguiente define un punto en WKT:

POINT(-121 41)

Por lo general, para describir una característica espacial, WKT se incorpora en un formato de archivo de contenedor, como un archivo CSV, o en una tabla de base de datos. Por lo general, una fila de archivo o de tabla corresponde a la característica espacial. Todo el archivo o toda la tabla corresponden a la colección de características. A fin de cargar datos WKT en BigQuery, proporciona un esquema que especifique una columna GEOGRAPHY para los datos geoespaciales.

Por ejemplo, puedes tener un archivo CSV que contenga los siguientes datos:

"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

Puedes cargar este archivo si ejecutas el comando load de la herramienta de línea de comandos de bq:

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

Para obtener más información sobre la carga de datos en BigQuery, consulta Introducción a la carga de datos.

Para transmitir datos WKT a una tabla existente de BigQuery con una columna GEOGRAPHY, serializa los datos como una string en la solicitud a la API.

bq

Ejecuta el comando insert de la herramienta de línea de comandos de bq:

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

Python

Antes de probar este ejemplo, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Python.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas 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 obtener más información sobre la transmisión de datos en BigQuery, consulta Transmite datos a BigQuery.

También puedes convertir una string de texto WKT en un valor GEOGRAPHY con la función ST_GeogFromText.

Cómo cargar datos de GeoJSON

GeoJSON es un formato basado en JSON para geometrías y características espaciales. Por ejemplo, lo siguiente define un punto en GeoJSON:

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

Los datos de GeoJSON pueden contener cualquiera de los siguientes tipos de objetos:

  • Objetos de geometría. Un objeto de geometría es una forma espacial descrita como una unión de puntos, líneas y polígonos con agujeros opcionales.
  • Objetos de características. Un objeto de característica contiene una geometría y pares de nombre/valor adicionales, cuyo significado es específico en cada aplicación.
  • Colecciones de características. Una colección de características es un conjunto de objetos de características.

Existen dos maneras de cargar datos de GeoJSON en BigQuery:

Carga archivos GeoJSON delimitados por saltos de línea

Un archivo GeoJSON delimitado por saltos de línea contiene una lista de objetos de características GeoJSON, uno por línea en el archivo. Un objeto de característica GeoJSON es un objeto JSON con los siguientes miembros:

  • type: Para los objetos de atributos, el valor debe ser Feature. BigQuery valida el valor, pero no lo incluye en el esquema de la tabla.

  • geometry. El valor es un objeto GeoJSON Geometry o null. BigQuery convierte este miembro en un valor GEOGRAPHY.

  • properties. El valor es cualquier objeto JSON o nulo. Si el valor no es null, BigQuery carga cada miembro del objeto JSON como una columna de tabla separada. Para obtener más información sobre cómo BigQuery analiza los tipos de datos JSON, consulta Detalles de carga de datos JSON.

  • id. Es opcional. Si está presente, el valor puede ser una string o un número. BigQuery carga este valor en una columna llamada id.

Si el objeto de la función contiene otros miembros que no se enumeran aquí, BigQuery los convierte directamente en columnas de tabla.

Puedes cargar un archivo GeoJSON delimitado por saltos de línea con el comando bq load de la herramienta de línea de comandos de bq, de la siguiente manera:

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

Reemplaza lo siguiente:

  • DATASET es el nombre del conjunto de datos.
  • TABLE es el nombre de la tabla de destino.
  • FILE_PATH_OR_URI es una ruta de acceso a un archivo local o un URI de Cloud Storage.

En el ejemplo anterior, se habilita la detección automática de esquema. Para tener más control sobre cómo BigQuery convierte los valores dentro del objeto properties, puedes proporcionar un esquema explícito. Para obtener más información, consulta Especifica esquemas. Si proporcionas un esquema explícito, no incluyas una columna type de nivel superior en la definición de esquema. Para cada miembro del miembro properties, define columnas separadas, no una sola columna anidada.

Como se define en RFC 7946, una estructura de datos GeoJSON completa es un objeto JSON único. Muchos sistemas exportan datos de GeoJSON como un objeto FeatureCollection único que contiene todas las geometrías. Si deseas cargar este formato en BigQuery, debes convertir el archivo. Para ello, quita el objeto FeatureCollection a nivel raíz y divide los objetos de atributos individuales en líneas separadas. Por ejemplo, en el siguiente comando, se usa la herramienta de línea de comandos de jq para dividir un archivo GeoJSON en formato delimitado por saltos de línea:

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

Crea una tabla externa a partir de un archivo GeoJSON delimitado por saltos de línea

Puedes consultar un archivo GeoJSON delimitado por saltos de línea almacenado en Cloud Storage mediante la creación de una tabla externa. Para crear la tabla externa, usa la declaración DDL CREATE EXTERNAL TABLE. En la cláusula OPTIONS, configura la opción format como NEWLINE_DELIMITED_JSON y la opción json_extension como GEOJSON.

Ejemplo:

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

Cómo cargar datos de geometría GeoJSON

Las estadísticas geoespaciales admiten la carga de objetos de geometría GeoJSON individuales que se incorporan como strings de texto en otros tipos de archivos. Por ejemplo, puedes cargar un archivo CSV en el que una de las columnas contenga un objeto de geometría GeoJSON.

Si deseas cargar este tipo de datos GeoJSON en BigQuery, proporciona un esquema que especifique una columna GEOGRAPHY para los datos GeoJSON. Debes proporcionar el esquema de forma manual. De lo contrario, si la detección automática está habilitada, BigQuery carga los datos como un valor STRING.

Las estadísticas geoespaciales no admiten la carga de objetos de atributos GeoJSON o colecciones de atributos con este enfoque. Si necesitas cargar objetos de atributos, considera usar archivos GeoJSON delimitados por saltos de línea.

Para transmitir datos de GeoJSON a una tabla de BigQuery existente con una columna GEOGRAPHY, serializa los datos como una string en la solicitud a la API.

bq

Ejecuta el comando insert de la herramienta de línea de comandos de bq:

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

Python

Antes de probar este ejemplo, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Python.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas 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}")

También puedes convertir un objeto de geometría GeoJSON en un valor GEOGRAPHY con la función ST_GEOGFROMGEOJSON. Por ejemplo, puedes almacenar las geometrías como valores STRING y ejecutar una consulta que llame a ST_GEOGFROMGEOJSON.

Sistemas de coordenadas y bordes

En el análisis geoespacial, los puntos son posiciones en la superficie de un esferoide WGS84, expresadas como longitud y latitud geodésica. Un borde es una geodésica esférica entre dos extremos. (Es decir, los bordes son la ruta más corta en la superficie de una esfera).

El formato WKT no proporciona un sistema de coordenadas. Cuando se cargan datos WKT, las estadísticas geoespaciales suponen que los datos usan coordenadas WGS84 con bordes esféricos. Asegúrate de que los datos de origen coincidan con el sistema de coordenadas, a menos que las geografías sean lo suficientemente pequeñas como para que se pueda ignorar la diferencia entre los bordes esféricos y planos.

GeoJSON usa de forma explícita coordenadas WGS84 con bordes planos. Cuando cargas datos GeoJSON, los análisis geoespaciales convierten los bordes planos en bordes esféricos. Los análisis geoespaciales agregan puntos adicionales a la línea cuando es necesario para que la secuencia convertida de bordes permanezca a menos de 10 metros de la línea geodésica original. Este proceso se conoce como teselación o densificación no uniforme. No puedes controlar directamente el proceso de teselación.

Para cargar geografías con bordes esféricos, usa WKT. Para cargar geografías con bordes planos, que generalmente se denominan geometrías, es más simple usar GeoJSON. Sin embargo, si tus datos de geometría ya están en formato WKT, puedes cargar los datos como un tipo STRING y, luego, usar la función ST_GEOGFROMTEXT para convertirlos a valores GEOGRAPHY. Establece el parámetro planar en TRUE para interpretar los datos como planos.

Cuando elijas un formato de intercambio, asegúrate de comprender el sistema de coordenadas que usan tus datos de origen. La mayoría de los sistemas admiten de forma explícita el análisis de la geografía (a diferencia de la geometría) a partir de WKT; de lo contrario, adoptan bordes planos.

Tus coordenadas deben indicar primero la longitud y, luego, la latitud. Si la geografía tiene segmentos o bordes largos, deben ser teselados, porque las estadísticas geoespaciales los interpretan como geodésicas esféricas, que pueden no corresponder con el sistema de coordenadas en el que se originaron tus datos.

Orientación poligonal

En una esfera, cada polígono tiene un polígono complementario. Por ejemplo, un polígono que describe los continentes de la Tierra tendría un polígono complementario que describa los océanos de la Tierra. Debido a que los dos polígonos se describen mediante los mismos anillos de límite, se requieren reglas para resolver la ambigüedad relacionada con cuál de los dos polígonos se describe mediante una string WKT determinada.

Cuando cargas strings WKT y WKB desde archivos o mediante la transferencia de transmisión, las estadísticas geoespaciales suponen que los polígonos en la entrada están orientados de la siguiente manera: si atraviesas el límite del polígono en el orden de los vértices de entrada, el interior del polígono está a la izquierda. Las estadísticas geoespaciales usan la misma regla para la exportación de objetos de geografía a strings WKT y WKB.

Si usas la función ST_GeogFromText para convertir una string WKT a un valor GEOGRAPHY, el parámetro oriented especificará cómo la función determinará el polígono:

  • FALSE: Interpreta la entrada como el polígono con el área más pequeña. Este es el comportamiento predeterminado.

  • TRUE: Usa la regla de orientación hacia la izquierda que se describió anteriormente. Esta opción te permite cargar polígonos con un área más grande que un hemisferio.

Debido a que las strings GeoJSON se definen en un mapa plano, la orientación se puede determinar sin ambigüedad, incluso si la entrada no sigue la regla de orientación definida en la especificación de formato GeoJSON, RFC 7946.

Cómo manejar datos espaciales que tienen un formato incorrecto

Cuando cargas datos espaciales de otras herramientas en BigQuery, es posible que se produzcan errores de conversión debido a datos no válidos de WKT o GeoJSON. Por ejemplo, un error como Edge K has duplicate vertex with edge N indica que el polígono tiene vértices duplicados (además del primero y el último).

A fin de evitar problemas de formato, puedes utilizar una función que genere resultados compatibles con los estándares. Por ejemplo, cuando exportas datos desde PostGIS, puedes usar la función ST_MakeValid de PostGIS para estandarizar el resultado. De manera alternativa, puedes importar tus datos como texto y, luego, convertirlos llamando ST_GEOGFROMTEXT o ST_GEOGFROMGEOJSON con el parámetro make_valid. Cuando make_valid es TRUE, estas funciones intentan reparar polígonos no válidos.

Para encontrar o ignorar los datos que tienen un formato incorrecto, utiliza el prefijo de la función SAFE a fin de generar los datos problemáticos. Por ejemplo, en la siguiente consulta, se usa el prefijo SAFE para recuperar los datos espaciales que tienen un formato incorrecto.

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

Limitaciones

Los análisis geoespaciales no son compatibles con las siguientes funciones en formatos geoespaciales:

  • Geometrías tridimensionales. Esto incluye el sufijo "Z" en el formato WKT y la coordenada de altitud en el formato GeoJSON.
  • Sistemas de referencia lineal. Esto incluye el sufijo "M" en el formato WKT.
  • Objetos de geometría WKT aparte de las primitivas de geometría y las geometrías multiparte. En particular, las estadísticas geoespaciales solo admiten Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon y GeometryCollection.

Consulta ST_GeogFromGeoJson y ST_GeogFromText para conocer las restricciones específicas de los formatos de entrada GeoJson y WKT.

Carga datos geoespaciales de Google Earth Engine

Google Earth Engine es una plataforma de datos geoespacial que compila y analiza estadísticas de imágenes de observación de la Tierra y satelitales mediante datos de trama, en los que los datos se organizan sobre una cuadrícula de celdas que representan información sobre imágenes digitales. Si bien BigQuery funciona principalmente con datos vectoriales tabulares, los usuarios pueden usar sus datos de BigQuery junto con datos de trama de Earth Engine para incorporar conjuntos de datos de vector y de trama en sus flujos de trabajo.

Para obtener información sobre cómo exportar datos de Earth Engine a BigQuery, consulta Exporta a BigQuery.

Transformación de datos geoespaciales

Si la tabla contiene columnas separadas para la longitud y la latitud, puedes transformar los valores en geografías con las funciones geográficas de GoogleSQL, como ST_GeogPoint. Por ejemplo, si tienes dos columnas DOUBLE para longitud y latitud, puedes crear una columna de geografía con la siguiente consulta:

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

BigQuery puede convertir strings WKT y GeoJSON a tipos de geografía. Si tus datos están en otro formato, como Shapefiles, usa una herramienta externa para convertir los datos a un formato de archivo de entrada compatible, como un archivo CSV, con columnas GEOGRAPHY codificadas como WKT o GeoJSON.

Particiona y agrupa en clústeres datos geoespaciales

Puedes particionar y agrupar en clústeres las tablas que contengan columnas GEOGRAPHY. Puedes usar una columna GEOGRAPHY como columna de agrupamiento en clústeres, pero no puedes usar una columna GEOGRAPHY como columna de partición.

Si almacenas datos de GEOGRAPHY en una tabla y tus consultas filtran datos con un predicado espacial, asegúrate de que la columna GEOGRAPHY agrupe los datos. Por lo general, esto mejora el rendimiento de las consultas y podría reducir el costo. Un predicado espacial llama a una función de geografía booleana y tiene una columna GEOGRAPHY como uno de los argumentos. En el siguiente ejemplo, se muestra un predicado espacial que usa la función ST_DWithin:

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

Usa JOIN con datos espaciales

Las JOIN espaciales son uniones de dos tablas con una función geográfica de predicado en la cláusula WHERE. Por ejemplo:

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

Las uniones espaciales funcionan mejor cuando se conservan los datos de geografía. En el ejemplo anterior, se crean los valores de geografía en la consulta. Es más eficaz guardar los valores de geografía en una tabla de BigQuery.

Por ejemplo, en la siguiente consulta, se recuperan los pares de longitud y latitud, y se los convierte en puntos geográficos. Cuando ejecutes esta consulta, debes especificar una tabla de destino nueva para almacenar los resultados de la consulta, como se muestra a continuación:

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

BigQuery implementa JOIN espaciales optimizadas para operadores INNER JOIN y CROSS JOIN con las siguientes funciones de predicado de GoogleSQL:

Las uniones espaciales no están optimizadas en los siguientes casos:

  • En uniones LEFT, RIGHT o FULL OUTER
  • En casos que involucran uniones ANTI
  • Cuando se niega el predicado espacial

Una JOIN que usa el predicado ST_DWithin solo está optimizada cuando el parámetro de distancia es una expresión constante.

Exporta datos espaciales

Cuando exportas datos espaciales desde BigQuery, los valores de la columna GEOGRAPHY siempre se formatean como strings WKT. Para exportar datos en formato GeoJSON, usa la función ST_AsGeoJSON.

Si las herramientas que usas para analizar los datos exportados no comprenden el tipo de datos GEOGRAPHY, puedes convertir los valores de las columnas en strings con una función geográfica como ST_AsText o ST_AsGeoJSON. Las estadísticas geoespaciales agregan puntos adicionales a la línea cuando es necesario para que la secuencia de bordes convertida permanezca a menos de 10 metros de la línea geodésica original.

Por ejemplo, la siguiente consulta usa ST_AsGeoJSON para convertir los valores de GeoJSON en strings.

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

Los datos resultantes se verían de la siguiente manera:

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

La línea GeoJSON tiene dos puntos adicionales. Las estadísticas geoespaciales agregan estos puntos para que la línea GeoJSON siga de cerca la misma ruta en el suelo que la línea original.

¿Qué sigue?