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!

asked 17 Nov '16, 13:56

Mik77's gravatar image

Mik77
21113
accept rate: 0%

edited 17 Nov '16, 14:19

1

What is the "new" and "old" street name in this context?

(17 Nov '16, 14:14) SimonPoole ♦

old name is names of street in comunist period new name of street is name that street get when USSR crash.

(17 Nov '16, 14:20) Mik77

You want to add the old name, or what do you want to do?

(08 Dec '16, 09:59) Wetitpig0

@Mik77 The 2nd comment means something like "Stalingrad" & "Volgograd"?

(08 Dec '16, 10:01) Wetitpig0

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.

permanent link

answered 08 Dec '16, 16:50

escada's gravatar image

escada
17.9k16153284
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×84
×26

question asked: 17 Nov '16, 13:56

question was seen: 1,375 times

last updated: 08 Dec '16, 16:50

powered by OSQA