# Spatial joining two line layers and checking intersection in PostgreSQL/PostGIS

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
• ...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

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

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