NOTICE: help.openstreetmap.org 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

Noisee
6111
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 ♦
82.5k927201273
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.name, planet_osm_line.way FROM planet_osm_line WHERE planet_osm_line.name 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
1

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 a.name='FirstName' AND b.name='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
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:

×535
×230
×3

question asked: 16 Apr '12, 11:16

question was seen: 4,364 times

last updated: 16 Apr '12, 12:08

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