assign_vertex_id

[shafeer]

while executing : SELECT assign_vertex_id('road_nav',0.001,'geom','gid'); this query gets the 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

ERROR: cannot EXECUTE a null querystring CONTEXT: PL/pgSQL function "assign_vertex_id" line 20 at EXECUTE statement

********** Error **********

ERROR: cannot EXECUTE a null querystring SQL state: 22004 Context: PL/pgSQL function "assign_vertex_id" line 20 at EXECUTE statement

I have previously executed the same query for my another table where I successfully executed the query. But in my new table I am unable to execute this query. In my geometry_columns I have the table with specific srid like this [ 26551 "" "public" "Road_Nav" "the_geom" 2 -1 "MULTILINESTRING" ] Can anyone help me in this error. Thanks in advance for your replies.

[daniel]

Could you post some information about your "road_nav" table?

If you use command line, run

db=# \d road_nav

Also information about PostgreSQL version, OS, etc. would be helpful.

[shafeer]

Thanks for your quick replyy Daniel.

CREATE TABLE "Road_Nav"
(
  gid integer NOT NULL,
  "RSID" double precision,
  "A_NAME" character varying(200),
  "E_NAME" character varying(150),
  "SUFFIX" character varying(2),
  "RD_TYPE" character varying(10),
  "ONEWAY" character varying(2),
  "RD_CLASS" character varying(20),
  "RD_CLASS_N" integer,
  "MOT_RT_NO" double precision,
  "ALT_E_NAME" character varying(100),
  "ALT_A_NAME" character varying(100),
  "STATE_ABBR" character varying(15),
  "CITYR" character varying(32),
  "CITYL" character varying(32),
  "LENGTH" double precision,
  "METERS" double precision,
  "SPEED" integer,
  "SECONDS" integer,
  "FT_SECONDS" double precision,
  "TF_SECONDS" double precision,
  "L_F_ADD" double precision,
  "L_T_ADD" double precision,
  "R_F_ADD" double precision,
  "R_T_ADD" double precision,
  "ZIPR" character varying(10),
  "ZIPL" character varying(10),
  "FNODE" integer,
  "TNODE" integer,
  "T_ZLEV" integer,
  "F_ZLEV" integer,
  "A_CITY" character varying(20),
  "E_CITY" character varying(20),
  "ST_NUMBER" double precision,
  "A_MOT_NAME" character varying(100),
  "E_MOT_NAME" character varying(100),
  "LC_RT_NO" double precision,
  "AVG_SPEED" double precision,
  "OFF_SPEED" double precision,
  "A_CITYR" character varying(50),
  "E_CITYR" character varying(50),
  "A_CITYL" character varying(50),
  "E_CITYL" character varying(50),
  "COUNTRY_A" character varying(8),
  "M_ROUTE_NO" character varying(16),
  "SHAPE_Leng" double precision,
  the_geom geometry,
  CONSTRAINT "Road_Nav_pkey" PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = (-1))
)
WITH (OIDS=FALSE);
ALTER TABLE "Road_Nav" OWNER TO postgres;

My postgres version is 8.3 and im using Ubuntu OS...

The strange fact is that I could execute the same quey in my another table named 'ksa_1'

which has the structure..

CREATE TABLE ksa_1
(
  gid integer NOT NULL,
  source integer,
  target integer,
  length double precision,
  "RSID" double precision,
  "A_NAME" character varying(200),
  "E_NAME" character varying(150),
  "SUFFIX" character varying(2),
  "RD_TYPE" character varying(10),
  "ONEWAY" character varying(2),
  "RD_CLASS" character varying(20),
  "RD_CLASS_N" integer,
  "MOT_RT_NO" double precision,
  "ALT_E_NAME" character varying(100),
  "ALT_A_NAME" character varying(100),
  "STATE_ABBR" character varying(15),
  "CITYR" character varying(32),
  "CITYL" character varying(32),
  "LENGTH_1" double precision,
  "METERS" double precision,
  "SPEED" integer,
  "SECONDS" integer,
  "FT_SECONDS" double precision,
  "TF_SECONDS" double precision,
  "L_F_ADD" double precision,
  "L_T_ADD" double precision,
  "R_F_ADD" double precision,
  "R_T_ADD" double precision,
  "ZIPR" character varying(10),
  "ZIPL" character varying(10),
  "FNODE" integer,
  "TNODE" integer,
  "T_ZLEV" integer,
  "F_ZLEV" integer,
  "A_CITY" character varying(20),
  "E_CITY" character varying(20),
  "ST_NUMBER" double precision,
  "A_MOT_NAME" character varying(100),
  "E_MOT_NAME" character varying(100),
  "LC_RT_NO" double precision,
  "AVG_SPEED" double precision,
  "OFF_SPEED" double precision,
  "A_CITYR" character varying(50),
  "E_CITYR" character varying(50),
  "A_CITYL" character varying(50),
  "E_CITYL" character varying(50),
  "COUNTRY_A" character varying(8),
  "M_ROUTE_NO" character varying(16),
  the_geom geometry,
  reverse_cost double precision,
  CONSTRAINT ksa_1_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (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 (srid(the_geom) = (-1))
)
WITH (OIDS=FALSE);
ALTER TABLE ksa_1 OWNER TO postgres;

The only difference I found in these two table is that the geom column is MultiLinestring? in my first (road_nav) and Linestring in my second table (ksa_1) .. also there is an extra column named reverse_cost double precision, in my second table..

[daniel]

Just one note for readability: if you write

{{{
   your code
}}}

it makes reading much easier. Formatting is a bit annoying with TRAC.

Regarding your error:

I think you're missing some required attributes. You need to add the source and the target column first, see http://pgrouting.postlbs.org/wiki/WorkshopFOSS4G2008/ch06#Createnetworktopology

[shafeer]

Thanks for your reply.

The actual issue was not in adding the source and target columns. I have already done that. The issue seems to be in the type of "the-geom" column. Previously it was MultiLineString? but nw I changed the topology using arcGis and generated the shapefile for the corresponding shape file and created a new table with type as LineString?. Now the functions works fine. But what is the real issue in MultiLinestring? and LineString?.

[daniel]

Could it be that "the_geom" is Linestring in your dataset, but in your geometry_columns table it is still Multilinestring.

You could recreate your geometry column like in this example: http://pgrouting.postlbs.org/wiki/Workshop-LoadRoutingData#Addthegeometrycolumn:

[shafeer]

Ok thats fine ..

Daniel can u pls help me in generating text generation in routing. For example when the route is shown I need a direction description to be generated saying whether to turn left or right .. take u-turn etc.

Can u pls reply me quickly regarding this as our project is in bottle neck.

[daniel]

I think now it's time to leave here and move to the forum or mailing list. I recommend you to read the mailing list archive since this question has been discussed there already.

This ticket is definitely closed, I think.

[shafeer]

ok. thanks for ur assist

[shafeer]

While drawing line using pgrouting query the line starts from a few points ahead of the start location and ends at beyound the end location. I knew that its because the linestring consider the end points. How to get the exact start and end linestring.

[daniel]

Sorry, can you please stop asking questions here and use the forum or mailing list for this.

This is the bug and task tracker and the chance is very low that other people than me will answer you here.

So take this as an advice and use better the mailing list ( http://lists.postlbs.org/mailman/listinfo/pgrouting-users), if you want to get people help you.