pgRouting needs legacy.sql

[hamish]

Hi,

I realize we never had a ticket for this even though we knew about it:

pgRouting isn't working since it is not happy with PostGIS 2.0. It needs legacy.sql loaded as an extension.

thanks, Hamish

[hamish]

hopefully the upgrade to PostGIS 2.0 (+legacy.sql) is ok now thanks to Brian with r9819,20.

this seems to work:

  SELECT name FROM ways;

but when I run the quickstart I get this error from the Dijkstra query:

  ERROR:  source contains a null value

the main /var/log/postgres log contains a number of errors which might or might not be relevant.

The wrapper function from the quickstart, both with an without ST_AsText(), gives the same error, with this added:

  CONTEXT: PL/pgSQL function "dijkstra_sp" line 13 at FOR over EXECUTE statement

see also #1094

thanks, Hamish

[dkastl]

(In [9821]) fixed #1089 - osm2pgsql import creates Multigeometry, which fails with PostGIS 2.0

[dkastl]

Legacy.sql should solve problems with PostGIS 2.0, but it didn't prevent to make assign_vertex_id function to fail, missing to update source and target attribute because osm2pgrouting creates the geometry column as Multigeometry. But PostGIS 2.0 refuses to use ST_StartPoint and ST_EndPoint on MultiLinestring.

[hamish]

thanks Daniel, dropped the old DB and tested the new method by hand, it works now. :)

yearning for some visual feedback I made a couple more changes in svn to try and get the workshop's GeoExt OpenLayers demo going. I recentered the map over the OSM extract in routing-*.html and adjusted the PG_DB and PG_USER variables in php/pgrouting.php, but no routing renders :-( Maybe something else needs tweaking too?

thanks, Hamish

[hamish]

Replying to hamish:

yearning for some visual feedback I made a couple more changes in svn to try and get the workshop's GeoExt OpenLayers demo going. I recentered the map over the OSM extract in routing-*.html and adjusted the PG_DB and PG_USER variables in php/pgrouting.php, but no routing renders :-( Maybe something else needs tweaking too?

with some help from Brian I got it working:

• needed to symlink the workshop's web dir into /var/www, as the php needed to be served to run

• After RTFM, it needed 'trust' level set for IPv4 'host' access in pg_hba.conf. Wondering ways to avoid that... ~/.pgpass is already set up and seems to be working elsewhere. ?

Hamish

[dkastl]

• needed to symlink the workshop's web dir into /var/www, as the php needed to be served to run

I think that's written in the workshop documentation. The reason to not link the source directly is, that workshop participants should make a copy into their home directory. So if they would get stuck somewhere, they can easily roll back.

• After RTFM, it needed 'trust' level set for IPv4 'host' access in pg_hba.conf. Wondering ways to avoid that... ~/.pgpass is already set up and seems to be working elsewhere. ?

It would be possible to use a different user, configured in the PHP script. User "user" is specific for Live DVD. It's probably bad practice to use "postgres". I may change this for the next workshop version.

But for now I would say it's OK, because the workshop manual describes all these steps. Glad to know though that the workshop still works with the latest release.