Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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)
)
WITH (
  OIDS=FALSE
);
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
  (tlid);

-- Index: tiger.idx_tiger_edges_countyfp

-- DROP INDEX tiger.idx_tiger_edges_countyfp;

CREATE INDEX idx_tiger_edges_countyfp
  ON tiger.edges
  USING btree
  (countyfp);

-- Index: tiger.idx_tiger_edges_tfidl

-- DROP INDEX tiger.idx_tiger_edges_tfidl;

CREATE INDEX idx_tiger_edges_tfidl
  ON tiger.edges
  USING btree
  (tfidl);

-- Index: tiger.idx_tiger_edges_tfidr

-- DROP INDEX tiger.idx_tiger_edges_tfidr;

CREATE INDEX idx_tiger_edges_tfidr
  ON tiger.edges
  USING btree
  (tfidr);

-- 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
  (the_geom);
share|improve this question

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;

 is_null
---------
 t
(1 row)

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

share|improve this answer

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)
RETURNS BIGINT 
AS 
$$ 

DECLARE
    _r record; 
    _id bigint; 
    _srid integer;

BEGIN

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

    SELECT

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

    INTO _r FROM vertices_tmp WHERE

        ST_DWithin(the_geom, p, tolerance)

    ORDER BY d LIMIT 1; IF FOUND THEN

        _id:= _r.id;

    ELSE

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

    END IF;

    RETURN _id;

END; $$ LANGUAGE 'plpgsql' VOLATILE STRICT; 


-----------------------------------------------------------------------
-- 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)
RETURNS VARCHAR AS
$$
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)';

    BEGIN   
        srid := Find_SRID(user::text, quote_ident(geom_table), quote_ident(geo_cname));
    EXCEPTION
        WHEN SQLSTATE 'P0001' THEN
            srid := Find_SRID('public', quote_ident(geom_table), quote_ident(geo_cname));
    END;

    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 '
    LOOP

        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;
    END LOOP;

    RETURN 'OK';

END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT; 
share|improve this answer
    
It's really important to make sure that any MULTILINESTRING geometries are single part when using this modified version of "routing_topology.sql". –  Allan Adair Sep 7 '11 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. –  Michael Wagner Sep 10 '11 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');
share|improve this answer
    
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 '11 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 '11 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 '11 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 '11 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 '11 at 6:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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