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

Question

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 

Answer 1

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.

Answer 2

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.