[postgis-users] merging linestrings between roadcrossings

[Daniel Grum]

Stephen Woodbridge schrieb:
> Nicklas Avén wrote:
>>
>> Hallo
>>  
>> I have a quite big dataset (approx 1.2 mill rows) with roads. What I 
>> would like to to is merging all linestrings between crossings so I 
>> get one linestring between two crossings or between a crossing and 
>> the end of the road. Now there can be many small parts cut by a 
>> bridge or some border. For quality I also have to check so no 
>> linestrings are just passing a crossing too, but that is secondary 
>> because I don't think that is a problem with this dataset.
>>  
>> A while ago I saw a solution on this list which included merging all 
>> and dumping, but I think that will be a little heavy in this case.
>>  
>> I have been struggling some with recursive queries but I haven't 
>> found the way.
>> How to do it?
>
> Nicklas,
>
> If I understand what you want correctly, the problem is probably best 
> solved, by something like the following:
>
> 1) assign unique nodes to all segment end points and add start_node_id 
> and end_node_id to all your edges. look at pgRouting this have code to 
> do this already implemented.
>
> vertex_ids table
> uid, lat, lon
>
> 2) add a num_segments column to you unique node table
> 3) update vertex_ids set num_segments=(select count(*) from edges e 
> where e.start_node_id=uid or e.end_node_id=uid);
>
> now num_segments will tell you what you need to know
>
> num_segments
>  0   - should not happen
>  1   - these are dead end streets
>  2   - these are joinable segments
>  3+  - these are crossing segments
>
> For the joinable segments create a new joined segment use the segments
>   select * from edges
>    where e.start_node_id=<uid_in_question>
>       or e.end_node_id=<uid_in_question>;
>
> insert that, and delete the two old segments and fixup your node counts.
>
> -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
Hi

I think I have the same problem!

Are there any ideas how I can construct an SQL script that join 
streets-->merging linestrings that are not joined?

So a user often build street, that endnodes are not joined with the 
other street. So, if the endnode is an a buffer to a digitized 
street-->the sql will join this line?!

Is there a way to do this?!

--daniel