I am running an analysis to find lines that is covered by a polygon. I have to create the polygon from a road network (hence the buffer and union) because the the lines are not exactly equal. The query works but it takes a long time to run, about 350 seconds on my dataset of about 20 000 buffered and united lines.

Is there a way I could modify it to make it execute faster?

with poly as  (
    SELECT ST_UNION(ST_BUFFER(lines.geom, 3)) as newgeom 
        FROM roads
    when ST_coveredby(l.geom, poly.newgeom)
    then l.geom
        ST_Intersection(geom, poly.newgeom) end as geom
from lines as l
    inner join poly
        on ST_Intersects(l.geom, poly.newgeom)
            and not ST_touches(l.geom, poly.newgeom)

I believe this query would be faster and produce the same / equivalent results:

WITH temp AS (
    ST_Intersection(ST_Buffer(r.geom, 3), l.geom) AS newgeom
FROM lines l
    JOIN roads r
        ON ST_DWithin(l.geom, r.geom, 2.99) -- 2.99 instead of 3 to exclude geometries that only touch
    ST_Union(newgeom) AS geom
FROM temp
GROUP BY road_id

This way the spatial join and the spatial filter would be done in the same step (using the ST_DWithin function) and making use of spatial indexes of both layers.

  • Thank you, that workeded great, loads faster. 1.1 seconds! The returned geometry was slightly different but did not change the end result. – geogrow May 23 at 9:47

It is slow because there is no spatial index on the buffered geometries.

You could create a temporary table with a spatial index, populate it with the buffered geometries, and run the query on this temp table.

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.