I am trying to select all segments (among millions of them) that are less than X metres from a few thousands polygons obtained from a raster dataset (meaning they've got quite a complex geometry - many vertices) OR less than X metres from another set of thousands polygons (less complex geometry). I am attempting to do so using ST_DWithin and a double LEFT JOIN, but it's proving to be very slow (more than 48 hours running and it hasn't finished yet).
Projected Coordinate System (British National Grid)
All 3 tables have a spatial index
This is the query I'm trying with:
CREATE TABLE result AS SELECT t1.segments_id , t2.polyg1_id , t3.polyg2_id FROM segments t1 LEFT JOIN polyg1 t2 ON ST_DWithin(t1.geom, t2.geom, X) LEFT JOIN polyg2 t3 ON ST_DWithin(t1.geom, t3.geom, X)
Once this query is finished I would just need to discard all records from
result that have no polyg1_id or polyg2_id values.
Is there any better (more efficient) ways to achieve what I'm trying to obtain?