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

In planet_osm_roads we have few LINESTRING()'s for one street. How can i get full street?

I'll try to merge all LINESTRING's, but the sequense is broken..

Sorry for my bad eng.

Many thx! Best regards, Alex.

asked 16 Apr '12, 11:16

Noisee's gravatar image

accept rate: 0%

What you want is not possible because streets often cannot be represented as simple linestrings. This is only one of many examples where the attempt will fail:

alter brauhof karlsruhe

If you explain more precisely what you are trying to achieve in the end then maybe we can help.

permanent link

answered 16 Apr '12, 11:33

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
accept rate: 23%

Thanks for reply!

I need to find intersection of two street's.

What i do:

  1. Get street as many LINESTRING's

SELECT planet_osm_line.osm_id,, planet_osm_line.way FROM planet_osm_line WHERE LIKE ('%<street_name>%') ORDER BY planet_osm_line.way

2. Try to union (not merge, my mistake!), out = MULTISTRING()

SELECT ST_AsText(ST_Union(ST_GeomFromText(''),ST_GeomFromText('')));

3. Convert MULTISTRING to LINESTRING (LINESTRING need for function ST_Intersection())

SELECT ST_AsText(ST_LineMerge(ST_GeomFromText('')));

Totally, sometimes street pieces (or short lines) go in wrong sequence, and stage 2 is fail.

I can't find data (or rule) in database, on what basis the road can be drawn correctly..

Best regards, Alex.

(16 Apr '12, 11:49) Noisee

To find the intersection between two streets of which you know the name, try

SELECT st_intersection(a.way,b.way) FROM planet_osm_line a, planet_osm_line b WHERE a.highway is not null AND b.highway is not null AND'FirstName' AND'SecondName' AND a.way && b.way;

This query will benefit from an index on the "name" column. It could occasionally return empty geometries, or more than one geometry.

(16 Apr '12, 11:58) Frederik Ramm ♦

Many thanks, it works!:)

If i get more flexible solution, i'll paste it here.

Best regards, Alex.

(16 Apr '12, 12:08) Noisee

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]( "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: 16 Apr '12, 11:16

question was seen: 4,473 times

last updated: 16 Apr '12, 12:08

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