Types de données géospatiales¶

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://epsg.io/4326).

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). Snowflake utilise 14 décimales pour stocker les coordonnées GEOGRAPHY. Lorsque les données comportent des décimales dépassant cette limite, les coordonnées sont arrondies afin de respecter la contrainte de longueur spécifiée.

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.), nous vous suggérons de convertir et de 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 utilise 14 décimales pour stocker les coordonnées GEOMETRY. Lorsque les données comportent des décimales dépassant cette limite, les coordonnées sont arrondies afin de respecter la contrainte de longueur spécifiée.

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.

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 :

• Texte bien connu (WKT)

• Binaire bien connu (WKB)

• WKT et WKB étendus (EWKT et EWKB) (voir la note sur le traitement d” EWKT et d” EWKB)

• IETF GeoJSON (voir la note sur le traitement de GeoJSON)

Les références suivantes sur Open Geospatial Consortium’s Simple Feature Access peuvent également vous être utiles :

• Architecture commune

• Option SQL

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

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 GEOGRAPHY et GEOMETRY dans les jeux de résultats (respectivement).

Ces paramètres peuvent avoir l’une des valeurs suivantes :

Pour EWKT et EWKB, le SRID est toujours 4326 dans la sortie. Voir Traitement EWKT et EWKB pour les valeurs GEOGRAPHY.

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, et ainsi de suite.

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

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 suivant 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 OR REPLACE 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 |
+--------------------------------------------------------------------------------------------+

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.

Changement du système de référence spatiale (SRS) et SRID d’un objet GEOMETRY¶

Pour modifier les SRS et SRID d’un objet GEOMETRY existant, appelez la fonction ST_TRANSFORM en lui transmettant le nouveau SRID. La fonction renvoie un nouvel objet GEOMETRY avec le nouveau SRID et les coordonnées converties pour utiliser le SRS. Par exemple, pour renvoyer un objet GEOMETRY pour geometry_expression qui utilise le SRS pour SRID 32633, exécutez l’instruction suivante :

SELECT ST_TRANSFORM(geometry_expression, 32633);

Si le SRID d’origine n’est pas défini correctement dans l’objet GEOMETRY existant, spécifiez le SRID d’origine en tant qu’argument supplémentaire. Par exemple, si geometry_expression est un objet GEOMETRY qui utilise le SRID 4326, et que vous voulez le transformer pour utiliser le SRID 28992, exécutez l’instruction suivante :

SELECT ST_TRANSFORM(geometry_expression, 4326, 28992);

Si un objet GEOMETRY utilise les coordonnées correctes pour un SRS, mais a un SRID erroné, vous pouvez corriger le SRID en appelant la fonction ST_SETSRID. Par exemple, l’instruction suivante définit le SRID pour geometry_expression à 4326 tout en laissant les coordonnées inchangées :

SELECT ST_SETSRID(geometry_expression, 4326);

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¶

Vous pouvez charger directement les données des fichiers CSV ou JSON/AVRO dans une zone de préparation (c’est-à-dire sans transformation de copie) dans une colonne GEOGRAPHY.

• CSV : les valeurs de chaîne de la colonne CSV correspondante sont analysées comme GeoJSON, WKT, EWKT, WKB, ou EWKB (voir TO_GEOGRAPHY(VARCHAR)).

• JSON/AVRO : les valeurs JSON dans le fichier sont interprétées comme GeoJSON (voir TO_GEOGRAPHY(VARIANT)).

  Voir aussi Traitement GeoJSON pour les valeurs GEOGRAPHY.

Le chargement de données à partir d’autres formats de fichiers (Parquet, ORC, et ainsi de suite) 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ées SQL-Java 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 ou GEOMETRY comme argument et comme valeur de retour.

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

Si une UDF JavaScript renvoie GEOGRAPHY ou GEOMETRY, le corps de l’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
$$;

Utilisation des données géospatiales avec des UDFs Python¶

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

Si une UDF Python a un argument de type GEOGRAPHY ou GEOMETRY, cet argument est représenté comme un objet GeoJSON, qui est converti en un objet Python dict à l’intérieur du corps de l’UDF.

Si une UDF Python renvoie GEOGRAPHY ou GEOMETRY, le corps de l’UDF est censé renvoyer un objet Python dict conforme à la structure de GeoJSON.

Par exemple, cette UDF Python renvoie le nombre de géométries distinctes qui constituent un type composite GEOGRAPHY :

CREATE OR REPLACE FUNCTION py_numgeographys(geo GEOGRAPHY)
RETURNS INTEGER
LANGUAGE PYTHON
RUNTIME_VERSION = 3.10
PACKAGES = ('shapely')
HANDLER = 'udf'
AS $$
from shapely.geometry import shape, mapping
def udf(geo):
    if geo['type'] not in ('MultiPoint', 'MultiLineString', 'MultiPolygon', 'GeometryCollection'):
        raise ValueError('Must be a composite geometry type')
    else:
        g1 = shape(geo)
        return len(g1.geoms)
$$;

Consultez Snowflake Labs pour plus d’exemples d’UDFs Python. Certains d’entre eux permettent des manipulations spatiales complexes ou simplifient l’ingestion de données. Par exemple, cette UDF permet de lire des formats qui ne sont pas pris en charge de manière native, tels que Shapefiles (.SHP), TAB, KML, GPKG, et d’autres.

Utilisation d’objets GEOGRAPHY avec H3¶

H3 est un index géospatial hiérarchique qui divise le monde en cellules hexagonales dans un système de grille global discret.

Snowflake fournit des fonctions SQL qui vous permettent d’utiliser H3 avec des objets GEOGRAPHY. Vous pouvez utiliser ces fonctions pour :

• Obtenir l’ID de la cellule H3 (index) pour un objet GEOGRAPHY qui représente un point (et vice versa).

• Obtenir les IDs de l’ensemble minimal de cellules H3 qui couvrent un objet GEOGRAPHY.

• Obtenir les IDs des cellules H3 qui ont des centroïdes dans un objet GEOGRAPHY qui représente un polygone.

• Obtenir l’objet GEOGRAPHY qui représente la limite d’une cellule H3.

• Obtenir les parents et les enfants d’une cellule H3 donnée.

• Obtenir la longitude et la latitude du centre de gravité d’une cellule H3 (et vice versa).

• Obtenir la résolution d’une cellule H3.

• Obtenir la représentation hexadécimale d’un ID de cellule H3 (et vice versa).

Pour plus d’informations sur ces fonctions, voir Fonctions géospatiales

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.

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.

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_GEOMPOINT(13.4814, 52.5015),
    ST_GEOMPOINT(-121.8212, 36.8252))
  AS distance_in_degrees;

+---------------------+
| DISTANCE_IN_DEGREES |
|---------------------|
|       136.207708844 |
+---------------------+

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

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)' )); Copy +--------------------------+ | 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)' )); Copy +--------------------------+ | NAME                     | |--------------------------| |                  Germany | |                  Belgium | |              Netherlands | |           United Kingdom | | United States of America | +--------------------------+

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

Copy

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 :

Compréhension des effets des formes non valides sur les fonctions géospatiales¶

Différentes fonctions géospatiales ont des effets différents lorsque vous transmettez un objet GEOGRAPHY ou GEOMETRY pour une forme non valide.

Utilisation de formes non valides¶

Les sections suivantes expliquent comment autoriser les fonctions à créer des formes non valides et comment déterminer si un objet GEOGRAPHY ou GEOMETRY représente une forme non valide ou réparée.

TO_GEOGRAPHY( <input> [, <allowInvalid> ] )

ST_GEOGFROMWKB( <input> [, <allowInvalid> ] )

ST_GEOGFROMWKT( <input> [, <allowInvalid> ] )

TO_GEOMETRY( <input> [, <allowInvalid> ] )

ST_GEOMFROMWKB( <input> [, <allowInvalid> ] )

ST_GEOMFROMWKT( <input> [, <allowInvalid> ] )

SELECT TO_GEOMETRY('LINESTRING(100 102,100 102)', TRUE);

SELECT TO_GEOMETRY('LINESTRING(100 102,100 102)', TRUE) AS g, ST_ISVALID(g);