Trabaja con datos de BigQuery GIS

BigQuery GIS te permite 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 llamar a una de las funciones geográficas de SQL estándar para usar el tipo de datos GEOGRAPHY.

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.

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 bq de la herramienta de línea de comandos de load:

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 esta muestra, 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. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery.

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.

BigQuery GIS admite la carga de objetos de geometría GeoJSON individuales incorporados 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.

BigQuery GIS no admite la carga de objetos de características GeoJSON, colecciones de características ni el formato de archivo GeoJSON.

A fin de cargar datos de GeoJSON en BigQuery, proporciona un esquema que especifique una columna GEOGRAPHY para los datos de GeoJSON. Da formato a los objetos de geometría como strings de texto, no como objetos JSON, incluso si el archivo de datos es un archivo JSON delimitado 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 esta muestra, 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. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery.

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.

Sistemas de coordenadas y bordes

En BigQuery GIS, 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, BigQuery GIS supone 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 se cargan datos GeoJSON, BigQuery GIS convierte los bordes planos en los bordes esféricos. BigQuery GIS agrega 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. 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 BigQuery GIS los interpreta 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, BigQuery GIS supone 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. BigQuery GIS usa 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

BigQuery GIS no admite las siguientes características en los 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, BigQuery GIS solo admite 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.

Transforma datos de BigQuery GIS

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 SQL estándar, 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 de BigQuery GIS

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 SQL estándar:

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. BigQuery GIS agrega 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. BigQuery GIS agrega estos puntos para que la línea GeoJSON siga de cerca la misma ruta en el suelo que la línea original.

¿Qué sigue?