Datentypen für Geodaten¶

GEOGRAPHY-Datentyp¶

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

Punkte auf der Erde werden als Längengrade (von -180 Grad bis +180 Grad) und Breitengrade (-90 bis +90) dargestellt.

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, sollten Sie diese Daten konvertieren und in GEOGRAPHY-Spalten speichern, anstatt die Daten in ihren ursprünglichen Formaten 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.

GEOMETRY-Datentyp¶

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 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 folgende Referenzen hilfreich:

• Simple Feature Access des Open Geospatial mit „Common Architecture“ und SQL-Option:

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

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

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

Unterstützten 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 Spalten vom Typ GEOGRAPHY bzw. GEOMETRY in entsprechenden Resultsets.

Die Parameter GEOGRAPHY_OUTPUT_FORMAT und GEOMETRY können einen der folgenden Werte haben:

Parameterwert	Beschreibung
GeoJSON (Standard)	Das GEOGRAPHY/GEOMETRY-Ergebnis wird als ein OBJECT-Wert im GeoJSON-Format gerendert.
WKT	Das GEOGRAPHY/GEOMETRY-Ergebnis wird als ein VARCHAR-Wert im WKT-Format gerendert.
WKB	Das GEOGRAPHY/GEOMETRY-Ergebnis wird als ein BINARY-Wert im WKB-Format gerendert.
EWKT	Das GEOGRAPHY/GEOMETRY-Ergebnis wird als ein VARCHAR-Wert im EWKT-Format gerendert.
EWKB	Das GEOGRAPHY/GEOMETRY-Ergebnis wird als ein BINARY-Wert im EWKB-Format gerendert.

Für EWKT und EWKB ist die SRID in der Ausgabe immer „4326“. Siehe den Hinweis zur Verwendung von EWKT und EWKB.

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 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 Beispiel erstellt eine Tabelle mit einer Spalte GEOGRAPHY, fügt Daten im Format WKT ein und gibt die Daten in verschiedenen Ausgabeformaten zurück.

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

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

Ausführen von DML.Operationen auf GEOGRAPHY/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¶

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

• 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 Hinweis zur Verwendung von GeoJSON mit GEOGRAPHY-Werten.

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 SQL-Java-Datentypen für Parameter und Rückgabetypen und Übergeben eines GEOGRAPHY-Werts an eine Inline-Java-UDF.

Verwenden von Geodaten in JavaScript-UDFs¶

In JavaScript-UDFs kann der Datentyp GEOGRAPHY als Argument und als Rückgabewert verwendet werden.

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

Wenn eine JavaScript-UDF den Datentyp GEOGRAPHY 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
$$;

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

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

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.