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.