Accélérer les requêtes géospatiales grâce à l’optimisation de la recherche

Snowflake logo in black (no text) Fonctionnalité Enterprise Edition

Cette fonctionnalité nécessite Enterprise Edition (ou une édition supérieure). Pour en savoir plus sur la mise à niveau, veuillez contacter le support Snowflake.

Le service d’optimisation des recherches peut améliorer les performances des requêtes dont les prédicats utilisent des fonctions géospatiales avec des objets GEOGRAPHY.

Note

Vous devez activer cette fonctionnalité pour des colonnes spécifiques à l’aide de la clause ON dans la commande ALTER TABLE … ADD SEARCH OPTIMIZATION. Par exemple :

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON GEO(mygeocol);
Copy

Les requêtes qui utilisent des fonctions géospatiales ne sont pas améliorées si vous omettez la clause ON.

Les sections suivantes fournissent plus de détails :

Note

Les objets GEOMETRY ne sont pas encore pris en charge.

Prédicats pris en charge avec des fonctions géospatiales

Pour les requêtes dont les prédicats utilisent les fonctions suivantes :

Le service d’optimisation des recherches peut améliorer les performances si :

  • Une expression d’entrée est une colonne GEOGRAPHY dans une table, et

  • L’autre expression en entrée est une constante GEOGRAPHY (créée par une conversion ou une fonction constructrice).

  • Pour ST_DWITHIN, l’argument de distance est une constante REAL non négative.

Notez que cette fonctionnalité présente les mêmes limitations que celles qui s’appliquent au service d’optimisation des recherches.

Autres remarques sur les performances

Étant donné que le service d’optimisation de la recherche est conçu pour des prédicats très sélectifs et que les prédicats filtrent par proximité entre les objets géospatiaux, le clustering des objets géospatiaux par proximité dans la table peut entraîner de meilleures performances. Vos données peuvent faire l’objet d’un clustering en spécifiant l’ordre de tri lors du chargement des données ou en utilisant le clustering automatique, selon que la table de base change fréquemment :

Chargement de données pré-triées

Si les données de votre table de base ne changent pas souvent, vous pouvez spécifier l’ordre de tri lors du chargement des données. Vous pouvez ensuite activer l’optimisation de la recherche sur la colonne GEOGRAPHY. Par exemple :

CREATE TABLE new_table AS SELECT * FROM source_table ORDER BY st_geohash(geom);
ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Copy

Après chaque modification importante apportée à vos données de base, vous pouvez trier manuellement les données.

Clustering automatique

S’il y a des mises à jour fréquentes de votre table de base, vous pouvez utiliser la commande ALTER TABLE … CLUSTER BY … pour activer le clustering automatique afin que la table fasse automatiquement l’objet d’un reclustering à mesure qu’elle change.

L’exemple suivant ajoute une nouvelle colonne geom_geohash de type VARCHAR et stocke le géohachage ou l’index H3 de la colonne GEOGRAPHY geom dans cette nouvelle colonne. Il active ensuite le clustering automatique avec la nouvelle colonne comme clé de cluster. Avec cette approche, les parties du tableau qui changent feront l’objet d’un reclustering automatique.

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

Exemples qui utilisent des fonctions géospatiales

Les instructions suivantes créent et configurent la table utilisée dans les exemples de cette section. La dernière instruction utilise la clause ON dans la commande ALTER TABLE … ADD SEARCH OPTIMIZATION pour ajouter une optimisation de la recherche pour la colonne GEOGRAPHY g1.

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

Exemples de prédicats pris en charge

La requête suivante est un exemple de requête prise en charge par le service d’optimisation des recherches. Le service d’optimisation de la recherche peut utiliser les chemins d’accès à la recherche pour améliorer les performances de cette requête :

SELECT id FROM geospatial_table WHERE
  ST_INTERSECTS(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));
Copy

Voici des exemples de prédicats supplémentaires pris en charge par le service d’optimisation des recherches :

...
  ST_INTERSECTS(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)
Copy
...
  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)
Copy
...
  ST_CONTAINS(
    g1,
    TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))
Copy
...
  ST_WITHIN(
   TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
   g1)
Copy
...
  ST_WITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
Copy
...
  ST_COVERS(
    TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
    g1)
Copy
...
  ST_COVERS(
    g1,
    TO_GEOGRAPHY('POINT(0 0)'))
Copy
...
  ST_COVEREDBY(
    TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
    g1)
Copy
...
  ST_COVEREDBY(
    g1,
    TO_GEOGRAPHY('POINT(-122.35 37.55)'))
Copy
...
  ST_DWITHIN(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1,
    100000)
Copy
...
  ST_DWITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    100000)
Copy

Exemples de construction de constantes GEOGRAPHY

Voici des exemples de prédicats qui utilisent différentes fonctions de conversion et de construction pour la constante GEOGRAPHY.

...
  ST_INTERSECTS(
    g1,
    ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
Copy
...
  ST_INTERSECTS(
    ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)
Copy
...
  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)
Copy
...
  ST_WITHIN(
    ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
    g1)
Copy
...
  ST_COVERS(
    g1,
    ST_MAKEPOINT(0.2, 0.8))
Copy
...
  ST_INTERSECTS(
    g1,
    ST_MAKELINE(
      TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
      TO_GEOGRAPHY('POINT(0.8 0.2)')))
Copy
...
  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)
Copy
...
  ST_WITHIN(
    g1,
    TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))
Copy
...
  ST_COVERS(
    g1,
    ST_GEOGPOINTFROMGEOHASH('s00'))
Copy
Langage: Français