[postgis-users] Merge lines

[Sufficool, Stanley]

My first crack at the issue was to use a 3 time self join on table2.st_startpoint and table1.st_endpoint with a 3rd table to check that no other 3rd line shared the endpoint. This ensured proper directionality, but there was no function as st_linemerge(table1.geom, table2.geom). Maybe I missed something. That query also would only have merged 2 coincident lines at a time.


> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Kevin Neufeld
> Sent: Wednesday, October 07, 2009 8:40 AM
> To: martin.fafard at geoprojection.com; PostGIS Users Discussion
> Subject: Re: [postgis-users] Merge lines
> 
> 
> Correct.  It looks like the below query will return 
> coincident lines along contiguous sections of 3 or more (it also 
> assumes your network is properly directionalized).
> 
> This is a problem of transitive closure.  I once solved this 
> iteratively by first finding all the degree-2 nodes in my 
> network, then one node at at time, merging the two lines that 
> share the node.
> 
> A modern approach might be to solve this using PostgreSQL's 
> new recursive SQL syntax. Cheers, Kevin
> 
> Martin Fafard wrote:
> > Stanley
> > 
> > Thank you
> > But your query merge two contiguous lines but not 3 or more 
> contiguous
> > lines...
> > 
> > Martin
> > 
> > Sufficool, Stanley a écrit :
> >> Does this look correct?
> >>
> >> select st_linemerge(st_collect(cs1.the_geom))
> >> from (
> >> 	select the_geom, st_startpoint(cs_s.the_geom) as linepoint
> >> 	from mylinetable cs_s
> >> 	UNION ALL 
> >> 	select the_geom, st_endpoint(cs_e.the_geom) as linepoint
> >> 	from mylinetable cs_e
> >>
> >> ) as cs1
> >> group by linepoint
> >> having count(*) = 2
> >>
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>