4

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?

3
+500

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)
  • Just fantastic - a few tweaks to match my table names and geom column names and I'm good to go - I can use this as a big query, rather than a function like ST_Distance, but a few more tweaks and I can pretty much get exactly what I want... many thanks!!! – DPSSpatial Mar 27 '17 at 16:12
1

The error says, what is wrong: your sub-query returns more than one row:

(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 ( '750909', '818767', '768952')) limit 1)

Instead, it should return a single row with an array of id's, like:

ARRAY[84234, 38905, 107851]

You can change your query above to return all ID's as array instead of rows with the array_agg function. So instead of SELECT id ... it should work to use:

SELECT array_agg(id) ...
  • that gives an error that the_geom must be in a group by, which after I figure that out, gives me the same "more than one row returned" error... – DPSSpatial Mar 14 '17 at 18:10
  • also wondering if I'm going the right direction here - how can existing analysis using functions like ST_Distance and ST_MakeLine take advantage of pgRouting instead? Pie in the sky for me for a few years now... still getting stumped on seemingly basic things (but have no experience in custom functions...) – DPSSpatial Mar 14 '17 at 19:09
  • Why do you need to ORDER BY and LIMIT 1 when you already query explicitly for certain student ID's or school numbers? The purpose subqueries isn't really clear to me. – dkastl Mar 15 '17 at 3:22
  • the ORDER BY and LIMIT 1 are from samples by Regina where the geometry given in the subquery is used to find the closest node in the ways_vertices_pgr table using the <-> (this is the only place I've seen this operator before) and LIMIT 1 means only return the 1st NODE ID - so combined with the sample in the pgRouting Tutorial from 2016 Bonn... what I'm trying to do is mimic the ST_Distance and ST_Make line as they both take geometries from two tables (students, schools). – DPSSpatial Mar 15 '17 at 13:26
  • Daniel, I added some SQL to show how we use ST_Distance and what we'd like to do with pgRouting... hope that helps give more context... – DPSSpatial Mar 15 '17 at 15:25

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.