0

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:

SELECT 
    tags->'name' AS name    
FROM 
    nodes 
WHERE 
    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 Dec 22 '16 at 10:22
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, privacy policy and cookie policy

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