[pgrouting-dev] Building some tools to work with TSP

[Helder Alves]

Steve,

Thanks for your time. Definitely will try that!

As I had no problem creating the vertices table and the graph check
returned "OK", I assumed everything was OK.

--
Helder Alves
On Dec 13, 2013 1:57 AM, "Stephen Woodbridge" <woodbri at swoodbridge.com>
wrote:

> On 12/12/2013 7:13 PM, Helder Alves wrote:
>
>> Hi again Steve!
>>
>> You got it right.
>>
>> All the simple pgr_trsp queries you suggested returned:
>>
>> *seq;id1;id2;cost*
>> 0;-1;0;0
>>
>> The result from pgr_analyzegraph check is "OK".
>>
>>     SELECT id1 as path,
>>     ST_Length_Spheroid(st_astext(st_linemerge(st_union(b.the_geom))),
>>     'SPHEROID["GRS_1980",6378137,298.257222101]')
>>        FROM pgr_kdijkstraPath(
>>            'SELECT id, source, target, cost, reverse_cost FROM ways',
>>            98306, array[146098], true, true) a,
>>                  ways b
>>     WHERE a.id3=b.id <http://b.id>
>>     GROUP by id1
>>     ORDER by id1;
>>
>>
>> Definitely I think something is not OK. The output of the query above is
>> the following one:
>>
>> *path;st_length_spheroid*
>> 146098;4374.81625857547
>>
>> As may be guessing already, this query for the other 2 pairs of vids
>> (228347, 228369 and 228369,98306) also returned the expected distances.
>>
>> What do you suggest as next step?
>>
>
> https://github.com/pgRouting/pgrouting/wiki/Working-with-OSM-Data
>
> This is a script that I use to create a big rectangular grid of
> overlapping horizontal and vertical lines that I call pgr_nodenetwork on,
> the join it back to the original table to make a useful table for
> pgRouting. Mostly I offer it so you can see how to:
>
> 1. run pgr_nodenetwork
> 2. create a new table using a join
> 3. and then create a topology
>
> You probably need to run the three steps above to get a usable table for
> pgRouting.
>
> -Steve
>
>
> [] ~/work/osrm-tools$ cat mk-testdb2
> #/bin/sh
>
> DBNAME=osrm_test
> DBUSER=postgres
> DBHOST=mappy
>
> dropdb -U $DBUSER -h $DBHOST $DBNAME
> createdb -U $DBUSER -h $DBHOST $DBNAME
>
> psql -U $DBUSER -h $DBHOST $DBNAME <<EOF
> create extension postgis;
> create extension pgrouting;
>
> -- create 50x50 grid of overlapping lines horizontal and vertical
>
> create table ddunnoded (
>   id serial not null primary key,
>   name text,
>   dir_travel character(1),
>   speed_cat character(1)
> );
> select addgeometrycolumn('ddunnoded', 'the_geom', 4326, 'LINESTRING', 2);
>
> insert into ddunnoded (dir_travel, speed_cat, name, the_geom)
> select case when s1%7 = 0 then 'F' when s1%7 = 4 then 'T' else 'B' end,
>        (random()*4+1)::character,
>        'H'||s1,
>        st_setsrid(st_makeline(st_makepoint(-77.0, 42.0+(3.0/50.0*(s1-1))),
>                               st_makepoint(-74.0,
> 42.0+(3.0/50.0*(s1-1)))), 4326)
>   from (select generate_series(1,50) as s1) as foo
> union all
> select case when s1%11 = 0 then 'F' when s1%11 = 7 then 'T' else 'B' end,
>        (random()*4+1)::character,
>        'V'||s1,
>        st_setsrid(st_makeline(st_makepoint(-77.0+(3.0/50.0*(s1-1)), 42.0),
>                               st_makepoint(-77.0+(3.0/50.0*(s1-1)),
> 45.0)), 4326)
>   from (select generate_series(1,50) as s1) as foo;
>
> -- node the grid so we can use it
> select pgr_nodenetwork('ddunnoded',0.000001);
>
>
> -- cat m/sec   kph     mph
> -- 1   25      90      56
> -- 2   18      65      40
> -- 3   15      54      33
> -- 4   10      36      22
> -- 5    5      18      11
>
> -- copy the noded table into a table we can use for a graph
> -- and add the required columns
> create table ddnoded2 (
>   gid serial not null primary key,
>   id integer,
>   name text,
>   source integer,
>   target integer,
>   dir_travel character(1),
>   speed_cat character(1),
>   roundabout character(1) default 'N',
>   tunnel character(1) default 'N',
>   bridge character(1) default 'N',
>   len_m float8,
>   cost float8
> );
> select addgeometrycolumn('ddnoded2', 'the_geom', 4326, 'LINESTRING', 2);
>
> insert into ddnoded2 (id, name, dir_travel, speed_cat, len_m, cost,
> the_geom)
> select a.id,
>        name,
>        dir_travel,
>        speed_cat,
>        st_length2d_spheroid(a.the_geom, 'SPHEROID["GRS_1980",6378137,298.257222101]')
> as len_m,
>        st_length2d_spheroid(a.the_geom, 'SPHEROID["GRS_1980",6378137,298.257222101]')
> / case when speed_cat='1' then  25.0
>            when speed_cat='2' then  18.0
>            when speed_cat='3' then  15.0
>            when speed_cat='4' then  10.0
>            else 5.0 end as cost,
>        a.the_geom
>   from ddunnoded_noded a, ddunnoded b
>  where a.old_id=b.id
>  order by a.old_id, a.sub_id;
>
> -- now create a topology
> select pgr_createtopology('ddnoded2', 0.000001, id:='gid');
>
> create table ddnoded2_restrictions (
>     id serial not null primary key,
>     n_via integer,
>     n_from integer,
>     n_to integer,
>     is_forbidden boolean,
>     to_way_id integer
> );
>
> -- Total query runtime: 8080 ms.
> EOF
>
> _______________________________________________
> pgrouting-dev mailing list
> pgrouting-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-dev/attachments/20131213/eeddf1c2/attachment-0001.html>