<p dir="ltr">Steve, </p>
<p dir="ltr">Thanks for your time. Definitely will try that! </p>
<p dir="ltr">As I had no problem creating the vertices table and the graph check returned "OK", I assumed everything was OK. </p>
<p dir="ltr">--<br>
Helder Alves </p>
<div class="gmail_quote">On Dec 13, 2013 1:57 AM, "Stephen Woodbridge" <<a href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</a>> wrote:<br type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
On 12/12/2013 7:13 PM, Helder Alves wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi again Steve!<br>
<br>
You got it right.<br>
<br>
All the simple pgr_trsp queries you suggested returned:<br>
<br>
*seq;id1;id2;cost*<br>
0;-1;0;0<br>
<br>
The result from pgr_analyzegraph check is "OK".<br>
<br>
SELECT id1 as path,<br>
ST_Length_Spheroid(st_astext(<u></u>st_linemerge(st_union(b.the_<u></u>geom))),<br>
'SPHEROID["GRS_1980",6378137,<u></u>298.257222101]')<br>
FROM pgr_kdijkstraPath(<br>
'SELECT id, source, target, cost, reverse_cost FROM ways',<br>
98306, array[146098], true, true) a,<br>
ways b<br>
WHERE a.id3=<a href="http://b.id" target="_blank">b.id</a> <<a href="http://b.id" target="_blank">http://b.id</a>><br>
GROUP by id1<br>
ORDER by id1;<br>
<br>
<br>
Definitely I think something is not OK. The output of the query above is<br>
the following one:<br>
<br>
*path;st_length_spheroid*<br>
146098;4374.81625857547<br>
<br>
As may be guessing already, this query for the other 2 pairs of vids<br>
(228347, 228369 and 228369,98306) also returned the expected distances.<br>
<br>
What do you suggest as next step?<br>
</blockquote>
<br>
<a href="https://github.com/pgRouting/pgrouting/wiki/Working-with-OSM-Data" target="_blank">https://github.com/pgRouting/<u></u>pgrouting/wiki/Working-with-<u></u>OSM-Data</a><br>
<br>
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:<br>
<br>
1. run pgr_nodenetwork<br>
2. create a new table using a join<br>
3. and then create a topology<br>
<br>
You probably need to run the three steps above to get a usable table for pgRouting.<br>
<br>
-Steve<br>
<br>
<br>
[] ~/work/osrm-tools$ cat mk-testdb2<br>
#/bin/sh<br>
<br>
DBNAME=osrm_test<br>
DBUSER=postgres<br>
DBHOST=mappy<br>
<br>
dropdb -U $DBUSER -h $DBHOST $DBNAME<br>
createdb -U $DBUSER -h $DBHOST $DBNAME<br>
<br>
psql -U $DBUSER -h $DBHOST $DBNAME <<EOF<br>
create extension postgis;<br>
create extension pgrouting;<br>
<br>
-- create 50x50 grid of overlapping lines horizontal and vertical<br>
<br>
create table ddunnoded (<br>
id serial not null primary key,<br>
name text,<br>
dir_travel character(1),<br>
speed_cat character(1)<br>
);<br>
select addgeometrycolumn('ddunnoded', 'the_geom', 4326, 'LINESTRING', 2);<br>
<br>
insert into ddunnoded (dir_travel, speed_cat, name, the_geom)<br>
select case when s1%7 = 0 then 'F' when s1%7 = 4 then 'T' else 'B' end,<br>
(random()*4+1)::character,<br>
'H'||s1,<br>
st_setsrid(st_makeline(st_<u></u>makepoint(-77.0, 42.0+(3.0/50.0*(s1-1))),<br>
st_makepoint(-74.0, 42.0+(3.0/50.0*(s1-1)))), 4326)<br>
from (select generate_series(1,50) as s1) as foo<br>
union all<br>
select case when s1%11 = 0 then 'F' when s1%11 = 7 then 'T' else 'B' end,<br>
(random()*4+1)::character,<br>
'V'||s1,<br>
st_setsrid(st_makeline(st_<u></u>makepoint(-77.0+(3.0/50.0*(s1-<u></u>1)), 42.0),<br>
st_makepoint(-77.0+(3.0/50.0*(<u></u>s1-1)), 45.0)), 4326)<br>
from (select generate_series(1,50) as s1) as foo;<br>
<br>
-- node the grid so we can use it<br>
select pgr_nodenetwork('ddunnoded',0.<u></u>000001);<br>
<br>
<br>
-- cat m/sec kph mph<br>
-- 1 25 90 56<br>
-- 2 18 65 40<br>
-- 3 15 54 33<br>
-- 4 10 36 22<br>
-- 5 5 18 11<br>
<br>
-- copy the noded table into a table we can use for a graph<br>
-- and add the required columns<br>
create table ddnoded2 (<br>
gid serial not null primary key,<br>
id integer,<br>
name text,<br>
source integer,<br>
target integer,<br>
dir_travel character(1),<br>
speed_cat character(1),<br>
roundabout character(1) default 'N',<br>
tunnel character(1) default 'N',<br>
bridge character(1) default 'N',<br>
len_m float8,<br>
cost float8<br>
);<br>
select addgeometrycolumn('ddnoded2', 'the_geom', 4326, 'LINESTRING', 2);<br>
<br>
insert into ddnoded2 (id, name, dir_travel, speed_cat, len_m, cost, the_geom)<br>
select <a href="http://a.id" target="_blank">a.id</a>,<br>
name,<br>
dir_travel,<br>
speed_cat,<br>
st_length2d_spheroid(a.the_<u></u>geom, 'SPHEROID["GRS_1980",6378137,<u></u>298.257222101]') as len_m,<br>
st_length2d_spheroid(a.the_<u></u>geom, 'SPHEROID["GRS_1980",6378137,<u></u>298.257222101]') / case when speed_cat='1' then 25.0<br>
when speed_cat='2' then 18.0<br>
when speed_cat='3' then 15.0<br>
when speed_cat='4' then 10.0<br>
else 5.0 end as cost,<br>
a.the_geom<br>
from ddunnoded_noded a, ddunnoded b<br>
where a.old_id=<a href="http://b.id" target="_blank">b.id</a><br>
order by a.old_id, a.sub_id;<br>
<br>
-- now create a topology<br>
select pgr_createtopology('ddnoded2', 0.000001, id:='gid');<br>
<br>
create table ddnoded2_restrictions (<br>
id serial not null primary key,<br>
n_via integer,<br>
n_from integer,<br>
n_to integer,<br>
is_forbidden boolean,<br>
to_way_id integer<br>
);<br>
<br>
-- Total query runtime: 8080 ms.<br>
EOF<br>
<br>
______________________________<u></u>_________________<br>
pgrouting-dev mailing list<br>
<a href="mailto:pgrouting-dev@lists.osgeo.org" target="_blank">pgrouting-dev@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-dev" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-dev</a><br>
</blockquote></div>