[postgis-users] Merge lines

[Martin Fafard]

Hi

I have created source and target with "assign_vertex_id" function from 
pgRouting.
Now, the query... I know that I need less than 3 times the same point 
number (source or target) and then, the same source or target number has 
to be on different ID = merge theses lines
merge1.jpg attached

Thank you for your help

Martin F

Sufficool, Stanley a écrit :
> 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
>>
>>     
>> ------------------------------------------------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com 
>> Version: 8.5.421 / Virus Database: 270.14.5/2419 - Release Date: 10/07/09 05:18:00
>>
>>     
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091007/7b84e778/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: merge1.jpg
Type: image/jpeg
Size: 137920 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091007/7b84e778/attachment.jpg>