[postgis-users] merging linestrings between roadcrossings

[Stephen Woodbridge]

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