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.

BigQuery GIS agrega compatibilidad a un tipo de datos GEOGRAPHY con SQL estándar. El tipo de datos GEOGRAPHY representa un conjunto de puntos en la superficie de la Tierra. Un conjunto de puntos es un grupo de puntos, líneas y polígonos en el esferoide de referencia WGS84 con bordes geodésicos.

Llama a una de las funciones geográficas de SQL estándar para usar el tipo de datos GEOGRAPHY. El resultado de las funciones geográficas se procesa como WKT (texto conocido). WKT utiliza un formato de longitud en primer lugar y latitud en segundo lugar.

Formatos de datos geoespaciales

Los puntos individuales en la Tierra pueden describirse solo a través de un par (longitude, latitude). Para describir geografías más complejas, como líneas y polígonos, BigQuery te permite cargar datos geoespaciales en una columna GEOGRAPHY si los datos están en uno de los siguientes formatos admitidos:

Diferencias entre WKT y GeoJSON

Objetos de geometría frente a características espaciales y colecciones de características

A continuación, se indican los tipos comunes de objetos usados cuando se trabaja con datos espaciales:

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

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. Por ejemplo, un punto en WKT se vería de la siguiente manera:

POINT(-121 41)

Por lo general, para describir una característica espacial, WKT se incorpora en una tabla de base de datos o en algún formato de archivo de contenedor, a menudo un archivo CSV. Por lo general, una fila de archivo o de tabla corresponde a la característica espacial. Todo el archivo o la tabla corresponden a la colección de características.

WKB es una versión binaria del formato WKT.

GeoJSON es un formato basado en JSON más complejo para geometrías y características espaciales. Por ejemplo, un punto en GeoJSON se vería de la siguiente manera:

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

GeoJSON se usa para describir una de las opciones que se indican a continuación:

  • Un objeto de geometría individual. Un objeto único puede tener una forma espacial compleja, descrita como una unión de puntos, líneas y polígonos con agujeros opcionales. Esto es similar al uso del formato WKT.
  • Un objeto de característica. Un objeto de característica es un objeto con una geometría, más propiedades adicionales y arbitrarias con nombre. Esto es similar a una fila en un archivo CSV con una columna WKT.
  • Una colección de características. Las colecciones de características son un conjunto de objetos de características similares a una tabla en una base de datos o un archivo CSV con muchas filas y columnas.

BigQuery GIS solo admite objetos de geometría individuales en GeoJSON. Actualmente, BigQuery GIS no admite objetos de características GeoJSON, colecciones de características o el formato de archivo GeoJSON.

Sistemas de coordenadas y bordes

El formato WKT no proporciona un sistema de coordenadas, por lo que BigQuery GIS debe definir uno. En BigQuery GIS, los puntos WKT 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. En GeoJSON, el sistema de coordenadas consta explícitamente de coordenadas WGS84 con bordes planos.

Para generar una conversión entre estos dos tipos de bordes, BigQuery GIS agrega puntos adicionales a la línea cuando es necesario para que la secuencia convertida de bordes permanezca dentro de los 10 metros de la línea original. Este es un proceso conocido como teselación o densificación no uniforme. En la actualidad, no puedes controlar directamente el proceso de teselación.

Para importar geografías con bordes esféricos, usa WKT como en el siguiente ejemplo:

SELECT
  *,
  ST_GeogFromText(wkt) AS g
FROM
  mytable

Para importar geografías con bordes planos, a menudo llamadas “geometrías”, debes usar GeoJSON como en el siguiente ejemplo:

SELECT
  *,
  ST_GeogFromGeoJSON(geocol) AS g
FROM
  mytable

También puedes excluir la columna GeoJSON original de los resultados, como se muestra a continuación:

SELECT
  * EXCEPT(geocol),
  ST_GeogFromGeoJSON(geocol) AS geocol
FROM
  mytable

Asegúrate de usar el formato adecuado. La mayoría de los sistemas anunciarán su compatibilidad para analizar la geografía (no la geometría) de WKT, o adoptarán bordes planos, en cuyo caso GeoJSON debería usarse como un formato de intercambio.

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.

Cuando creas objetos de geografía a partir de una string WKT mediante la función ST_GeogFromText, hay dos opciones para determinar el polígono que describe la string WKT descritas a continuación:

  1. Interpretar la entrada como el polígono con el área más pequeña (predeterminado). No supone polígonos orientados (oriented = FALSE).

  2. Suponer polígonos orientados para permitir la carga de polígonos con un área más grande que la de un hemisferio (oriented = TRUE).

Estas reglas no se aplican cuando cargas strings GeoJSON. 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 GeoJSON RFC 7946, sección 3.1.6: polígono: anillos externos en sentido antihorario, anillos internos en sentido horario.

Carga datos de BigQuery GIS

Cuando cargas datos de BigQuery GIS en BigQuery, puedes especificar una columna GEOGRAPHY en la definición de esquema de la tabla. Cuando especificas el tipo de datos de la columna como GEOGRAPHY, BigQuery GIS puede detectar si los datos están en formato WKT o GeoJSON.

Cuando cargas objetos de geometría GeoJSON en una columna GEOGRAPHY, deben formatearse como strings de texto, no como objetos JSON. Esto se aplica incluso si el objeto se carga desde un archivo JSON delimitado por saltos de línea.

Si cargas datos mediante la detección automática de esquemas, los valores de geografía se cargan como STRING. Por el momento, la detección automática de esquemas no puede detectar columnas de geografía.

Si deseas obtener más información sobre cómo cargar datos en BigQuery, consulta Introducción a la carga de datos desde Cloud Storage.

Transforma datos de BigQuery GIS

Si la tabla contiene columnas distintas para longitud y latitud, puedes transformar los valores en geografías con las funciones de geografía 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 mediante la siguiente consulta:

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

En la actualidad, BigQuery admite la conversión de strings WKT y GeoJSON a tipos de geografía. El formato Shapefiles y muchos otros formatos deben convertirse mediante el uso de herramientas externas.

Cómo abordar datos espaciales que tienen un formato incorrecto

Cuando cargas datos en BigQuery, es posible que encuentres datos WKT o GeoJSON no válidos de otras herramientas que no pueden convertirse en una columna GEOGRAPHY. 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 utilizar la función ST_MakeValid para estandarizar el resultado.

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

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 GEOGRAPHY en una tabla que está particionada o agrupada en clústeres, y tus consultas filtran datos mediante un predicado espacial, debes asegurarte de que los datos de geografía tengan un compactado espacial. Un predicado espacial llama a una función de geografía booleana y tiene una columna GEOGRAPHY como uno de los argumentos. En la siguiente muestra, aparece un predicado espacial que utiliza la función ST_DWithin:

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

Por ejemplo, si tienes una tabla con columnas para COUNTRY, STATE y ZIP, agrega una columna a la tabla a fin de almacenar una versión concatenada de estas columnas. El siguiente fragmento de consulta lo demuestra:

CONCAT(country, '+', IFNULL(state, ''), '+', IFNULL(zip, '')) as loc

En este ejemplo, IFNULL se utiliza para eliminar los valores faltantes. Después de crear la columna concatenada, puedes utilizarla para agrupar en clústeres la tabla.

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(zcta_geom) AS polygon,
    COUNT(*) AS bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
    `bigquery-public-data.geo_us_boundaries.us_zip_codes` AS zip_codes
WHERE ST_DWithin(
         zip_codes.zcta_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 columna en strings mediante 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, en la siguiente consulta, se 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.

Trabaja con la geografía en las bibliotecas cliente de BigQuery

En la actualidad, solo la biblioteca cliente de BigQuery para Python es compatible con el tipo de datos GEOGRAPHY. Para otras bibliotecas cliente, convierte los valores GEOGRAPHY en strings con la función ST_ASTEXT o ST_ASGEOJSON. Por ejemplo, puedes usar la función ST_AsText de la siguiente manera: ST_AsText(ANY_VALUE(zip_regions_geometry.geometry)) AS geometry.

La conversión a texto mediante ST_AsText almacena solo un valor, y la conversión a WKT significa que los datos se anotan como un tipo STRING en lugar de un tipo GEOGRAPHY.

Pasos siguientes

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.