Types de données géospatiales

Snowflake offre un support natif pour les fonctionnalités géospatiales telles que les points, les lignes et les polygones sur la surface de la Terre.

Dans ce chapitre :

Types de données

Snowflake fournit les types de données suivants pour les données géospatiales :

  • Le type de données GEOGRAPHY, qui modélise la Terre comme si elle était une sphère parfaite.

  • Le type de données GEOMETRY, qui représente les caractéristiques dans un système de coordonnées planes (euclidien, cartésien).

Type de données GEOGRAPHY

Le type de données GEOGRAPHY suit la norme WGS 84 (ID de référence spatiale 4326 ; pour plus de détails, voir https://spatialreference.org/ref/epsg/wgs-84/).

Les points sur la Terre sont représentés par des degrés de longitude (de -180 degrés à +180 degrés) et de latitude (-90 à +90 degrés).

L’altitude n’est actuellement pas prise en charge.

Les segments de ligne sont interprétés comme des arcs géodésiques sur la surface de la Terre.

Snowflake fournit également des fonctions géospatiales qui fonctionnent sur le type de données GEOGRAPHY.

Si vous disposez de données géospatiales (par exemple, des données de longitude et de latitude, WKT, WKB, GeoJSON, etc.), vous devez convertir et stocker ces données dans des colonnes GEOGRAPHY, plutôt que de conserver les données dans leur format d’origine dans des colonnes VARCHAR, VARIANT ou NUMBER. Le stockage de vos données dans des colonnes GEOGRAPHY peut améliorer considérablement les performances des requêtes qui utilisent la fonctionnalité géospatiale.

Type de données GEOMETRY

Le type de données GEOMETRY représente les caractéristiques dans un système de coordonnées planes (euclidien, cartésien).

Les coordonnées sont représentées par des paires de nombres réels (x, y). Actuellement, seules les coordonnées 2D sont prises en charge.

Les unités de X et Y sont déterminées par le système de référence spatiale (SRS) associé à l’objet GEOMETRY. Le système de référence spatiale est identifié par l’identificateur de système de référence spatiale (SRID). À moins que le SRID ne soit fourni lors de la création de l’objet GEOMETRY ou en appelant ST_SETSRID, le SRID est 0.

Snowflake fournit un ensemble de fonctions géospatiales qui opèrent sur le type de données GEOMETRY. Pour ces fonctions :

  • Toutes les fonctions supposent des coordonnées planes, même si la géométrie utilise un SRS non plan.

  • Les fonctions de mesure (par exemple, ST_LENGTH) utilisent les mêmes unités que le système de coordonnées.

  • Pour les fonctions qui acceptent plusieurs expressions GEOMETRY comme arguments (par exemple, ST_DISTANCE), les expressions d’entrée doivent être définies dans le même SRS.

Entrée et sortie géospatiales

Les sections suivantes traitent des formats standard et des types d’objets pris en charge lors de la lecture et de l’écriture de données géospatiales.

Formats d’entrée et de sortie standard pris en charge

Les types de données GEOGRAPHY et GEOMETRY prennent en charge les formats industriels standard suivants pour l’entrée et la sortie :

Vous pouvez également trouver les références suivantes utiles :

Tout écart par rapport à ces normes est indiqué explicitement dans la documentation de Snowflake.

Note sur le traitement GeoJSON des valeurs GEOGRAPHY

Les normes WKT et WKB spécifient uniquement un format ; la sémantique des objets WKT/WKB dépend du système de référence - par exemple, un plan ou une sphère.

La norme GeoJSON, en revanche, spécifie à la fois un format et sa sémantique : les points GeoJSON sont explicitement des coordonnées WGS 84 et les segments de ligne GeoJSON sont censés être des arêtes planes (lignes droites).

Contrairement à cela, le type de données GEOGRAPHY de Snowflake interprète tous les segments de ligne - y compris ceux entrés ou sortis au format GeoJSON - comme des arcs géodésiques. En substance, Snowflake traite GeoJSON comme du WKT au format JSON avec une sémantique sphérique.

Note sur le traitement EWKT et EWKB des valeurs GEOGRAPHY

EWKT et EWKB sont des formats non standard introduits par PostGIS. Ils améliorent les formats WKT et WKB en incluant un identificateur de système de référence spatiale (SRID), qui spécifie le système de référence de coordonnées à utiliser avec les données. Snowflake ne prend actuellement en charge que WGS84, qui correspond à SRID=4326.

Par défaut, Snowflake génère une erreur si une valeur d’entrée EWKB ou EWKT contient un SRID autre que 4326. Inversement, toutes les valeurs de sortie EWKB et EWKT ont un SRID = 4326.

Types d’objets géospatiaux pris en charge

Les types de données GEOGRAPHY et GEOMETRY peuvent stocker les types d’objets géospatiaux suivants :

  • Objets géospatiaux WKT / WKB / EWKT / EWKB / GeoJSON :

    • Point

    • MultiPoint

    • LineString

    • MultiLineString

    • Polygone

    • MultiPolygon

    • GeometryCollection

  • Ces objets géospatiaux spécifiques à GeoJSON :

    • Fonctionnalité

    • FeatureCollection

Spécification du format de sortie des jeux de résultats

Les paramètres de session GEOGRAPHY_OUTPUT_FORMAT et GEOMETRY_OUTPUT_FORMAT contrôlent le rendu des colonnes de type GEOGRAPHY et GEOMETRY dans les jeux de résultats (respectivement).

Les paramètres GEOGRAPHY_OUTPUT_FORMAT et GEOMETRY peuvent avoir l’une des valeurs suivantes :

Valeur du paramètre

Description

GeoJSON (par défaut)

Le résultat GEOGRAPHY / GEOMETRY est rendu sous la forme d’un OBJECT au format GeoJSON.

WKT

Le résultat GEOGRAPHY / GEOMETRY est rendu sous la forme d’un VARCHAR au format WKT.

WKB

Le résultat GEOGRAPHY / GEOMETRY est rendu sous la forme d’un BINARY au format WKB.

EWKT

Le résultat GEOGRAPHY / GEOMETRY est rendu sous la forme d’un VARCHAR au format EWKT.

EWKB

Le résultat GEOGRAPHY / GEOMETRY est rendu sous la forme d’un BINARY au format EWKB.

Pour EWKT et EWKB, le SRID est toujours 4326 dans la sortie. Consultez la note sur le traitement d” EWKT et d” EWKB.

Ce paramètre concerne tous les clients, y compris l” UI de Snowflake et le client de ligne de commande SnowSQL, ainsi que les pilotes et connecteurs JDBC, ODBC, node.js, python, etc.

Par exemple : le pilote JDBC renvoie les métadonnées suivantes pour une colonne de résultat de type GEOGRAPHY (colonne i dans cet exemple) :

  • If GEOGRAPHY_OUTPUT_FORMAT='GeoJSON' or GEOMETRY_OUTPUT_FORMAT='GeoJSON':

    • ResultSetMetaData.getColumnType(i) renvoie java.sql.Types.VARCHAR.

    • ResultSetMetaData.getColumnClassName(i) renvoie "java.lang.String".

  • Si GEOGRAPHY_OUTPUT_FORMAT='WKT' ou 'EWKT', ou si : GEOMETRY_OUTPUT_FORMAT='WKT' ou 'EWKT' :

    • ResultSetMetaData.getColumnType(i) renvoie java.sql.Types.VARCHAR.

    • ResultSetMetaData.getColumnClassName(i) renvoie "java.lang.String".

  • Si GEOGRAPHY_OUTPUT_FORMAT='WKB' ou 'EWKB', ou si GEOMETRY_OUTPUT_FORMAT='WKB' ou 'EWKB' :

    • ResultSetMetaData.getColumnType(i) renvoie java.sql.Types.BINARY.

    • ResultSetMetaData.getColumnClassName(i) renvoie "[B" (tableau d’octets).

Note

Les APIs pour récupérer les noms de type spécifiques à la base de données (getColumnTypeName dans JDBC et le descripteur SQL_DESC_TYPE_NAME dans ODBC) renvoient toujours GEOGRAPHY et GEOMETRY pour le nom de type, quelle que soit la valeur des paramètres GEOGRAPHY_OUTPUT_FORMAT et GEOMETRY_OUTPUT_FORMAT. Pour plus de détails, voir :

Exemples d’insertion et d’interrogation de données GEOGRAPHY

Le code ci-dessous montre des exemples d’entrée et de sortie pour le type de données GEOGRAPHY. Remarques :

  • Pour les coordonnées en WKT, EWKT et GeoJSON, la longitude apparaît avant la latitude (par exemple POINT(lon lat)).

  • Pour la sortie WKB et EWKB, il est supposé que le paramètre BINARY_OUTPUT_FORMAT est défini sur HEX (la valeur par défaut du paramètre).

L’exemple crée une table avec une colonne GEOGRAPHY, insère des données au format WKT et renvoie les données dans différents formats de sortie.

create table geospatial_table (id INTEGER, g GEOGRAPHY);
insert into geospatial_table values
    (1, 'POINT(-122.35 37.55)'), (2, 'LINESTRING(-124.20 42.00, -120.01 41.99)');
alter session set GEOGRAPHY_OUTPUT_FORMAT='GeoJSON';
select g
    from geospatial_table
    order by id;
+------------------------+
| G                      |
|------------------------|
| {                      |
|   "coordinates": [     |
|     -122.35,           |
|     37.55              |
|   ],                   |
|   "type": "Point"      |
| }                      |
| {                      |
|   "coordinates": [     |
|     [                  |
|       -124.2,          |
|       42               |
|     ],                 |
|     [                  |
|       -120.01,         |
|       41.99            |
|     ]                  |
|   ],                   |
|   "type": "LineString" |
| }                      |
+------------------------+
alter session set GEOGRAPHY_OUTPUT_FORMAT='WKT';
select g
    from geospatial_table
    order by id;
+-------------------------------------+
| G                                   |
|-------------------------------------|
| POINT(-122.35 37.55)                |
| LINESTRING(-124.2 42,-120.01 41.99) |
+-------------------------------------+
alter session set GEOGRAPHY_OUTPUT_FORMAT='WKB';
select g
    from geospatial_table
    order by id;
+------------------------------------------------------------------------------------+
| G                                                                                  |
|------------------------------------------------------------------------------------|
| 01010000006666666666965EC06666666666C64240                                         |
| 010200000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 |
+------------------------------------------------------------------------------------+
alter session set GEOGRAPHY_OUTPUT_FORMAT='EWKT';
select g
    from geospatial_table
    order by id;
+-----------------------------------------------+
| G                                             |
|-----------------------------------------------|
| SRID=4326;POINT(-122.35 37.55)                |
| SRID=4326;LINESTRING(-124.2 42,-120.01 41.99) |
+-----------------------------------------------+
alter session set GEOGRAPHY_OUTPUT_FORMAT='EWKB';
select g
    from geospatial_table
    order by id;
+--------------------------------------------------------------------------------------------+
| G                                                                                          |
|--------------------------------------------------------------------------------------------|
| 0101000020E61000006666666666965EC06666666666C64240                                         |
| 0102000020E610000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 |
+--------------------------------------------------------------------------------------------+

Utilisation de données géospatiales dans Snowflake

Les sections suivantes traitent des formats standard et des types d’objets pris en charge lors de la lecture et de l’écriture de données géospatiales.

Comprendre les effets de l’utilisation de différents SRIDs avec GEOMETRY

Dans une colonne GEOMETRY, vous pouvez insérer des objets qui ont des SRIDs différents. Si la colonne contient plus d’un SRID, certaines des optimisations importantes des performances ne sont pas appliquées. Cela peut entraîner des requêtes plus lentes, en particulier lors de la jonction sur un prédicat géospatial.

Exécution des opérations DML sur des colonnes GEOGRAPHY et GEOMETRY

Lorsqu’une colonne GEOGRAPHY ou GEOMETRY est la cible d’une opération DML (INSERT, COPY, UPDATE, MERGE ou CREATE TABLE AS…), l’expression source de la colonne peut être n’importe lequel des types suivants :

  • GEOGRAPHY ou GEOMETRY : une expression de type GEOGRAPHY ou GEOMETRY est généralement le résultat d’une fonction d’analyse, d’une fonction constructeur ou d’une colonne GEOGRAPHY ou GEOMETRY existante. Pour une liste complète des fonctions et catégories de fonctions prises en charge, voir Fonctions géospatiales.

  • VARCHAR : interprété comme une chaîne au format WKT, WKB (format hexadécimal), EWKT, EWKB (format hexadécimal), ou GeoJSON (voir TO_GEOGRAPHY(VARCHAR)).

  • BINARY : interprété comme un WKB binaire (voir TO_GEOGRAPHY(BINARY) et

  • TO_GEOMETRY(BINARY)).

  • VARIANT : interprété comme un objet GeoJSON (voir TO_GEOGRAPHY(VARIANT) et TO_GEOMETRY(VARIANT)).

Chargement des données géospatiales à partir de zones de préparation

Les données des fichiers CSV ou JSON / AVRO dans une zone de préparation peuvent être chargées directement (c’est-à-dire sans transformations de copie) dans une colonne GEOGRAPHY.

Le chargement de données à partir d’autres formats de fichiers (Parquet, ORC, etc.) est possible via une transformation COPY.

Utilisation de données géospatiales avec des UDFs Java

Les UDFs Java autorisent le type GEOGRAPHY comme argument et comme valeur de retour. Voir Mappages de type de donnée SQL-Java pour les paramètres et les types de retour et Transmission d’une valeur GEOGRAPHY à une UDF Java en ligne pour plus de détails.

Utilisation des données géospatiales avec des UDFs JavaScript

Les UDFs JavaScript autorisent le type GEOGRAPHY comme argument et comme valeur de retour.

Si un UDF JavaScript a un argument de type GEOGRAPHY, cet argument sera visible en tant qu’objet JSON au format GeoJSON à l’intérieur du corps UDF.

Si un UDF JavaScript renvoie GEOGRAPHY, le corps UDF devrait renvoyer un objet JSON au format GeoJSON.

Par exemple, ces deux UDFs JavaScript sont à peu près équivalents aux fonctions intégrées ST_X et ST_MAKEPOINT :

create or replace function my_st_x(G geography) returns real
language javascript
as
$$
  if (G["type"] != "Point")
  {
     throw "Not a point"
  }
  return G["coordinates"][0]
$$;

create or replace function my_st_makepoint(LNG real, LAT real) returns geography
language javascript
as
$$
  g = {}
  g["type"] = "Point"
  g["coordinates"] = [ LNG, LAT ]
  return g
$$;

Choix du type de données géospatiales à utiliser (GEOGRAPHY ou GEOMETRY)

Les sections suivantes expliquent les différences entre les types de données GEOGRAPHY et GEOMETRY :

Comprendre les différences entre GEOGRAPHY et GEOMETRY

Bien que les types de données GEOGRAPHY et GEOMETRY définissent tous deux des caractéristiques géospatiales, ils utilisent des modèles différents. Le tableau suivant résume les différences.

Type de données GEOGRAPHY

Type de données GEOMETRY

  • Définit les caractéristiques sur une sphère.

  • Seulement le système de coordonnées WGS84. SRID est toujours 4326.

  • Les coordonnées sont la latitude (-90 à 90) et la longitude (-180 à 180) en degrés.

  • Les résultats des opérations de mesure (ST_LENGTH, ST_AREA, etc.) sont en mètres.

  • Les segments sont interprétés comme des arcs géodésiques sur la surface de la Terre.

  • Définit les caractéristiques sur un plan.

  • Tout système de coordonnées est pris en charge.

  • L’unité des valeurs de coordonnées est définie par le système de référence spatiale.

  • Les résultats des opérations de mesure (ST_LENGTH, ST_AREA, etc.) sont dans la même unité que les coordonnées. Par exemple, si les coordonnées d’entrée sont en degrés, les résultats sont en degrés.

  • Les segments sont interprétés comme des lignes droites sur le plan.

Exemples comparant les types de données GEOGRAPHY et GEOMETRY

Les exemples suivants comparent les résultats des fonctions géospatiales en utilisant les types de données GEOGRAPHY et GEOMETRY en entrée.

Exemple 1 : interrogation de la distance entre Berlin et San Francisco

Le tableau suivant compare la sortie de ST_DISTANCE pour les types GEOGRAPHY et GEOMETRY :

ST_DISTANCE Utilisation de l’entrée . GEOGRAPHY

ST_DISTANCE Utilisation de l’entrée . GEOMETRY

SELECT ST_DISTANCE(
         ST_POINT(13.4814, 52.5015),
         ST_POINT(-121.8212, 36.8252))
       AS distance_in_meters;
+--------------------+
| DISTANCE_IN_METERS |
|--------------------|
|   9182410.99227821 |
+--------------------+
SELECT ST_DISTANCE(
         ST_GEOM_POINT(13.4814, 52.5015),
         ST_GEOM_POINT(-121.8212, 36.8252))
       AS distance_in_degrees;
+---------------------+
| DISTANCE_IN_DEGREES |
|---------------------|
|       136.207708844 |
+---------------------+

Comme indiqué dans l’exemple ci-dessus :

  • Avec les valeurs d’entrée GEOGRAPHY, les coordonnées d’entrée sont en degrés, et la valeur de sortie est en mètres. (Le résultat est 9 182 km.)

  • Avec des valeurs GEOMETRY d’entrée, les coordonnées d’entrée et la valeur de sortie sont des degrés. (Le résultat est 136,208 degrés.)

Exemple 2 : interrogation de la région de l’Allemagne

Le tableau suivant compare la sortie de ST_AREA pour les types GEOGRAPHY et GEOMETRY :

ST_AREA Utilisation de l’entrée . GEOGRAPHY

ST_AREA Utilisation de l’entrée . GEOMETRY

SELECT ST_AREA(border) AS area_in_sq_meters
  FROM world_countries
  WHERE name = 'Germany';
+-------------------+
| AREA_IN_SQ_METERS |
|-------------------|
|  356379183635.591 |
+-------------------+
SELECT ST_AREA(border) as area_in_sq_degrees
  FROM world_countries_geom
  WHERE name = 'Germany';
+--------------------+
| AREA_IN_SQ_DEGREES |
|--------------------|
|       45.930026848 |
+--------------------+

Comme indiqué dans l’exemple ci-dessus :

  • Avec les valeurs d’entrée GEOGRAPHY, les coordonnées d’entrée sont en degrés, la valeur de sortie est en mètres carrés. (Le résultat est 356,379 km^2.)

  • Avec les valeurs d’entrée GEOMETRY, les coordonnées d’entrée sont en degrés, et la valeur de sortie est en degrés carrés. (Le résultat est 45,930 degrés carrés.)

Exemple 3 : interrogation des noms de pays chevauchant la ligne Berlin-San Francisco

Le tableau suivant compare la sortie de ST_INTERSECTS pour les types GEOGRAPHY et GEOMETRY :

ST_INTERSECTS Utilisation de l’entrée . GEOGRAPHY

ST_INTERSECTS Utilisation de l’entrée . GEOMETRY

SELECT name FROM world_countries WHERE
  ST_INTERSECTS(border,
    TO_GEOGRAPHY(
      'LINESTRING(13.4814 52.5015, -121.8212 36.8252)'
    ));
+--------------------------+
| NAME                     |
|--------------------------|
| Germany                  |
| Denmark                  |
| Iceland                  |
| Greenland                |
| Canada                   |
| United States of America |
+--------------------------+
SELECT name FROM world_countries_geom WHERE
  ST_INTERSECTS(border,
    TO_GEOMETRY(
      'LINESTRING(13.4814 52.5015, -121.8212 36.8252)'
    ));
+--------------------------+
| NAME                     |
|--------------------------|
| Germany                  |
| Belgium                  |
| Netherlands              |
| United Kingdom           |
| United States of America |
+--------------------------+
Countries intersecting when using GEOGRAPHY Countries intersecting when using GEOMETRY

Comprendre les différences de la validation des données d’entrée

Pour créer un objet GEOMETRY ou GEOGRAPHY pour une forme d’entrée, vous devez utiliser une forme qui est dans un format valide, conformément aux règles OGC des caractéristiques simples. Les sections suivantes expliquent comment la validité des données d’entrée diffère entre GEOMETRY et GEOGRAPHY.

Une forme peut être valide en GEOGRAPHY mais non valide en GEOMETRY

Une forme donnée peut être un objet GEOGRAPHY valide mais un objet GEOMETRY non valide (et vice versa).

Par exemple, les polygones qui s’auto-intersectent ne sont pas autorisés par les règles OGC. Un ensemble donné de points peut définir des arêtes qui se croisent dans le domaine cartésien mais pas sur une sphère. Prenons le polygone suivant :

POLYGON((0 50, 25 50, 50 50, 0 50))

Dans le domaine cartésien, ce polygone se dégrade en une ligne et, par conséquent, n’est pas valide.

Cependant, sur une sphère, ce même polygone ne se coupe pas et est valide :

POLYGON((0 50, 25 50, 50 50, 0 50)) invalid geometry, but valid geography

Les fonctions de conversion et de construction traitent la validation différemment

Lorsque les données d’entrée ne sont pas valides, les fonctions GEOMETRY et GEOGRAPHY gèrent la validation de différentes manières :

  • Les fonctions de construction et de conversion en objets GEOGRAPHY (par exemple, TO_GEOGRAPHY) peuvent tenter de réparer la forme pour gérer des problèmes tels que les boucles non fermées, les pointes, les coupures et les boucles qui s’entrecoupent dans les polygones.

    Si la fonction réussit à réparer la forme, elle renvoie un objet GEOGRAPHY.

  • Les fonctions de construction et de conversion en objets GEOMETRY (par exemple, TO_GEOMETRY) ne prennent pas en charge la possibilité de réparer la forme.

Conversion entre GEOGRAPHY et GEOMETRY

Snowflake ne prend pas en charge le transfert de GEOGRAPHY à GEOMETRY (et vice versa). Snowflake ne prend pas non plus en charge les transformations d’objets qui utilisent des systèmes de référence spatiale différents (SRS).

Si vous devez convertir un objet GEOGRAPHY en un objet GEOMETRY (ou GEOMETRY en GEOGRAPHY), vous pouvez utiliser GeoJSON comme format intermédiaire.

L’exemple suivant convertit l’objet GEOGRAPHY geography_expression en un objet GEOMETRY avec le SRID 0 :

SELECT TO_GEOMETRY(ST_ASGEOJSON(geography_expression));

Si vous devez définir le SRID de l’objet GEOMETRY, appelez la fonction ST_SETSRID. Par exemple, pour convertir l’objet GEOGRAPHY geography_expression en un objet GEOMETRY avec le SRID 4326 :

SELECT ST_SETSRID(TO_GEOMETRY(ST_ASGEOJSON(geography_expression)), 4326);