PostGIS Spatial Relationships sorgu performansı

Question

700 milyondan fazla nokta ve tampon bölge olarak bir çokgen veri kümesi içeren çok büyük bir veri kümem var.

Görevim tampon bölge içindeki tüm noktaları çıkarmak ve yeni bir tablo oluşturmaktır.

Kodum aşağıdadır. Küçük nokta veri kümesiyle test ediyorum ve iyi çalışıyor.

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

Maalesef sorgu 1 gün sürdü ve tamamlanması için herhangi bir işaret göstermedi.

Sorguyu hızlandırmak için kodumu optimize etmem için herhangi bir tavsiye var mı?

Güncelleme: Açıklayın çıktısı

"İç İçe Döngü (maliyet = 0.41..17773703.88 satır = 6789472 genişlik = 208)"

"-> Tampon çoklu üzerinde Sıralı Tarama (maliyet = 0.00..18.50 satır = 850 genişlik = 32)"

"-> Site noktasında idx_site kullanarak Dizin Taraması (maliyet = 0.41..20902.23 satır = 799 genişlik = 208)"

"Dizin Koşulu: (geo_loc && poly.wkb_geometry)"

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

"JIT:"

"İşlevler: 6"

"Seçenekler: Inlining true, Optimization true, İfadeler true, Deforming true"

Answer 1

Sanırım burada neler olup bittiğini yazdığınızda:

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

PostgreSQL CROSS JOINiki tablo arasında bir yapıyor . Maddede birden çok tablo listelendiğinde, FROMpostgres iki tablo kaynağının Kartezyen ürününe eşit sayıda satıra sahip bir tabloyla sonuçlanan bir CROSS JOIN kaynak kullanır .

bunu önlemek için şu şekilde kullanabilirsiniz WHERE EXISTS:

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

Bu, PostgreSQL'in yalnızca uzamsal birleşimi hesaplamasına ve sonsuza kadar süren tam çapraz birleşim için geçici bir tablo oluşturmasına gerek kalmamasına izin verir.

Answer 2

Çok sayıda kaydı filtrelemek için nokta tablosu indeksini kullanarak çokgenleri sürüş tablosu olarak kullanmak daha hızlı olabilir. Bu, PostGIS'in ST_Intersectsher poligonu hazırlayarak uzamsal koşulu optimize etmesini sağlar .

Bu, aşağıdakiler kullanılarak zorlanabilir 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);

Çokgenlerin çok sayıda köşesi varsa, ST_Subdividenokta sorgusunu yapmadan önce onları parçalara ayırmak daha hızlı olabilir :

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