[pgrouting-users] some progress with assign_vertex_id

[Stephen Woodbridge]

julien bourgeois wrote:
> 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

This error is a postgis error generated because you have violated a 
check constraint. I would recommend make a separate database vor each 
dataset you want to load into pgrouting. The code is not currently 
designed to support multiple datasets in a singel database even if they 
are in different schemas.

To faciliate this, I created a template_routing database like:

  createdb -U postgres -E UNICODE template_routing
  createlang -U postgres plpgsql template_routing
  psql -U postgres -f lwpostgis.sql template_routing
  psql -U postgres -f spatial_ref_sys.sql template_routing
  psql -U postgres -f routing_core.sql template_routing
  psql -U postgres -f routing_core_wrappers.sql template_routing
  psql -U postgres -f routing_dd.sql template_routing
  psql -U postgres -f routing_dd_wrappers.sql template_routing
  psql -U postgres -f routing_tsp.sql template_routing
  psql -U postgres -f routing_tsp_wrappers.sql template_routing

Then when I need to create a new database I do:

createdb -U postgres -E UNICODE -T template_routing my_new_db

and my_new_db has all the stuff I need for routing preloaded into it. So 
all I need to do is load my data, and run assign_vertex_id() to get started.

> 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
> }}}

I'm not sure why this error is generated, but if you run 
assign_vertex_id() and then want to rerun it on the same database, you 
will get this error. The fix is simple, just reload

psql -U postgres -f routing_core_wrappers.sql routing

> 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  ;) ?

Not an expert, but hope this helps.

-Steve

> Cheers,
> Julien
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.postlbs.org
> http://lists.postlbs.org/mailman/listinfo/pgrouting-users