I'm having some trouble figuring out if I've got the right data loaded for PgRouting.

Using the bash script included in PostGIS 2.0, I loaded Tiger2010 data for U.S. State California.

  1. The edges table contains the_geom column, data type geometry. Using Underdark's example, it seems I need the roads in a Multilinestring format in order to begin generating routes. Can the data in the edges table be converted into type multilinestring? The table query is below.
  2. I altered my geocoded location data to data type geography. PgRouting's shortest_path function needs the data in integer format. How do I convert the geography type point into an integer that shortest_path can use?

Thanks for your patience.

CREATE TABLE tiger.edges
  gid integer NOT NULL DEFAULT nextval('edges_gid_seq'::regclass),
  statefp character varying(2),
  countyfp character varying(3),
  tlid numeric(10,0),
  tfidl numeric(10,0),
  tfidr numeric(10,0),
  mtfcc character varying(5),
  fullname character varying(100),
  smid character varying(22),
  lfromadd character varying(12),
  ltoadd character varying(12),
  rfromadd character varying(12),
  rtoadd character varying(12),
  zipl character varying(5),
  zipr character varying(5),
  featcat character varying(1),
  hydroflg character varying(1),
  railflg character varying(1),
  roadflg character varying(1),
  olfflg character varying(1),
  passflg character varying(1),
  divroad character varying(1),
  exttyp character varying(1),
  ttyp character varying(1),
  deckedroad character varying(1),
  artpath character varying(1),
  persist character varying(1),
  gcseflg character varying(1),
  offsetl character varying(1),
  offsetr character varying(1),
  tnidf numeric(10,0),
  tnidt numeric(10,0),
  the_geom geometry,
  CONSTRAINT edges_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269)
ALTER TABLE tiger.edges OWNER TO postgres;
GRANT ALL ON TABLE tiger.edges TO postgres;
GRANT ALL ON TABLE tiger.edges TO gis_group;

-- Index: tiger.idx_edges_tlid

-- DROP INDEX tiger.idx_edges_tlid;

CREATE INDEX idx_edges_tlid
  ON tiger.edges
  USING btree

-- Index: tiger.idx_tiger_edges_countyfp

-- DROP INDEX tiger.idx_tiger_edges_countyfp;

CREATE INDEX idx_tiger_edges_countyfp
  ON tiger.edges
  USING btree

-- Index: tiger.idx_tiger_edges_tfidl

-- DROP INDEX tiger.idx_tiger_edges_tfidl;

CREATE INDEX idx_tiger_edges_tfidl
  ON tiger.edges
  USING btree

-- Index: tiger.idx_tiger_edges_tfidr

-- DROP INDEX tiger.idx_tiger_edges_tfidr;

CREATE INDEX idx_tiger_edges_tfidr
  ON tiger.edges
  USING btree

-- Index: tiger.tiger_edges_the_geom_gist

-- DROP INDEX tiger.tiger_edges_the_geom_gist;

CREATE INDEX tiger_edges_the_geom_gist
  ON tiger.edges
  USING gist

3 Answers 3


I think that I may have had a similar issue. The pgRouting assign_vertex_id() in the 1.05 release is a bit outdated. To see if it was fixed in a later version, I downloaded the most recent source from the Git repository (https://github.com/pgRouting/pgrouting). The "ST_" prefix issue had been resolved in routing_topology.sql.

Unfortunately you can't run assign_vertex_id() directly on MULTILINESTRING geometries because ST_StartPoint() and ST_EndPoint() will return NULL values. I don't think this has always been true, but as far as I can tell it is a current issue.

SELECT ST_StartPoint('MULTILINESTRING((-29 -27,-30 -29.7,-36 -31,-45 -33),(-45.2 -33.2,-46 -32))'::geometry) IS NULL AS is_null;

(1 row)

In my particular case I was able to modify my data and that seemed to solve the problem.


Following is a modified version of the "routing_topology.sql" script. It provides a solution for two of the issues mentioned previously:

  • Your tables don't have to be in schema "public" any longer.
  • If you use PostGIS 2.0 and the ST_StartPoint() and ST_EndPoint() functions, they will return NULL if your geometry is of type "MULTILINESTRING". That's now fixed, so even with a MULTILINESTRING start- and end point will be properly returned.

Replace the content of your "routing_topology.sql" file with the code below and run the script again.

-- This function should not be used directly. Use assign_vertex_id instead
-- Inserts a point into a temporary vertices table, and return an id
--  of a new point or an existing point. Tolerance is the minimal distance
--  between existing points and the new point to create a new point.
-- Last changes: 16.04.2008
-- Author: Christian Gonzalez
CREATE OR REPLACE FUNCTION point_to_id(p geometry, tolerance double precision)

    _r record; 
    _id bigint; 
    _srid integer;


        _srid := Find_SRID(user::text, 'vertices_tmp', 'the_geom');
        WHEN SQLSTATE 'P0001' THEN
            _srid := Find_SRID('public', 'vertices_tmp', 'the_geom');


        ST_Distance(the_geom, p) AS d, id, the_geom

    INTO _r FROM vertices_tmp WHERE

        ST_DWithin(the_geom, p, tolerance)


        _id:= _r.id;


        INSERT INTO vertices_tmp(the_geom) VALUES (p); _id:=lastval();

    END IF;

    RETURN _id;


-- Fill the source and target_id column for all lines. All line ends
--  with a distance less than tolerance, are assigned the same id
-- Last changes: 16.04.2008
-- Author: Christian Gonzalez

CREATE OR REPLACE FUNCTION assign_vertex_id(geom_table varchar, tolerance double precision, geo_cname varchar, gid_cname varchar)
    _r record;
    source_id int;
    target_id int;
    srid integer;

    DROP TABLE vertices_tmp;

    EXECUTE 'CREATE TABLE vertices_tmp (id serial)';

        srid := Find_SRID(user::text, quote_ident(geom_table), quote_ident(geo_cname));
        WHEN SQLSTATE 'P0001' THEN
            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);

    FOR _r IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,'
        || ' ST_StartPoint(ST_GeometryN('|| quote_ident(geo_cname) ||', 1)) AS source,'
            || ' ST_EndPoint(ST_GeometryN('|| quote_ident(geo_cname) ||', 1)) as target'
        || ' FROM ' || quote_ident(geom_table) || ' WHERE ' || quote_ident(geo_cname) || ' IS NOT NULL '

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

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

    RETURN 'OK';

  • It's really important to make sure that any MULTILINESTRING geometries are single part when using this modified version of "routing_topology.sql". Sep 7, 2011 at 11:37
  • The geometries in the table that you pass to the "assign_vertex_id" method should be single part anyway since these geometries represent edges in the topological context. Sep 10, 2011 at 14:56

Your geometries are already MULTILINESTRING. Otherwise they would not fulfill the following constraint:

CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL),

For routing with shortest_path() you need node IDs not edge IDs. To generate node IDs use assign_vertex_id() function.

assign_vertex_id() will fill certain columns in your edges table, but you have to create them first. From http://www.pgrouting.org/docs/foss4g2008/ch06.html:

ALTER TABLE ways ADD COLUMN source integer;
ALTER TABLE ways ADD COLUMN target integer;
SELECT assign_vertex_id('ways', 0.00001, 'the_geom', 'gid');
  • assign_vertex_id returns an error for this view, CREATE view geocoder.tiger.routing_test AS SELECT * ,assign_vertex_id('geocoder.tiger.edges', 0.000001, 'the_geom', 'gid') as nodes from geocoder.tiger.edges; For this query select * from geocoder.tiger.routing_test limit 3 Error is "ERROR: find_srid() - couldnt find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch? CONTEXT: PL/pgSQL function "assign_vertex_id" line 20 at assignment"
    – juheesus
    Aug 16, 2011 at 17:57
  • ST_startpoint() works on LINESTRINGS (postgis.org/docs/ST_StartPoint.html). I don't think assign_vertex_id() can be used in view creation statements. You should try creating a table instead.
    – underdark
    Aug 16, 2011 at 18:14
  • I'm a little further along. There's a table called geometry_columns and edges was not in it. PostGIS had the way to add it documented, so that's done. But it still doesn't work. a simple SELECT * ,assign_vertex_id('geocoder.tiger.edges', 0.000001, 'the_geom', 'gid') fails with the same message.
    – juheesus
    Aug 16, 2011 at 20:27
  • Alright, further along still... The function assign_vertex_id has technical limitations. It's only looking for the table in public. That's not where postGIS 2.0 tiger2010 loading script puts edges. It also uses old names for functions. no st_ prefix sprinkled throughout the functions.
    – juheesus
    Aug 16, 2011 at 22:38
  • Well, you can always use the method I posted in underdark.wordpress.com/2011/02/07/…. It's slower than assign_vertex_id() though. Or move the table to public and back after creation of vertex ids.
    – underdark
    Aug 17, 2011 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.