I imported ~1000 paths from somewhere into my postgis database to a linestring field.

I had the problem that the paths were divided into chunks, and those chunks were mixed up in some cases.

Suppose a linestring that was divided at point number 50 and 70:

  1. Chunk A: points 1-50
  2. Chunk B: points 51-70
  3. Chunk C: points 71-100

When I migrated that into my database, they got mixed, so the resulted linestring could end up like this:

  1. Chunk A: points 1-50
  2. Chunk C: points 71-100
  3. Chunk B: points 51-70

So that produces a jump from 50 to 71 and another one from 100 to 51

I want to be able to reorder those chunks so I would like to construct a SQL query to detect which paths are mixed, then I could manually rearrange them.

It would be desirable to have a SQL update query to solve this problem, but I think that detection is easier (I presume there are ~5% or less of paths with errors)

EDIT: I think the script for detection could check if a path contains a pair of consecutive points too far away. Maybe a SQL that orders paths from the path that contains the longest segment, would be good.

How could I make a function to get the length of the max segment in a linestring?


1 Answer 1


This is a link to the same question with it's corresponding answer on stackoverflow:

Postgis reorder mixed up linestring chunks / max_segment_length(linestring)

So if someone gets here, there is the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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