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?