The general problem is to allocate town's kids to their nearest school and get the distance and the geometry.
Tools in use is Postgresql 10.6, PostGIS 2.4.3 and PGRouting 2.5.2
There are three tables: school_locations (n=5) kids_locations and (n=1500) roads
So far the schools and the kids have be allocated to their nearest network nodes and the nearest school and the distance have been calculated.
The pairing results are stored in a table that can be seen below. To get this result pgr_dijkstraCost has been used. The drawback is that it does not return the geometry. So another algorithm has to be used to create a new table or update a geom field in the result table mentioned above.
Here my SQL so far:
CREATE TABLE shortest_path AS ( SELECT kids.id AS kid_id , subquery.start_vid AS source , school.id AS school_id , subquery.end_vid AS target , subquery.agg_cost FROM ( SELECT row_number() OVER (PARTITION BY start_vid ORDER BY agg_cost ASC) row_num , * FROM pgr_dijkstraCost ('SELECT id, source, target, length as cost FROM roads WHERE length >= 0' , ( SELECT array_agg(node_id) FROM kids_location) , ( SELECT array_agg(node_id) FROM school_location) , FALSE) ORDER BY start_vid , agg_cost) AS subquery INNER JOIN kids_location AS kids ON (kids.node_id = start_vid) INNER JOIN school_location AS school ON (school.node_id = end_vid) WHERE row_num = 1);
The resulting table looks like this:
kid_id | source | school_id | target | agg_cost 523 | 164933 | 1 | 156849 | 5202 522 | 43687 | 1 | 156849 | 4470 521 | 115352 | 1 | 156849 | 4088 520 | 64630 | 2 | 130234 | 4748 519 | 114828 | 2 | 130234 | 4138 518 | 82010 | 2 | 130234 | 5443 517 | 87980 | 2 | 130234 | 6237
The problem is that I know how to calculate the geometry for one pair but I do not know how to do it for the entire table. I guess looping should solve the problem, but I do not have the knowledge to do so.
SQL for one pair of kid<->school:
SELECT seq , edge , b.geom FROM pgr_dijkstra (' SELECT id, source, target, length as cost FROM roads' , 43687 , 156849 , FALSE) AS a INNER JOIN roads AS b ON (a.edge = b.id) ORDER BY seq;
This would be the hardcoded version. I could construct n version of this SQL but looping through the table and setting source and targed would be the better solution. I imagine this is common problem but I could not find a solution so far. If anyone could help me with code please.