0

I'm trying to solve the travelling salesman problem with locations pulled from a database, e.g.:

mydb=# SELECT id::integer, location_lng::float AS x, location_lat::float AS y FROM addresses where region_id = 76 ORDER BY id;
  id   |      x      |     y     
--------+-------------+-----------
   169 | -114.130995 | 51.039266
  1946 | -114.122258 | 51.026517
  2693 | -114.135513 | 51.031368
  3080 |  -114.12952 | 51.037816
  3601 | -114.138326 | 51.037731
  3819 | -114.135513 | 51.031326
  3831 | -114.128151 | 51.033531
  4001 | -114.139744 | 51.031526
  5918 | -114.134081 | 51.031903
 64222 | -114.122329 | 51.030329
 74248 |  -114.13445 | 51.025564
 99867 | -114.145568 | 51.033872
104685 | -114.134065 | 51.036855
131470 | -114.138351 | 51.036562
131504 | -114.125255 | 51.031422
231023 |  -114.12083 | 51.038339
398043 | -114.125218 | 51.031422
414612 | -114.122333 | 51.033205
535239 | -114.145643 | 51.034149
(19 rows)

And then I use the function as documented:

mydb=# SELECT seq, id1, id2, round(cost::numeric, 2) AS cost
mydb-# FROM pgr_tsp
mydb-# ('SELECT id::integer, location_lng::float AS x, location_lat::float AS y FROM addresses where region_id = 76 ORDER BY id', 1);
seq | id1 | id2 | cost 
-----+-----+-----+------
(0 rows)

Any ideas why I'm not getting any results?

  • Can you share the data so someone can give it a test run? – underdark Jul 21 '14 at 17:25
  • The data is the result from the SQL query (which is provided separately above). The docs indicate that the query result as depicted above is what pgr_tsp() expects. Or am I missing something? – René Fournier Jul 22 '14 at 17:25
1

You specify that the route should start on node 1 but there is no node 1 in the query result set. That can't work ...

According to the documentation http://docs.pgrouting.org/dev/src/tsp/doc/index.html, you should specify the start point id.

This works:

create table tsp_test (id integer, x double precision, y double precision); insert into tsp_test (id,x,y) values ( 169 , -114.130995 , 51.039266); insert into tsp_test (id,x,y) values ( 1946 , -114.122258 , 51.026517); insert into tsp_test (id,x,y) values ( 2693 , -114.135513 , 51.031368); insert into tsp_test (id,x,y) values ( 3080 , -114.12952 , 51.037816); insert into tsp_test (id,x,y) values ( 3601 , -114.138326 , 51.037731); insert into tsp_test (id,x,y) values ( 3819 , -114.135513 , 51.031326); insert into tsp_test (id,x,y) values ( 3831 , -114.128151 , 51.033531); insert into tsp_test (id,x,y) values ( 4001 , -114.139744 , 51.031526); insert into tsp_test (id,x,y) values ( 5918 , -114.134081 , 51.031903); insert into tsp_test (id,x,y) values ( 64222 , -114.122329 , 51.030329); insert into tsp_test (id,x,y) values ( 74248 , -114.13445 , 51.025564); insert into tsp_test (id,x,y) values ( 99867 , -114.145568 , 51.033872); insert into tsp_test (id,x,y) values (104685 , -114.134065 , 51.036855); insert into tsp_test (id,x,y) values (131470 , -114.138351 , 51.036562); insert into tsp_test (id,x,y) values (131504 , -114.125255 , 51.031422); insert into tsp_test (id,x,y) values (231023 , -114.12083 , 51.038339); insert into tsp_test (id,x,y) values (398043 , -114.125218 , 51.031422); insert into tsp_test (id,x,y) values (414612 , -114.122333 , 51.033205); insert into tsp_test (id,x,y) values (535239 , -114.145643 , 51.034149);

SELECT seq, id1, id2, round(cost::numeric, 2) AS cost FROM pgr_tsp('SELECT id, x, y FROM tsp_test order by id', 169);

returns

seq;id1;id2;cost 0;0;169;0.00 1;12;104685;0.00 2;8;5918;0.00 3;2;2693;0.00 4;7;4001;0.01 5;11;99867;0.00 6;18;535239;0.01 7;5;3819;0.01 8;10;74248;0.01 9;13;131470;0.00 10;4;3601;0.01 11;3;3080;0.00 12;6;3831;0.00 13;14;131504;0.00 14;16;398043;0.00 15;9;64222;0.00 16;1;1946;0.01 17;17;414612;0.01 18;15;231023;0.01

  • I understood that the node refers to the index number in the result set from the SQL query, not the actual ID. Are you saying it should be the ID? Because I tried that too: SELECT seq, id1, id2, round(cost::numeric, 2) AS cost FROM pgr_tsp ('SELECT id::integer, location_lng::float AS x, location_lat::float AS y, location AS geom FROM addresses where region_id = 100 ORDER BY id', 169); ...And it didn't work. – René Fournier Jul 18 '14 at 20:55
  • Anyone? The above answer didn't help me. Can anybody add something here? – René Fournier Jul 20 '14 at 23:53
0

I have been stuck on the same problem and found that you need to have your start id (in your case 1) in the list that you are trying to run the pgr_tsp on. If your table looked like below then your sql should work.

Example db

  id   |      x      |     y     
--------+-------------+-----------
     1 |    -114.****| 51.****
   169 | -114.130995 | 51.039266
  1946 | -114.122258 | 51.026517
  2693 | -114.135513 | 51.031368
  3080 |  -114.12952 | 51.037816
  3601 | -114.138326 | 51.037731
  3819 | -114.135513 | 51.031326
  3831 | -114.128151 | 51.033531
  4001 | -114.139744 | 51.031526
  5918 | -114.134081 | 51.031903
 64222 | -114.122329 | 51.030329
 74248 |  -114.13445 | 51.025564
 99867 | -114.145568 | 51.033872
104685 | -114.134065 | 51.036855
131470 | -114.138351 | 51.036562
131504 | -114.125255 | 51.031422
231023 |  -114.12083 | 51.038339
398043 | -114.125218 | 51.031422
414612 | -114.122333 | 51.033205
535239 | -114.145643 | 51.034149
(19 rows)

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.