[pgrouting-users] Cannot Create a Network Nodes network nodes

Piyush_Shah · February 15, 2011, 11:02am

I am new to pgroutine so ineed some help

i am using PostgreSql 8.4 , Postgis 1.5.1, pgRouting-1.03_pg-8.4.2

But when i trying to create network node through assign_vertex_id

“SELECT assign_vertex_id (‘India_highway’, 0.00001, ‘the_geom’, ‘gid’);”

it gives following ERROR

[WARNING ] SELECT assign_vertex_id (‘India_highway’, 0.00001, ‘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 14 at EXECUTE statement
ERROR: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function “assign_vertex_id” line 20 at EXECUTE statement

so please help me what is the problem

PIYUSH M SHAH

dkastl · February 15, 2011, 3:06pm

How does your network table look like?
Did you make sure that column names match and also the data types?

Daniel

2011/2/15 Piyush Shah <piyush_shah@ymail.com>

I am new to pgroutine so ineed some help

i am using PostgreSql 8.4 , Postgis 1.5.1, pgRouting-1.03_pg-8.4.2

But when i trying to create network node through assign_vertex_id

“SELECT assign_vertex_id (‘India_highway’, 0.00001, ‘the_geom’, ‘gid’);”

it gives following ERROR

[WARNING ] SELECT assign_vertex_id (‘India_highway’, 0.00001, ‘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 14 at EXECUTE statement
ERROR: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function “assign_vertex_id” line 20 at EXECUTE statement

so please help me what is the problem

PIYUSH M SHAH

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

Piyush_Shah · February 16, 2011, 7:46am

Thank You for your Reply,

But I know What is the problem when i had see the assign_vertex_id function in that there is one for loop for taking srid value from input geom_table at that point only Error is occurring so i had statically assign the srid := 4326 (my Spatial table projection) the function works perfectly.

But you can help to understand what is the problem in that “for loop”

I am sending the function body to you in that i had highlighted the code where error is occurring (See Below) :-

CREATE OR REPLACE FUNCTION assign_vertex_id(geom_table character varying, tolerance double precision, geo_cname character varying, gid_cname character varying)
RETURNS character varying AS
$BODY$
DECLARE
_r record;
source_id int;
target_id int;
srid integer;
BEGIN

BEGIN
DROP TABLE vertices_tmp;
EXCEPTION
WHEN UNDEFINED_TABLE THEN
END;

EXECUTE ‘CREATE TABLE vertices_tmp (id serial)’;

– FOR _r IN EXECUTE ‘SELECT srid(geometry_columns) from ‘’’|| quote_ident(geom_table)||‘’‘;’ LOOP
– srid := _r.srid;
–END LOOP;
srid := 4326;

EXECUTE ‘SELECT addGeometryColumn(’‘vertices_tmp’‘, ‘‘the_geom’’, ‘||srid||’, ‘‘POINT’’, 2)’;
CREATE INDEX vertices_tmp_idx ON vertices_tmp USING GIST (the_geom);

FOR _r IN EXECUTE ‘SELECT ’ || quote_ident(gid_cname) || ’ AS id,’
|| ’ StartPoint(‘|| quote_ident(geo_cname) ||’) AS source,’
|| ’ EndPoint(‘|| quote_ident(geo_cname) ||’) as target’
|| ’ FROM ’ || quote_ident(geom_table)
LOOP

source_id := point_to_id(setsrid(_r.source, srid), tolerance);
target_id := point_to_id(setsrid(_r.target, srid), tolerance);

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

RETURN ‘OK’;

END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE STRICT
COST 100;
ALTER FUNCTION assign_vertex_id(character varying, double precision, character varying, c

PIYUSH M SHAH

— On Tue, 15/2/11, Daniel Kastl daniel@georepublic.de wrote:

From: Daniel Kastl daniel@georepublic.de
Subject: Re: [pgrouting-users] Cannot Create a Network Nodes network nodes
To: “pgRouting users mailing list” pgrouting-users@lists.osgeo.org
Date: Tuesday, 15 February, 2011, 3:06 PM

How does your network table look like?
Did you make sure that column names match and also the data types?

Daniel

2011/2/15 Piyush Shah piyush_shah@ymail.com

I am new to pgroutine so ineed some help

i am using PostgreSql 8.4 , Postgis 1.5.1, pgRouting-1.03_pg-8.4.2

But when i trying to create network node through assign_vertex_id

“SELECT assign_vertex_id (‘India_highway’, 0.00001, ‘the_geom’, ‘gid’);”

it gives following ERROR

[WARNING ] SELECT assign_vertex_id (‘India_highway’, 0.00001, ‘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 14 at EXECUTE statement
ERROR: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function “assign_vertex_id” line 20 at EXECUTE statement

so please help me what is the problem

PIYUSH M SHAH

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

-----Inline Attachment Follows-----

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

dkastl · February 16, 2011, 8:04am

2011/2/16 Piyush Shah <piyush_shah@ymail.com>

Thank You for your Reply,

But I know What is the problem when i had see the assign_vertex_id function in that there is one for loop for taking srid value from input geom_table at that point only Error is occurring so i had statically assign the srid := 4326 (my Spatial table projection) the function works perfectly.

But you can help to understand what is the problem in that “for loop”

I am sending the function body to you in that i had highlighted the code where error is occurring (See Below) :-

You network data table has probably no correct entry in the “geometry_columns” table or the SRID of the geometry is not set correctly (-1?).
In general it’s a good idea to have this set correctly. But this is not a pgRouting specific problem.

Daniel

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de