1

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.

  • well, the function is called pgr_dijkstraCost since it's supposed to return only the cost ,) the concept of iterating over a table is implemented in PostgreSQL's SQL dialect with the LATERAL join...but in this case, running a pgr_Dijkstra Many-to-Many routing and filter out the minimum agg_cost per start_vid from the result should be the better idea. – ThingumaBob Nov 20 '18 at 10:09
1

I'm not sure how, but you should let your query run on every row of the interemediate table, passing the source and target as the two hardcoded parameter in yout query.

Then you can group by by kid_id, using as aggregate function ST_Collect or array_agg. It would group each path as row that you can select togheter with kid_id.

This is just a rough idea, but I don't have a DB to try implementing a specific query.

EDIT: Can you try something like this? However I'm not sure 'bout its complexity since is joining 3 tables

SELECT
     ST_COLLECT(b.geom), kid_id
FROM
    pgr_dijkstra ('
               SELECT id, source, target,
                       length as cost FROM roads'
    , sp.source
    , sp.target
    , FALSE) AS a
JOIN shortest_path AS sp
INNER JOIN roads AS b ON (a.edge = b.id)
GROUP_BY kid_id;
  • Thanks. And yes, that's what I want to do. But not the hardcoded way but something like looping throught the indermediate table and passing source and target to the last SQL above. But I do not know how to write that loop. – hoge6b01 Nov 20 '18 at 8:36
0

After doing some trail and error I came about a solution where looping is not needed. Probably there are better solutions but this SQL returns what I need to know: The kid's nearest school, the distance and the geom.

CREATE TABLE shortest_path AS (
SELECT
    kid_id
    , school_id
    , sum AS length
    , geom
FROM (
    SELECT
        row_number() OVER (PARTITION BY
                b.id
            ORDER BY
                sum(t.cost) ASC) AS row_num
            , kids.id AS kid_id
            , school.id AS school_id
            , sum(t.cost)
            , st_collect (d.geom)::geometry (Linestring
                , 3857) AS geom
        FROM (
            SELECT
                *
            FROM
                pgr_dijkstra ('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
                        , end_vid
                        , path_seq) t
                    LEFT JOIN kids_location AS kids ON (kids.node_id = start_vid)
                    LEFT JOIN school_location AS school ON (school.node_id = end_vid)
                    LEFT JOIN roads AS rd ON (t.edge = rd.id)
                GROUP BY
                    kids.id
                    , school.id
                ORDER BY
                    kids.id
                    , sum ASC) AS subquery
            WHERE
                row_num = 1
);

ALTER TABLE shortest_path
   ADD PRIMARY KEY (kid_id);

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.