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://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). 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.), 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 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)

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

• Open Geospatial Consortium Simple Feature Access / Common Architecture et option SQL :

  • https://www.opengeospatial.org/standards/sfa

  • https://www.opengeospatial.org/standards/sfs

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

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)');

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='GeoJSON';

Copy

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

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='WKT';

Copy

select g
    from geospatial_table
    order by id;
+-------------------------------------+
| G                                   |
|-------------------------------------|
| POINT(-122.35 37.55)                |
| LINESTRING(-124.2 42,-120.01 41.99) |
+-------------------------------------+

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='WKB';

Copy

select g
    from geospatial_table
    order by id;
+------------------------------------------------------------------------------------+
| G                                                                                  |
|------------------------------------------------------------------------------------|
| 01010000006666666666965EC06666666666C64240                                         |
| 010200000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 |
+------------------------------------------------------------------------------------+

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='EWKT';

Copy

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

Copy

alter session set GEOGRAPHY_OUTPUT_FORMAT='EWKB';

Copy

select g
    from geospatial_table
    order by id;
+--------------------------------------------------------------------------------------------+
| G                                                                                          |
|--------------------------------------------------------------------------------------------|
| 0101000020E61000006666666666965EC06666666666C64240                                         |
| 0102000020E610000002000000CDCCCCCCCC0C5FC00000000000004540713D0AD7A3005EC01F85EB51B8FE4440 |
+--------------------------------------------------------------------------------------------+

Copy

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

Copy

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

Copy

Notez que 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);

Copy

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.

• 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 : Note sur le traitement GeoJSON des valeurs 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é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 sera 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 sera 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.8
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).

Les fonctions SQL pour H3 sont répertoriées ci-dessous :

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_GEOM_POINT(13.4814, 52.5015),
         ST_GEOM_POINT(-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 | +--------------------------+ Copy	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 | +--------------------------+ Copy

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.

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