SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 26.03.2008 14:04:39

Руслан
Участник
Зарегистрирован: 26.03.2008
Сообщений: 13

Помогите посчитать расстояние(соствить запрос)

Есть таблица поля N(float),E(float) - это координаты широта, долгота.
Необходимо определить пройденный путь, т.е. точку ((N1-N0)в квадрате + (E1-E0)в квадрате)из всего этого извлекаем корень, т.е. получаем гипотенузу - расстояние + тоже самое но для точек E1,E2,N1,N2 и т.д. соответственно group by date. Спасибо.


Вот пример таблицы
  N         E        V    time        date   
6055,6158    7635,3448    8,37    01:58:45    26.3.2008   
6055,5918    7635,3271    1,16    01:59:14    26.3.2008   
6055,6488    7635,0718    24,06    01:59:45    26.3.2008   
6055,6652    7635,009    0,22    02:00:15    26.3.2008   
6055,7758    7635,1027    33,4    02:00:45    26.3.2008   
6056,0301    7635,3751    30,41    02:01:15    26.3.2008   
6056,0983    7635,3962    12,54    02:01:44    26.3.2008   
6056,1721    7635,0806    25,16    02:02:15    26.3.2008   
6056,2663    7634,7016    32,2    02:02:45    26.3.2008   
6056,383    7634,2131    31,68    02:03:15    26.3.2008   
6056,3917    7634,0426    0,09    02:03:45    26.3.2008   
6056,4995    7634,0017    30,33    02:04:15    26.3.2008   
6056,7174    7633,9876    1,69    02:04:45    26.3.2008   
6056,723    7633,9866    10,16    02:05:15    26.3.2008   
6056,9343    7633,848    30,09    02:05:45    26.3.2008   
6057,0804    7633,6895    16,68    02:06:15    26.3.2008   
6057,2065    7633,55        17,82    02:06:45    26.3.2008   
6057,2166    7633,3815    19,78    02:07:15    26.3.2008   
6057,177    7633,2608    0,11    02:07:45    26.3.2008   
6057,1766    7633,2619    0,08    02:08:16    26.3.2008   
6057,1765    7633,2625    0,02    02:08:46    26.3.2008   
6057,1762    7633,2632    0,04    02:09:15    26.3.2008   
6057,182    7633,2537    7,42    02:09:45    26.3.2008   
6057,1599    7632,9938    19,44    02:10:15    26.3.2008   
6057,0673    7632,864    17,96    02:10:45    26.3.2008   
6056,9981    7632,8603    9,56    02:11:15    26.3.2008   
6056,9294    7632,8609    0,1    02:11:45    26.3.2008   
6056,9117    7632,5609    27,05    02:12:15    26.3.2008   
6056,9033    7632,1646    20,06    02:12:45    26.3.2008   
6056,8985    7631,9141    9,97    02:13:11    26.3.2008

Неактивен

 

#2 26.03.2008 14:51:25

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите посчитать расстояние(соствить запрос)

Если нужно расстояние за день, то надо воспользовать аггрегирующей функцией SUM()

SELECT SUM( sqrt((N1-N0)*(N1-N0)+ (E1-E0)*(E1-E0)) ) FROM таблица GROUP BY date;

Неактивен

 

#3 26.03.2008 15:07:21

Руслан
Участник
Зарегистрирован: 26.03.2008
Сообщений: 13

Re: Помогите посчитать расстояние(соствить запрос)

Да но как обозначить в запросе точки N1,N2,N3 и т.д. есть поле n как  запросе обозначить расст(n1-n0, e1-e0) + расст(n2-n1,e2-e1) +...... расст(nk - n (k-1),ek - e (k-1)). В вашем запросе фигурирует только n0 и n1, может я неправильно вопрос задал.

Неактивен

 

#4 26.03.2008 15:30:49

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите посчитать расстояние(соствить запрос)

Ясно, у Вас в таблице только одна координата. Предлагаю усовершенствовать таблицу, чтобы в ней хранились не точки, а отрезки:
ALTER TABLE таблица ADD N1 FLOAT;
UPDATE таблица t1 SET N1 = IFNULL( (SELECT N FROM таблица t2 WHERE t2.date=t1.date and t2.time>t1.time ORDER BY t2.time ASC LIMIT 1), t1.N)
В N1 запишется координата следующей точки, а если последняя за день (IFNULL), то та же координата.
Аналогично создайте E1. Затем запрос, который выше, но N вместо N0. В принципе подзапрос можно втиснуть и в запрос, который приведен выше.

Неактивен

 

#5 26.03.2008 15:55:44

Руслан
Участник
Зарегистрирован: 26.03.2008
Сообщений: 13

Re: Помогите посчитать расстояние(соствить запрос)

Слабоват я в этих тонкостях. Точки мне необходимо хранить в том виде как они есть. В таблице на верху, есть два столбца N и E берем
первую запись N0=6055,6158    E0=7635,3448 вычитаем из нее вторую  E1=6055,5918    N1=7635,3271 (вычет соответственно E0-E1 и N0-N1 или наоборот не суть важно квадрат знак уничтожит), получается соответственно  E0-E1=6055,6158-6055,5918=0,024   N0-N1=7635,3448 - 7635,3271 =0,0177, возводим разности в квадраты складываем их и извлекаем из них корень, далее проделываем тоже самое для N2,N1 и E2,E1 и складываем с предыдущим отрезком и так до конца таблицы, т.е. груп бай дэйт

Неактивен

 

#6 26.03.2008 15:58:45

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите посчитать расстояние(соствить запрос)

в MySQL нельзя выполнять операции над числами из разных строк. GROUP BY также оперирует строками.

Либо изменяете структуру таблицы, хотя бы на время расчетов, либо используете подзапрос для получение координаты следующей точки.

Неактивен

 

#7 26.03.2008 16:03:32

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите посчитать расстояние(соствить запрос)

SELECT SUM( sqrt( POW(N1-N, 2) + POW(E1-E, 2) ) ) FROM таблица t1 GROUP BY date;

в запрос выше вместо N1 подставляете IFNULL(SELECT N FROM таблица t2 WHERE t2.date=t1.date AND t2.time>t1.time ORDER BY t2.time LIMIT 1), t1.N), вместо E1 подставляете IFNULL(SELECT E FROM таблица t2 WHERE t2.date=t1.date AND t2.time>t1.time ORDER BY t2.time LIMIT 1), t1.E)

Неактивен

 

#8 26.03.2008 16:04:35

Руслан
Участник
Зарегистрирован: 26.03.2008
Сообщений: 13

Re: Помогите посчитать расстояние(соствить запрос)

Хорошо, а Вы бы не могли накидать как приблизно это будет выглядеть

Неактивен

 

#9 26.03.2008 16:11:08

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите посчитать расстояние(соствить запрос)

SELECT SUM( sqrt( POW(
IFNULL(SELECT N FROM таблица t2 WHERE t2.date=t1.date AND t2.time>t1.time ORDER BY t2.time LIMIT 1)
-N, 2) + POW(
IFNULL(SELECT E FROM таблица t2 WHERE t2.date=t1.date AND t2.time>t1.time ORDER BY t2.time LIMIT 1), t1.E)
-E, 2) ) ) FROM таблица t1 GROUP BY date;

Слово таблица замените на название Вашей таблицы

Неактивен

 

#10 26.03.2008 16:17:08

Руслан
Участник
Зарегистрирован: 26.03.2008
Сообщений: 13

Re: Помогите посчитать расстояние(соствить запрос)

Спасибо, подскажите, возможно ли чтобы сервер баз данных проделывал сам такую операцию скажем каждые N минут и держал эту циферку где нибудь в другой таблице, или же целесообразнее это делать один раз при запросе. Я исхожу из того что если строк будет слишком много скажем тысяч 30-40, сколько это может занять времени.

Неактивен

 

#11 26.03.2008 16:24:38

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите посчитать расстояние(соствить запрос)

Можно, например
DROP TABLE IF EXISTS new_table
CREATE TABLE new_table SELECT ... далее запрос

и выпоонять это по cron
Проще поменять структуру таблицы, пост #4

Неактивен

 

#12 26.03.2008 19:00:06

Руслан
Участник
Зарегистрирован: 26.03.2008
Сообщений: 13

Re: Помогите посчитать расстояние(соствить запрос)

Вот только понять не могу в запросе две таблицы а у меня одна, если поставить одну и ту же таблицу то абра кадабра выходит, как отлчить две даты и времени

Неактивен

 

#13 26.03.2008 20:37:35

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите посчитать расстояние(соствить запрос)

Если Вы про длинный запрос, то он должен работать, если Вы укажете одну и ту же таблицу. Дело в том, что таблица в подзапросе видна только в пределах данного подзапроса, а внутри этого подзапроса различение происходит с помощью алиасов t1 и t2

Неактивен

 

#14 26.03.2008 20:45:12

Руслан
Участник
Зарегистрирован: 26.03.2008
Сообщений: 13

Re: Помогите посчитать расстояние(соствить запрос)

Выдал нолики, правда 9 строк как и количесвто дат, как алиасы назначить

Неактивен

 

#15 26.03.2008 20:51:13

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите посчитать расстояние(соствить запрос)

Алиасы уже назначены smile Это слова t1 и t2 после имени таблицы

Вот так, правильно

SELECT SUM( sqrt( POW(
IFNULL((SELECT N FROM таблица t2 WHERE t2.date=t1.date AND t2.time>t1.time ORDER BY t2.time LIMIT 1), t1.N)
-N, 2) + POW(
IFNULL((SELECT E FROM таблица t2 WHERE t2.date=t1.date AND t2.time>t1.time ORDER BY t2.time LIMIT 1), t1.E)
-E, 2) ) ) FROM таблица t1 GROUP BY date;

Неактивен

 

#16 26.03.2008 21:00:31

Руслан
Участник
Зарегистрирован: 26.03.2008
Сообщений: 13

Re: Помогите посчитать расстояние(соствить запрос)

Трындец запрос занял секунд 7-8 это при 1440 строк, а штука строк это можно чай попить

Неактивен

 

#17 26.03.2008 21:02:55

Руслан
Участник
Зарегистрирован: 26.03.2008
Сообщений: 13

Re: Помогите посчитать расстояние(соствить запрос)

Спасибо, но я тут по незнанию ни праймари кейс ни индексы не использую, может из-за этого так долго

Неактивен

 

#18 26.03.2008 21:09:31

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Помогите посчитать расстояние(соствить запрос)

добавьте ключ на поле time и на поле date
ALTER TABLE имя_таблицы ADD KEY(time), ADD KEY(date)

Неактивен

 

Board footer

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