pgRouting from geocoded user input

Question

have created database by tool osm2pgrouting. On my website can user enter start and end point.

How can I find ids of nodes? Because I have only text value of nodes which enter user, but tables generated by tool does not.
UPDATE: On the website will be 2 inputs for 2 points (start and end) user enter for example New York (as start) and Los Angeles (as end). Next I call service to my geoserver and select route, and return tiles or geoJSON. But for selecting route I use pgr_dijkstra function which requires node IDs, but I have only NY and LA in text format. How can I convert this to coordinates or osm_id??

This is how my table of nodes looks:

This is reason why I need convert text values of user to id or coordinates.

Answer 1

The are several loaders for OSM, aimed at different end uses -- routing, rendering with Mapnik, raw format. It can be a somewhat confusing at first to understand the differences, and they all produce difference output tables.

osm2pgrouting imports OSM in a format that is optimized for routing, ie, nodes and edges (with costs). It does not include any geocoding information that will allow you to enter an address and get an osm_id that you can link back to a start or end node in a routing calculation.

If you want geocoding in OSM, you can use the nominatum service, for example, from the docs:

http://nominatim.openstreetmap.org/search?q=135+pilkington+avenue,+birmingham&format=xml&polygon=1&addressdetails=1

which returns JSON including the osm_id of the node, eg,

<searchresults timestamp="Sat, 07 Nov 09 14:42:10 +0000" querystring="135 pilkington, avenue birmingham" polygon="true">
<place 
  place_id="1620612" osm_type="node" osm_id="452010817"
...... etc 

If you wish to do this locally, in order to get OSM data in a format including address information, you need to use the osmosis loader which loads the data into the native format of OSM: ways, nodes and relations table, complete with the hstore tags columns, which contain key/value pairs containing extra information such as an address, a highway type, etc.

If you install nominatim locally (plus osmosis, which is part of that process), then to create your routing table you can use the pgr_createTopology function, passing in the ways table as the edge table, eg:

SELECT pgr_createTopology('ways', 0.0001);

However, in practice, I have found this function to be very fussy, and you might be better of using osmosis and osm2pgrouting separately, or use osm2pgrouting and use the external nominatim geocoder service to match you addresses to osm_ids.