Как рассчитать расстояние в select array?

Question

[Devolobnya]

Здравствуйте.
Есть таблица с треком

CREATE TABLE positions
 (
id INTEGER GENERATED BY DEFAULT AS IDENTITY,
date_add TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
latitude DOUBLE PRECISION NOT NULL,
longitude DOUBLE PRECISION NOT NULL,
current_point_n geometry
);
current_point_n=st_geometryfromtext('POINT('|| latitude ||' '|| longitude ||')',4326);

Пробовал выполнить

SELECT ST_Distance( ARRAY( SELECT current_point_n FROM positions ORDER BY id) );

но получаю ошибку

функция st_distance(geometry[]) не существует

Если пробовать выстроить linestring и рассчитать длину линии

SELECT st_Length(ST_MakeLine
( ARRAY( SELECT current_point_n FROM positions ORDER BY id) ));

То получаю неправильный результат.
Как можно правильно рассчитать сумму расстояний между точками в select?
Спасибо.

Comments

[Akina]

Как можно правильно рассчитать сумму расстояний между точками в select?

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

[Devolobnya]

Akina, А пример можно?

[Akina]

Devolobnya, CREATE TABLE уже есть, добавь к нему INSERT INTO с примером данных и требуемый результат для именно таких данных.

Данные - желательно представимые на листке в клетку, а не реальные геокоординаты, есссно.

[Devolobnya]

Akina,

insert into positions (date_add,latitude,longitude)values
('2021-01-07 01:00:00','55.715954','37.458170'),
('2021-01-07 02:00:00','55.715930,'37.458160'),
('2021-01-07 03:00:00','55.715950','37.458150');

и потом на

update positions set
current_point_n=st_geometryfromtext('POINT('|| latitude ||' '|| longitude ||')',4326);

поскольку нет возможности изменить insert

[Akina]

CREATE FUNCTION update_point() 
RETURNS trigger 
AS $update_point$
BEGIN
    NEW.current_point_n := st_geometryfromtext('POINT('|| NEW.latitude ||' '|| NEW.longitude ||')',4326);
    RETURN NEW;
END;
$update_point$ LANGUAGE plpgsql;

CREATE TRIGGER update_point
BEFORE INSERT OR UPDATE
ON positions
FOR EACH ROW EXECUTE PROCEDURE update_point();

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e007...

[Devolobnya]

Akina, Спасибо. Не додумался до триггера и функции.

[Devolobnya]

Akina, А результат расстояние в метрах
Select distance from position where ....
И условия выборки. Например по дате.

[Akina]

Devolobnya, длину пройденного пути считаешь, что ли? ну так

WITH cte AS (
    SELECT current_point_n <-> LAG(current_point_n) OVER (ORDER BY date_add) single_length
    FROM positions
    WHERE date_add BETWEEN '2021-01-07 00:00:00' AND '2021-01-07 23:59:59'
)
SELECT SUM(single_length) total_length
FROM cte;

[Devolobnya]

Akina, Спасибо! Все как и полагается.