NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

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?)

asked 13 Mar '12, 07:19

zzzzteph's gravatar image

zzzzteph
30557
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

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.

permanent link

answered 13 Mar '12, 07:45

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
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

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:

×147
×111

question asked: 13 Mar '12, 07:19

question was seen: 5,778 times

last updated: 17 Mar '12, 11:09

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum