Постгис переупорядочить смешанные фрагменты строки / max_segment_length(строка строки)?

Question

Я импортировал ~1000 путей откуда-то в мою базу данных postgis в поле строки.

(РЕДАКТИРОВАТЬ) Мой стол такой

+---------+---------------+------------------+
| id(int) | name(varchar) | path(LINESTRING) |
+---------+---------------+------------------+
| 123     | foo           | 000002...        |
| 124     | bar           | 000002...        |

У меня была проблема, что каждый из путей был разделен на куски, и эти куски были перепутаны в некоторых случаях.

Предположим, что линейная линия была разделена в точках 50 и 70:

1. Кусок А: очки 1-50
2. Кусок Б: очки 51-70
3. Кусок C: баллы 71-100

Когда я перенес это в свою базу данных, они смешались, поэтому полученная строка может в итоге выглядеть так:

1. Кусок А: очки 1-50
2. Кусок C: баллы 71-100
3. Кусок Б: очки 51-70

Так что получается скачок с 50 до 71 и еще один с 100 до 51

(РЕДАКТИРОВАТЬ) Когда я импортировал эти пути, разделенные на куски, я предположил, что они были упорядочены, но факт был в том, что некоторые были смешаны, и из-за этого некоторые из моих строк были упорядочены с точками, как во втором примере.

Я хочу иметь возможность переупорядочить эти чанки (точек), поэтому я хотел бы создать SQL-запрос, чтобы определить, какие пути имеют смешанные точки, а затем я мог бы вручную (с помощью инструмента, созданного с использованием открытых слоев) переставить их.

Было бы желательно иметь запрос на обновление SQL, чтобы решить эту проблему, но я думаю, что обнаружение легче (я предполагаю, что ~5% или меньше путей с ошибками)

РЕДАКТИРОВАТЬ 3: Я думаю, что скрипт для обнаружения может проверить, содержит ли путь пару последовательных точек слишком далеко. Может быть, SQL, который упорядочивает пути от пути, который содержит самый длинный сегмент, был бы хорош.

Как я могу сделать функцию, чтобы получить длину максимального сегмента в строке?

Здесь я показываю пример: Вот как это в базе данных

Вот как я хочу это исправить

EDIT4: как я планировал в EDIT3, можно было написать функцию, чтобы найти самое длинное расстояние между двумя последовательными точками в строке строк, итерируя по точкам строки, используя ST_NPoints() и ST_PointN (), затем можно было бы сделать запрос, чтобы упорядочить пути с этим самым длинным расстоянием. Весьма вероятно, что линейные линии, которые имеют слишком большое расстояние, представляют описанную проблему. Таким образом я смог бы их обнаружить и исправить вручную.

Результат обнаружения SQL будет выглядеть примерно так:

                                             |ordered by this|
+---------+---------------+------------------+---------------+
| id(int) | name(varchar) | path(LINESTRING) |  msbtcp(int)  |
+---------+---------------+------------------+---------------+
| 123     | foo           | 000002...        | 1000          |
| 124     | bar           | 000002...        | 800           |

* msbtcp будет результатом функции: max_separation_between_two_consecutive_points(path)

Answer 1

Это звучит немного запутанно, однако, если вы только после максимального расстояния между двумя последовательными точками в строке:

CREATE OR REPLACE FUNCTION max_distance_in_linestring(line geometry) RETURNS float as $BODY$
DECLARE
    i integer;
    n integer;
    d float;
    m float;
BEGIN
    d := 0;
    n := ST_NPoints(line);
    i := 2;
    LOOP
        EXIT WHEN i >= n;
        m := ST_Distance(ST_PointN(line,i-1),ST_PointN(line,i));
        -- use for lon,lats:
        -- m := ST_Distance(ST_PointN(line,i-1)::geography,ST_PointN(line,i)::geography);
        IF m > d THEN
            d := m;
        END IF;
        i := i + 1;
    END LOOP;
    RETURN d;
END;
$BODY$
LANGUAGE plpgsql;

SELECT max_distance_in_linestring('LINESTRING(0 0, 1 1, 2 2)'::geometry);
SELECT max_distance_in_linestring('LINESTRING(0 0, 4 3, 2 2)'::geometry);

Вы можете переадресовать вызовы ST_PointN::geography для получения расстояния в метрах.

SQL будет выглядеть так:

SELECT
  name, path 
FROM
  paths
ORDER BY
  max_distance_in_linestring(path) DESC

Answer 2

Что вы хотите знать, так это то, как сегменты связаны, поэтому вам нужно сравнить первую и последнюю точку каждого сегмента со всеми остальными сегментами:

SELECT foo.gid as segment_a, bar.gid as segment_b
    FROM 
        segments AS foo, 
        (SELECT the_geom, gid FROM segments) AS bar
    WHERE 
        bar.gid != foo.gid AND ( -- avoid same segments  
        ST_DWithin( 
                ST_GeometryN(foo.the_geom,  ST_NumGeometries(foo.the_geom)) , -- last from foo
                ST_GeometryN(bar.the_geom,  ST_NumGeometries(bar.the_geom)) , -- last from bar
                0.00005 ) OR -- precision, depends of your SRID
        ST_DWithin( 
                ST_GeometryN(foo.the_geom,  1) , -- first from foo (start = 1 index)
                ST_GeometryN(bar.the_geom,  ST_NumGeometries(bar.the_geom)) , -- last from bar
                0.00005 ) OR -- precision, depends of your SRID
        ST_DWithin( 
                ST_GeometryN(foo.the_geom,  ST_NumGeometries(foo.the_geom)) , -- last from foo
                ST_GeometryN(bar.the_geom,  1) , -- first from bar
                0.00005 ) OR -- precision, depends of your SRID
        ST_DWithin( 
                ST_GeometryN(foo.the_geom,  1) , -- first from foo 
                ST_GeometryN(bar.the_geom,  1) , -- first from bar
                0.00005 ) ) -- precision, depends of your SRID

вуаля...! Вы можете сохранить эти ссылки в новой таблице и объединить связанные сегменты с помощью ST_Union.