[postgis-users] merging linestrings between roadcrossings

[Stephen Woodbridge]

If you use the code from pgRouting to create the unique vertex ids you 
can assign a tolerance to use when matching nodes. then I would write a 
stored procedure to apply the test and rules that are appropriate to 
your data model.

I don't have my postgis manual handy but there is a function the will 
allow you to merge two segments IIRC. If not it is pretty easy to 
extract the points of the two segments and create a new one combining 
the two sets.

-Steve

Daniel Grum wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users