PostGIS Reader

Developers
1

Hello.

I just wrote a PostGIS-reader that can create a GraphHopper graph directory directly from a PostGIS Table or View. This is just basically derived from the Shapefile-reader.

With this, we can:

  • use custom network data like the DRM ( http://www.drm.jp/english/drm/e_index.htm ) instead of just OSM.

  • add business logic to the route by having custom costs derived from the Views.

  • add special values to the attributes. In our application, we need the DRM IDs after a shortest path has been created, so the ID was placed in the OSM Name tag when creating a view, and we obtain the ID with a getName() after a path creation.

  • create semi-dynamic routes by just deleting the graph directory and restarting graphhopper at certain time intervals to create a new graph with newer cost values.

Again for the DRM, we created a view like the sample below, and then created directly a graph from it for GraphHopper.

create view drmview
(osm_id,maxspeed,oneway,fclass,name,geom )
as select id,0,‘B’::text,
case
when rdclasscd = ‘1’ then ‘motorway’
when rdclasscd = ‘2’ then ‘motorway’
when rdclasscd = ‘3’ then ‘trunk’
when rdclasscd = ‘4’ then ‘primary’
when rdclasscd = ‘5’ then ‘primary’
when rdclasscd = ‘6’ then ‘secondary’
when rdclasscd = ‘7’ then ‘tertiary’
when rdclasscd = ‘9’ then ‘road’
end,
id::text,geom
from mesh5339;

The source code is in my forked GraphHopper repository in GitHub under the postgis branch. I hope that this can be useful for somebody else as well.

2 Likes
2

Really cool, thanks a lot! I can imagine that this is really interesting for people using Postgres or need to debug routing network changes.

Some questions:

  • How do you handle changes? Will the import process triggered once a change comes in?
  • Do you plan to add an installation guide? (or did I just not find it)
  • Why did you choose to fork instead of a separate project that uses unmodified GraphHopper classes? It is probably easier to upgrade to recent versions.
3

Hello.

Aside for re-creating the graph at timed intervals and forcing GraphHopper to re-read it, I haven’t given this much thought. This might be very interesting to do. Is anybody doing something similar?

I wrote a little documentation for this: graphhopper/README-PostGIS.md at postgis · mbasa/graphhopper · GitHub

Creating a fork was easier for testing and debugging for me, and I can just point people to the branch for downloads of the entire package. Curious though, is the master branch of GraphHopper used as the development branch? I thought the master was the stable branch.

4

There is some work regarding a more dynamic graph in the real time part of public transit but we can only adjust the graph a bit and the API is not really designed for this (we have to improve here). So re-creation is currently the best thing.

I wrote a little documentation for this

Nice.

Curious though, is the master branch of GraphHopper used as the development branch? I thought the master was the stable branch.

It is a stable development branch :slight_smile: … so it should be stable (green tests) but can break if unwanted bugs.

5

Great work @mbasa, thanks!

I am currently working on reading data from PostGIS and create a GH graph from this. I took the liberty to transform @mbasa’s fork into a GraphHopper module, update to the latest version of GraphHopper and extending some of the functions, fix some minor issues, etc.

You can find the current state here, contributions are welcome :slight_smile: :

1 Like