[PostGIS/PostgreSQL] Ophalen buren en buren van buren

[jdh009]

Mijn vraag
Ik ben bezig om mijn query te verbeteren die op basis van een unieke lijst opgegeven ID's twee dingen terug moet geven:

Geometrie (vlakken) en ID's van de:

1. de objecten zelf
2. de direct aangrenzende buren
3. de direct aangrenzende buren van deze buren

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="GDAL 1.10.1, released 2013/08/26" TOPOLOGY RASTER

Wat ik al gevonden of geprobeerd heb:
Ik heb een werkende query om de buren op te halen maar deze is echt enorm sloom bij een database met miljoenen records.Dus om dezelfde query nogmaals te draaien is bijna niet te doen. Ik heb een spatial index op de geometry zitten genaamd bgtregister_geometry_idx maar zie zo niet of deze gebruikt word wanneer ik een explain draai.

SQL:

SELECT r2.geometry, 
       r2.bgtid 
FROM   "pb34"."bgtregister" AS r2 
WHERE  St_dimension(r2.geometry) = 2 
AND    st_intersects(St_curvetoline(r2.geometry), 
       ( 
              SELECT St_buffer(St_multi(St_union(St_curvetoline(r1.geometry))),0.1) 
              FROM   "pb34"."bgtregister" AS r1 
              WHERE  St_dimension(r1.geometry) = 2 
              AND    r1.bgtid IN(1,2,3,4,5,207,208,209,210,211,212,213,214,215,216,217,218,219,220,12498,12502,24663,17411,119945,24663)))

Bij een klein aantal (<10 stuks) gaat de query nog vrij vlot maar daarboven gaat ie al snel richting de minuten tot zelfs uren. Is het mogelijk om het gebruik van de genoemde spatial index te forceren of de bovenstaande code anders te schrijven om zo hetzelfde resultaat te behalen geheel uitgevoerd in een query?

De buren van de buren haal ik op dit moment op via een ETL tool (FME) waarbij ik de buffer al gemaakt hebt in een workflow. Ik vrees dat als ik dit nu wil doen via bovenstaande code dat die dan jaren bezig is.

SQL:

SELECT M.geometry, 
       M.bgtid 
FROM "pb34"."bgtregister" M 
WHERE ST_Intersects(ST_CurveToLine(geometry),ST_GeomFromText('@Value(wkt)',28992)) AND st_dimension(geometry) = 2

@Value(wkt) = de geometrie van de objecten en de buren als 1 geometrisch object.

[ Voor 34% gewijzigd door jdh009 op 22-11-2019 15:53 ]

[The Eagle]

Interessante. Ben zelf niet zo thuis in geo objecten maar heb er zijdelings mee te maken, dus ik ga deze even volgen.
Wat me als eerste opviel is het grote aantal haakjes. Met name op het einde van die hele lijst cijfer. Daar staan er twee en ik vraag me af waar de counterpart van de laatste is. Iets zegt me dat je wel eens onnodig data bij elkaar zou kunnen nemen.

Kun je je code iig even wat aanpassen zodat ie beter leesbaar is? Wellicht heeft jouzelf dat ook meer inzicht

[jdh009]

The Eagle schreef op vrijdag 22 november 2019 @ 13:38:
Interessante. Ben zelf niet zo thuis in geo objecten maar heb er zijdelings mee te maken, dus ik ga deze even volgen.
Wat me als eerste opviel is het grote aantal haakjes. Met name op het einde van die hele lijst cijfer. Daar staan er twee en ik vraag me af waar de counterpart van de laatste is. Iets zegt me dat je wel eens onnodig data bij elkaar zou kunnen nemen.

Kun je je code iig even wat aanpassen zodat ie beter leesbaar is? Wellicht heeft jouzelf dat ook meer inzicht

Dank voor de feedback. Ik heb hem aangepast naar een meer pretty print opmaak. Zag dat ik op het einde juist een ) miste. Voor de leesbaarheid heb ik ook een behoorlijk aantal nummers weggehaald maar in theorie gaan daar dus alle geselecteerde id's in waarover je deze selectie wilt maken.

[Knutselsmurf]

Wat mij opvalt, is dat je St_curvetoline(r2.geometry) gebruikt in je intersect. Dat is waarschijnlijk de reden dat de index niet wordt gebruikt.

Ik zou dan óf die functie niet gebruiken, óf het resultaat van die functie voor ieder record in een extra veld opslaan en op dat veld je index leggen

[doskabouter]

Of een index op St_curvetoline(r2.geometry) maken.
Kan je de hele explain van je query hier eens neerzetten?