2

I have two line layers in PostgreSQL/PostGIS. I want to the select data which are not intersecting. When I am writing this query it is taking a lot of time. How can I make it faster and see the result?

SELECT Geometry into Qlayer5
FROM qlayer4, egmap_20170316_tmc_polyline
WHERE st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false  

and I have also tried using right join but none of them giving output.

SELECT Geometry INTO Qlayer5
FROM qlayer4
RIGHT JOIN egmap_20170316_tmc_polyline ON
st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false 
  • non-intersections are tricky; your query will result in a carestian product of both tables, that's why it is so slow. your join condition matches every pair of lines that do not intersect; so if table A has 1 row and table B 100, with no intersection between them, you will get 100 rows returned! – ThingumaBob Apr 15 at 8:33
  • 1
    ...or better, when table A has 3 rows and B 100, with no intersection, your query will return 300 rows. similar, with 1000 in A and 1000 in B, Postgres fetches 1000000 rows after computing each of them... – ThingumaBob Apr 15 at 9:01
  • Ya understand but i dont find any other way to filter out non intersected lines.i am trying your queries now .Do you have any suggestions? – poonam patel Apr 15 at 9:13
  • my suggestion is my answer. I'm just trying to explain why your query will fail with useless results after a lifetime of execution...,) – ThingumaBob Apr 15 at 9:17
5

Non-intersections are tricky. To avoid a cartesian product of the joined tables you need to either

  • check against a collection/union of the joined tables' geometries:

    WITH
      col AS (
        SELECT ST_Collect(geom) AS geom
        FROM   egmap_20170316_tmc_polyline
      )
    SELECT *
    FROM   qlayer4 AS a
    JOIN   col AS b
      ON   NOT ST_Intersects(a.geom, b.geom)
    ;
    

    which will be rather inefficient for larger tables (indexes are less effective and the amount of vertices to traverse each time is extremely high)

  • or better, check against existence of intersections:

    SELECT *
    FROM   qlayer4 AS a
    WHERE NOT EXISTS (
      SELECT 1
      FROM   egmap_20170316_tmc_polyline AS b
      WHERE  ST_Intersects(a.geom, b.geom)
    );
    

    which will at least use the index efficiently

Note that, even with the more optimized second query, this is a heavy operation and will likely take some time.

  • you genius. second query solved my problem almost – poonam patel Apr 16 at 3:19
0

Try this first for seeing the result of your query:

SELECT a.Geometry
FROM qlayer4 AS a,
egmap_20170316_tmc_polyline AS b  
WHERE
st_intersects(a.Geometry, b.geom) = false;

If it returns the expected result, insert the "INTO Qlayer5" statement.

  • Thank you @blabbath. This query i have tried earlier .it did not finish till two hours so, i stopped. – poonam patel Apr 15 at 6:44
  • @poonampatel How many objects are in your tables? Have you set a spatial index? – blabbath Apr 15 at 7:00
  • yes i have set spatial index .i have quite a huge data. – poonam patel Apr 15 at 7:38
  • Then, depending on your hardware specs and tuning parameters, 2 hours might just not be enough time. – blabbath Apr 15 at 7:45
  • I also think the same @blabbath. that is why i am looking for efficient solution – poonam patel Apr 15 at 8:33

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.