I have loaded OpenStreetMap data into a PostgreSQL database using Osmosis. This is done by using the snapshot schema.

I filtered the data and loaded only: country & city information

osmosis --read-pbf planet-latest.osm.pbf --tf accept-nodes place=country,city --tf reject-relations --tf reject-ways --lp --write-pgsql host="localhost" database="testing" user="postgres" password="s5mLzcqy"

This data is correctly loaded, but now I want to query this data. The output of this query should be the name of the city and next to it the name of the country.

I already have this query which extracts the name of the cities:

    tags->'name' AS name    
    tags->'place' IN ('city')

Can someone tell me how I can also get the names of the country the city is in?

Update: I tried to work with the ST_Contains() function, but I can't get it to work. Is there someone that has experience in working with this function?

  • You have to query for all elements the city is located in and then look at their tags. Alternatively install a geocoder such as Nominatim.
    – scai
    Commented Dec 22, 2016 at 10:22

1 Answer 1


You can't perform any spatial relations like ST_Contains cause you've got only nodes in your database. To do this you need also country borders as areas. You can get this from OSM (filter by boundary=administrative and admin_level=2) or other sources (e.g. http://www.naturalearthdata.com/). When you'll get it proper query will be something like this:

SELECT c.name as city, b.name as country
FROM nodes c
JOIN borders b on ST_Intersects(b.way, c.way)

Alternative way is to use an 'is_in:country' tag, but it's obsolete and many cities don't have it nowadays.

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.