Hi!! I am a student of Engineering and I'm using OSM data for my final university project.

My problem is this: Let us place ourselves in Madrid, Serrano street is defined by various 'ways' and I wonder if anyone knows any way to detect ALL Serrano street at once, without having to resort to different 'ways' that compose this street.

Currently, I have downloaded the data from Madrid in SQL .


asked 24 Feb '16, 15:51

eleena18's gravatar image

accept rate: 0%

edited 24 Feb '16, 20:31

aseerel4c26's gravatar image

aseerel4c26 ♦

(24 Feb '16, 20:16) aseerel4c26 ♦

If you want to get a combined geometry out of your SQL database, you could do something like this. Instead of

SELECT name, highway, way FROM planet_osm_line


SELECT name, highway, ST_UNION(way) AS way FROM planet_osm_line GROUP BY name, highway

This will combine all objects that have the same name and highway type into single lines (where possible).

permanent link

answered 24 Feb '16, 19:47

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
accept rate: 23%


Aha, good point. Note that some streets change type, so this heuristic still won't be 100%. See this - the street "Kochanova" starts from south as residential, continues north as steps, and then again as residential: http://www.openstreetmap.org/way/32024324 (just an example I found by casually glancing at the map; probably a more common occurence would be residential-tertiary-secondary)

(25 Feb '16, 09:46) Piskvor

Also beware of streets with the same name but in different parts of a city. Using st_intersects and a rather more complicated query gets round this issue. You will also probably need to cast your st_union() with st_multi() so that everything becomes a multilinestring.

(25 Feb '16, 11:15) SK53 ♦

I don't have ways defined as geometric...

I have a table with the characteristics of the way, another one with the nodes that compose it and another with its tags..

(25 Feb '16, 16:13) eleena18

well that doesn't technically matter: in this case you are searching for connected graphs which share the same name attribute. You may find something I wrote about building such a graph from open data for streetlights describes a solution to a similar problem: http://sk53-osm.blogspot.co.uk/2013/04/maps-for-dogs-or-lamp-posts-in-chains.html. So use your way_nodes table to find 1) all interconnected ways, and 2) build graphs from this.

(27 Feb '16, 19:09) SK53 ♦


(29 Feb '16, 14:36) eleena18

No. You could theoretically map a street as a relation consisting of the various ways, but this is rare. Most streets are indeed mapped as multiple segments with the same name.

permanent link

answered 24 Feb '16, 16:03

Piskvor's gravatar image

accept rate: 37%

Your answer
toggle preview

Follow this question

By Email:

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



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:


question asked: 24 Feb '16, 15:51

question was seen: 2,378 times

last updated: 29 Feb '16, 14:36

powered by OSQA