[postgis-users] merging linestrings between roadcrossings

[Nicklas Avén]

Hallo Stephen, thanks for answer I think I have been moving in the direction of your solution but with a little bit other approach. I don't want to modify my large table so I want to get some id (groupid) , identifying my new merged roads and then in the end merge them together to a new table.  What I have got is a sql-query that seems to be working on smaller tables, but I have to run it over and over again until no more rows are affected. But it does about what you mentionwed Stephen if I get things right: 
update test set groupid = c.groupid from 
 (
 select  max(groupid) as groupid , array_agg(groupid) as gidarray from
  (
  select groupid, (st_dump(st_collect(startpoint(the_geom), endpoint(the_geom)))).geom as thepoint from 
   (
   select groupid, st_union(the_geom) as the_geom from test group by groupid
   ) a -- The lines unioned to not repeat the same thing again (this I guess is the weakest part
  ) b --All the start and end points 
 group by thepoint having count(*) =2
 ) c -- the highest of the included gids
where test.groupid = any (gidarray) -- all roadparts in this array should get this highest gidvalue /Nicklas
2009-12-01 Stephen Woodbridge wrote:

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=
> or e.end_node_id=;
>
>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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091201/36fed77d/attachment.html>