How to get the street new name and old name and add the same id to each

Question

I need to get the street new name and old name and street name in other language and have conexion between its in form:

+---------+----------------------------+------------------+
| city    | street name                |  street_id       |
+---------+----------------------------+------------------+
| NewYork | new street name            | 7                |
+---------+----------------------------+------------------+
| NewYork | old street name            | 7 (the same id)  |
+---------+----------------------------+------------------+
| NewYork | other language street name | 7 (the same id)  |
+---------+----------------------------+------------------+

I get streets and city with

select * from planet_osm_line where ( planet_osm_line.highway='living_street' or planet_osm_line.highway='motorway' or planet_osm_line.highway='primary' or planet_osm_line.highway='proposed' or planet_osm_line.highway='raceway' or planet_osm_line.highway='residential' or planet_osm_line.highway='road' or planet_osm_line.highway='secondary' or planet_osm_line.highway='tertiary' or planet_osm_line.highway='track' or planet_osm_line.highway='trunk' or planet_osm_line.highway='unclassified' or planet_osm_line.route='road' or planet_osm_line.highway='service' or planet_osm_line.highway='cycleway' ) order by planet_osm_line.osm_id;

And whe i seach street by id i get old and new street name and name of that street in other languages!

Answer 1

I doubt that you will find a lot of information in OSM about old street names. But OSM has 3 different fields for what you need : name, old_name, name:XX where XX is the language code. As for old_name, there is no guarantee it is the name during the period you want.

I am not very familiar with the database scheme, but all info about a street is stored in 1 row using hstore. In case you need 3 different rows, you will need 3 queries (one for each "name") and make the union of their results to get the table you want.

How to get the street new name and old name and add the same id to each

Follow this question

Related questions