Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#1089 closed defect (fixed)

pgRouting needs legacy.sql

Reported by: hamish Owned by: live-demo@…
Priority: blocker Milestone: OSGeoLive6.5
Component: OSGeoLive Keywords: 6.5, pgrouting, postgis
Cc:

Description

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

Change history (7)

comment:1 Changed 7 years ago by 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

comment:2 Changed 7 years ago by hamish

Type: taskdefect

comment:3 Changed 7 years ago by dkastl

Resolution: fixed
Status: newclosed

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

comment:4 Changed 7 years ago by 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?.

comment:5 Changed 7 years ago by 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

comment:6 in reply to:  5 ; Changed 7 years ago by 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

comment:7 in reply to:  6 Changed 7 years ago by 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.

Note: See TracTickets for help on using tickets.