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. Elle est souvent décrite à 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 est 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.
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 êtreFeature
. BigQuery valide la valeur mais ne l'inclut pas dans le schéma de la table.geometry
. La valeur est un objet GeoJSONGeometry
ounull
. BigQuery convertit ce membre en valeurGEOGRAPHY
.properties
. Cette valeur correspond à un objet JSON quelconque ou à une valeur nulle. Si la valeur n'est pasnull
, 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éeid
.
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
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.
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 en flux continu, 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 les informations provenant 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 les 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 en conjonction avec des données de trame depuis Earth Engine pour intégrer à la fois 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 section 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.