[GIS] Newbie PostGIS Geometry and Multilinestring Clarification

pgroutingpostgis

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);

Best Answer

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.

Related Question