2

I am new to geocoding and am trying to build a geo application. I used osm2pgsql to convert my osm data to pgsql gis extended database with the following tables planet_osm_line, planet_osm_polygon, planet_osm_point, planet_osm_ways, ...etc

My first query is to find the distance between 2 randomly given addresses using the above generated tables.

Can someone explain how to query these tables to attain the result ?

The duplicate Convert OSM File to get addresses using Osm2pgsql which was linked does not have proper information to query tables.

For example, I need to know which column has the name of the streets and which column has the house numbers and which column has names of the city or state and how to query between them to geocode or reverse geocode.

Improve this question
1
  • Nominatim prefers tables from osm2pgsql. With the tables i have locally from osm2pgsql, is it possible to install nominatim also locally to perform these queries ?
    – Yogi
    Commented Feb 1, 2017 at 11:41

1 Answer 1

Reset to default
1

The columns to look for are addr:state, addr:city, addr:street and addr:housenumber. Unfortunately, not all are in the default osm2pgsql database scheme. You have to add the others manually before importing the data.

Apart from that, it might be that not all columns are filled with data by the contributors. You might have to get state and city from a spatial query with admin polygons. Maybe a nominatim database (also populated by osm2pgsql) is better suited for your needs.

Nominatim has a different table layout than the default database scheme used for Mapnik. So you have to re-import the data with the nominatim scheme. And Nominatim does the spatial queries from admin polygons too before you can utilize the data.

Improve this answer
2
  • Could you please elaborate on how the columns addr:state, addr:street, addr:city... can be added ? Can it be done while importing using osm2pgsql ? I don't want to use nominatim because it requires a seperate webserver and I am limited not to use webserver.
    – Yogi
    Commented Feb 8, 2017 at 18:14
  • You can edit the default.style file in the osm2pgsql folder with any text editor. Just add the lines similar to addr:housenumber.
    – AndreJ
    Commented Feb 8, 2017 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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