[PostgreSQL/PostGIS] Versnellen ST_Intersects query

[jdh009]

Mijn vraag
Ik ben op dit moment bezig om een query te schrijven die op basis van een lijst met id's alle informatie ophaalt uit de tabel van de opgegeven id's en de buurvlakken (geometrisch gezien). Dit is de eerste keer dat ik een query schrijf die er tientallen minuten over doet of langer.

De dataset bevat het een deel van Nederland en de ID's in de IN functie zijn (semi) willekeurig). Totaal aantal records in de dataset is gemiddeld 200k maar kan in theorie oplopen tot een miljoen.

De totale code:

SQL:

SELECT * 
FROM "schema"."tabel" 
WHERE st_dimension(geometry) = 2 AND ST_Intersects(ST_CurveToLine(geometry), (
    SELECT ST_Buffer(ST_Multi(ST_Union(ST_CurveToLine(geometry))),0.5) 
FROM  "schema"."tabel" 
WHERE st_dimension(geometry) = 2 AND id IN(1,2,3,4,5,6,7,8,9,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,12502,24663)
))

Om de buren te bepalen pak ik de geometrie van de objecten die ik al in beeld heb en voeg de geometrie samen tot één object die ik vervolgens buffer met 0,5 meter.

SQL:

SELECT ST_Buffer(ST_Multi(ST_Union(ST_CurveToLine(geometry))),0.5) 
FROM  "schema"."tabel" 
WHERE st_dimension(geometry) = 2 AND id IN(1,2,3,4,5,6,7,8,9,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,12502,24663)

De volgende stap is degene die vrij lang duurt en waarmee ik kijk of objecten in de gehele dataset raakvlak hebben met mijn subset, dit zijn de objecten die ik uiteindelijk in zijn geheel wil hebben

SQL:

ST_Intersects(ST_CurveToLine(geometry), Resultaat van de select hierboven)

Uiteindelijk werkt het maar het is zo sloom dat ik hoop dat er mogelijkheden zijn om dit gehele proces te versnellen.

Relevante software en hardware die ik gebruik:
PostgreSQL 9.3.24
POSTGIS 2.1.2 r12389
GEOS 3.4.2-CAPI-1.8.2 r3921
PROJ Rel. 4.8.0, 6 March 2012
GDAL 1.10.1, released 2013/08/26
LIBXML 2.9.1

Wat ik al gevonden of geprobeerd heb:
De code in batches van een paar duizend id's draaien waarbij steeds id >= 0 AND id <5000. Netto is dit sneller dan alles in één keer draaien.

Op internet lees ik wel wat over spatial indexes maar heb te weinig ervaring met SQL om dit soort zaken te proberen.

Zijn er nog andere manieren om een intersect te versnellen?

[BCC]

Ja, met een Spatial Index, dus daar zul je je toch even op in moeten lezen http://postgis.net/workshops/postgis-intro/indexing.html

code:

CREATE INDEX schema_table_geometry_index
  ON "schema"."table"
  USING GIST (geometry);

[ Voor 57% gewijzigd door BCC op 12-04-2019 12:31 ]

[doskabouter]

Je kan st_dwithin eens proberen ipv buffers te gaan gebruiken.
ik kan zo even niet beoordelen uit hoeveel punten je geometry gemiddeld zijn, maar mijn ervaring is dat als dat groot is, je het best zo min mogelijk geo-functies gebruikt.

En natuurlijk even verifieren dat de index daadwerkelijk gebruikt wordt.