I have a layer with roads (4000 features) and a layer with routes (80 features). I want to select every road located on a route, combined with the route-type.
Normaly I would use
select road.geom, road.id, route.type from road, route where st_contains(route.geom, road.geom).
Because the routes are not properly digitized I need to buffer the routes to select all roads contained by a route. So when I do
select road.geom, road.id, route.type from road, route where st_contains(st_buffer(route.geom, 3), road.geom) the query keeps running for a long time without result.
How can I make this query running faster?