SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 25.03.2016 10:27:55

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Группировка координат трассы

Добрый день! Прошу помочь.
Есть таблица с координатами трассы, скорость и время в точке.

CREATE TABLE route (
  ID bigint(20) NOT NULL,
  Lon varchar(12) NOT NULL,
  Lat varchar(12) NOT NULL,
  Speed int(11) NOT NULL,
  DateStan datetime NOT NULL,
  PRIMARY KEY (ID)
)...
Нужно сгруппировать одинаковые значения точек, когда машина стоит на месте
Group by Lon, Lat, Speed;
Но так чтобы повторные точки, когда машина возвращается на прежнее место, не попадали в группировку.
Например, есть значения (условные): Lon, Lat, Speed, DateStan
1. Lon1, Lat1, Speed1, DateStan1 -- Стоит
2. Lon1, Lat1, Speed1, DateStan2 -- Стоит
3. Lon2, Lat2, Speed2, DateStan3 -- Уехала
4. Lon1, Lat1, Speed1, DateStan4 -- Возвратилась в исходную точку

Даты всегда разные и DateStan1<DateStan2<DateStan3<DateStan4

В результате должно быть
1. Lon1, Lat1, Speed1
2. Lon2, Lat2, Speed2
3. Lon1, Lat1, Speed1

То есть, записи 1 и 2 сгруппировались, но запись 4 осталась отдельно, хотя у нее те-же значения, что и у записей 1 и 2, но другое время.

Неактивен

 

#2 26.03.2016 10:03:20

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка координат трассы

klow написал:

То есть, записи 1 и 2 сгруппировались, но запись 4 осталась отдельно, хотя у нее те-же значения, что и у записей 1 и 2, но другое время.

Время у записей 1 и 2 тоже разное. Важен факт, что в промежутке машина уезжала. Можно на лету с помощью пользовательских переменных генерировать доп поле, в котором объединять интервалы стоянки в один этап и уже потом делать группировку с учетом этого поля.(но пипец производительности)
Будет
Lon, Lat, Speed, DateStan, Dop
1. Lon1, Lat1, Speed1, DateStan1, 1 -- Стоит
2. Lon1, Lat1, Speed1, DateStan2, 1 -- Стоит
3. Lon2, Lat2, Speed2, DateStan3, 2 -- Уехала
4. Lon1, Lat1, Speed1, DateStan4, 3 -- Возвратилась в исходную точку

Group by Dop, Lon, Lat, Speed;

Неактивен

 

#3 26.03.2016 18:49:31

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Группировка координат трассы

Вариант, производительность не существенна, так как выборка будет на уже на предварительно отобранных записях, примерно, 1-3 тыс.
Спасибо!

Неактивен

 

#4 26.03.2016 19:57:56

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка координат трассы

Группировка то вам и не нужна, нужно из нескольких подряд идущих строк где машина стоит (speed=0) оставить первую.
Т.е. проходим всю таблицу по возрастанию времени и для строк speed=0 смотрим скорость в предыдущей строке и если тоже 0, то отбрасываем эту строку

MariaDB [test]> select * from route;
+----+------+------+-------+-----------+
| ID | Lon  | Lat  | Speed | DateStan  |
+----+------+------+-------+-----------+
|  1 | Lon1 | Lat1 |     0 | DateStan1 |
|  2 | Lon1 | Lat1 |     0 | DateStan2 |
|  3 | Lon2 | Lat2 |     4 | DateStan3 |
|  4 | Lon1 | Lat1 |     0 | DateStan4 |
+----+------+------+-------+-----------+
4 rows in set (0.00 sec)

MariaDB [test]> select id, lon, lat, speed, datestan from
(select if(speed=0,if(@s=speed,0,1),1) y, r.*, @s:=speed from route r, (select @s:=-1) x order by datestan) t
where y;
+----+------+------+-------+-----------+
| id | lon  | lat  | speed | datestan  |
+----+------+------+-------+-----------+
|  1 | Lon1 | Lat1 |     0 | DateStan1 |
|  3 | Lon2 | Lat2 |     4 | DateStan3 |
|  4 | Lon1 | Lat1 |     0 | DateStan4 |
+----+------+------+-------+-----------+
3 rows in set (0.00 sec)


или без переменных

MariaDB [test]> select * from route r where speed<>0 or
(speed=0 and speed<>ifnull((select speed from route where datestan < r.datestan order by datestan desc limit 1),5));
+----+------+------+-------+-----------+
| ID | Lon  | Lat  | Speed | DateStan  |
+----+------+------+-------+-----------+
|  1 | Lon1 | Lat1 |     0 | DateStan1 |
|  3 | Lon2 | Lat2 |     4 | DateStan3 |
|  4 | Lon1 | Lat1 |     0 | DateStan4 |
+----+------+------+-------+-----------+
3 rows in set (0.00 sec)

Неактивен

 

#5 26.03.2016 21:43:22

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Группировка координат трассы

Извини, немного протупил и не до конца рассказал задачу. Думал не важно. sad
Мне нужно, кроме того, получить начальное и конечное время стоянки.
Но в любом случае спасибо!
Думаю, что гораздо проще будет эту задачу переложить на приложение. Там это решается просто, но есть и свои недостатки.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson