Datentypen für Geodaten¶

Datentyp GEOGRAPHY¶

Der Datentyp GEOGRAPHY verwendet WGS-84 als globales Georeferenzsystem (EPSG-ID 4326; Einzelheiten siehe https://epsg.io/4326).

Punkte auf der Erde werden als Längengrade (von -180 Grad bis +180 Grad) und Breitengrade (-90 bis +90) dargestellt. Snowflake speichert GEOGRAPHY-Koordinaten mit 14 Dezimalstellen. Wenn die Daten Dezimalstellen enthalten, die dieses Limit überschreiten, werden die Koordinaten gerundet, um die Einhaltung der angegebenen Längenbeschränkung sicherzustellen.

Höhenangaben werden derzeit nicht unterstützt.

Liniensegmente werden als geodätische Bögen auf der Erdoberfläche interpretiert.

Snowflake bietet auch Geodatenfunktionen, die den Datentyp GEOGRAPHY verarbeiten.

Wenn Sie Geodaten (z. B. Längen- und Breitengrad, WKT, WKB, GeoJSON usw.) haben, empfehlen wir Ihnen, diese Daten zu konvertieren und in GEOGRAPHY-Spalten zu speichern, anstatt die Daten in ihrem ursprünglichen Format in VARCHAR-, VARIANT- oder NUMBER-Spalten zu behalten. Das Speichern Ihrer Daten in GEOGRAPHY-Spalten kann die Leistung von Abfragen, die Geofunktionen verwenden, erheblich verbessern.

Datentyp GEOMETRY¶

Der Datentyp GEOMETRY repräsentiert Merkmale eines planaren (euklidischen, kartesischen) Koordinatensystems.

Die Koordinaten werden als Paare von reellen Zahlen (x, y) dargestellt. Derzeit werden nur 2D-Koordinaten unterstützt.

Die Maßeinheiten von X und Y werden durch das räumliche Bezugssystem (Spatial Reference System, SRS) bestimmt, das dem GEOMETRY-Objekt zugeordnet ist. Das räumliche Bezugssystem wird durch die Kennung des räumlichen Bezugssystems (Spatial Reference System Identifier, SRID) identifiziert. Wenn die SRID weder beim Erstellen des GEOMETRY-Objekts noch durch Aufruf von ST_SETSRID bereitgestellt wird, hat die SRID den Wert 0.

Snowflake speichert GEOMETRY-Koordinaten mit 14 Dezimalstellen. Wenn die Daten Dezimalstellen enthalten, die dieses Limit überschreiten, werden die Koordinaten gerundet, um die Einhaltung der angegebenen Längenbeschränkung sicherzustellen.

Snowflake bietet eine Reihe von Geodatenfunktionen, die den Datentyp GEOMETRY verarbeiten. Für diese Funktionen gilt Folgendes:

• Alle Funktionen setzen planare Koordinaten voraus, auch wenn die Geometrie ein nicht-planares SRS verwendet.

• Die Messfunktionen (z. B. ST_LENGTH) verwenden die gleichen Einheiten wie das Koordinatensystem.

• Bei Funktionen, die mehrere GEOMETRY-Ausdrücke als Argumente akzeptieren (z. B. ST_DISTANCE), müssen die Eingabeausdrücke im selben SRS definiert sein.

Unterstützte Standardformate für die Ein- und Ausgabe¶

Die Datentypen GEOGRAPHY und GEOMETRY unterstützen die folgenden branchenspezifischen Standardformate für die Ein- und Ausgabe:

• Well-Known Text (WKT)

• Well-Known Binary (WKB)

• Erweitertes WKT und WKB (EWKT und EWKB) (siehe den Hinweis zur Behandlung von EWKT und EWKB)

• IETF GeoJSON (siehe den Hinweis zur Verwendung von GeoJSON)

Möglicherweise sind auch die folgenden Referenzen für Simple Feature Access des Open Geospatial hilfreich:

• Common Architecture

• SQL Option

Jede Abweichung von diesen Standards wird ausdrücklich in der Snowflake-Dokumentation vermerkt.

Unterstützte Typen von Geodatenobjekten¶

Die Datentypen GEOGRAPHY und GEOMETRY können die folgenden Typen von Geodatenobjekten speichern:

• WKT/WKB/EWKT/EWKB/GeoJSON-Geodatenobjekte:

  • Punkt

  • MultiPoint

  • LineString

  • MultiLineString

  • Polygon

  • MultiPolygon

  • GeometryCollection

• GeoJSON-spezifische Geodatenobjekte:

  • Funktion

  • FeatureCollection

Angeben des Ausgabeformats für Resultsets¶

Die Sitzungsparameter GEOGRAPHY_OUTPUT_FORMAT und GEOMETRY_OUTPUT_FORMAT steuern das Rendern von GEOGRAPHY- und GEOMETRY-Spalten in (den jeweiligen) Resultsets.

Diese Parameter können einen der folgenden Werte annehmen:

Für EWKT und EWKB ist die SRID in der Ausgabe immer „4326“. Siehe EWKT- und EWKB- Handhabung für GEOGRAPHY-Werte.

Dieser Parameter betrifft alle Clients, einschließlich Snowflake-UI und SnowSQL-Befehlszeilenclient sowie Treiber und Konnektoren für JDBC, ODBC, Node.js, Python usw.

Beispiel: Der JDBC-Treiber gibt die folgenden Metadaten für eine GEOGRAPHY-typisierte Ergebnisspalte zurück (Spalte i in diesem Beispiel):

• Wenn GEOGRAPHY_OUTPUT_FORMAT='GeoJSON' oder GEOMETRY_OUTPUT_FORMAT='GeoJSON':

  • ResultSetMetaData.getColumnType(i) gibt java.sql.Types.VARCHAR zurück.

  • ResultSetMetaData.getColumnClassName(i) gibt "java.lang.String" zurück.

• Wenn GEOGRAPHY_OUTPUT_FORMAT='WKT' oder 'EWKT', oder wenn GEOMETRY_OUTPUT_FORMAT='WKT' oder 'EWKT':

  • ResultSetMetaData.getColumnType(i) gibt java.sql.Types.VARCHAR zurück.

  • ResultSetMetaData.getColumnClassName(i) gibt "java.lang.String" zurück.

• Wenn GEOGRAPHY_OUTPUT_FORMAT='WKB' oder 'EWKB', oder wenn GEOMETRY_OUTPUT_FORMAT='WKB' oder 'EWKB':

  • ResultSetMetaData.getColumnType(i) gibt java.sql.Types.BINARY zurück.

  • ResultSetMetaData.getColumnClassName(i) gibt "[B" (Bytearray).

Beispiele für das Einfügen und Abfragen von GEOGRAPHY-Daten¶

Der folgende Code zeigt die Ein- und Ausgabe von Beispieldaten mit dem Datentyp GEOGRAPHY. Beachten Sie Folgendes:

• Bei den Koordinaten in WKT, EWKT und GeoJSON wird erst die Länge, dann die Breite angezeigt (z. B. POINT(lon lat)).

• Bei WKB- und EWKB-Ausgaben wird angenommen, dass der Parameter BINARY_OUTPUT_FORMAT auf HEX (Standardwert des Parameters) gesetzt ist.

Das folgende Beispiel erstellt eine Tabelle mit einer GEOGRAPHY-Spalte, fügt Daten im WKT-Format ein und gibt die Daten in verschiedenen Ausgabeformaten zurück.

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

Erläuterungen zu den Auswirkungen verschiedener SRIDs mit GEOMETRY¶

In einer GEOMETRY-Spalte können Sie Objekte einfügen, die unterschiedliche SRIDs haben. Wenn die Spalte mehr als eine SRID enthält, kommen einige wichtige Leistungsoptimierungen nicht zur Anwendung. Dies kann zur Verlangsamung von Abfragen führen, insbesondere bei der Verwendung von geografischen/räumlichen Prädikaten in Verknüpfungen (Join).

Ändern des räumlichen Bezugssystems (SRS) und der SRID eines GEOMETRY-Objekts¶

Um das SRS und die SRID eines bestehenden GEOMETRY-Objekts zu ändern, rufen Sie die Funktion ST_TRANSFORM auf und übergeben die neue SRID. Die Funktion liefert ein neues GEOMETRY-Objekt mit der neuen SRID und den Koordinaten, die für die Verwendung mit dem SRS umgewandelt wurden. Um beispielsweise ein GEOMETRY-Objekt für geometry_expression zurückzugeben, das das SRS für SRID 32633 verwendet, führen Sie die folgende Anweisung aus:

SELECT ST_TRANSFORM(geometry_expression, 32633);

Wenn die Original-SRID im bestehenden GEOMETRY-Objekt nicht korrekt eingestellt ist, geben Sie die Original-SRID als zusätzliches Argument an. Wenn geometry_expression beispielsweise ein GEOMETRY-Objekt ist, das SRID 4326 verwendet, und Sie dieses in SRID 28992 umwandeln möchten, führen Sie die folgende Anweisung aus:

SELECT ST_TRANSFORM(geometry_expression, 4326, 28992);

Wenn ein GEOMETRY-Objekt die richtigen Koordinaten für ein SRS verwendet, aber die falsche SRID hat, können Sie die SRID korrigieren, indem Sie die Funktion ST_SETSRID aufrufen. Die folgende Anweisung setzt zum Beispiel die SRID für geometry_expression auf 4326, während die Koordinaten unverändert bleiben:

SELECT ST_SETSRID(geometry_expression, 4326);

Ausführen von DML-Operationen auf GEOGRAPHY- und GEOMETRY-Spalten¶

Wenn eine GEOGRAPHY/GEOMETRY-Spalte das Ziel einer DML-Operation (INSERT, COPY, UPDATE, MERGE oder CREATE TABLE AS …) ist, kann der Quellenausdruck der Spalte folgende Datentypen aufweisen:

• GEOGRAPHY/GEOMETRY: Ein Ausdruck vom Typ GEOGRAPHY oder GEOMETRY ist normalerweise das Ergebnis einer Analysefunktion (Parsing), einer Konstruktorfunktion oder einer vorhandenen GEOGRAPHY/GEOMETRY-Spalte. Eine vollständige Liste der unterstützten Funktionen und Funktionskategorien finden Sie unter Geodatenfunktionen.

• VARCHAR: Wird als WKT, WKB, EWKT, EWKB (im Hex-Format) oder als GeoJSON-formatierte Zeichenfolge interpretiert (siehe TO_GEOGRAPHY(VARCHAR)).

• BINARY: Wird als WKB Binary interpretiert (siehe TO_GEOGRAPHY(BINARY)) und

• TO_GEOMETRY(BINARY)).

• VARIANT: Wird als GeoJSON-Objekt interpretiert (siehe TO_GEOGRAPHY(VARIANT)) und TO_GEOMETRY(VARIANT).

Laden von Geodaten aus Stagingbereichen¶

Sie können Daten aus CSV- oder JSON/AVRO-Dateien in einem Stagingbereich direkt (d .h. ohne COPY-Transformationen) in eine GEOGRAPHY-Spalte laden.

• CSV: Zeichenfolgenwerte aus der entsprechenden CSV-Spalte werden als GeoJSON, WKT, EWKT, WKB oder EWKB analysiert (siehe TO_GEOGRAPHY(VARCHAR)).

• JSON/AVRO: Die JSON-Werte in der Datei werden als GeoJSON interpretiert (siehe TO_GEOGRAPHY(VARIANT)).

  Siehe auch GeoJSON-Handhabung für GEOGRAPHY-Werte.

Das Laden von Daten aus anderen Dateiformaten (Parquet, ORC usw.) ist über eine COPY-Transformation möglich.

Verwenden von Geodaten in Java-UDFs¶

In Java-UDFs kann der Datentyp GEOGRAPHY als Argument und als Rückgabewert verwendet werden. Weitere Informationen dazu finden Sie unter Zuordnung von Datentypen zwischen SQL und Java und Übergeben eines GEOGRAPHY-Werts an eine Inline-Java-UDF.

Verwenden von Geodaten in JavaScript-UDFs¶

In JavaScript-UDFs können die Datentypen GEOGRAPHY und GEOMETRY als Argument und als Rückgabewert verwendet werden.

Wenn ein JavaScript UDF ein Argument vom Typ GEOGRAPHY oder GEOMETRY hat, wird dieses Argument im UDF-Body als JSON-Objekt im GeoJSON-Format angezeigt.

Wenn eine JavaScript-UDF den Datentyp GEOGRAPHY oder GEOMETRY zurückgibt, wird erwartet, dass der UDF-Body ein JSON-Objekt im GeoJSON-Format zurückgibt.

Beispielsweise entsprechen die beiden folgenden JavaScript-UDFs in etwa den integrierten Funktionen ST_X und 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
$$;

Verwenden von Geodaten in Python-UDFs¶

In Python-UDFs können die Datentypen GEOGRAPHY und GEOMETRY als Argument und als Rückgabewert verwendet werden.

Wenn eine Python-UDF ein Argument vom Typ GEOGRAPHY oder GEOMETRY hat, wird dieses Argument als GeoJSON-Objekt dargestellt, das innerhalb des UDF-Body in ein Python-dict-Objekt umgewandelt wird.

Wenn eine Python-UDF den Datentyp GEOGRAPHY oder GEOMETRY zurückgibt, wird vom UDF-Body erwartet, dass er ein Python-dict-Objekt zurückgibt, das der Struktur von GeoJSON entspricht.

Im folgenden Beispiel gibt die Python-UDF die Anzahl unterschiedlicher Geometrien zurück, die einen zusammengesetzten GEOGRAPHY-Typ bilden:

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

In Snowflake Labs finden Sie weitere Beispiele für Python-UDFs. Einige dieser UDFs ermöglichen komplexe räumliche Manipulationen oder vereinfachen die Datenerfassung. Beispielsweise ermöglicht diese UDF das Lesen von Formaten, die nicht nativ unterstützt werden, wie Shapefiles (.SHP), TAB, KML, GPKG und andere.

Verwenden von GEOGRAPHY-Objekten mit H3¶

H3 ist ein hierarchischer geographischer Index, der die Welt in hexagonale Zellen eines diskreten globalen Gittersystems unterteilt.

Snowflake bietet SQL-Funktionen, mit denen Sie H3 mit GEOGRAPHY-Objekten verwenden können. Sie können diese Funktionen für Folgendes verwenden:

• Abrufen der ID der H3-Zelle (Index) für ein GEOGRAPHY-Objekt, das einen Punkt repräsentiert (und umgekehrt).

• Abrufen der IDs der minimalen Menge von H3-Zellen, die ein GEOGRAPHY-Objekt abdecken.

• Abrufen der IDs der H3-Zellen, die Zentroide innerhalb eines GEOGRAPHY-Objekts haben, das ein Polygon repräsentiert.

• Abrufen des GEOGRAPHY-Objekts, das die Begrenzung einer H3-Zelle repräsentiert.

• Abrufen der übergeordneten und untergeordneten Zellen einer bestimmten H3-Zelle.

• Abrufen von Längen- und Breitengrad des Zentroids einer H3-Zelle (und umgekehrt).

• Abrufen der Auflösung einer H3-Zelle.

• Abrufen der hexadezimalen Darstellung der ID einer H3-Zelle (und umgekehrt).

Weitere Informationen zu diesen Funktionen finden Sie unter Geodatenfunktionen.

Erläuterungen zu den Unterschieden zwischen GEOGRAPHY und GEOMETRY¶

Beide Datentypen GEOGRAPHY und GEOMETRY definieren geografische Merkmale, verwenden dabei aber unterschiedliche Modelle. In der folgenden Tabelle sind die Unterschiede zusammengefasst.

Beispiele zum Vergleich der Datentypen GEOGRAPHY und GEOMETRY¶

In den folgenden Beispielen wird die Ausgabe der Geodatenfunktionen bei Verwendung der Datentypen GEOGRAPHY und GEOMETRY als Eingabetypen verglichen.

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

Beispiel 3: Abfrage der Namen von Ländern, die auf der Linie Berlin - San Francisco liegen¶

Die folgende Tabelle vergleicht die Ausgabe von ST_INTERSECTS für GEOGRAPHY-Typen und GEOMETRY-Typen:

ST_INTERSECTS mit . GEOGRAPHY-Eingabe	ST_INTERSECTS mit . GEOMETRY-Eingabe
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 | +--------------------------+

Erläuterungen zu den Unterschieden bei der Validierung der Eingabedaten¶

Um für eine Eingabeform ein entsprechendes GEOMETRY- oder GEOGRAPHY-Objekt zu erstellen, müssen Sie eine Form verwenden, die gemäß den OGC-Regeln für einfache Merkmale wohlgeformt und gültig ist. In den nächsten Abschnitten wird erläutert, wie sich die Gültigkeit der Eingabedaten zwischen GEOMETRY und GEOGRAPHY unterscheidet.

Eine Form kann für GEOGRAPHY gültig, aber für GEOMETRY ungültig sein¶

Eine gegebene Form kann ein gültiges GEOGRAPHY-Objekt sein, aber ein ungültiges GEOMETRY-Objekt (und umgekehrt).

So sind beispielsweise sich selbst schneidende Polygone nach den OGC-Regeln nicht zulässig. Eine gegebene Menge von Punkten kann Kanten definieren, die sich im kartesischen Koordinatensystem schneiden, aber nicht auf einer Kugel. Betrachten Sie das folgende Beispiel:

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

Copy

Im kartesischen System wird dieses Polygon zu einer Linie reduziert und ist daher ungültig.

Auf einer Kugel hingegen schneidet sich dasselbe Polygon nicht selbst und ist gültig:

Erläuterungen zu den Auswirkungen ungültiger Formen auf Geodatenfunktionen¶

Verschiedene Geodatenfunktionen haben unterschiedliche Auswirkungen, wenn Sie ein GEOGRAPHY- oder GEOMETRY-Objekt für eine ungültige Form übergeben.

Verwenden von ungültigen Formen¶

In den nächsten Abschnitten wird erläutert, wie Sie zulassen, dass Funktionen ungültige Formen erstellen, und wie Sie feststellen, ob ein GEOGRAPHY- oder GEOMETRY-Objekt eine ungültige oder reparierte Form repräsentiert.

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