1

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?

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.

  • I will try this if after a few more hours the query I'm trying has not finished yet (hard to stop a query after 48 hours running!). However, I have been told I should always use joins for this type of queries (I don't really understand the reasoning behind it though) – Pitrako Junior Oct 7 at 8:15
  • 2 INNER JOINS would not work because the first JOIN would not keep all the necessary segments to run the second JOIN. – Pitrako Junior Oct 7 at 10:51
  • From your question I understand you intend an AND condition, so a segment must be in proximity of both tables to be considered? If so, removing unmatched segments in the first JOIN is quite what you want. In your first comment, if you were referring to @Vince's comment on your other question, that is not what it meant; you should explicitly use the JOIN syntax instead of listing tables by , (which translates into CROSS JOIN), and the requirements of your other question is completely different: here, you are specifically looking for the existence of rows by condition. – ThingumaBob Oct 7 at 11:07
  • 1
    Unless you're using a toaster oven as a database server, with a USB 1.0 thumbdrive on a SATlink shared device, 48 hours is 42 hours too long to run this query. – Vince Oct 7 at 11:37
  • Sorry, my mistake!! It's not AND but OR. Just amended the description. – Pitrako Junior Oct 7 at 13:29
0

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.

  • Now, I must admit I haven't got the slightest idea why dividing the 2 JOINS into two different queries is faster than having both JOINS into the same query (????). A lot faster actually. – Pitrako Junior Oct 9 at 14:11

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.