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 |
One Answer:
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:
If you explain more precisely what you are trying to achieve in the end then maybe we can help. answered 16 Apr '12, 11:33  Frederik Ramm ♦ |
Thanks for reply!
I need to find intersection of two street's.
What i do:
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.way2. 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.
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.
Many thanks, it works!:)
If i get more flexible solution, i'll paste it here.
Best regards, Alex.