Utiliser des données géospatiales

Les analyses géospatiales vous permettent d'analyser des données géographiques dans BigQuery. Les données géographiques sont également appelées données géospatiales.

Les types d'objets courants associés aux données spatiales sont les suivants :

Une géométrie représente une surface sur la Terre. Cet objet est souvent décrit à l'aide de points, de lignes, de polygones ou d'un ensemble de points, de lignes et de polygones. Une collection de géométrie est une géométrie qui représente l'union spatiale de toutes les formes de la collection.
Une fonctionnalité spatiale représente un objet spatial logique. Elle combine une géométrie avec des attributs supplémentaires spécifiques à l'application.
Une collection de caractéristiques spatiales est un ensemble de caractéristiques spatiales.

Dans BigQuery, le type de données GEOGRAPHY représente une valeur géométrique ou une collection de géométries. Pour représenter les caractéristiques spatiales, créez une table avec une colonne GEOGRAPHY pour la géométrie, ainsi que des colonnes supplémentaires pour les attributs. Chaque ligne de la table est une caractéristique spatiale, et la table entière représente une collection de caractéristiques spatiales.

Le type de données GEOGRAPHY décrit un ensemble de points à la surface de la Terre. Un ensemble de points correspond à un ensemble de points, de lignes et de polygones sur le sphéroïde de référence WGS84, avec des arêtes géodésiques. Vous pouvez utiliser le type de données GEOGRAPHY en appelant l'une des fonctions de géographie de GoogleSQL.

Charger des données géospatiales

Les points uniques sur la Terre peuvent être définis par une paire (longitude, latitude). Par exemple, vous pouvez charger un fichier CSV contenant des valeurs de longitude et de latitude, puis utiliser la fonction ST_GEOGPOINT pour les convertir en valeurs GEOGRAPHY.

Pour les zones géographiques plus complexes, vous pouvez charger les formats de données géospatiales suivants dans une colonne GEOGRAPHY :

Texte connu (WKT)
Binaire connu (WKB)
GeoJSON

Charger des données WKT ou WKB

WKT est un format de texte permettant de décrire des formes géométriques individuelles à l'aide de points, de lignes, de polygones avec des trous facultatifs, ou d'un ensemble de points, de lignes ou de polygones. WKB est la version binaire du format WKT. WKB peut être encodé en hexadécimal pour les formats non compatibles avec les données binaires, comme JSON.

Par exemple, le code suivant définit un point dans WKT :

POINT(-121 41)

Pour décrire une fonctionnalité spatiale, WKT est généralement intégré dans un format de fichier conteneur, tel qu'un fichier CSV ou dans une table de base de données. Une ligne de fichier ou de table correspond généralement à la fonctionnalité spatiale. L'ensemble du fichier ou de la table correspond à la collection de la fonctionnalité. Pour charger des données WKT dans BigQuery, fournissez un schéma qui spécifie une colonne GEOGRAPHY pour les données géospatiales.

Par exemple, vous disposez d'un fichier CSV contenant les données suivantes :

"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

Vous pouvez charger ce fichier en exécutant la commande load de l'outil de ligne de commande bq :

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

Pour en savoir plus sur le chargement des données dans BigQuery, consultez la page intitulée Présentation du chargement de données.

Pour diffuser des données WKT en streaming vers une table BigQuery existante avec une colonne GEOGRAPHY, sérialisez les données sous forme de chaîne dans la requête API.

bq

Exécutez la commande insert de l'outil de ligne de commande bq :

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

Python

Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Python.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

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}")

Pour en savoir plus sur la diffusion de données en streaming dans BigQuery, consultez la page Diffuser des données en streaming dans BigQuery.

Vous pouvez également convertir une chaîne de texte WKT en valeur GEOGRAPHY à l'aide de la fonction ST_GeogFromText.

Charger des données GeoJSON

GeoJSON est un format basé sur JSON pour les géométries et les fonctionnalités spatiales. Par exemple, le code suivant définit un point en GeoJSON :

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

Les données GeoJSON peuvent contenir l'un des types d'objets suivants :

Objets Geometry : Un objet de géométrie est une forme spatiale, décrite comme une union de points, de lignes et de polygones avec des trous facultatifs.
Objets d'éléments géographiques. Un objet d'élément géographique contient une géométrie et des paires nom/valeur supplémentaires, dont la signification est spécifique à une application.
Collections de caractéristiques. Une collection de fonctionnalités spatiales est un ensemble d'objets d'éléments géographiques.

Pour charger des données GeoJSON dans BigQuery, deux possibilités s'offrent à vous :

Charger des fichiers GeoJSON délimités par un retour à la ligne.
Charger des objets de géométrie GeoJSON intégrés dans d'autres types de fichiers.

Charger des fichiers GeoJSON délimités par un retour à la ligne

Un fichier GeoJSON délimité par un retour à la ligne contient une liste d'objets de caractéristiques GeoJSON, à raison d'un par ligne. Un objet de caractéristiques GeoJSON est un objet JSON avec les membres suivants :

type. Pour les objets de caractéristiques, la valeur doit être Feature. BigQuery valide la valeur mais ne l'inclut pas dans le schéma de la table.
geometry. La valeur est un objet GeoJSON Geometry ou null. BigQuery convertit ce membre en valeur GEOGRAPHY.
properties. Cette valeur correspond à un objet JSON quelconque ou à une valeur nulle. Si la valeur n'est pas null, BigQuery charge chaque membre de l'objet JSON sous la forme d'une colonne de table distincte. Pour en savoir plus sur la manière dont BigQuery analyse les types de données JSON, consultez la section Détails du chargement des données JSON.
id Facultatif. Si cette valeur est présente, il peut s'agir d'une chaîne ou d'un nombre. BigQuery charge cette valeur dans une colonne nommée id.

Si l'objet de caractéristiques contient d'autres membres non répertoriés ici, BigQuery convertit ces membres directement en colonnes de table.

Vous pouvez charger un fichier GeoJSON délimité par un retour à la ligne à l'aide de la commande bq load de l'outil de ligne de commande bq, en procédant comme suit :

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

Remplacez les éléments suivants :

DATASET est le nom de votre ensemble de données.
TABLE est le nom de la table de destination.
FILE_PATH_OR_URI est un chemin d'accès à un fichier local ou à un URI Cloud Storage.

L'exemple précédent active la détection automatique de schéma. Pour mieux contrôler la façon dont BigQuery convertit les valeurs dans l'objet properties, vous pouvez fournir un schéma explicite à la place. Pour en savoir plus, consultez la page Spécifier des schémas. Si vous fournissez un schéma explicite, n'incluez pas de colonne de premier niveau type dans la définition de schéma. Pour chaque membre du membre properties, définissez des colonnes distinctes plutôt qu'une seule colonne imbriquée.

Conformément à la RFC 7946, une structure de données GeoJSON complète est un objet JSON unique. De nombreux systèmes exportent les données GeoJSON sous la forme d'un objet FeatureCollection unique contenant toutes les géométries. Pour charger ce format dans BigQuery, vous devez convertir le fichier en supprimant l'objet FeatureCollection au niveau racine et en fractionnant les objets de caractéristiques individuelles en lignes distinctes. Par exemple, la commande suivante utilise l'outil de ligne de commande jq pour fractionner un fichier GeoJSON afin d'obtenir un format délimité par un retour à la ligne :

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

Créer une table externe à partir d'un fichier GeoJSON délimité par un retour à la ligne

Vous pouvez interroger un fichier GeoJSON délimité par un retour à la ligne stocké dans Cloud Storage en créant une table externe. Pour créer la table externe, utilisez l'instruction LDD CREATE EXTERNAL TABLE. Dans la clause OPTIONS, définissez l'option format sur NEWLINE_DELIMITED_JSON et l'option json_extension sur GEOJSON.

Exemple :

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

Charger des données de géométrie GeoJSON

Les analyses géospatiales permettent de charger des objets de géométrie GeoJSON individuels intégrés sous forme de chaînes de texte dans d'autres types de fichiers. Par exemple, vous pouvez charger un fichier CSV dont l'une des colonnes contient un objet de géométrie GeoJSON.

Pour charger ce type de données GeoJSON dans BigQuery, fournissez un schéma qui spécifie une colonne GEOGRAPHY pour les données GeoJSON. Vous devez fournir le schéma manuellement. Dans le cas contraire, si la détection automatique est activée, BigQuery charge les données sous la forme d'une valeur STRING.

Les analyses géospatiales ne sont pas compatibles avec le chargement d'objets de caractéristiques GeoJSON ou de collections de caractéristiques à l'aide de cette approche. Si vous devez charger des objets de caractéristiques, envisagez d'utiliser des fichiers GeoJSON délimités par un retour à la ligne.

Pour diffuser des données GeoJSON en streaming dans une table BigQuery existante avec une colonne GEOGRAPHY, sérialisez les données sous forme de chaîne dans la requête API.

bq

Exécutez la commande insert de l'outil de ligne de commande bq :

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

Python

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}")

Vous pouvez également convertir un objet de géométrie GeoJSON en valeur GEOGRAPHY à l'aide de la fonction ST_GEOGFROMGEOJSON. Par exemple, vous pouvez stocker les géométries en tant que valeurs STRING, puis exécuter une requête qui appelle ST_GEOGFROMGEOJSON.

Systèmes de coordonnées et arêtes

Dans les analyses géospatiales, les points sont des positions sur la surface d'un sphéroïdde WGS84, exprimées par une longitude et une latitude géodésique. Une arête est une géodésique sphérique entre deux points de terminaison. (c'est-à-dire que les arêtes constituent le chemin le plus court à la surface d'une sphère).

Le format WKT ne fournit pas de système de coordonnées. Lors du chargement de données WKT, les analyses géospatiales supposent que les données utilisent des coordonnées WGS84 avec des arêtes sphériques. Assurez-vous que vos données sources correspondent à ce système de coordonnées, sauf si les zones géographiques sont suffisamment petites pour que la différence entre les arêtes sphériques et planes soit ignorée.

GeoJSON utilise explicitement les coordonnées WGS84 avec des arêtes planes. Lors du chargement de données GeoJSON, les analyses géospatiales convertissent les arêtes planes en arêtes sphériques. Les analyses géospatiales ajoutent des points supplémentaires à la ligne si nécessaire, de sorte que la séquence d'arêtes convertie reste à moins de 10 mètres de la ligne géodésique d'origine. Ce processus est appelé tessellation ou densification non uniforme. Il n'est actuellement pas possible de contrôler directement le processus de tessellation.

Pour charger des zones géographiques avec des arêtes sphériques, utilisez WKT. Pour charger des zones géographiques avec des arêtes planes, souvent appelées géométries, il est plus simple d'utiliser GeoJSON. Toutefois, si vos données de géométrie sont déjà au format WKT, vous pouvez également charger les données en tant que type STRING, puis utiliser la fonction ST_GEOGFROMTEXT pour la conversion en valeurs GEOGRAPHY. Définissez le paramètre planar sur TRUE pour interpréter les données en tant que planes.

Lorsque vous choisissez un format d'échange, veillez à comprendre le système de coordonnées utilisé par vos données source. La plupart des systèmes sont explicitement compatibles avec l'analyse de la zone géographique (par opposition à la géométrie) à partir de WKT, ou ils appliquent des arêtes planes.

Vos coordonnées doivent être longitude d'abord, puis latitude. Si la géographie comporte des segments ou des arêtes longs, vous devez les assembler, car les analyses géospatiales les interprètent comme des géodésiques sphériques, qui peuvent ne pas correspondre au système de coordonnées d'où proviennent vos données.

Orientation des polygones

Sur une sphère, chaque polygone dispose d'un polygone complémentaire. Par exemple, un polygone décrivant les continents de la Terre disposerait d'un polygone complémentaire décrivant les océans de la Terre. Comme les deux polygones sont décrits par les mêmes anneaux de limite, des règles sont nécessaires pour résoudre l'ambiguïté duquel des deux polygones est décrit par une chaîne WKT donnée.

Lorsque vous chargez des chaînes WKT et WKB à partir de fichiers ou en utilisant une ingestion de flux, les analyses géospatiales supposent que les polygones de l'entrée sont orientés comme suit : si vous traversez la limite du polygone dans l'ordre des sommets d'entrée, l'intérieur du polygone se trouve sur la gauche. Les analyses géospatiales utilisent la même règle lors de l’exportation d’objets géographiques vers des chaînes WKT et WKB.

Si vous utilisez la fonction ST_GeogFromText pour convertir une chaîne WKT en valeur GEOGRAPHY, le paramètre oriented spécifie comment la fonction détermine le polygone :

FALSE : Interpréte l'entrée comme le polygone ayant la plus petite surface. Il s'agit du comportement par défaut.
TRUE : utilise la règle d'orientation de gauche décrite précédemment. Cette option vous permet de charger des polygones dont la surface est supérieure à celle d'un hémisphère.

Étant donné que les chaînes GeoJSON sont définies sur une carte plane, l'orientation peut être déterminée sans ambiguïté, même si l'entrée ne suit pas la règle d'orientation définie dans la spécification du format GeoJSON.RFC 7946.

Traiter des données spatiales mal formatées

Lorsque vous chargez des données spatiales à partir d'autres outils dans BigQuery, des erreurs de conversion peuvent survenir en raison de données WKT ou GeoJSON incorrectes. Par exemple, une erreur telle que Edge K has duplicate vertex with edge N indique que le polygone comporte des sommets en double (outre le premier et le dernier).

Pour éviter les problèmes de mise en forme, vous pouvez utiliser une fonction qui génère une sortie conforme aux standards. Par exemple, lorsque vous exportez des données à partir de PostGIS, vous pouvez utiliser la fonction ST_MakeValid de PostGIS pour normaliser la sortie. Vous pouvez également importer vos données sous forme de texte, puis les convertir en appelant la méthode ST_GEOGFROMTEXT ou ST_GEOGFROMGEOJSON avec le paramètre make_valid. Lorsque make_valid a la valeur TRUE, ces fonctions tentent de réparer des polygones non valides.

Pour rechercher ou ignorer les données mal mises en forme, utilisez le préfixe de fonction SAFE pour exclure les données problématiques. Par exemple, la requête suivante utilise le préfixe SAFE pour récupérer des données spatiales mal formatées.

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

Contraintes

Les analyses géospatiales n'acceptent pas les fonctionnalités suivantes dans les formats géospatiaux :

Les géométries tridimensionnelles. Cela inclut le suffixe "Z" au format WKT et les coordonnées d'altitude au format GeoJSON.
Les systèmes de référence linéaires. Cela inclut le suffixe "M" au format WKT.
Les objets de géométrie WKT autres que les primitives de géométrie ou les géométries en plusieurs parties. Plus précisément, les analyses géospatiales n'acceptent que Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon et GeometryCollection.

Consultez ST_GeogFromGeoJson et ST_GeogFromText pour connaître les contraintes spécifiques aux formats d'entrée GeoJson et WKT.

Charger des données géospatiales Google Earth Engine

Google Earth Engine est une plate-forme de données géospatiales qui compile et analyse des insights à partir d'images satellite et d'observation de la Terre à l'aide de données de trame, où les données sont organisées sur une grille de cellules représentant des informations sur des images numériques. Bien que BigQuery fonctionne principalement avec des données vectorielles tabulaires, les utilisateurs peuvent utiliser leurs données BigQuery avec des données de trame depuis Earth Engine pour incorporer des ensembles de données vectorielles et de trame dans leurs workflows.

Pour en savoir plus sur l'exportation des données Earth Engine vers BigQuery, consultez la page Exporter vers BigQuery.

Transformer des données géospatiales

Si votre table contient des colonnes distinctes pour la longitude et la latitude, vous pouvez transformer les valeurs en données géographiques en utilisant les fonctions de géographie de GoogleSQL, telles que ST_GeogPoint. Par exemple, si vous avez deux colonnes DOUBLE pour la longitude et la latitude, vous pouvez créer une colonne de géographie à l'aide de la requête suivante :

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

BigQuery peut convertir des chaînes WKT et GeoJSON en types géographiques. Si vos données sont dans un autre format, tel que Shapefile, utilisez un outil externe pour les convertir dans un format de fichier d'entrée compatible (tel qu'un fichier CSV), avec des colonnes GEOGRAPHY encodées en tant que chaînes WKT ou GeoJSON.

Partitionner et mettre en cluster des données géospatiales

Vous pouvez partitionner et mettre en cluster des tables contenant des colonnes GEOGRAPHY. Vous pouvez utiliser une colonne GEOGRAPHY en tant que colonne de clustering, mais vous ne pouvez pas utiliser une colonne GEOGRAPHY en tant que colonne de partitionnement.

Lorsque vous stockez des données GEOGRAPHY dans une table, si vos requêtes filtrent les données à l'aide d'un prédicat spatial, assurez-vous que votre table est bien mise en cluster par la colonne GEOGRAPHY. De manière générale, cela améliore les performances des requêtes et peut réduire les coûts. Un prédicat spatial appelle une fonction de géographie booléenne et comporte une colonne GEOGRAPHY parmi ses arguments. L'exemple suivant illustre un prédicat spatial utilisant la fonction ST_DWithin :

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

Utiliser des jointures avec des données spatiales

Les jointures spatiales sont des jointures de deux tables avec une fonction géographique de prédicat dans la clause filtre (WHERE). Exemple :

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

Les jointures spatiales fonctionnent mieux lorsque les données géographiques sont persistantes. L'exemple ci-dessus crée les valeurs géographiques dans la requête. Il est plus performant de stocker les valeurs géographiques dans une table BigQuery.

Par exemple, la requête suivante récupère les paires de longitude et de latitude et les convertit en points géographiques. Lorsque vous exécutez cette requête, vous spécifiez une nouvelle table de destination pour en stocker les résultats.

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

BigQuery implémente des jointures spatiales optimisées pour les JOINTURES INTERNES et les JOINTURES CROISÉES avec les fonctions de prédicat suivantes pour Google SQL:

Les jointures spatiales ne sont pas optimisées :

pour les jointures GAUCHES, DROITES ou EXTERNES COMPLÈTES ;
dans les cas impliquant des jointures ANTI ;
lorsque le prédicat spatial est annulé.

Une jointure qui utilise le prédicat ST_DWithin est optimisé uniquement si le paramètre de distance est une expression constante.

Exporter des données spatiales

Lorsque vous exportez des données spatiales à partir de BigQuery, les valeurs de la colonne GEOGRAPHY sont toujours mises en forme en tant que chaînes WKT. Pour exporter des données au format GeoJSON, utilisez la fonction ST_AsGeoJSON.

Si les outils que vous utilisez pour analyser les données exportées ne fonctionnent pas avec le type de données GEOGRAPHY, vous pouvez convertir les valeurs des colonnes en chaînes à l'aide d'une fonction géographique, telle que ST_AsText ou ST_AsGeoJSON. Les données géospatiales ajoutent des points supplémentaires à la ligne si nécessaire, de sorte que la séquence d'arêtes convertie reste à moins de 10 mètres de la ligne géodésique d'origine.

Par exemple, la requête suivante utilise ST_AsGeoJSON pour convertir des valeurs GeoJSON en chaînes.

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

Les données résultantes pourraient ressembler à ceci :

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

La ligne GeoJSON comporte deux points supplémentaires. Les analyses géospatiales ajoutent ces points afin que la ligne GeoJSON suive de près le même chemin au sol que la ligne d'origine.

Étape suivante

Pour commencer à utiliser les analyses géospatiales, consultez la page Premiers pas avec les analyses géospatiales pour les analystes de données.
Pour en savoir plus sur les options de visualisation des analyses géospatiales, consultez la page Visualiser des données géospatiales.
Pour en savoir plus sur les fonctions GoogleSQL dans les analyses géospatiales, consultez la page Fonctions de géographie dans Google SQL.