Postgis reorder mixed up linestring chunks / max_segment_length(linestring)?

Question

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?

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.