2

I am interested in the following query:

Given a set of query points ($\leq 10$), how many of them have distance at most $s$ meters to the closest point on the closest street.

What's the most efficient way of writing / pre-processing this query?

1) Pre-process the roads (in osm2pgsql) by replacing each road by a Buffer of $s$ meter width. Then do stabbing queries using ST_Contains.

2) Replace each query point by a circle of radius $s$ and query if any road is intersected by a query circle.

3) Other Approaches?

1

From your questions it seems to be a pretty easy query:

SELECT count(*) total FROM points INNER JOIN streets ON ST_DWithin(points.geom, streets.geom, maxdistance)

I would like to make use of your question to point out that option 2) is a bad idea since creating circles (aka buffers) is an expensive and unnecessary task when you are only interested in a maximum distance. Option 1) is not needed but make sure you have spatial indices on your geometries so you automatically make use of the bounding boxes.

  • thank you very much for your answer. Being new to databases I have a basic question: You join the points with the streets, but in my use case the points would be provided as user input to the query. Would that still work? – user695652 Feb 16 '16 at 20:54
  • Yes, that would still work but the way you do it depends a bit on how many points each time and what kind of program interface you use. It would be best if you can phrase that in a new question, with a reference to this one. You can set this one to accepted for this case, if applicable. – tilt Feb 16 '16 at 21:45

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.