I explain my geoprocess:

  1. start of a very long linestring
  2. Segmented linestring, 20 meter segments with ST_Segmentize, save the order of segments, ex 0 is start segment and 200 end segment
  3. I need to select certain segments within the linestring; selecting by order, ex: select segments between 10 and 20
  4. with the selected segements I need to join them in 1 single linestring


the union of the selected segments does not generate a valid segment according to the answer

Converting MultiLinestring to Linestring with PostGIS?

I usually use st_union to join the segments, and it works correctly while these are valid; the problem is that I have linestring not valid; this generate multilinestring

this is the case:

enter image description here

posible solution???

I think the best solution could be a recursive st_union that links all the segments in a certain order

generating step by step, singing linestring to generate a valid and simple linestring, this approach would be correct?

or can there be any tool in postgis that realizes this?

  • This is quite late a reply, but have you tried using st_collect() instead? – scabecks Feb 21 '19 at 4:40

Perhaps extract the individual points using ST_DumpPoints, and then connect them into a single LineString using ST_MakeLine(ST_Collect(pts)).


I found a few issues with this question and the data in the answer.

First, you said you are using ST_segmentize to split your linestring into 20 meter segments. It doesn't actually create a multilinestring composed of 20m linestrings. Read the documentation here

You could split your linestring into a multilinestring with 20 meter segments using ST_LineSubstring

Second: the multilinestring provided in the answer you referenced can be easily converted into a linestring with ST_Makeline, as dr_jts stated. If you use ST_LineSubtring to create a multilinestring of 20 meter segments, you could then select the segments, in order, as shown below, using the data from the link you referenced. To select the segments, you need to use ST_Dump, not ST_DumpPoints

select st_astext(st_makeline(geom)) from ST_Dump(ST_GeomFromText('MULTILINESTRING((-3.16420835153456 55.9269166007097,-3.164222 55.926918),(-3.1642070167833 55.9269296196706,-3.16421351659546 55.9268662214904),
(-3.16421351659546 55.9268662214904,-3.16421636372824 55.9268384509897),(-3.16421636372824 55.9268384509897,-3.16422182573761 55.9267851753802),(-3.16422182573761 55.9267851753802,-3.16422870102352 55.926718114886),
(-3.16422870102352 55.926718114886,-3.16423309121073 55.926675293667),(-3.16423309121073 55.926675293667,-3.16423565148822 55.9266503211093),(-3.16423565148822 55.9266503211093,-3.16424103159897 55.9265978443265),
(-3.16424103159897 55.9265978443265,-3.16424680776317 55.9265415044985),(-3.16424680776317 55.9265415044985,-3.16425267254583 55.9264843002995),(-3.16425267254583 55.9264843002995,-3.16425541048045 55.9264575949012),
(-3.16425541048045 55.9264575949012,-3.16426111146586 55.9264019883556),(-3.16426111146586 55.9264019883556,-3.1642667032531 55.9263474469124),(-3.1642667032531 55.9263474469124,-3.16426957768543 55.9263194101362),
(-3.16426957768543 55.9263194101362,-3.16427488261739 55.9262676666359),(-3.16427488261739 55.9262676666359,-3.16428009893088 55.9262167875066),(-3.16428009893088 55.9262167875066,-3.164282741107 55.9261910161221),
(-3.1642875546472 55.9261440655823,-3.164282741107 55.9261910161221),(-3.1642875546472 55.9261440655823,-3.16429466890915 55.9260746741522),(-3.16429466890915 55.9260746741522,-3.16430092974527 55.9260136069079),
(-3.16430092974527 55.9260136069079,-3.16430822838418 55.9259424170929),(-3.16430822838418 55.9259424170929,-3.16431547242401 55.925871759829),(-3.16431547242401 55.925871759829,-3.16431448732505 55.9258328901507),
(-3.16431770120536 55.9257327846001,-3.16431547242401 55.925871759829),(-3.164339 55.925777,-3.16431770120536 55.9257327846001))')) where path[1] > 10 and path[1] <20

If this doesn't work, can you post an example of a multilinestring where select st_astext(st_makeline(geom)) from ST_Dump(ST_GeomFromText('MULTILINESTRING fails?

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.