I have a PostgreSQL table of students with lat, lon and geom, and a table of schools with the same, and a table of roads that has been converted to nodes.
I'm trying to find the driving distance to the nearest school for each student. But I'm new to PostgreSQL and pgRouting, but I'm not seeing a great way to structure the multiple joins to work with the pgr_drivingDistance function.
Ideally I would end up with a new table that would have the student id, the id of the closest school via driving distance, and the distance.
Any thoughts on how to approach this would be great.
Update addressing Jendrusk's questions:
- What do you mean by converting roads to nodes? Maybe you meant network graph?
select pgr_createTopology('roads', 0.0001, 'geom', 'id')
- Are you able to find at least one way and the trouble is with many ways?
The challenge is finding the shortest way for multiple students. I could take the lat/lon for one student, find the nearest node and then calculate distances that way, but I need to do it for hundreds.
- Nearest school euclidean or by the roads?
- What was the work-flow of this conversion form roads to nodes?
- What are the names of tables and column names in this tables?
Three tables: Kids, roads, schools. Kids and schools have a GID, lat, lon, geom, and a bunch of other non-spatial info. Roads has GID, geom, source, target and length.
- How big are your tables with students and schools?
100k students, several dozen schools.
- Is it OK to select nearest school euclidean
No, interested in driving distance.
Also function st_DrivingDistance is not finding route, but it's creating set of edges reachable in specified time (e.g. how far you can go in 15 minutes).
Also 2 you need distance from student to nearest school but which is nearest if you don't know the distance? Do you have some kind of relation between schools and students? If not you have to count distance from one student to every school to know which is nearest. In most cases nearest euclidean (in straight line) will be also nearest by the roads, but if you have some special cases in your graph such as river with one bridge you can't be sure.<<<
Again, I do not need to find the route. I need to identify the closest school based on driving distance. My impression is that using pgr_drivingDistance it is possible to create a table containing driving distance length from a specified node to all other nodes in a network. You can scope this operation to nodes within a particular distance of the index node, and obviously, in my case, only consider nodes that are the closest node to a school.
I'm basing this info on the following post: http://anitagraser.com/2011/05/13/catchment-areas-with-pgrouting-driving_distance/
I believe pgr_drivingDistance can limit the query based on a number of different costs, including driving time and distance, depending on how the cost is specified both in the query and in the data.