Как получить дистанцию по множествам точек в Mysql?

Question

[Devolobnya]

Добрый день.
Есть таблица с id(ai), таймштампом(now()), широтой(VAR(10)), долготой(VAR(10)),точкой(point).
Каким запросом можно получить дистанцию(сумму расстояний st_distance) по интервалу времени?
Спасибо!

Comments

[d'Ivan]

А какие попытки были?

[Akina]

Вообще-то мало что понятно.

Что за точка, которая point, в таблице. и как она соотносится с широтой и долготой из той же записи?
Расстояние st_distance - между чем и чем?

Замените вербальное описание таблицы на полный CREATE TABLE. Приведите пример данных (5-10 записей) в виде INSERT INTO. Покажите требуемый результат для именно этих данных, с пояснениями, почему так.

[Devolobnya]

Akina,
Добрый день.
CREATE TABLE `tc_pos`
(
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`deviceid` INT(11) NOT NULL,
`dateadd` DATETIME NULL DEFAULT current_timestamp(),
`latitude` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',,
`longitude` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',,
`currentpoint` POINT NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE

)
Записи в таблице.
1,2,'2020-07-15 01:01:00','55.790813', '37.093059';
2,2,'2020-07-15 01:01:50','55.925980', '37.206356';
3,2,'2020-07-15 01:02:00','55.925980', '37.206356';
4,2,'2020-07-15 01:02:39','55.948333', '37.044307';

Необходимо получить расстояние по условию dateadd between (т.е. интервал времени)
Т.е. сложить расстояние между id=1 и id=2, id=2 и id=3,id=3 и id=4 используя для расчета st_distance.
Результат - dateadd between '2020-07-15 01:01:00' and '2020-07-15 01:02:39' = 3344 метров
currentpoint это представление в виде типа point широты и долготы

Дмитрий Написал рабочее решение, но долгое при большом количестве записей..

[Akina]

Devolobnya, вот опять. В структуре есть поле currentpoint, а в данных вместо него большой кукиш... две запятые подряд... неужели нельзя с первого раза выложить РАБОТАЮЩИЙ код?

[Дмитрий]

Devolobnya, explain запроса? Вангую что нет индекса по датам

[Devolobnya]

Akina,
там бинарник.
тип point geometry

[Devolobnya]

Дмитрий,
Добавил
INDEX `position_deviceid_dateadd` (`dateadd`, `fixtime`) USING BTREE
Получил такой explain

|_. id |_. select_type |_. table |_. type |_. possible_keys |_. key |_. key_len |_. ref |_. rows |_. Extra |
| 1 | PRIMARY | tc_pos | ref | position_deviceid_dateadd | position_deviceid_dateadd | 4 | const | 126758 | Using index condition |
| 3 | DEPENDENT SUBQUERY | next | ref | position_deviceid_dateadd | position_deviceid_dateadd | 4 | const | 126758 | Using index condition; Using where |

[Дмитрий]

Devolobnya,
1. а что за fixtime и зачем?
2. Ну учитывая что он начал его использовать - то должно стать быстрее.
3. А если бы вы в запросе указывали версию мускул то возможно были другие решения - в 8 через оконные функции, там наверное было бы быстрее

[Devolobnya]

Дмитрий,
Сорри.
Очепятка по п1
INDEX `position_deviceid_dateadd` (`deviceid`,`dateadd`) USING BTREE;

п2. Почти минуту выбирает за сутки

п3. mysql Ver 15.1 Distrib 10.6.7-MariaDB,

[Дмитрий]

п2. Почти минуту выбирает за сутки

Это информация ни о чем - за сутки может быть 10 миллионов записей, может быть 2.

[Devolobnya]

Дмитрий,
За сутки в среднем накапливается около 5000 записей

[Дмитрий]

Я хз есть ли оконные функции в этой версии MariaDb.
Но в Mysql 8 можно так.

select sum(distance) from (select
    ST_Distance_Sphere(
	  	point(points.longtitude, points.latitude),
		point(
	  		lead(longtitude) over( order by points.date),
			lead(latitude) over( order by points.date)
		)
	) as distance
from points
		where date between '2022-07-01 00:00:00' and '2022-07-05 00:00:00'
order by date asc) as t;

Тогда depended query должен исчезнуть.

З.Ы. Ну и стоит только перегнать координаты в Point. Если уж вы собираетесь в базе юзать gis - дык надо юзать поля для этого предназначенные.

[Devolobnya]

Дмитрий,
Супер.
Самое оно.
Спасибо!

Answer 1

[Дмитрий]

Как то так?

select sum(distance) from (select 
	ST_Distance_Sphere(
	  point(points.longtitude, points.latitude), 
	  (select 
	   		point(next.longtitude, next.latitude) 
	   from 
	   		points as next 
	   where 
	   		next.date > points.date 
	   and
	   		next.date between '2022-07-01 00:00:00' and '2022-07-05 00:00:00'
	   order by date asc limit 1)) as distance 
from points
		where date between '2022-07-01 00:00:00' and '2022-07-05 00:00:00'
 order by date asc) as t;

Comments

[Devolobnya]

Спасибо.
Самое оно!