Context Navigation

dijkstra (#16) - Error while executing assign_vertex_id (#184) - Message List

Error while executing assign_vertex_id

I have successfully imported a map data into a postgresql database. posgis /pgrouting funtions are all in place. but when i tried to run assign_vertex_id, i always get this error. sample querry :

SELECT assign_vertex_id('Brunei_Mainroads_polyline', 0.001, 'the_geom', 'gid');

error message:

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: cannot EXECUTE a null querystring CONTEXT: PL/pgSQL function "assign_vertex_id" line 33 at EXECUTE statement

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

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

please can anyone help me out?

Thank you

ilotus

Tree View Flat View (newer first) Flat View (older first)

Message #639

Hi,

It is difficult to tell what's the reason without knowing your table structure and data it contains. Can you please confirm few things?

1)Does table contain source and target fields?

2)Is everything OK with geometry column? Do you have an entry for it in geometry_columns table with proper srid?

3)Do you really have gid column with non-null values?

anton

11/13/08 08:50:45 (2 years ago)
- Message #640
  
  Hello Anton Daniel, Thank you for coming to my rescue. How I wish I could print screen my database structure for you to take a look.
  
  (1). My table contains both source and target field as required. (2). I have an entry of the geometry column at the geometry_coulumn with an SRID = 4326 (3). The gid column does not have a null value. In fact (gid = id) where both are integer. The id column was created while the map data is being imported into the Database.
  
  Maybe you might want to take a look at the SQL pane
  
  -- Table: "Brunei_Mainroads" -- DROP TABLE "Brunei_Mainroads"; CREATE TABLE "Brunei_Mainroads" (
  
  "name" character(60), id integer, geom geometry, source integer, target integer, gid integer, the_geom geometry, length double precision, CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2), CONSTRAINT enforce_srid_geom CHECK (srid(geom) = 4326)
  
  ) WITH (OIDS=TRUE); ALTER TABLE "Brunei_Mainroads" OWNER TO postgres; -- Index: "Brunei_Mainr_geom_1226372261878" -- DROP INDEX "Brunei_Mainr_geom_1226372261878"; CREATE INDEX "Brunei_Mainr_geom_1226372261878"
  
  ON "Brunei_Mainroads" USING gist (geom);
  
  -- Index: geom_idx -- DROP INDEX geom_idx; CREATE INDEX geom_idx
  
  ON "Brunei_Mainroads" USING gist (geom);
  
  -- Index: source_idx -- DROP INDEX source_idx; CREATE INDEX source_idx
  
  ON "Brunei_Mainroads" USING btree (source);
  
  -- Index: target_idx -- DROP INDEX target_idx; CREATE INDEX target_idx
  
  ON "Brunei_Mainroads" USING btree (target);
  
  Thank you and hope this information can give you an insight on my database structure. I look forward to your kind response.
  
  ilotus
  
  11/13/08 11:55:13 (2 years ago)
  - Message #641
    
    Hmmm, it looks OK except of the fact that you call assign_vertex_id for 'the_geom' column, but there are 2 geometry columns in you table and one of them - geom - has 2 indexes. Generally it's OK, but I suspect that you have some mess with geometry columns and probably you have only one entry at geometry_columns table.
    
    Can you check it once again and if nothing helps please post a small piece of your data.
    
    anton
    
    11/13/08 13:41:23 (2 years ago)
    - Message #648
      
      Hello anton Daniel, Thanks for your advice! I was able to solve the problem with the function assign_vertex_id. I assign my SRID to a value. E.g srid := 4326; then I was able to execute the function. I’m still wondering why srid := i.srid; could not work as required. Anyway, can you explain the logic behind this? Thank you.
      
      ilotus
      
      11/17/08 10:35:03 (2 years ago)
      - Message #649
        
        You mean want to know the logic behind assign_vertex_id() function?
        
        anton
        
        11/17/08 10:39:32 (2 years ago)
        
        Message #651
        
        yes! i'm not too familiar with pgplql nor c. so i would love to know why srid :i.srid did not work. i believe that the i.srid here is refering to the SRID in my geometry_column. Any explaination would be highly appreciated. Thank you.
        
        ilotus
        
        11/17/08 10:45:23 (2 years ago)
        
        Message #652
        
        Ah, right! Now I understand - the function reads srid column value from geometry_column table assuming that there is only one entry for each table. In your case when you have 2 entries it reads only one and not necessary the right one.
        
        So, please give my few minutes to write a patch.
        
        anton
        
        11/17/08 10:52:52 (2 years ago)
        
        Message #653
        
        OK, so please download the new version of routing_topology.sql from svn, or apply this patch:
        
        --- routing_topology.sql 2008-11-17 11:00:43.000000000 +0900 +++ routing_topology_old.sql 2008-11-17 11:00:47.000000000 +0900 @@ -71,9 +71,7 @@ EXECUTE 'CREATE TABLE vertices_tmp (id serial)'; - FOR _r IN EXECUTE 'SELECT srid FROM geometry_columns WHERE f_table_name='''|| quote_ident(geom_table)||''';' LOOP - srid := _r.srid; - END LOOP; + srid := Find_SRID('public',quote_ident(geom_table),quote_ident(geo_cname)); EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'', '||srid||', ''POINT'', 2)'; CREATE INDEX vertices_tmp_idx ON vertices_tmp USING GIST (the_geom);
        
        anton
        
        11/17/08 11:05:54 (2 years ago)
        
        Message #1696
        
        Hallo Anton,
        
        can I ask you for help with probably same problem with assign_vertex_id. I followed your discussion but now I am not sure what to do to improve it.
        Here is my table and short sample of content :
        my error:
        
        roads_db=# select assign_vertex_id('roads2', 0.0001, 'the_geom', 'gid'); COMMENT: CREATE TABLE create implicit sequency "vertices_tmp_id_seq" for 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 35 at EXECUTE statement
        Table "public.roads2" columns | Typ e | Modificators -----------+-----------------------+------------------------------------------------------ gid | integer | not null default nextval('roads2_gid_seq'::regclass) fcc | smallint | road_id | bigint | on | character varying(50) | df | smallint | fw | smallint | sl | smallint | fy | smallint | ds | smallint | fc | smallint | rn | character varying(10) | rne | character varying(16) | toll_road | smallint | toll | smallint | urban | smallint | oc_admin8 | character varying(6) | oc_admin9 | character varying(6) | level_b | smallint | level_m | smallint | level_e | smallint | bt | smallint | nc | smallint | oneway | character varying(2) | meter | double precision | id | integer | the_geom | geometry | source | integer | target | integer | Indexes: "roads2_pkey" PRIMARY KEY, btree (gid) Constraints: "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2) "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL) "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
        
        gid | fcc | road_id | on | df | fw | sl | fy | ds | fc | rn | rne | toll_road | toll | urban | oc_admin8 | oc_admin9 | level_b | level_m | level_e | bt | nc | oneway | meter | id | the_geom | source | target -------+------+----------+-------------------------+----+----+----+----+----+----+--------+--------------+-----------+------+-------+-----------+-----------+ ---------+---------+---------+----+----+--------+--------+------+-------------------------------------------------------------------------------------------- ------------------+--------+-------- 1 | 4110 | 10563657 | | 1 | 3 | 0 | 0 | 1 | 3 | 572 | | 0 | 0 | 0 | 501913 | | 0 | 0 | 0 | 0 | 2 | | 226.9 | 0 | 0105000020E6100000010000000102000000020000008C784BD9BF4D314002DB8D93530F48404087CDACC34D314 08F0EF403520F4840 | | 2 | 4110 | 10563657 | | 1 | 3 | 0 | 0 | 1 | 3 | 572 | | 0 | 0 | 0 | 501913 | | 0 | 0 | 0 | 0 | 2 | | 226.9 | 0 | 0105000020E610000001000000010200000002000000F8ADD143BA4D31408FE4B8DA550F48408C784BD9BF4D314 002DB8D93530F4840 | | 3 | 4110 | 10563657 | | 1 | 3 | 0 | 0 | 1 | 3 | 572 | | 0 | 0 | 0 | 501913 | | 0 | 0 | 0 | 0 | 2 | | 226.9 | 0 | 0105000020E61000000100000001020000000200000010E356AEB44D3140CFCAE321580F4840F8ADD143BA4D314 08FE4B8DA550F4840 | |
        
        Dzouzeph
        
        10/14/10 18:42:55 (5 weeks ago)
        
        Message #1699
        
        Dobrý večer, Dzouzeph.
        
        Odkud jste? Česko?
        
        Well, it is difficult to tell what's wrong, but please check the following:
        
        all the_geom are not null
        what's the result of following query SELECT srid FROM geometry_columns WHERE f_table_name=roads2 ?
        
        anton
        
        10/14/10 21:39:05 (5 weeks ago)
        
        Message #1703
        
        Zdravim
        je fajn citat ze su tu aj susedia z Ciech :)
        
        1. yes you have right I had in my dataset one record where the_geom was null. I removed this record and assign_vertex_id works right. thanks a lot for help.
        
        2. I run that sql you wrote and get error :
        
        roads_db=# SELECT srid FROM geometry_columns WHERE f_table_name=roads2; ERROR: Column "roads2" do not exists LINE 1: SELECT srid FROM geometry_columns WHERE f_table_name=roads2;
        
        ... what is wrong?
        
        Dzouzeph
        
        10/15/10 18:03:27 (5 weeks ago)
        
        Message #1704
        
        Hi Anton,
        assign_vertex_id went OK afterthen I removed record where the_geom is null but when I check data the column source and target I noticed that from 4000 records little more than 1000 records (segments) has source=target.
        Therefore also find route stops at this segments.
        My original data looks OK and it is line with more segments but same way or direction digitized therefore I dont understand where could be a problem.
        Can you give me som idea please.
        
        Dzouzeph
        
        10/15/10 20:29:11 (5 weeks ago)
        
        Message #1705
        
        in meantime I find out where was problem.
        my dataset has quite small segments (10m) and for assign_vertext_id at fist I put tollerance 0.0001 which too hight. Now I used tolerance 0.00001 and route is generated OK and also now dataset doeasnt have SOURCE and TARGET columns equal.
        
        Dzouzeph
        
        10/16/10 02:38:06 (5 weeks ago)

Tree View Flat View (newer first) Flat View (older first)