Выберите записи, расстояние между которыми составляет 5 км и выше
Я работаю над приложением местоположения, и мне нужно получить все местоположения из моей таблицы местоположений Mysql, которые имеют расстояние в 5 км между ними.
В таблице местоположения Ex есть следующие позиции:
id Latitude Longitude
1 22.7499180 75.8950577
2 22.7498474 75.8950653
3 22.7498035 75.8950424
4 22.7497787 75.8950729
5 22.7498245 75.8950806
6 22.7497902 75.8950272
7 22.7497864 75.8950424
8 22.7497768 75.8950500
9 22.7497864 75.8950577
10 22.7497921 75.8950653
11 22.7497597 75.8950653
12 22.7498283 75.8950653
13 22.7497978 75.8950577
Итак, из таблицы выше, как мне нужно получить результаты, похожие на это
id Latitude Longitude Distance (>=5Km)
1 22.7499180 75.8950577 --
4 22.7497787 75.8950729 6km (From lat long of id 1)
8 22.7497768 75.8950500 8km (From lat long of id 4)
11 22.7497597 75.8950653 6km (From lat long of id 8)
13 22.7497978 75.8950577 10km (From lat long of id 11)
Я много искал, чтобы получить такие результаты, но я получил запрос только для получения результата на основе фиксированного lat/long или фиксированного радиуса. Пожалуйста, помогите с запросом Mysql, если это возможно.
Изменить (из комментария OP)
Мне нужно рассчитать расстояние от последнего выбранного значения... Для Ex. Начать с записи 1. Расстояние 1 сравнивается с записью 2, 5 км, по сравнению с записью 3 также < 5 км, по сравнению с 4 его расстояние > 5 км, поэтому мы храним его в списке. СЛЕДУЮЩИЙ ЗАПИСЬ БУДЕТ СРАВНЕН С ЗАПИСЬЮ 4. так что расстояние 4 будет сравниваться с 5, а если запись 5 будет иметь расстояние > 5 км от 4, то будет сделано следующее сравнение с записью 5 в качестве эталона.
Ответы
Ответ 1
Нет хранимой процедуры, просто чистой необузданной славы sql:
SET @prevLong=-1.0000;
SET @prevLat=-1.0000;
SET @currDist=1.0000;
select id, diff from (
select id,
@prevLat prev_lat,
@currDist:= 6371 * 2 * (atan2(sqrt(sin(radians(@prevLat - lat)/2)
* sin(radians(@prevLat - lat)/2)
+ cos(radians(lat))
* cos(radians(@prevLat))
* sin(radians(@prevLong - longi)/2)
* sin(radians(@prevLong - longi)/2))
,sqrt(1-(sin(radians(@prevLat - lat)/2)
* sin(radians(@prevLat - lat)/2)
+ cos(radians(lat))
* cos(radians(@prevLat))
* sin(radians(@prevLong
- longi)/2)
* sin(radians(@prevLong - longi)/2))))) diff,
@prevLong prevLong,
case when @currdist > 5 then @prevLat:=lat else null end curr_lat,
case when @currDist > 5 then @prevLong:= longi else null end curr_long
from latLong
order by id asc
) a where diff > 5
SQLFiddle, чтобы доказать, что магия реальна:
http://sqlfiddle.com/#!9/7e4fe/19
Edit
В Codeigniter вы можете использовать следующие переменные:
$this->db->query("SET @prevLong=-1.0000");
$this->db->query("SET @prevLat=-1.0000");
$this->db->query("SET @prevDist=-1.0000");
Затем выполните ваш запрос как обычно
$query= $this->db->query("SELECT ...");
Ответ 2
Итак, вам нужно рассчитать расстояние от Lat Lon, а затем проверить, превышает ли результат более 5 км. Проблема с вашими данными образца заключается в том, что рассчитанные расстояния находятся в пределах метров, поэтому вы не получите никакого результата. Я думаю, у вас есть еще несколько мест для проверки в вашей таблице.
попробовать
SELECT
a.id, a.Latitude, a.Longitude, CONCAT(a.ID,"-",b.ID) as 'FromTo',
6371 * acos(
cos(radians( b.Latitude ))
* cos(radians( a.Latitude ))
* cos(radians( b.Longitude ) - radians( a.Longitude ))
+ sin(radians( b.Latitude ))
* sin(radians( a.Latitude ))) as distance
FROM new_table a INNER JOIN new_table b ON a.id <> b.id
HAVING distance >= 0.001
ORDER BY id, distance;
Я установил условие having больше метра HAVING distance >= 0.001
. если вы хотите проверить, как отрегулировать km соответственно!
ИЗМЕНИТЬ
это не решение поститься, возможно, вам придется немного подкорректировать его, но процедура будет выглядеть как
DELIMITER $$
CREATE PROCEDURE `calcDistWithin`(IN dist double)
BEGIN
declare maxTempID int;
declare maxTblID int;
declare breakLoop boolean;
SET breakLoop = FALSE;
DROP TEMPORARY TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp (ID int, Latitude double, Longitude double, distance double, toID varchar(10));
DROP TEMPORARY TABLE IF EXISTS tmpOUT;
CREATE TEMPORARY TABLE tmpOUT (ID int, Latitude double, Longitude double, distance double, toID varchar(10));
INSERT INTO tmp select ID, Latitude, Longitude, 0, "---" from new_table limit 1;
INSERT INTO tmpOUT select ID, Latitude, Longitude, 0, "---" from new_table limit 1;
SELECT ID INTO maxTblID FROM new_table ORDER BY ID DESC LIMIT 1;
SELECT ID into maxTempID FROM tmp ORDER BY ID DESC LIMIT 1;
WHILE breakLoop = FALSE DO
IF EXISTS (SELECT
6371 * acos(
cos(radians( b.Latitude ))
* cos(radians( a.Latitude ))
* cos(radians( b.Longitude ) - radians( a.Longitude ))
+ sin(radians( b.Latitude ))
* sin(radians( a.Latitude ))) as distance
FROM tmp a INNER JOIN new_table b
WHERE a.ID < b.ID AND a.ID = maxTempID
HAVING distance >= dist
LIMIT 1) THEN
INSERT INTO tmpOUT SELECT
b.ID, b.Latitude, b.Longitude,
6371 * acos(
cos(radians( b.Latitude ))
* cos(radians( a.Latitude ))
* cos(radians( b.Longitude ) - radians( a.Longitude ))
+ sin(radians( b.Latitude ))
* sin(radians( a.Latitude ))) as distance,
a.ID
FROM tmp a INNER JOIN new_table b
WHERE a.ID < b.ID AND a.ID = maxTempID
HAVING distance >= dist
ORDER BY a.ID, b.ID, distance
LIMIT 1;
INSERT INTO tmp SELECT ID, Latitude, Longitude, distance, toID FROM tmpOUT ORDER BY ID DESC LIMIT 1;
SELECT ID into maxTempID FROM tmpOUT order by ID DESC LIMIT 1;
ELSE
SET breakLoop = TRUE;
END IF;
END WHILE;
SELECT * FROM tmpOUT;
END$$
DELIMITER ;
чтобы вызвать его, просто используйте
CALL calcDistWithin(5.00)
Ответ 3
Я не уверен, что вы хотите использовать чистое sql-решение или нет.
Я могу только дать решение с другим языком.
Я предполагаю использовать id для определения ближайшего node.
List locat= new List();
last = getTheLastRecord();
locat.add(last);
count = getTheCountOfRecord();
for(int i=1;i<count;i++){//i = 1 because last record already read.
Record r = getRecord(i);
if(compareDistance(r,last)>5000){
locat.add(r);
last = r;
}
}
//наконец, список locat будет содержать любое местоположение, которое вы хотите.