osm2pgrouting - Using pgRouting for 'multiple pedestrians' on an existing table of 'students'?

I'm trying to use the pgRouting sample for 'multiple pedestrians going to the same destination' to determine the 1) length and 2)geometry of students to their schools.

Essentially I'm trying to write functions that use pgRouting the way ST_Distance and ST_MakeLine accept geometry from students and schools which we use frequently.

Here is the logic we use to get students 'straight line' distance to a particular school (works for multiple schools too):

select

stu.studentid
, sch.schnum
, ST_Distance(stu.geom, sch.geom)


from dpsdata."Geo_Active_Student" as stu

, dpsdata."Schools_Current" as sch

where stu.studentid in ('750909', '818767', '768952')

and sch.schnum = '215'

The way ST_Distance accepts two geometries, and the where clause specifies which geometries to pass in (schnum, studentid's) is the same way I'm trying to get the pgRouting tools to work in a function.

I can get the 'single pedestrian' samples to work pointing to my existing student and school tables:

--one student to one school
SELECT * FROM pgr_dijkstra('
    SELECT gid AS id,
         source,
         target,
         length AS cost
        FROM osm.osm_ways',

     ---student

    (SELECT id FROM osm.osm_ways_vertices_pgr ORDER BY the_geom <->
     (select geom from dpsdata."Geo_Active_Student" as stu where stu.studentid = '750909') limit 1 )::integer,  
---school
 (SELECT id FROM osm.osm_ways_vertices_pgr ORDER BY the_geom <-> 
 (select geom from dpsdata."Schools_Current" as sch where sch.schnum = '215') limit 1 ) 

   , directed := false
     );

However, I can't think of the logic that would generate the same in the 'multiple pedestrians' sample using the same method of passing in multiple geometry instances for each student:

(note the use of the in statement to select which students I want to run 'multiple pedestrian' against)

    --many students to one school
    SELECT * FROM pgr_dijkstra('
        SELECT gid AS id,
             source,
             target,
             length_m AS cost
            FROM osm.osm_ways',

--instead of passing in an array of ID's, get them from the source tables

        --ARRAY[84234, 38905, 107851], 149952

--student

        (SELECT id FROM osm.osm_ways_vertices_pgr ORDER BY the_geom <->
         (select geom from dpsdata."Geo_Active_Student" as stu where stu.studentid in ( '750939', '814767', '768922')) limit 1)

    --school
         , (SELECT id FROM osm.osm_ways_vertices_pgr ORDER BY the_geom <-> 
     (select geom from dpsdata."Schools_Current" as sch where sch.schnum = '215') limit 1 ) 



        , directed := false
        );

The error returned is: ERROR: more than one row returned by a subquery used as an expression

Is there either an error in logic or SQL that I have missed?

Answer

It seems that the problem is with the the multi-student sub-query below you were using to replace the single student query:

--student

(SELECT id FROM osm.osm_ways_vertices_pgr ORDER BY the_geom <->
 (select geom from dpsdata."Geo_Active_Student" as stu where stu.studentid in ( '750939', '814767', '768922')) limit 1)

The single-student sub-query below you were trying to "vectorize" seems to be finding the closest network vertex to a given student.

 ---student
(SELECT id FROM osm.osm_ways_vertices_pgr ORDER BY the_geom <->
 (select geom from dpsdata."Geo_Active_Student" as stu where stu.studentid = '750909') limit 1 ),  

The replacement above is incorrect in itself as you would be ORDER BY distance from osm.osm_ways_vertices_pgr to multiple students. The dimension on both sides of the <-> operator don't match in general, which will give you the ERROR: more than one row returned by a subquery ....

There may be more succinct ways, but one way of solving the problem is to encapsulate that statement in a function.

CREATE OR REPLACE FUNCTION closest_vertex_stu(stu_id bigint)
RETURNS bigint AS $$
  SELECT id FROM ways_vertices_pgr ORDER BY the_geom <->
    (select the_geom from ways_vertices_pgr as stu where stu.id = stu_id) limit 1;
$$ LANGUAGE SQL;

And then call it to get the array needed by pgr_dijkstra() e.g.:

SELECT array_agg(closest_vertex_stu(stu.id)) 
 FROM ways_vertices_pgr as stu WHERE stu.id in ( '6549', '1458', '9224');

(which returns

  array_agg     
------------------
 {1458,6549,9224}

(Note: as the other answer pointed out, if you omit the array_agg, Postgres will give you another ERROR: more than one row returned by a subquery)

And finally plug it in:

--many students to one school --modified version
SELECT * FROM pgr_dijkstra('
        SELECT gid AS id,

             source,
             target,
             length_m AS cost
            FROM ways',
--instead of passing in an array of ID's, get them from the source tables
        --ARRAY[84234, 38905, 107851], 149952
--student
        (SELECT array_agg(closest_vertex_stu(stu.id))
      FROM ways_vertices_pgr as stu WHERE stu.id in ( '6549', '1458', '9224') )
--school

         ,(SELECT id FROM ways_vertices_pgr ORDER BY the_geom <-> 
     (select the_geom from ways_vertices_pgr as sch where sch.id = '13224') limit 1 ) 
        , directed := false
        );

Note that I have used the ways_vertices_pgr as stu to mimic your actual student table (and similarly sch table); and changed osm.osm_ways_vertices_pgr to ways_vertices_pgr etc. as I am using a more recent version (pgrouting 2.3 and Postgres 9.6). The id fields for students were also assumed to be id of type bigint (instead of integer). There may be other minor changes due to version differences.

Some outputs using the fake data noted above (which is the same as the solution to the exercise you linked):

seq | path_seq | start_vid | node  | edge  |       cost       |     agg_cost     
-----+----------+-----------+-------+-------+------------------+------------------
   1 |        1 |      1458 |  1458 |  9579 | 122.730552004008 |                0

   2 |        2 |      1458 | 15780 |  9580 | 6.81560014546899 | 122.730552004008
   3 |        3 |      1458 |   341 |  9581 | 4.34905086403575 | 129.546152149477
   4 |        4 |      1458 |  6588 |   402 | 17.8063617725158 | 133.895203013513
   5 |        5 |      1458 |  8421 | 19113 | 14.0364441671311 | 151.701564786028
...
  33 |       33 |      1458 |   505 | 19236 | 44.5745770978563 | 1511.97228432811
  34 |       34 |      1458 | 13224 |    -1 |                0 | 1556.54686142597
  35 |        1 |      6549 |  6549 |  7019 | 87.8579034775688 |                0
  36 |        2 |      6549 |  4004 |  7018 | 10.3932702792749 | 87.8579034775688
...

115 |       70 |      9224 | 13224 |    -1 |                0 | 2491.75714906807
(115 rows)