Re: merging linestrings between roadcrossings
Nicklas Avén <nicklas.aven <at> jordogskog.no>
2009-12-01 15:21:45 GMT
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
2009-12-01 Stephen Woodbridge wrote:
Nicklas Avén wrote:
>> 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?
>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.
>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
> 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.
>postgis-users mailing list
>postgis-users <at> postgis.refractions.net
postgis-users mailing list
postgis-users <at> postgis.refractions.net