Cómo trabajar 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.

A fin de utilizar el tipo de datos GEOGRAPHY, llama a una de las funciones geográficas de SQL estándar. 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

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

  • Una geometría individual o un valor GEOGRAPHY representa un área de superficie en 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 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 específicos de la aplicación adicionales y arbitrarios.
  • Una colección de características espaciales es un conjunto de objetos de características.

WKT es un formato de texto para describir formas de geometría individuales con el uso de puntos, líneas, polígonos con orificios opcionales, o un conjunto de puntos, líneas o polígonos. Por ejemplo, un punto en WKT se vería de la siguiente manera:

POINT(-121 41)

A fin de describir una característica espacial, WKT generalmente está incorporado en algún formato de archivo del contenedor, a menudo un archivo CSV, o en una tabla de base de datos. Por lo general, una fila de archivo o una fila de tabla corresponden 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 utiliza para describir una de las opciones que se indican a continuación:

  • Un objeto de geometría individual. Ten en cuenta que un solo objeto puede tener una forma espacial compleja, que se describe como una unión de puntos, líneas y polígonos con orificios 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 define uno. En BigQuery GIS, los puntos de WKT son posiciones en la superficie de un esferoide WGS84 (longitud + 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 en que sea 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, utiliza WKT como en el siguiente ejemplo:

SELECT
  *,
  ST_GeogFromText(wkt) AS g
FROM
  [TABLE]

Para importar geografías con bordes planos (a menudo denominadas geometrías en otros sistemas), utiliza GeoJSON como en el siguiente ejemplo:

SELECT
  *,
  ST_GeogFromGeoJSON(geocol) AS g
FROM
  [TABLE]

También puedes excluir la columna GeoJSON original de los resultados:

SELECT
  * EXCEPT(geocol),
  ST_GeogFromGeoJSON(geocol) AS geocol
FROM
  [TABLE]

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

Tus coordenadas deben ser longitud, latitud y, si tienes segmentos o bordes largos, tendrás que asegurarte de que estén teselados porque BigQuery GIS los interpreta como geodésicos esféricos que pueden no corresponder al sistema de coordenadas en el que se originaron los datos.

Cómo cargar datos de GIS

Cuando cargas datos de GIS en BigQuery, puedes especificar una columna GEOGRAPHY en la definición del 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 los 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 está cargando 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 STRINGs. Actualmente, 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.

Cómo transformar los datos de GIS

Si la tabla contiene columnas separadas 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 con el uso de la siguiente consulta.

SELECT
  *,
  ST_GeogPoint(longitude, latitude) AS g
FROM
  [TABLE]

Actualmente, 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 tratar con 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 del ú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 utiliza el prefijo SAFE para recuperar los datos espaciales que tienen un formato incorrecto.

SELECT
  geojson AS bad_geojson
FROM
  [TABLE]
WHERE
  geojson IS NOT NULL
  AND SAFE.ST_GeogFromGeoJson(geojson) IS NULL

Cómo realizar la partición y el agrupamiento en clústeres de los datos de GIS

Puedes particionar y agrupar en clústeres las tablas que contienen columnas GEOGRAPHY, pero una columna GEOGRAPHY no puede utilizarse como la columna de partición o la columna de agrupamiento en clústeres.

Cuando almacenas datos GEOGRAPHY en una tabla que está particionada o agrupada en clústeres, si las consultas filtran datos mediante el uso de un predicado espacial, asegúrate de que los datos de geografía estén compactados de manera 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.

Cómo utilizar las sentencias JOIN con datos espaciales

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

#standardSQL
-- how many stations within 1 mile range of each zip code?
SELECT
    zipcode zip,
    ST_GeogFromText(ANY_VALUE(zip_codes.zipcode_geom)) polygon,
    COUNT(*) bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` bike_stations,
    `bigquery-public-data.utility_us.zipcode_area` zip_codes
WHERE ST_DWithin(
         ST_GeogFromText(zip_codes.zipcode_geom),
         ST_GeogPoint(bike_stations.longitude, bike_stations.latitude),
         1609.34)
GROUP BY zip
ORDER BY bike_stations desc

Las sentencias JOIN espaciales se implementan para los operadores INNER JOIN y CROSS JOIN con las siguientes funciones de predicado de SQL estándar:

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, latitud y se los convierte en puntos geográficos. Cuando ejecutas esta consulta, especificas una tabla de destino nueva para almacenar los resultados de la consulta.

SELECT
  *,
  ST_GeogPoint(pLongitude, pLatitude) AS p
FROM
  [TABLE]

Cómo exportar datos espaciales

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

Si las herramientas que utilizas para analizar los datos exportados no comprenden el tipo de datos GEOGRAPHY, puedes convertir los valores de columna en strings mediante el uso de una función geográfica, como ST_AsText o ST_AsGeoJSON. BigQuery GIS agrega puntos adicionales a la línea en que sea necesario para que la secuencia convertida de bordes permanezca dentro de los 10 metros de la línea geodésica original.

Por ejemplo, en la siguiente consulta, se utiliza 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] ] }

Ten en cuenta que la línea GeoJSON tiene dos puntos adicionales. BigQuery GIS agrega estos puntos de modo que la línea GeoJSON siga de cerca la misma ruta en el suelo que la línea original.

Cómo trabajar con la geografía en las bibliotecas cliente de BigQuery

Actualmente, las bibliotecas cliente de BigQuery no admiten el tipo de datos GEOGRAPHY.

Si utilizas bibliotecas cliente con BigQuery GIS, convierte los valores de GEOGRAPHY en WKT cuando sea necesario. Por ejemplo, utiliza la función ST_AsText: ST_AsText(ANY_VALUE(zip_regions_geometry.geometry)) AS geometry.

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

¿Qué sigue?

¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…

¿Necesitas ayuda? Visita nuestra página de asistencia.