Write Nominatim-like geocode query using only PostGIS functions?

Question

I'm new to PostGIS and was anticipating being able to write geocode queries using only the functions provided by PostGIS. However, once I loaded my database using osm2pgsql and discovered tables like "point," "polygon," etc., it wasn't at all apparent how I could create the equivalent of a Nominatim query such as this one for a street address in Cologne, Germany:

# This returns "lat":"50.9412187","lon":"6.95511930515345"
http://nominatim.openstreetmap.org/search?q=margarethenkloster+5,+50667,+cologne,+de

I know that latitude/longitude data is kept in the nodes table but the columns found in the point, line, and polygon tables don't seem to lend themselves to this type of query.

Is it possible to write a SQL query against a PostgreSQL/PostGIS database that will mimic the above call to the Nominatim web service or must I use Nominatim?

Thanks.

Answer 1

Here shows how to make these queries: https://gist.github.com/jpetazzo/5177554 It shows how to do it downloading the OSM database in PostGis format first. For example, you can make a query like:

SELECT name,way FROM osm_points WHERE place IN ('town', 'city');

(The way column should be the GPS coordinates of the center of the city.)

And you will get the points already as spatial objects, with latitude and longitude.

If you select a polygon or a line in OSM database, you can get the coordinates (X and Y) with ST_Centroid() function.

Maybe it suits your needs.