3

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.

4
  • The hard part of geocoding is (usually) figuring out which parts of the address are which. If you can constrain the input, and you know you have specific data that matches (or a set of fallback rules), then you can probably try matching on the OSM data. A tool like nominatim is non-trivial, because of the variations in worldwide address formats. In Australia, the street number would come after the address. Can you try editing your question with why you don't want to use nominatim (or other existing geocoder) and any info that might constrain your problem? – BradHards Dec 10 '13 at 23:47
  • 1
    Thanks for your comment but I believe I'll leave the question as-is. I'll know which parts of the address are what. Given that I know, for example, what the street number, street name, and postal code are, I'd like to know if I can use those element to create a geocode query utilizing the functions provided by PostGIS or if it's mandatory to use a geocoding layer/engine like Nominatim. – Jim Dec 11 '13 at 20:29
  • 1
    That information would be useful in the question. – BradHards Dec 11 '13 at 21:07
  • Have you seen this blog entry: klauszanders.de/blog/2012/10/15/… – mkennedy Dec 11 '13 at 22:07
0

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.

2
  • can you include a short summary as the link may get offline someday? – MrXsquared Jul 9 '20 at 16:51
  • Thanks! I added now. Is it ok, already? – Vitor Vieira Vasconcelos Jul 10 '20 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.