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
>[email protected]
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
