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 ) select case when ST_coveredby(l.geom, poly.newgeom) then l.geom else 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)