pgRouting and PostGIS 2.0 (and hardcoded field names)

We have had very good fortune with running pgRouting 1.0.5 on PostGIS 2.0 We asked Steve Woodbridge about it some time ago, he said he wasn't sure if it would work, but I'm glad to report that after 2 months of using it intensively, virtually all bugs we're finding would also be present in PostGIS 1.x


RENAMED POSTGIS FUNCTIONS

You need to make a few changes to routing_core_wrappers.sql before you load it, or remember to re-load it after you make changes. PostGIS 2.0 changed the names of a bunch of functions, enforcing the ST_* naming convention, so that AsText() is now ST_AsText()

Open up routing_core_wrappers.sql and do some search-n-replace:

• X(   with   ST_ X(
• Y(   with   ST_Y(
• SRID(   with   ST_SRID(
• SETSRID(   with   ST_SETSRID(
• STARTPOINT(   with   ST_STARTPOINT(
• ENDPOINT(   with   ST_ENDPOINT(

THE GEOMETRY FIELD

The geometry field in PostGIS 1.x is "the_geom" and pgRouting expects that too. PostGIS 2.0 names it "geom" Some possible fixes include:

• When you load the data via shp2pgsql, supply "-g the_geom"
• If you already loaded, you can simply rename the column: "ALTER TABLE trails RENAME COLUMN geom TO the_geom"
• You can create a view. Performance hit, though, maybe not a good move.
• You can edit routing_core_wrappers.sql and do a search-and-replace so pgRouting expects "geom"

For ourselves, we went with an entirely new table and named the column "the_geom" We had a need to separate out the trails data as displayed on the map, from the trails data used for routing. More on that in some other post if it's interesting enough.


THE LENGTH / COST FIELD

The "length" field is used as the cost of traversing a segment. You need this field and it needs to be named "length":

ALTER TABLE trails ADD COLUMN length double precision;
UPDATE trails SET length=ST_LENGTH(geom);

 UPDATE: The "length" field is hardcoded into routing_core_wrappers.sql as the "cost" of a segment for routing purposes. I suggest this search-and-replace as well, to separate the length from the cost.

• length::double precision as cost     with     cost

This does mean that you'll need a "cost" field in your data:

ALTER TABLE trails ADD COLUMN cost double precision;
UPDATE trails SET cost=length;

THE GID FIELD

The primary key field must be "gid" and not something else like "id" If you loaded up via shp2pgsql this probably  is already the case. But just saying, if your table wasn't created by shp2pgsql you may need to do more search-n-replace in routing_core_wrappers.sql to rename "gid"

And if you use a mix of them, some tables with gid and some with a different primary key name, well, you'll have a tough time.