[pgrouting-users] some progress with assign_vertex_id

[julien bourgeois]

Hi All,

I'm made some progress with assign_vertex_id, but still cannot get it working
with our dataset.

 * Version of pgRouting:
http://www.davidgis.fr/download/pgRouting-1.02_pg-8.2.9.zip

 * I set routing database and import functions as follows (no errors):
{{{
createdb -U postgres -E UNICODE routing
createlang -U postgres plpgsql routing
psql -U postgres -f lwpostgis.sql routing
psql -U postgres -f spatial_ref_sys.sql routing
psql -U postgres -f routing_core.sql routing
psql -U postgres -f routing_core_wrappers.sql routing
psql -U postgres -f routing_dd.sql routing
psql -U postgres -f routing_dd_wrappers.sql routing
psql -U postgres -f routing_tsp.sql routing
psql -U postgres -f routing_tsp_wrappers.sql routing
}}}

 * Then the workflow is the following:
{{{
shp2pgsql -s 32632 -c -I -S file.shp victoria > victoria.sql
/*
 * maybe have to "drop table victoria;"
 */
psql -U postgres routing -f victoria.sql
ALTER TABLE victoria ADD COLUMN source integer;
ALTER TABLE victoria ADD COLUMN target integer;
ALTER TABLE victoria ADD COLUMN length double precision;
SELECT assign_vertex_id('victoria', 0.001, 'the_geom', 'gid');
}}}

 * With the sample dataset found in
http://www.davidgis.fr/download/troncon_route.zip it seems to work:
{{{
routing=# SELECT assign_vertex_id('victoria', 0.001, 'the_geom', 'gid');
NOTICE:  CREATE TABLE will create implicit sequence
"vertices_tmp_id_seq" for serial column vertices_tmp.id"
CONTEXT:  SQL statement "CREATE TABLE vertices_tmp (id serial)"
PL/pgSQL function "assign_vertex_id" line 21 at execute statement
NOTICE:  10 out of 61 edges processed
NOTICE:  20 out of 61 edges processed
NOTICE:  30 out of 61 edges processed
NOTICE:  40 out of 61 edges processed
NOTICE:  50 out of 61 edges processed
NOTICE:  60 out of 61 edges processed
 assign_vertex_id
------------------
 OK
(1 row)
}}}

 * With my own dataset I get 2 different error messages with 2
consecutive calls:
{{{
routing=# SELECT assign_vertex_id('victoria', 0.001, 'the_geom', 'gid');
NOTICE:  CREATE TABLE will create implicit sequence
"vertices_tmp_id_seq" for serial column "vertices_tmp.id"
CONTEXT:  SQL statement "CREATE TABLE vertices_tmp (id serial)"
PL/pgSQL function "assign_vertex_id" line 21 at execute statement
ERROR:  new row for relation "vertices_tmp" violates check constraint
"enforce_geotype_the_geom"
CONTEXT:  SQL statement "INSERT INTO vertices_tmp (the_geom) VALUES ( $1 )"
PL/pgSQL function "point_to_id" line 13 at SQL statement
PL/pgSQL function "assign_vertex_id" line 61 at assignment

routing=# SELECT assign_vertex_id('victoria', 0.001, 'the_geom', 'gid');
NOTICE:  CREATE TABLE will create implicit sequence
"vertices_tmp_id_seq" for serial column "vertices_tmp.id"
CONTEXT:  SQL statement "CREATE TABLE vertices_tmp (id serial)"
PL/pgSQL function "assign_vertex_id" line 21 at execute statement
ERROR:  relation with OID 52123 does not exist
CONTEXT:  SQL statement "SELECT id, the_geom FROM vertices_tmp WHERE
distance(the_geom,  $1 ) <  $2 "
PL/pgSQL function "point_to_id" line 7 at SQL statement
PL/pgSQL function "assign_vertex_id" line 61 at assignment
}}}

Since it works with the troncon_route dataset, i am enclined to think
that the problem
lies in my dataset.

 * What kind of error could it be (if inferrable from the error messages) ?
 * Any idea from the experts  ;) ?

Cheers,
Julien