[pgrouting-users] pgRouting with Postgres 9.0 on Windows?

pgRouting pgrouting-users
1

I’m trying to get pgRouting working with OSM data loaded into PostGIS. There’s quite a learning curve as I am trying to work with the route data from an entire planet.osm file, and I’m completely new to PostGIS & Postgres (but not SQL/databases in general, or the concepts of routing and geospatial processing).

I’ve got the data loaded up into PostGIS with osm2po. I’m running with Windows 7 x32 (x64 is available but PostGIS does not support this yet). About to try pgRouting and I find I don’t have it installed (I thought I’d installed it earlier). And this is where I find the Windows build is expecting Postgres 8.4 , and not the 9.0 I have installed!

Is there a Windows build of pgRouting which will work with Postgres 9.0?
Or I do have to uninstall 9.0, re-install with 8.4, and then reload the OSM data?
(I still have the osm2po-produced scripts, but it would still take a day+ to reload it all).

I see that pgRouting only works with node numbers. However I shall have start/end points defined by coordinates. I’m still trying to figure the database out. Presumably I can use a query to find the closest node to each coordinate, although I can only see the link definitions and not the node definitions? (or are these implicit in the links? ie. a link has a start node number and a start coordinate).

Has anyone done this with osm2po produced data? What did your SELECT statements look like?
(also does anyone have a sample pgRouting SELECT that works with psm2po-produced tables?)

Best regards,

Richard Marsden

2

2011/1/7 Richard Marsden <winwaed@gmail.com>

I’m trying to get pgRouting working with OSM data loaded into PostGIS. There’s quite a learning curve as I am trying to work with the route data from an entire planet.osm file, and I’m completely new to PostGIS & Postgres (but not SQL/databases in general, or the concepts of routing and geospatial processing).

You could try with a smaller area first. Cool that it worked with planet.osm
OSM files for certain countries are available here for example: http://downloads.cloudmade.com/
(You can download files with street network data only)

I’ve got the data loaded up into PostGIS with osm2po. I’m running with Windows 7 x32 (x64 is available but PostGIS does not support this yet). About to try pgRouting and I find I don’t have it installed (I thought I’d installed it earlier). And this is where I find the Windows build is expecting Postgres 8.4 , and not the 9.0 I have installed!

There are probably many Windows users in the pgRouting community, but obviously just a few that can build Windows binaries.
So the currently available builds were reported to us from users. It would be nice to find some volunteers, who could maintain the Windows builds.

Is there a Windows build of pgRouting which will work with Postgres 9.0?
Or I do have to uninstall 9.0, re-install with 8.4, and then reload the OSM data?
(I still have the osm2po-produced scripts, but it would still take a day+ to reload it all).

osm2po is independent from pgRouting, so you don’t need to rebuild everything.

I see that pgRouting only works with node numbers. However I shall have start/end points defined by coordinates. I’m still trying to figure the database out. Presumably I can use a query to find the closest node to each coordinate, although I can only see the link definitions and not the node definitions? (or are these implicit in the links? ie. a link has a start node number and a start coordinate).

A-Star and Dijkstra work with node ID’s, Shooting Star with link ID’s.
You need to find the closest link/node to your start point to get the start/end node/link
Yes, a link has a start node and a end node.

Has anyone done this with osm2po produced data? What did your SELECT statements look like?
(also does anyone have a sample pgRouting SELECT that works with psm2po-produced tables?)

To make it work the same way as in the tutorial I created a “view”, for example like this:

CREATE VIEW view_ph_topo AS SELECT id AS gid, type_name AS class_id, st_length(geom_way) AS length, osm_name AS name, x1, y1, x2, y2, reverse_cost, cost, km, kmh, priority, source, target, target_angle, osm_id, pid, osm_source_id, osm_target_id, geom_way AS the_geom, geom_source, geom_target, length(transform(geom_way, 900913)) AS meters, length(transform(geom_way, 900913)) AS meters_rc, 0.0::double precision AS to_cost, NULL::text AS rule FROM ph_topo;

Maybe the table layout of osm2po has changed again, so I can’t say it works exactly like this.
Then you should be able make a query like the ones in the tutorials.

Daniel

Best regards,

Richard Marsden

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

–
Georepublic UG & Georepublic Japan
eMail: daniel.kastl@georepublic.de
Web: http://georepublic.de

3

Thanks for the reply Daniel.

Yes I could try a smaller file :slight_smile:
Eventually I will want to work with the entire planet.osm, but I guess the re-tries will be quicker :slight_smile:

There are probably many Windows users in the pgRouting community, but obviously just a few that can build Windows binaries.
So the currently available builds were reported to us from users. It would be nice to find some volunteers, who could maintain the Windows builds.

Thanks. Okay - for now I’ll probably have to reinstall the older version then.

osm2po is independent from pgRouting, so you don’t need to rebuild everything.

Sorry I wasn’t clear. osm2po produces a big giant SQL script (my German is not good enough to work out how to get it to write directly into PostGIS). Creating this script is what takes the most time. I still have that script, so it does not have to be re-generated.

I see that pgRouting only works with node numbers. However I shall have start/end points defined by coordinates. I’m still trying to figure the database out. Presumably I can use a query to find the closest node to each coordinate, although I can only see the link definitions and not the node definitions? (or are these implicit in the links? ie. a link has a start node number and a start coordinate).

A-Star and Dijkstra work with node ID’s, Shooting Star with link ID’s.
You need to find the closest link/node to your start point to get the start/end node/link
Yes, a link has a start node and a end node.

Thanks. At this point I hadn’t decided which to use. I assume all use the same link definitions with implicit node definitions.

To make it work the same way as in the tutorial I created a “view”, for example like this:

CREATE VIEW view_ph_topo AS SELECT id AS gid, type_name AS class_id, st_length(geom_way) AS length, osm_name AS name, x1, y1, x2, y2, reverse_cost, cost, km, kmh, priority, source, target, target_angle, osm_id, pid, osm_source_id, osm_target_id, geom_way AS the_geom, geom_source, geom_target, length(transform(geom_way, 900913)) AS meters, length(transform(geom_way, 900913)) AS meters_rc, 0.0::double precision AS to_cost, NULL::text AS rule FROM ph_topo;

Maybe the table layout of osm2po has changed again, so I can’t say it works exactly like this.
Then you should be able make a query like the ones in the tutorials.

Thanks I’ll have a look & compare - it should help when translating the tutorials and documentation. Hopefully it will be enough to get me going.

Thanks,

Richard Marsden