Abfrageleistung für PostGIS Spatial Relationships

Question

Ich habe einen sehr großen Datensatz, der über 700 Millionen Punkte enthält, und einen Polygon-Datensatz als Pufferzone.

Meine Aufgabe ist es, alle Punkte innerhalb der Pufferzone zu extrahieren und eine neue Tabelle zu erstellen.

Unten ist mein Code. Ich teste es mit einem kleinen Punktdatensatz und es funktioniert gut.

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

Leider dauerte die Abfrage 1 Tag und zeigte keine Anzeichen für den Abschluss.

Gibt es Ratschläge zur Optimierung meines Codes, um die Abfrage zu beschleunigen?

Update: Die Ausgabe von Explain

Verschachtelte Schleife (Kosten = 0,41..17773703,88 Zeilen = 6789472 Breite = 208)

"-> Seq Scan auf Puffer Poly (Kosten = 0,00..18,50 Zeilen = 850 Breite = 32)"

"-> Index-Scan mit idx_site am Standortpunkt (Kosten = 0,41..20902,23 Zeilen = 799 Breite = 208)"

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

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

"JIT:"

"Funktionen: 6"

"Optionen: Inlining true, Optimierung true, Expressions true, Deforming true"

Answer 1

Ich denke, was hier los ist, ist das, wenn Sie schreiben:

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

PostgreSQL führt eine CROSS JOINzwischen den beiden Tabellen durch. Wenn mehrere Tabellen in der FROMKlausel aufgeführt sind, verwendet postgres eine CROSS JOIN Quelle , die zu einer Tabelle mit einer Anzahl von Zeilen führt, die dem kartesischen Produkt der Quelle mit zwei Tabellen entspricht .

um zu vermeiden, dass Sie verwenden können WHERE EXISTSals:

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

Dadurch kann PostgreSQL nur den räumlichen Join berechnen und muss keine temporäre Tabelle des vollständigen Cross-Joins erstellen, was ewig dauert.

Answer 2

Es ist möglicherweise schneller, die Polygone als Treibertabelle zu verwenden und den Punkttabellenindex zu verwenden, um die große Anzahl von Datensätzen herauszufiltern. Auf diese Weise kann PostGIS das ST_Intersectsräumliche Prädikat optimieren, indem jedes Polygon vorbereitet wird.

Dies kann erzwungen werden mit 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);

Wenn die Polygone eine große Anzahl von Scheitelpunkten haben, können sie schneller ST_Subdividefragmentiert werden, bevor die Punktabfrage ausgeführt wird:

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