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?

 

Thanks

/Nicklas

 

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
>
>