Using table joins with ST_DWithin to optimise process?

Question

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?

Answer 1

If an existence check is what you're after, better use EXISTS:

SELECT s.segments_id
FROM   segments AS s
WHERE  EXISTS (
  SELECT 1
  FROM   pt1 AS p
  WHERE  ST_DWithin(s.geom, p.geom, <dist>)
) OR  EXISTS (
  SELECT 1
  FROM   pt2 AS p
  WHERE  ST_DWithin(s.geom, p.geom, <dist>)
);

This has two major advantages over JOINs:

• the EXISTS will stop the execution of it's statement when it encounters the first condition match (1 -> TRUE -> found a match), thus reduces table traversal significantly when used with an appropriate index (like the spatial index and ST_DWithin)
• the OR will stop the whole block for the current row once the first statement returns TRUE; again, this will greatly increase performance

As a general note, if (non-) existence is what you are looking for, (NOT) EXISTS will beat JOINs in most cases.

Answer 2

In the end I have found a fairly fast way to do it (at least compared to what I have been trying with). Basically, I have just split my original query in two.

Instead of:

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)

I have tried with:

CREATE TABLE result_1 AS
SELECT t1.segment_id, t2.polyg1_id, t1.geom
FROM segments t1
LEFT JOIN polyg1 t2
ON ST_DWithin(t1.geom, t2.geom, X)


CREATE TABLE result_2 AS
SELECT t1.segment_id, t1.polyg1_id, t3.polyg2_id, t1.geom
FROM result_1 t1
LEFT JOIN polyg2 t3
ON ST_DWithin(t1.geom, t3.geom, X)

And then I just need to get those segments where either polyg1_id or polyg2_id is populated, and then obtain unique segments (get rid segments with multiple results). Finally I INNER JOIN with the original table of segments to get back the geometries.

CREATE TABLE result AS
SELECT t1.segment_id, t1.geom FROM segments t1
INNER JOIN 
(SELECT DISTINCT(segment_id) FROM
   (SELECT segment_id FROM result_2
    WHERE (polyg1_id >= 0) OR (polyg2_id >= 0))
    select1)
select2
ON t1.segment_id = select2.segment_id

As I said, it seems a bit convoluted and not very elegant, but so far it's proved to be the quickest way.