2

I have lines and polygons in a PostGIS database. The lines may intersect with these polygons. I want to select all polygons that do not intersect with a line but do intersect with its buffered area.

Currently I have the following query:

    SELECT
        f.*
    FROM 
        polygons f 
        INNER JOIN lines l ON ST_Intersects(f.geom, ST_Buffer(l.geom, 50))
    WHERE 
        f.id NOT IN (
            SELECT 
                f.id
            FROM 
                polygons f 
                INNER JOIN lines l ON ST_Intersects(f.geom, l.geom)   
        )

This query takes very long and I was wondering if there is a more performative way to achieve what I want.

3

Because of the buffer, the spatial index is not used. Because of the inner join, you are building a buffer for every line and for every polygon.

I would use st_dwithin instead of a buffer, which makes use of available spatial indexes.

Also, I would use the same layer reference for the exclusion sub query

SELECT
   f.*
FROM 
   polygons f 
  INNER JOIN lines l ON ST_DWithin(f.geom,l.geom, 50)
WHERE NOT EXISTS (
      SELECT 1
      FROM lines l2 
      WHERE ST_Intersects(f.geom, l2.geom)   
      )

The above query ensure that there is no intersecting line at all, but will select the polygons multiple times if there are multiple lines within the proper distance.

To select a polygons once only, regardless of the number of nearby lines, the query would be

SELECT
   f.*
FROM 
   polygons f 
WHERE EXISTS (
      SELECT 1
      FROM lines l 
      WHERE ST_DWithin(f.geom,l.geom, 50)  
      )
  AND NOT EXISTS (
      SELECT 1
      FROM lines l2 
      WHERE ST_Intersects(f.geom, l2.geom)   
      )

If you want to work with single lines only (that is, apply the polygon selection per line, so if a line is at valid distance and a 2nd one is intersecting the polygon the polygon would be selected), the query can be simplified to

SELECT
   f.*
FROM 
   polygons f 
  INNER JOIN lines l 
     ON ST_DWithin(f.geom,l.geom, 50) 
     AND ST_Intersects(f.geom, l.geom) = FALSE  

or, to remove duplicates, to

SELECT
   f.*
FROM 
   polygons f 
WHERE EXISTS (
      SELECT 1
      FROM lines l 
      WHERE ST_DWithin(f.geom,l.geom, 50)
         AND ST_Intersects(f.geom, l.geom) = FALSE    
      )

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.