Utiliser des données BigQuery SIG

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

BigQuery SIG intègre laprise en charge des données de type GEOGRAPHY au langage SQL standard. Le type de donnée GEOGRAPHY représente un jeu de points sur la surface de la Terre. Un jeu 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.

Appelez l'une des fonctions de géographie du langage SQL standard pour utiliser le type de données GEOGRAPHY. Le résultat des fonctions de géographie est affiché au format WKT (texte connu). WKT utilise un format longitude, puis latitude.

Formats de données géospatiales

Les points uniques sur la Terre peuvent être définis par une paire (longitude, latitude). Pour définir des zones géographiques plus complexes telles que des lignes et des polygones, BigQuery vous permet de charger des données géospatiales dans une colonne GEOGRAPHY si les données sont dans l'un des formats pris en charge suivants :

Différences entre WKT et GeoJSON

Différences entre les objets à géométrie et les fonctionnalités spatiales et collections d'éléments géographiques

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

  • Une géométrie individuelle ou une valeur GEOGRAPHY 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étries représente une union spatiale de toutes les formes de la collection.
  • Une fonctionnalité spatiale représente un objet spatial logique. Elle combine la géométrie avec des attributs arbitraires supplémentaires spécifiques à l'application.
  • Une collection de fonctionnalités spatiales est un ensemble d'objets d'éléments géographiques.

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. Par exemple, un point dans WKT ressemblerait à ceci :

POINT(-121 41)

Pour décrire une fonctionnalité spatiale, WKT est généralement intégré dans un format de fichier de conteneur, souvent 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 d'éléments géographiques.

WKB est la version binaire du format WKT.

GeoJSON est un format plus complexe de géométrie et de fonctionnalités spatiales basé sur JSON. Par exemple, un point dans GeoJSON ressemblerait à ceci :

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

GeoJSON est utilisé pour décrire l’un des éléments suivants :

  • Un objet de géométrie individuel. Un objet individuel peut avoir une forme spatiale complexe, décrite comme une union de points, de lignes et de polygones avec des trous facultatifs. Cet objet est semblable à l'utilisation du format WKT.
  • Un objet Feature (élément géographique). Un objet d'élément géographique est un objet avec une géométrie, ainsi que des propriétés nommées supplémentaires et arbitraires. Cet objet est semblable à une ligne dans un fichier CSV avec une colonne WKT.
  • Une FeatureCollection (collection d'éléments géographiques). Une collection d'éléments géographiques est un ensemble d'objets d'éléments géographiques semblable à une table dans une base de données ou un fichier CSV comportant de nombreuses lignes et colonnes.

BigQuery SIG n'accepte que les objets à géométrie individuels dans GeoJSON. Il n'est pour l'instant pas compatible avec les objets de fonctionnalité GeoJSON ou le format de fichier GeoJSON.

Systèmes de coordonnées et arêtes

Le format WKT ne fournissant pas de système de coordonnées, BigQuery SIG en définit un. Dans BigQuery SIG, les points WKT sont des positions sur la surface d'un sphéroïde 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. Dans GeoJSON, le système de coordonnées est explicitement celui des coordonnées WGS84 avec des arêtes planes.

Pour effectuer la conversion entre ces deux types d'arêtes, BigQuery SIG ajoute des points supplémentaires à la ligne, si nécessaire, afin que la séquence d'arêtes convertie reste à moins de 10 mètres de la ligne d'origine. C'est un processus appelé tessellation ou densification non uniforme. Il n'est actuellement pas possible de contrôler directement le processus de tessellation.

Pour importer des géographies avec des arêtes sphériques, utilisez WKT comme dans l'exemple suivant :

SELECT
  *,
  ST_GeogFromText(wkt) AS g
FROM
  mytable

Pour importer des zones géographiques avec des arêtes planes, souvent appelées "géométries", utilisez GeoJSON comme dans l'exemple suivant :

SELECT
  *,
  ST_GeogFromGeoJSON(geocol) AS g
FROM
  mytable

Vous pouvez également exclure des résultats la colonne GeoJSON d'origine :

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

Veillez à utiliser le format adéquat. La plupart des systèmes mentionnent clairement s'ils sont compatibles avec l'analyse de la géographie (et non celle de la géométrie) avec WKT ou s'ils supposent des arêtes planes, auquel cas GeoJSON doit être utilisé comme format d'échange.

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 BigQuery GIS les interprète 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, BigQuery SIG suppose 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. BigQuery SIG utilise la même règle lors de l’exportation d’objets géographiques vers des chaînes WKT et WKB.

Lorsque vous créez des objets géographiques à partir d'une chaîne WKT à l'aide de la fonction ST_GeogFromText, deux options permettent de déterminer quel polygone est décrit par la chaîne WKT.

  1. (Par défaut) Interprétez l'entrée comme le polygone ayant la plus petite surface. Ne partez pas du principe que les polygones sont orientés (oriented = FALSE).

  2. Supposez que les polygones orientés permettent de charger des polygones dont la surface est supérieure à celle d’un hémisphère (oriented = TRUE).

Ces règles ne s'appliquent pas lorsque vous chargez des chaînes GeoJSON. Les chaînes GeoJSON étant 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 référence GeoJSON RFC 7946, section 3.1.6. "Polygon" traitant des anneaux externes dans le sens antihoraire et anneaux internes dans le sens horaire.

Charger des données BigQuery SIG

Lorsque vous chargez des données dans BigQuery SIG, vous pouvez spécifier une colonne GEOGRAPHY dans la définition du schéma de la table. Lorsque vous spécifiez la colonne avec le type de données GEOGRAPHY, BigQuery SIG peut détecter si les données sont au format WKT ou GeoJSON.

Lorsque vous chargez des objets de géométrie GeoJSON dans une colonne GEOGRAPHY, ils doivent être mis en forme en tant que chaînes de texte et non en tant qu'objets JSON. Cela est vrai même si l'objet est en cours de chargement à partir d'un fichier JSON délimité par un retour à la ligne.

Si vous chargez des données à l'aide de la détection automatique de schéma, les valeurs géographiques sont chargées sous forme de STRING. Actuellement, la détection automatique de schéma ne peut pas détecter les colonnes géographiques.

Pour en savoir plus sur le chargement de données dans BigQuery, consultez la page Introduction au chargement de données depuis Cloud Storage.

Transformer les données BigQuery SIG

Si votre table contient des colonnes distinctes pour la longitude et la latitude, vous pouvez transformer les valeurs en données géographiques à l'aide des fonctions de géographie du langage SQL standard 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 prend actuellement en charge la conversion des chaînes WKT et GeoJSON en types géographiques. Le format Shapefile ainsi que de nombreux autres formats doivent être convertis à l'aide d'outils externes.

Traiter des données spatiales à la mise en forme incorrecte

Lorsque vous chargez des données dans BigQuery, il est possible que vous rencontriez des données WKT ou GeoJSON incorrectes provenant d'autres outils et ne pouvant pas être converties en colonne GEOGRAPHY. Par exemple, une erreur telle que Edge K has duplicate vertex with edge N indique que le polygone a 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 depuis PostGIS, vous pouvez utiliser la fonction ST_MakeValid pour normaliser la sortie.

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 mises en forme.

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

Partitionner et mettre en cluster des données BigQuery SIG

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 partitionnée ou en cluster, si vos requêtes filtrent les données à l'aide d'un prédicat spatial, assurez-vous que vos données géographiques sont compactes. Un prédicat spatial appelle une fonction de géographie booléenne et comporte une colonne GEOGRAPHY parmi ses arguments. L'exemple suivant montre un prédicat spatial utilisant la fonction ST_DWithin :

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

Par exemple, si vous avez une table avec des colonnes COUNTRY, STATE et ZIP, ajoutez une colonne à la table pour stocker une version concaténée de ces colonnes. Le fragment de requête suivant illustre ce processus :

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

Dans cet exemple, IFNULL est utilisé pour éliminer les valeurs manquantes. Après avoir créé la colonne concaténée, vous pouvez l'utiliser pour mettre la table en cluster.

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 :

#standardSQL
-- how many stations within 1 mile range of each zip code?
SELECT
    zipcode AS zip,
    ST_GeogFromText(ANY_VALUE(zip_codes.zipcode_geom)) AS polygon,
    COUNT(*) AS bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
    `bigquery-public-data.utility_us.zipcode_area` AS 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

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 met en œuvre des jointures spatiales optimisées pour les JOINTURES INTERNES et les JOINTURES CROISÉES avec les fonctions de prédicat suivantes en langage SQL standard :

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ée uniquement lorsque 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 de colonne en chaînes à l'aide d'une fonction géographique telle que ST_AsText ou ST_AsGeoJSON. SIG BigQuery ajoute des points supplémentaires à la ligne si nécessaire afin 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 les 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. BigQuery SIG ajoute ces points afin que la ligne GeoJSON suive de près le même chemin au sol que la ligne d'origine.

Travailler avec la géographie dans les bibliothèques clientes BigQuery

Seule la bibliothèque cliente BigQuery pour Python prend actuellement en charge le type de données GEOGRAPHY. Pour les autres bibliothèques clientes, convertissez les valeurs GEOGRAPHY en chaînes à l'aide de la fonction ST_ASTEXT ou ST_ASGEOJSON. Par exemple, utilisez la fonction ST_AsText : ST_AsText(ANY_VALUE(zip_regions_geometry.geometry)) AS geometry.

La conversion en texte à l'aide de ST_AsText ne stocke qu'une seule valeur, et la conversion au format WKT signifie que les données seront annotées en tant que type STRING au lieu du type GEOGRAPHY.

Étapes suivantes

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.