2

I've downloaded the OpenStreetMap data from GeoFabrik and imported it into PostgreSQL as described in the first procedures on this page.

The import was successful. After that, I found a way of associating random points with its osm_id from this database. I know there are APIs like https://nominatim.openstreetmap.org/reverse.php?lat=-23.513442&lon=-46.384794&zoom=18&format=jsonv2 from Nominatim where I can get this information, but since there're limitations on the number of requests that we can make. I'd like to implement a solution to get this information locally with PostGIS.

I've seen this answer that solves the problem of how to find the closest road to the point... I've adapted it to my case as:

SELECT way,osm_id,ST_Distance(way,'SRID=3857;POINT(-3890646.5744145643 -899377.0801662721)'::geometry) FROM planet_osm_line r ORDER BY 2 ASC LIMIT 1;

It works fine and it returns the geometry and osm_id of the closest road from the point. However, even though the query returns after some seconds I'm in doubt if there are ways of optimizing the time of these results since in my case of use I want it to have a fast response to a large number of input points that arrive at the same time.

Should I worry about fragmenting the GeoFabrik data into smaller datasets (like a dataset with only the city that I'm interested) to have a faster response with the last query?

What strategies can I use to get a fast return of the osm_id and geometry from the OpenStreetMap database?

1 Answer 1

3

Use the 2D distance operator <->:

SELECT
    way,
    osm_id
FROM
    planet_osm_line AS osm
ORDER BY
    osm.way <-> 'SRID=3857;POINT(-3890646.5744145643 -899377.0801662721)'::GEOMETRY
LIMIT
    1
;

Related:

3
  • Thanks, it made a huge difference in the response time... My original query was taking around 5 seconds. With this solution, it takes around 50 milliseconds on my computer now. Considering I've got all the GeoFabrik data from Brazil (which is a huge country) and in my case I'm working with a single city. Would it make any difference to fragment the data into a smaller subset containing only the data from the city that I'm working with?
    – raylight
    Apr 22 at 17:22
  • I've made some tests by creating a new table with the data fragmented in a single city and I didn't see any significant change in the response time. So I'll assume that when indexes are being used properly it doesn't matter the size of the database. I'm not completely sure about this last statement though.
    – raylight
    Apr 23 at 7:04
  • 2
    @raylight well, since index lookups are somewhere in the realm of O(log n) complexity, you'd have to exponentially increase table sizes to see any significant differences - this is, as long as indexes fit into memory. I wouldn't bother splitting until any relation is above system memory capacity. You could look at clustering your data on a GeoHash index, or even the GIST index to start with - but 50ms is probably rather close to the overhead time PG needs to actually load the index to begin with, so I doubt you will see much better performance no matter what you do.
    – geozelot
    Apr 23 at 18:04

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.