Performances des requêtes PostGIS Spatial Relationships

Question

J'ai un très grand ensemble de données contenant plus de 700 millions de points et un ensemble de données polygonales comme zone tampon.

Ma tâche est d'extraire tous les points à l'intérieur de la zone tampon et de créer une nouvelle table.

Ci-dessous mon code. Je le teste avec un petit jeu de données ponctuel et cela fonctionne très bien.

create table schema1.result as

select point.* from

schema1.site as point, schema2.buffer as poly

Where ST_Intersects(point.geo_loc,poly.wkb_geometry);

Malheureusement, la requête a duré 1 jour et n'a montré aucun signe pour se terminer.

Y a-t-il des conseils pour optimiser mon code pour accélérer la requête?

Mise à jour: la sortie d'Explain

"Boucle imbriquée (coût = 0,41..17773703,88 lignes = 6789472 largeur = 208)"

"-> Seq Scan sur tampon poly (coût = 0,00..18,50 lignes = 850 largeur = 32)"

"-> Analyse d'index à l'aide de idx_site sur le point de site (coût = 0,41..20902,23 lignes = 799 largeur = 208)"

"Index Cond: (geo_loc && poly.wkb_geometry)"

"Filtre: st_intersects (geo_loc, poly.wkb_geometry)"

«JIT:»

"Fonctions: 6"

"Options: Inlining true, Optimization true, Expressions true, Deforming true"

Answer 1

Je pense que ce qui se passe ici, c'est que lorsque vous écrivez:

from schema1.site as point, schema2.buffer as poly

PostgreSQL effectue un CROSS JOINentre les deux tables. Lorsque plusieurs tables sont répertoriées dans la FROMclause, postgres utilise une CROSS JOIN source qui aboutit à une table avec un nombre de lignes égal au produit cartésien de la source des deux tables .

pour éviter que vous puissiez utiliser WHERE EXISTScomme:

SELECT
    *
FROM schema1.site AS point_table
WHERE EXISTS(
    SELECT 1 
    FROM schema2.buffer AS poly_table
    where ST_Intersects(point_table.geom, poly_table.geom)
);

Cela permet à PostgreSQL de calculer uniquement la jointure spatiale et de ne pas avoir à créer une table temporaire de la jointure croisée complète, ce qui prend une éternité.

Answer 2

Il peut être plus rapide d'utiliser les polygones comme table de pilotage, en utilisant l'index de la table de points pour filtrer le grand nombre d'enregistrements. Cela permet à PostGIS d'optimiser le ST_Intersectsprédicat spatial en préparant chaque polygone.

Cela peut être forcé en utilisant LATERAL:

SELECT pt.* FROM schema2.buffer AS poly
JOIN LATERAL (SELECT * FROM schema1.site) AS pt 
  ON ST_Intersects(poly.wkb_geometry, pt.geo_loc);

Si les polygones ont un grand nombre de sommets, il peut être plus rapide ST_Subdividede les fragmenter avant de faire la requête ponctuelle:

WITH poly AS (
  SELECT ST_Subdivide(wkb_geometry) AS geom FROM schema2.buffer 
)
SELECT pt.* FROM poly
JOIN LATERAL (SELECT * FROM schema1.site) AS pt 
  ON ST_Intersects(poly.geom, pt.geo_loc);