1

A user can choose between walking, bicycling, public transit, or driving for X minutes. Their current location is a latitude and longitude pair given by the HTML5 Geolocation API(wgs84). The (roughly 10000) points of interests' location(latitude and longitude pair from Google Geococding API(wgs84)) spread across North America is stored in Postgresql as a PostGIS geography data type.

I want a list of POIs, travel time, and the length of the route sorted by travel time ascending that the user can reach given the travel criteria. I don't need the route itself. Is using pgRouting the correct choice? If it is the right choice, how can I import a routable Open Street Map for the different travel methods? If not, I'm open to changing the software stack to accommodate for this, so long as the result is accurate and fast(< 100ms on zen2 and 64gb of RAM)

I initially thought of generating an isochrone map using OSRM or GraphHopper, and then using ST_Within to find all POIs within the generated isochrone. However, I don't want to deal with projections and it seems like redundant work. From my understanding, generating an isochrone map uses Dijkstra's algorithm and then ending a route when a certain cost has been reached. Why not instead end the route when it's at a certain proximity to a POI and duplicate and continue routing if the cost has not been reached?

Improve this question
6
  • OSRMs table service is pretty much exactly what you are looking for.
    – geozelot
    Commented Apr 4, 2020 at 7:05
  • You're right. The only thing it seems to be missing is limiting by travel time instead of the radius. If I were to use OSRM, how would I keep the database and it in sync?
    – No_name
    Commented Apr 4, 2020 at 7:21
  • What you are requesting there is Google server farm material. For OSRM, your machine cannot hold any routing graph for North America in memory; while that is technically no hurdle, falling back to sth. like shared memory will have an impact on speed. On the other hand, filtering nodes based on different transport modes and proximity in PG for pgRouting to perform in any reasonable amount of time is equally challenging. If you have sufficient CPUs, letting multiple OSRM instances run the table service on subsets of all POIs via shared memory would probably be the easiest and fastest.
    – geozelot
    Commented Apr 4, 2020 at 8:09
  • Since using OSRM's table service for all 10000 POIs is not feasible, how about using PostGIS <-> KNN to limit the POIs down to 25 and limit the radius with ST_DWithin? Does that lower the hardware requirements?
    – No_name
    Commented Apr 4, 2020 at 9:38
  • github.com/Project-OSRM/osrm-backend/issues/5704 I asked about limiting by travel time, and it seems as long as I have enough RAM, performance is not an issue. I will post an answer after I try out the proposed solution.
    – No_name
    Commented Apr 5, 2020 at 23:44

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.