1

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?

  • would this help you out? stackoverflow.com/questions/10126187/… – Gery Jan 15 '14 at 12:23
  • So what is the solution and how we can get Linestring instead of multilinstring?? !showing my route I am working on Postgis with PgRouting. I want to display my route on my android mobile app. but this kind of route is not displayed properly because it is Multilinestring. Linestring is displayed properly so that's why I want to convert my multilinestring into Linestring How to do that ??? – osqureshi Apr 18 '14 at 13:53
4

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.

  • 1
    Good answer. reason why ST_Linemerge fails linestring cannot not touch or cross itself. One option to get longest possible lines is to st_dump and then try merge all lines using start/end points and discard all results which return multi geometry – simplexio Jan 20 '14 at 11:13
  • 1
    Great answer. now as i see the visual part, i understand why this doesn't work. This would workif each node would have ONE incoming edge and ONE outgoing edge. So that is why for example merging 4, 5 and 6 will not work. That means i have to deal with multilinestrings when looking up my data. Thanks – NovumCoder Jan 20 '14 at 18:04
0

I had the same problem, and this is my solution.

When you create the topology for pgRouting, you establish a tolerance for connect the lines, which it means that all the lines not necessarily touch each other. Having this in mind you can use a simply ST_LineMerge command if you use ST_SnapToGrid to the tolerance that you established earlier in the topology (or even more if it's necesary).

It will look something like this:

ST_LineMerge(ST_SnapToGrid(YOUR_MULTILINESTRING,0.00001))

I hope it will be helpful.

Excuse my bad English.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.