[postgis-users] Merging 2 Linestring to get another Linestring

Thu Jan 10 01:08:34 PST 2008

Hi Kevin,

Thank you for your answer. I still have an open problem:

I was able to get a LINESTRING() when I used the geometry directly with
GeometryFromText(), but when I used the geometry directly I again got a
MULTILINESTRING() instead of a LINESTRING().

Here are my two geometries:

SELECT gid,AsText(the_geom) FROM admin.plz WHERE gid = 15 OR gid = 16;

15;"LINESTRING(697787.741 247263.451,697795.643 247285.35,697800.40623846
247265.71379369,697840.22672596 247230.52556801,697857.41357564
247211.64600192,697863.33904042 247202.1075178,697868.885915
247193.74698711,697893.1506172 247173.90833164,697904.60984576
247169.32667165)"

16;"LINESTRING(697904.60984576 247169.32667165,697937.81014344
247161.4233732,697983.59952742 247136.38272159,698017.72201345
247107.76725733,698036.84324328 247089.0631319,698038.242
247091.136,698095.594 247176.128,698133.958 247133.077,698180.269
247094.706,698219.524 247143.072,698231.671 247157.9,698233.2746542
247159.85760142,698175.00856403 247197.61458341,698163.3329707
247213.81319548,698152.70068758 247228.53139008,698152.892
247228.722,698155.343 247231.164,698144.14486829
247242.40781526,698146.526 247244.695,698200.74 247296.77,698216.05389007
247311.81569585,698222.168 247310.99,698243.567 247310.119,698263.273
247320.271,698284.946 247330.848,698297.835 247335.614,698312.43749951
247339.0551761,698313.574 247339.323,698285.096 247378.915,698252.121
247424.758,698262.313 247429.797,698268.42 247434.205,698287.356
247444.225,698293.723 247447.371,698317.925 247459.33,698319.393
247460.056,698352.073 247476.204,698365.703 247444.85,698379.644
247412.853,698393.578 247380.872,698407.529 247348.734,698421.483
247316.59,698426.725 247318.873,698437.00424285
247295.30551693,698450.40507561 247293.85890908,698466.35518599
247300.51456377,698494.06629765 247308.55414883,698498.88241182
247309.41108415,698551.37595726 247318.7513057,698578.70982688
247326.19051288,698607.8062372 247337.32603572,698659.14969397
247355.5832141,698668.50284867 247359.12136294,698687.7687112
247370.76779766,698688.7535124 247371.68476191,698695.08735303
247377.58230291,698720.60897496 247402.31714286,698727.44597887
247408.23890492,698730.68588476 247411.21739393,698729.24798758
247421.79729034,698736.415 247418.128,698743.275 247423.051)"

Here is my first SQL (like you proposed):

SELECT
AsText(ST_LineMerge(ST_Collect(GeometryFromText('LINESTRING(698743.275
247423.051,698736.415 247418.128,698729.24798758
247421.79729034,698730.68588476 247411.21739393,698727.44597887
247408.23890492,698720.60897496 247402.31714286,698695.08735303
247377.58230291,698688.7535124 247371.68476191,698687.7687112
247370.76779766,698668.50284867 247359.12136294,698659.14969397
247355.5832141,698607.8062372 247337.32603572,698578.70982688
247326.19051288,698551.37595726 247318.7513057,698498.88241182
247309.41108415,698494.06629765 247308.55414883,698466.35518599
247300.51456377,698450.40507561 247293.85890908,698437.00424285
247295.30551693,698426.725 247318.873,698421.483 247316.59,698407.529
247348.734,698393.578 247380.872,698379.644 247412.853,698365.703
247444.85,698352.073 247476.204,698319.393 247460.056,698317.925
247459.33,698293.723 247447.371,698287.356 247444.225,698268.42
247434.205,698262.313 247429.797,698252.121 247424.758,698285.096
247378.915,698313.574 247339.323,698312.43749951 247339.0551761,698297.835
247335.614,698284.946 247330.848,698263.273 247320.271,698243.567
247310.119,698222.168 247310.99,698216.05389007 247311.81569585,698200.74
247296.77,698146.526 247244.695,698144.14486829 247242.40781526,698155.343
247231.164,698152.892 247228.722,698152.70068758
247228.53139008,698163.3329707 247213.81319548,698175.00856403
247197.61458341,698233.2746542 247159.85760142,698231.671
247157.9,698219.524 247143.072,698180.269 247094.706,698133.958
247133.077,698095.594 247176.128,698038.242 247091.136,698036.84324328
247089.0631319,698017.72201345 247107.76725733,697983.59952742
247136.38272159,697937.81014344 247161.4233732,697904.60984576
247169.32667165)',21781),GeometryFromText('LINESTRING(697904.60984576
247169.32667165,697893.1506172 247173.90833164,697868.885915
247193.74698711,697863.33904042 247202.1075178,697857.41357564
247211.64600192,697840.22672596 247230.52556801,697800.40623846
247265.71379369,697795.643 247285.35,697787.741 247263.451)',21781))));

Result=LINESTRING(...) as we expected.

Here is my second SQL:

SELECT gid,AsText(the_geom) FROM admin.plz WHERE gid = 15 OR gid = 16;

Result: MULTILINESTRING(...)

I am confused why the first method is returning a LINESTRING() and the
second method is not. They use exactly the same input data. There must be
some subtile differences in the two SQL commands ...

Thank you for shedding some additional light on my situation.

Andreas

> ST_Collect() the geometries together and then use ST_LineMerge() to
> merge the multilinestring.
>
> This yields a single LineString:
> SELECT AsText(ST_LineMerge(ST_Collect('LINESTRING(...)'::geometry,
> 'LINESTRING(...)'::geometry)));
>
> Cheers,
> Kevin
>
> Andreas Neumann wrote:
>> Hi,
>>
>> I am trying to merge two adjacent LINESTRING, which share one
>> coordinate.
>> I tried several methods including ST_LineMerge(), ST_Union(),
>> ST_COLLECT()
>> but I always get a MULTILINESTRING (with 2 lines) and not one
>> LINESTRING.
>> I am sure that the two LINESTRINGS share one coordinate.
>>
>> Here are the two LINESTRINGS:
>> LINESTRING(698743.275 247423.051,698736.415 247418.128,698729.24798758
>> 247421.79729034,698730.68588476 247411.21739393,698727.44597887
>> 247408.23890492,698720.60897496 247402.31714286,698695.08735303
>> 247377.58230291,698688.7535124 247371.68476191,698687.7687112
>> 247370.76779766,698668.50284867 247359.12136294,698659.14969397
>> 247355.5832141,698607.8062372 247337.32603572,698578.70982688
>> 247326.19051288,698551.37595726 247318.7513057,698498.88241182
>> 247309.41108415,698494.06629765 247308.55414883,698466.35518599
>> 247300.51456377,698450.40507561 247293.85890908,698437.00424285
>> 247295.30551693,698426.725 247318.873,698421.483 247316.59,698407.529
>> 247348.734,698393.578 247380.872,698379.644 247412.853,698365.703
>> 247444.85,698352.073 247476.204,698319.393 247460.056,698317.925
>> 247459.33,698293.723 247447.371,698287.356 247444.225,698268.42
>> 247434.205,698262.313 247429.797,698252.121 247424.758,698285.096
>> 247378.915,698313.574 247339.323,698312.43749951
>> 247339.0551761,698297.835
>> 247335.614,698284.946 247330.848,698263.273 247320.271,698243.567
>> 247310.119,698222.168 247310.99,698216.05389007
>> 247311.81569585,698200.74
>> 247296.77,698146.526 247244.695,698144.14486829
>> 247242.40781526,698155.343
>> 247231.164,698152.892 247228.722,698152.70068758
>> 247228.53139008,698163.3329707 247213.81319548,698175.00856403
>> 247197.61458341,698233.2746542 247159.85760142,698231.671
>> 247157.9,698219.524 247143.072,698180.269 247094.706,698133.958
>> 247133.077,698095.594 247176.128,698038.242 247091.136,698036.84324328
>> 247089.0631319,698017.72201345 247107.76725733,697983.59952742
>> 247136.38272159,697937.81014344 247161.4233732,697904.60984576
>> 247169.32667165)
>>
>> and
>>
>> LINESTRING(697904.60984576 247169.32667165,697893.1506172
>> 247173.90833164,697868.885915 247193.74698711,697863.33904042
>> 247202.1075178,697857.41357564 247211.64600192,697840.22672596
>> 247230.52556801,697800.40623846 247265.71379369,697795.643
>> 247285.35,697787.741 247263.451)
>>
>> and here is one of the commands I tried:
>> SELECT ST_AsText(ST_LineMerge(geom)) FROM (SELECT
>> ST_Union(ST_Reverse(the_geom)) AS geom FROM admin.plz WHERE gid > 14) AS
>> the_geom;
>>
>> There are only 16 lines in the db and the gids 15 and 16 are the two I
>> want to merge.
>>
>> Thanks for any hints how I can get a sewed together LINESTRING.
>>
>> Andreas
>>
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

-- 
Andreas Neumann
Böschacherstrasse 6, CH-8624 Grüt/Gossau, Switzerland
Email: a.neumann at carto.net, Web:
* http://www.carto.net/ (Carto and SVG resources)
* http://www.carto.net/neumann/ (personal page)
* http://www.svgopen.org/ (SVG Open Conference)
* http://www.geofoto.ch/ (Georeferenced Photos of Switzerland)