<html>
<head>
<title></title>
<meta name="GENERATOR" content="MSHTML 8.00.6001.18852"></meta>
</head>
<body>Hallo Stephen, thanks for answer
<div align="left"> </div>
<div align="left">I think I have been moving in the direction of your solution but with a little bit other approach.</div>
<div align="left"> </div>
<div align="left">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. </div>
<div align="left"> </div>
<div align="left">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:</div>
<div align="left"> </div>
<div align="left"><br />
update test set groupid = c.groupid from <br />
(<br />
select max(groupid) as groupid , array_agg(groupid) as gidarray from<br />
(<br />
select groupid, (st_dump(st_collect(startpoint(the_geom), endpoint(the_geom)))).geom as thepoint from <br />
(<br />
select groupid, st_union(the_geom) as the_geom from test group by groupid<br />
) a -- The lines unioned to not repeat the same thing again (this I guess is the weakest part<br />
) b --All the start and end points <br />
group by thepoint having count(*) =2<br />
) c -- the highest of the included gids<br />
where test.groupid = any (gidarray) -- all roadparts in this array should get this highest gidvalue</div>
<div align="left"> </div>
<div align="left">/Nicklas</div>
<div align="left"><br />
2009-12-01 Stephen Woodbridge wrote:<br />
<br />
Nicklas Avén wrote:<br />
>> <br />
>> Hallo<br />
>> <br />
>> I have a quite big dataset (approx 1.2 mill rows) with roads. What I <br />
>> would like to to is merging all linestrings between crossings so I get <br />
>> one linestring between two crossings or between a crossing and the end <br />
>> of the road. Now there can be many small parts cut by a bridge or some <br />
>> border. For quality I also have to check so no linestrings are just <br />
>> passing a crossing too, but that is secondary because I don't think that <br />
>> is a problem with this dataset.<br />
>> <br />
>> A while ago I saw a solution on this list which included merging all and <br />
>> dumping, but I think that will be a little heavy in this case.<br />
>> <br />
>> I have been struggling some with recursive queries but I haven't found <br />
>> the way.<br />
>> How to do it?<br />
><br />
>Nicklas,<br />
><br />
>If I understand what you want correctly, the problem is probably best <br />
>solved, by something like the following:<br />
><br />
>1) assign unique nodes to all segment end points and add start_node_id <br />
>and end_node_id to all your edges. look at pgRouting this have code to <br />
>do this already implemented.<br />
><br />
>vertex_ids table<br />
>uid, lat, lon<br />
><br />
>2) add a num_segments column to you unique node table<br />
>3) update vertex_ids set num_segments=(select count(*) from edges e <br />
>where e.start_node_id=uid or e.end_node_id=uid);<br />
><br />
>now num_segments will tell you what you need to know<br />
><br />
>num_segments<br />
> 0 - should not happen<br />
> 1 - these are dead end streets<br />
> 2 - these are joinable segments<br />
> 3+ - these are crossing segments<br />
><br />
>For the joinable segments create a new joined segment use the segments<br />
> select * from edges<br />
> where e.start_node_id=
<uid_in_question></uid_in_question><br />
> or e.end_node_id=
<uid_in_question></uid_in_question>;<br />
><br />
>insert that, and delete the two old segments and fixup your node counts.<br />
><br />
>-Steve<br />
>_______________________________________________<br />
>postgis-users mailing list<br />
>postgis-users@postgis.refractions.net<br />
>http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
><br />
></div>
</body>
</html>