This is a static archive of our old OpenStreetMap Help Site. Please post any new questions and answers at community.osm.org.

Getting street names

Hi everyone.

How can I get street names from planet_osm_line? I use this query to get:

select planet_osm_line.name fromn planet_osm_line where (highway='living_street' or highway='motorway' or highway='primary' or highway='proposed' or highway='raceway' or highway='residential' or highway='road' or highway='secondary' or highway='tertiary' or highway='track' or highway='trunk' or highway='unclassified' or route='road') and LENGTH(Name)>5 group by planet_osm_line.name;

So I got really few names - about 1-2 thousands, but streets rows are about 400 000, that's are more real (where are other names?)

query streetnames

asked 13 Mar '12, 07:19

zzzzteph
30●5●5●7
accept rate: 0%

There are multiple possible database schemas possible, depending on which tool and which options you used to load the .osm into postgres. What is you schema ?

(13 Mar '12, 09:15) Vincent de P... ♦

osm2psql with standart options.

planet_osm_line planet_osm_nodes planet_osm_way planet_osm_polygon

(13 Mar '12, 09:40) zzzzteph

So, how can I get Street name from database (osm2psql) by its Id using sql query?

(13 Mar '12, 17:09) zzzzteph

One Answer:

Your query misses some street types that might well have names (e.g. service, cycleway). Other types in your query (motorway, trunk) often don't have names at all, just "ref" numbers. Also, there are places where even residential roads aren't named in OSM because they have been traced from aerial imagery and those who did the tracing lacked the local knowledge to add names.

What is the exact query that you executed (the one quoted in your question is not valid SQL), and on what data set? The number of all rows in planet_osm_lines matching your criteria is about 17.5 million on a current (March 2012) planet database.

answered 13 Mar '12, 07:45

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

This is it. It's for postgresql and it works fine, exept that I didn't get all names.

I use only "roads" street's. My database is only a part of planet_world (Europian part). I need some query with what I can get all roads names(motorway,trunk are not needed).

(13 Mar '12, 07:51) zzzzteph