[pgrouting-users] Problems recalculating the network topology

pgRouting pgrouting-users
1

Dear pgRouting tribe!

I would like to run assign_vertex_id() for a simple table, like the
following:

CREATE TABLE dhl_ret_l
(
  gid integer NOT NULL,
  the_geom geometry,
  source integer,
  target integer,
  length double precision,
  "cluster" integer,
  CONSTRAINT dhl_ret_l_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'LINESTRING'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3003)
)

This is fine for the first time. Now I would like to recalculate the
network topology, so I do:

SELECT DropGeometryColumn('public', 'vertices_tmp', 'the_geom');
DROP TABLE vertices_tmp;

and try to restart the calculation:

SELECT assign_vertex_id('dhl_ret_l', 0.00, 'the_geom', 'gid');

and get the following error:

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 14 at execute statement
CONTEXT: PL/pgSQL function "assign_vertex_id" line 29 at assignment
ERROR: relation with OID 5843275 does not exist
CONTEXT: SQL statement "SELECT
Distance(the_geom,GeometryFromText( AsText( $1 ), $2 )) AS d, id,
the_geom FROM vertices_tmp WHERE the_geom &&
Expand(GeometryFromText(AsText( $1 ), $2 ), $3 ) AND
Distance(the_geom, GeometryFromText(AsText( $1 ), $2 )) < $3 ORDER BY
d LIMIT 1"
PL/pgSQL function "point_to_id" line 13 at SQL statement
PL/pgSQL function "assign_vertex_id" line 29 at assignment

What am I doing wrongly?

Regards,

Peter

--
Dott. Peter Hopfgartner

R3 GIS Srl - GmbH
Via Johann Kravogl-Str. 2
I-39012 Meran/Merano (BZ)
Email: peter.hopfgartner@r3-gis.com
Tel. : +39 0473 494949
Fax : +39 0473 069902
www : http://www.r3-gis.com

XING : http://www.xing.com/go/invita/8917535

2

Peter Hopfgartner wrote:

Dear pgRouting tribe!

I would like to run assign_vertex_id() for a simple table, like the
following:

CREATE TABLE dhl_ret_l
(
  gid integer NOT NULL,
  the_geom geometry,
  source integer,
  target integer,
  length double precision,
  "cluster" integer,
  CONSTRAINT dhl_ret_l_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'LINESTRING'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3003)
)

This is fine for the first time. Now I would like to recalculate the
network topology, so I do:

SELECT DropGeometryColumn('public', 'vertices_tmp', 'the_geom');
DROP TABLE vertices_tmp;

Try doing:

DROP TABLE vertices_tmp CASCADE;

-Steve

and try to restart the calculation:

SELECT assign_vertex_id('dhl_ret_l', 0.00, 'the_geom', 'gid');

and get the following error:

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 14 at execute statement
CONTEXT: PL/pgSQL function "assign_vertex_id" line 29 at assignment
ERROR: relation with OID 5843275 does not exist
CONTEXT: SQL statement "SELECT
Distance(the_geom,GeometryFromText( AsText( $1 ), $2 )) AS d, id,
the_geom FROM vertices_tmp WHERE the_geom &&
Expand(GeometryFromText(AsText( $1 ), $2 ), $3 ) AND
Distance(the_geom, GeometryFromText(AsText( $1 ), $2 )) < $3 ORDER BY
d LIMIT 1"
PL/pgSQL function "point_to_id" line 13 at SQL statement
PL/pgSQL function "assign_vertex_id" line 29 at assignment

What am I doing wrongly?

Regards,

Peter

3

On Thu, 2010-07-08 at 11:49 -0400, Stephen Woodbridge wrote:

Peter Hopfgartner wrote:
> Dear pgRouting tribe!
>
> I would like to run assign_vertex_id() for a simple table, like the
> following:
>
> CREATE TABLE dhl_ret_l
> (
> gid integer NOT NULL,
> the_geom geometry,
> source integer,
> target integer,
> length double precision,
> "cluster" integer,
> CONSTRAINT dhl_ret_l_pkey PRIMARY KEY (gid),
> CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
> CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
> 'LINESTRING'::text OR the_geom IS NULL),
> CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3003)
> )
>
> This is fine for the first time. Now I would like to recalculate the
> network topology, so I do:
>
> SELECT DropGeometryColumn('public', 'vertices_tmp', 'the_geom');
> DROP TABLE vertices_tmp;

Try doing:

DROP TABLE vertices_tmp CASCADE;

-Steve

(...)

This does not change anything. Could it be that this is related to the
oldish 8.2 PostgreSQL?

Thanks,

Peter

--
Dott. Peter Hopfgartner

R3 GIS Srl - GmbH
Via Johann Kravogl-Str. 2
I-39012 Meran/Merano (BZ)
Email: peter.hopfgartner@r3-gis.com
Tel. : +39 0473 494949
Fax : +39 0473 069902
www : http://www.r3-gis.com

XING : http://www.xing.com/go/invita/8917535