Beschleunigen von Geodatenabfragen mit der Suchoptimierung¶
Dieses Feature erfordert Enterprise Edition (oder höher). Wenden Sie sich für ein Upgrade direkt an den Snowflake-Support.
Der Suchoptimierungsdienst kann die Leistung von Abfragen mit Prädikaten verbessern, bei denen Geodatenfunktionen mit GEOGRAPHY-Objekten verwendet werden.
Bemerkung
Sie müssen dieses Feature für bestimmte Spalten mit der ON-Klausel im Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION aktivieren. Beispiel:
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON GEO(mygeocol);
Abfragen, die Geodatenfunktionen verwenden, werden nicht verbessert, wenn Sie die ON-Klausel weglassen.
Weitere Details dazu finden Sie in den folgenden Abschnitten:
Bemerkung
GEOMETRY-Objekte werden noch nicht unterstützt.
Unterstützte Prädikate mit Geodatenfunktionen¶
Für Abfragen mit Prädikaten, die die folgenden Funktionen verwenden:
Der Suchoptimierungsdienst kann die Leistung verbessern, wenn Folgendes gilt:
Ein Eingabeausdruck ist eine GEOGRAPHY-Spalte in einer Tabelle.
Der andere Eingabeausdruck ist eine GEOGRAPHY-Konstante (die durch eine Konvertierung oder eine Konstruktorfunktion erstellt wird).
Für ST_DWITHIN ist das Distanzargument eine nicht negative REAL-Konstante.
Beachten Sie, dass für dieses Feature die gleichen Einschränkungen gelten wie für den Suchoptimierungsdienst.
Weitere Hinweise zur Performance¶
Da der Suchoptimierungsdienst für Prädikate konzipiert ist, die sehr selektiv sind, und da Prädikate nach der Nähe zwischen Geodatenobjekten filtern, kann das Clustering von Geodatenobjekten nach Nähe in der Tabelle zu einer besseren Leistung führen. Sie können Ihre Daten gruppieren, indem Sie entweder die Sortierreihenfolge beim Laden der Daten angeben oder Automatic Clustering verwenden, je nachdem, ob sich die Basistabelle häufig ändert:
- Laden von vorsortierten Daten
Wenn sich die Daten in Ihrer Basistabelle selten ändern, können Sie beim Laden der Daten die Sortierreihenfolge festlegen. Anschließend können Sie die Suchoptimierung für die Spalte GEOGRAPHY aktivieren. Beispiel:
CREATE TABLE new_table AS SELECT * FROM source_table ORDER BY st_geohash(geom); ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Nach jeder größeren Änderung Ihrer Stammdaten können Sie die Daten manuell neu sortieren.
Automatic Clustering¶
Wenn Ihre Basistabelle häufig aktualisiert wird, können Sie den Befehl ALTER TABLE … CLUSTER BY … verwenden, um Automatic Clustering zu aktivieren, damit die Tabelle bei Änderungen automatisch neu geclustert wird.
Das folgende Beispiel fügt eine neue Spalte geom_geohash vom Typ VARCHAR hinzu und speichert den Geohash- oder H3-Index der GEOGRAPHY-Spalte geom in dieser neuen Spalte. Anschließend wird Automatic Clustering aktiviert, wobei die neue Spalte der Clusterschlüssel ist. Bei diesem Ansatz werden die Teile der Tabelle, die sich ändern, automatisch neu geclustert.
CREATE TABLE new_table AS SELECT *, ST_GEOHASH(geom) AS geom_geohash FROM source_table;
ALTER TABLE new_table CLUSTER BY (geom_geohash);
ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Beispiele für die Verwendung von Geodatenfunktionen¶
Mit den folgenden Anweisungen wird die Tabelle erstellt und konfiguriert, die in den Beispielen dieses Abschnitts verwendet wird. Die letzte Anweisung verwendet die ON-Klausel im Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION, um die Suchoptimierung für die GEOGRAPHY-Spalte g1 hinzuzufügen.
CREATE OR REPLACE TABLE geospatial_table (id NUMBER, g1 GEOGRAPHY);
INSERT INTO geospatial_table VALUES
(1, 'POINT(-122.35 37.55)'),
(2, 'LINESTRING(-124.20 42.00, -120.01 41.99)'),
(3, 'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
ALTER TABLE geospatial_table ADD SEARCH OPTIMIZATION ON GEO(g1);
Beispiele für unterstützte Prädikate¶
Die folgende Abfrage ist ein Beispiel für eine vom Suchoptimierungsdienst unterstützte Abfrage. Der Suchoptimierungsdienst kann Suchzugriffspfade verwenden, um die Leistung dieser Abfrage zu verbessern:
SELECT id FROM geospatial_table WHERE
ST_INTERSECTS(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));
Im Folgenden finden Sie Beispiele für zusätzliche Prädikate, die vom Suchoptimierungsdienst unterstützt werden:
...
ST_INTERSECTS(
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1)
...
ST_CONTAINS(
TO_GEOGRAPHY('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
g1)
...
ST_CONTAINS(
g1,
TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))
...
ST_WITHIN(
TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
g1)
...
ST_WITHIN(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
...
ST_COVERS(
TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
g1)
...
ST_COVERS(
g1,
TO_GEOGRAPHY('POINT(0 0)'))
...
ST_COVEREDBY(
TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
g1)
...
ST_COVEREDBY(
g1,
TO_GEOGRAPHY('POINT(-122.35 37.55)'))
...
ST_DWITHIN(
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1,
100000)
...
ST_DWITHIN(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
100000)
Beispiele für die Konstruktion von GEOGRAPHY-Konstanten¶
Im Folgenden sind Beispiele für Prädikate aufgeführt, die verschiedene Umwandlungs- und Konstruktorfunktionen für die GEOGRAPHY-Konstante verwenden.
...
ST_INTERSECTS(
g1,
ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
...
ST_INTERSECTS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1)
...
ST_CONTAINS(
ST_GEOGRAPHYFROMEWKT('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
g1)
...
ST_WITHIN(
ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
g1)
...
ST_COVERS(
g1,
ST_MAKEPOINT(0.2, 0.8))
...
ST_INTERSECTS(
g1,
ST_MAKELINE(
TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
TO_GEOGRAPHY('POINT(0.8 0.2)')))
...
ST_INTERSECTS(
ST_POLYGON(
TO_GEOGRAPHY('SRID=4326;LINESTRING(0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)')),
g1)
...
ST_WITHIN(
g1,
TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))
...
ST_COVERS(
g1,
ST_GEOGPOINTFROMGEOHASH('s00'))