1

I need to find lines(line1) that are not connected to points at both end not connected by any lines e.g line1., line2 in this case.

I tried below query but Its taking too much time even with limit 10. it keeps running.

SELECT DISTINCT ON (1) t1.objectid,
                   t1.properties
FROM line1 t1,
     line1 t2,
     point1 t3,
     line2 t4
WHERE NOT ST_Intersects (t1.geom, t2.geom)
  AND t1.objectid <> t2.objectid
  AND NOT ST_Intersects(t1.geom, t3.geom)
  AND NOT ST_Intersects(t1.geom, t4.geom)
ORDER BY t1.objectid

1 Answer 1

1

The query is taking a long time because you are almost returning a complete cross product between the 4 tables. We can expect most lines not to intersect with each others (think of a road network, streets from a city do not intersect with streets from another city). A way to solve this is to use the opposite logic: search for lines that do intersect, and then "revert" this selection to keep the ones that don't intersect.

It would be a query similar to the following. Note that you shouldn't need the distinct clause anymore.

SELECT  t1.objectid,
         t1.properties
FROM line1 t1
WHERE NOT EXISTS ( 
 SELECT 1 FROM
     line1 t2,
     point1 t3,
     line2 t4
  WHERE (ST_Intersects (t1.geom, t2.geom)
      AND t1.objectid <> t2.objectid)
    OR  ST_Intersects(t1.geom, t3.geom)
    OR  ST_Intersects(t1.geom, t4.geom))
ORDER BY t1.objectid;

PS: This query with the OR will ignore lines that do intersect with any of the other layers. If you want to ignores the lines that do intersect with ALL layers, just replace the OR by AND.

PS2: of course you would need spatial index on each of the geometry columns

1
  • It still keep on running even after creating indexes? Sep 19, 2017 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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