First steps with pgrouting

I installed pgrouting today (not especially painful) and loaded some data both from osm2pgsl and osm2pgrouting. The tables generated from both are not 100% compatible. The worst thing is that the points in the planet_osm_point table are not usable as vertices for the graph algorithms of pgrouting.So, you need to find the closest vertex that is in the table ways (generated by osm2pgrouting) that is closest to the point from table planet_osm_point (generated by osm2pgsql). Ah, and the geometries are in different SRIDs, but I suppose I will fix that manually later, like so.

update osm_planet_point set way=ST_Transform(way,4326); -- standard SRID, not the weird one osm uses

After about half an hour of hacking around mildly frustrated (because I don’t have muscle memory for these libs, since I am new to the whole thing) ,the solution was to find the closest target (or source, doesn’t make a difference) osm_id to the point id that corresponds to a city, like so

select line.target as nearest_street, pt.osm_id as city
from planet_osm_point as pt,
ways as line 
where pt.name='Genève' and pt.place='city'
order by 
ST_Transform(line.the_geom,900913) <-> pt.way
 LIMIT 1;

Yet another issue with the Open Street Map data is the fuzziness of the names. I could not find the city Lucerne or Luzern and there is stuff in the names like:

 Peney-Dessous
 Aire-la-Ville
 Peney
 Les Clos
 Café de Peney
 Restaurant 2
 022
 021
 020
 017

OK, whatever that means. I run this query to try to find Zurich:

select name,place from planet_osm_point where name like 'Z%ri%'

and sure enough, there’s both Zurich and Zürich with an umlaut on the u and only the latter is listed as a city in the place column. (and there is also a Zürich that doesnt have anything in the place column). Thankfully, I just want one point inside Zurich, so any of these will do.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s