[GIS] SELECT assign_vertex_id(‘ways’, 0.00001, ‘the_geom’, ‘gid’); not work

osgeopgroutingpostgispostgresql

I'm using using OSgeo 6.5 dvd and studing pgrouting workshop (link) for get shortest path. I created network topology call "pgrouting – workshop". There are three commands to execute in section 5.2 they are :

-- Add "source" and "target" column
ALTER TABLE ways ADD COLUMN "source" integer;
ALTER TABLE ways ADD COLUMN "target" integer;

-- Run topology function
SELECT assign_vertex_id('ways', 0.00001, 'the_geom', 'gid');

Add source and target was executed successfully. topology function give errors.
Heres 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 15 at EXECUTE statement
ERROR:  function startpoint(geometry) does not exist
LINE 1: SELECT gid AS id, StartPoint(the_geom) AS source, EndPoint(t...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT gid AS id, StartPoint(the_geom) AS source, EndPoint(the_geom) as target FROM ways
CONTEXT:  PL/pgSQL function "assign_vertex_id" line 27 at FOR over EXECUTE statement

How do i fix this error?

I loaded the legacy.sql and noted that error has little bit changed :

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 15 at EXECUTE statement
ERROR:  function point_to_id(geometry, double precision) does not exist
LINE 1: SELECT point_to_id(setsrid(_r.source, srid), tolerance)
           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT point_to_id(setsrid(_r.source, srid), tolerance)
CONTEXT:  PL/pgSQL function "assign_vertex_id" line 33 at assignment

Best Answer

The version of pgRouting installed on the OSGeo LiveDVD 6.5 is not fully working with PostGIS 2.0, but you can solve most issues by loading the legacy.sql file to your database:

\i /usr/share/postgresql/9.1/contrib/postgis-2.0/legacy.sql

In most cases when an errors occurse, pgRouting functions are missing the ST_ prefix, for example ST_StartPoint(the_geom).

Related Question