1

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?

|improve this question
  • 1
    Are you buffering by 3 degrees or is it meters? – bugmenot123 Jun 1 '17 at 9:03
  • 3 meter. The CRS is epsg:31370 – PieterB Jun 1 '17 at 9:03
1

Ensuring the geom is spatially indexed in the road and route tables will be essential to the speed of this query.

Assuming this is already in place I would recommend trying ST_DWithin which 'Returns true if the geometries are within the specified distance of one another.'

ST_DWithin(route.geom, road.geom, 3);

To ensure only geometries that are fully within the specified distance, ST_DFullyWithin can be used instead.

ST_DFullyWithin(route.geom, road.geom, 3);
|improve this answer
  • The problem with ST_DWithin is the same as using ST_Intersects. It selects roads that are intersecting and not just are IN the perimeter of 3 meters – PieterB Jun 1 '17 at 9:14
  • 1
    Ah, I didn't see that requirement. In that case use ST_DFullyWithin instead – ProudGIS Jun 1 '17 at 9:22
  • I tried ST_DFullyWithin and I get no results... – PieterB Jun 1 '17 at 9:43
  • Does adjusting the distance bring in any results? I don't know what the dataset is like, so I can't tell if that's an expected result or not. – ProudGIS Jun 1 '17 at 10:00
  • when I increase the distance to 100, then I get 3 results – PieterB Jun 1 '17 at 10:07

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.