I have a table with ~15K records consisting in points with a 200m buffer and I need to investigate how many of them are in proximity of polygons contained in another table (~20K).

I wrote a function (really simple one) which uses a record variable (rowgeo) and then uses ST_Overlaps like this:

FROM pi a
WHERE ST_Overlaps(a.geom, rowgeo.buffer) INTO var 

well, the function is dramatically slow... it's running since yesterday morning. Both tables have a gist index on geometry column.

Any hints?


You don't need to buffer points before comparing.
Use ST_Dwithin and a JOIN:

SELECT table1.gid
FROM table1
JOIN table2
ON ST_DWithin(table1.geom,table2.geom,200);

Make sure, that both geom fields are in same SRID.
The distance value must be in the unit of the SRID.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.