0

I am trying to test if pgrouting works fine or not. To do so I have created a table containing the following attributes. 

  Columns:
 gid      |
 length   |
 the_geom |
 source   |
 target

Now my problem is that when I try to execute the assign_vertex_id function it is giving me the following error; 

PL/pgSQL function "assign_vertex_id" line 15 at EXECUTE statement
ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function "assign_vertex_id" line 32 at EXECUTE statement

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

ERROR: query string argument of EXECUTE is null
SQL state: 22004


Context: PL/pgSQL function "assign_vertex_id" line 32 at EXECUTE statement

Any suggestions what does this mean?

kgrittn
  • 18,113
  • 3
  • 39
  • 47
IT_info
  • 707
  • 4
  • 16
  • 36
  • 1
    It would be easier to figure out if you showed the column types, any indexes and constraints (basically, the output of `\d` would be good), and *especially* the failing query. Somehow you have a function running an `EXECUTE` statement as dynamically generated SQL, but the string it's being passed is `NULL`. One way this could happen is to concatenate a series of values where one of them is `NULL`. – kgrittn Sep 07 '12 at 20:05

2 Answers2

2

assign_vertex_id() is defined as part of PGrouting in routing_topology.sql. (Running \df+ assign_vertex_id would also give you the current source.) Line 32's EXECUTE statement is:

EXECUTE 'update ' || quote_ident(geom_table) || 
    ' SET source = ' || source_id || 
    ', target = ' || target_id || 
    ' WHERE ' || quote_ident(gid_cname) || ' =  ' || _r.id;

The error is that EXECUTE is being called with a NULL argument. How? Well, the SQL || operator means concatenate, and concatenating NULL to a string results in NULL:

=> select ('string' || null) is null;
 ?column? 
----------
 t
(1 row)

My guess is the gid column of the underlying table (_r.id here) contains NULLs, although I suppose it could be source/target_id too. Fix it, then add a constraint to prevent that going forward:

ALTER TABLE whatever ALTER COLUMN gid SET NOT NULL;
willglynn
  • 11,210
  • 48
  • 40
  • The gid colun is already set to not null. This is the structure of the table; CREATE TABLE way ( gid integer NOT NULL, length double precision, the_geom geometry(MultiLineString,4326), source integer, target integer, CONSTRAINT gid PRIMARY KEY (gid ) ) – IT_info Sep 08 '12 at 07:35
0

Are you using Postgis 2.0 and have a table with Multilinestrings?

The function ST_StartPoint() and ST_EndPoint() does not work on Multilinestrings anymore(http://postgis.refractions.net/docs/ST_StartPoint.html) in Postgis 2.0. So it will most likely fail there.

You need to convert your Multilinestrings to Linestrings. More info about that here:

Espen
  • 110
  • 8