[GIS] Cannot st_linemerge a multilinestring because its not properly ordered

linestringpostgis

i have a MultiLineString which i created by using st_union in many steps. Each of them are connected to each other according to their points, but the order is not correct. So ST_LineMerge is not able to merge it into a LineString.
How can i achieve this?

This is my MultiLineString in 900913 srid format:

MULTILINESTRING(
(752046.34 6609613.42,752031.98 6609603.16,751800.59 6609428.22,751682.81 6609321.4,751405.06 6609059.49,751383.56 6609036.3,751353.84 6609008.94,751213.87 6608872.92,751078.16 6608768.17,750848.05 6608639.95,750593.62 6608506.51),
(752459.37 6609800.24,752352.1 6609791.33,752316.78 6609780.56,752302.74 6609773.6,752224.21 6609734.72,752102.84 6609650.57,752046.34 6609613.42),
(752046.34 6609613.42,752053.33 6609604.93,752224.21 6609726.35,752304.53 6609767.89,752318.38 6609775.05,752352.05 6609784.75,752443.44 6609789.94,752459.37 6609800.24),
(754423.71 6610165.34,754263.85 6610124.54,754194.37 6610105.05,754118.84 6610083.63,754072.1 6610070.22,754003.31 6610046.07,753664.35 6609957.54,753508.22 6609934.87,753459.6 6609924.28,753070.6 6609785.38,752993.02 6609765.42,752888.42 6609764.66,752746.54 6609779.11,752592 6609790.56,752459.37 6609800.24),
(754537.9 6610199.51,754525.87 6610195.9,754502.91 6610189.16,754455.73 6610175.29,754423.71 6610165.34),
(754423.71 6610165.34,754428.17 6610146.76,754435.61 6610121.59,754440.55 6610115.05,754448.95 6610105.64,754543.51 6610132.54,754542.44 6610145.1,754537.9 6610199.51),
(754793.41 6610276.7,754777.65 6610271.55,754682.53 6610242.56,754674.56 6610240.39,754537.9 6610199.51),
(754831.57 6610283.65,754828.23 6610292.29,754822.8 6610297.51,754815.48 6610300.32,754806.5 6610299.7,754798.88 6610295.2,754794.56 6610289.05,754792.91 6610282.13,754793.41 6610276.7),
(754793.41 6610276.7,754795.53 6610271.39,754798.82 6610267.18,754804.33 6610263.46,754810.77 6610261.8,754816.12 6610262.11,754823.48 6610265.28,754827.91 6610269.59,754831.35 6610277.29,754831.57 6610283.65),
(757852.81 6610453.38,757666.67 6610445.76,756749.3 6610393.24,756710.68 6610390.93,756676.34 6610389.18,756670.67 6610388.9,756304.5 6610369.82,756271.02 6610368.25,755831.41 6610322.28,755569.29 6610278.21,755399.19 6610257.17,755335.29 6610252.29,755207.94 6610241.48,755199.37 6610242.7,755173.8 6610248.23,755083.04 6610276.62,755067.99 6610279.55,755043.75 6610283.1,755031.91 6610284.9,755017.83 6610287.06,754980.35 6610292.76,754899.88 6610286.53,754848.4 6610283.7,754831.57 6610283.65),
(759913.16 6610431.79,759788.76 6610427.97,759727.68 6610428.24,759544.19 6610433.82,759495.78 6610434.74,759154.64 6610446.1,758782.25 6610447.33,758760.91 6610447.85,758523.46 6610453.39,758502.34 6610453.78,758354.45 6610457.28,758169.92 6610458.46,758023.02 6610460.85,757941.21 6610459.66,757852.81 6610453.38),
(757852.81 6610453.38,757941.21 6610450.71,758017.65 6610445.92,758260.09 6610435.18,758333.49 6610432.92,758451.22 6610429.32,758505.98 6610427.64,758527.96 6610427.37,758618.78 6610425.75,759157.06 6610416.15,759186.22 6610416.28,759344.49 6610417.8,759516.74 6610413.12,759907.66 6610404.25)
)

It looks like PostGis cannot handle this. But my idea was to somehow reorder it, so intersecting points from two linestrings are neighbors in this multilinestring so ST_LineMerge works. But how to do this.
Probably writing a new function which splits up the MultiLineString and then concats them into a LineString by selecting the right LineString from the list until i end up with ONE LineString. But no idea how to split in a function and loop through it using PL/PGSQL.

Can anyone help?

Best Answer

After analysing your MultiLineString, I can say that st_linemerge cannot merge it, not because it's not properly ordered, but because it's impossible to be ordered and merged into a single LineString containing all its 12 parts.

Therefore, st_linemerge works as expected, as stated in st_LineMerge, and, because merging cannot be done, it returns the original MultiLineString. (see st_linemerge_original_multilinestring.sql)

Now, the explanation:

1) Your MultiLineString contains 12 parts (LineStrings):

enter image description here

2) To understand better how to reorder the parts (or if the parts can be reordered) I've choosed to work "visually", in QGIS. Here's a portion of the result, after I've finished to open the PostGIS table containing the MultiLineString: enter image description here

It's easy to see that this MultiLineString' shape, cannot pass the st_linemerge geometry validation tests. Here's the shapefile, if you want to see it in QIS.

3) I've gone further with splitting the MultiLineString in LineStrings, enter image description here

and after some makeover, i've obtained a more clearer picture of the twelve individual LineStrings: enter image description here

This is the shapefile, also, for you to analyse it in QIS.

To proove that st_linemerge works well, when you make a correct choice of the parts, please, include in the command the following LineStrings: 1,2,4,5,7,8,10 and 12. Here's the sql query: st_linemerge_only_eight_linestrings.sql Running this, you'll get a single, long, merged LineString.

Of course, there are a lot of combinations, but I see no way to reorder the actual twelve parts, to fit together into a valid LineString.

Related Question