select * from table_name where ((testLongitude-myLongitude)*(testLongitude-myLongitude)+(testLatitude-Latitude)*(testLatitude-Latitude))*value<=25
select * from table_name where ((testLongitude-myLongitude)*(testLongitude-myLongitude)+(testLatitude-myLatitude)*(testLatitude-myLatitude))*value<=25 --myLongitude,myLatitude是中心的坐标,value是经纬度差换算成距离km的数值,没有研究过,我认为距离比较近,精度要求不高的情况下是可以这样算的
select * from table_name where ((testLongitude-myLongitude)*(testLongitude-myLongitude)+(testLatitude-myLatitude)*(testLatitude-myLatitude))*value<=25 --myLongitude,myLatitude是中心的坐标,value是经纬度差换算成距离km的数值,没有研究过,我认为距离比较近,精度要求不高的情况下是可以这样算的 这样不对吧?
why?看看下面我写的存储过程吧,我觉得这种做法比较正确,不过有语法错误,您可以copy到数据库看看原因#----------------------------------------------------------------------------- #获取某经纬度附近5km的用户信息 DROP PROCEDURE IF EXISTS pro_getNearUser CREATE PROCEDURE pro_getNearUser(IN mylon DOUBLE,IN mylat DOUBLE,IN beginId INT,IN counts INT,IN dist INT) BEGIN DECLARE mylon DOUBLE; DECLARE mylat DOUBLE; DECLARE lon1 FLOAT; DECLARE lon2 FLOAT; DECLARE lat1 FLOAT; DECLARE lat2 FLOAT; -- calculate lon and lat for the rectangle: SET lon1 = mylon-dist/ABS(COS(RADIANS(mylat))*69); SET lon2 = mylon+dist/ABS(COS(RADIANS(mylat))*69); SET lat1 = mylat-(dist/69); SET lat2 = mylat+(dist/69); -- run the query: SELECT merchantId, 3956 * 2 * ASIN(SQRT( POWER(SIN((orig.userLon - dest.userLat) * PI()/180 / 2), 2) + COS(orig.userLat * PI()/180) * COS(dest.userLat * PI()/180) * POWER(SIN((orig.userLon -dest.userLon) * PI()/180 / 2), 2) )) AS distance FROM users dest, users orig WHERE dest.userLon BETWEEN lon1 AND lon2 AND dest.userLon BETWEEN lat1 AND lat2 HAVING distance < dist ORDER BY distance LIMIT beginId,counts; END #--------------------------------------------------------------------------------
why?看看下面我写的存储过程吧,我觉得这种做法比较正确,不过有语法错误,您可以copy到数据库看看原因#----------------------------------------------------------------------------- #获取某经纬度附近5km的用户信息 DROP PROCEDURE IF EXISTS pro_getNearUser CREATE PROCEDURE pro_getNearUser(IN mylon DOUBLE,IN mylat DOUBLE,IN beginId INT,IN counts INT,IN dist INT) BEGIN DECLARE mylon DOUBLE; DECLARE mylat DOUBLE; DECLARE lon1 FLOAT; DECLARE lon2 FLOAT; DECLARE lat1 FLOAT; DECLARE lat2 FLOAT; -- calculate lon and lat for the rectangle: SET lon1 = mylon-dist/ABS(COS(RADIANS(mylat))*69); SET lon2 = mylon+dist/ABS(COS(RADIANS(mylat))*69); SET lat1 = mylat-(dist/69); SET lat2 = mylat+(dist/69); -- run the query: SELECT merchantId, 3956 * 2 * ASIN(SQRT( POWER(SIN((orig.userLon - dest.userLat) * PI()/180 / 2), 2) + COS(orig.userLat * PI()/180) * COS(dest.userLat * PI()/180) * POWER(SIN((orig.userLon -dest.userLon) * PI()/180 / 2), 2) )) AS distance FROM users dest, users orig WHERE dest.userLon BETWEEN lon1 AND lon2 AND dest.userLon BETWEEN lat1 AND lat2 HAVING distance < dist ORDER BY distance LIMIT beginId,counts; END #--------------------------------------------------------------------------------以上存储过程针对的不是TestTab表,而是用户表。
select * from table_name where ((testLongitude-myLongitude)*(testLongitude-myLongitude)+(testLatitude-Latitude)*(testLatitude-Latitude))*value<=25
select * from table_name where ((testLongitude-myLongitude)*(testLongitude-myLongitude)+(testLatitude-myLatitude)*(testLatitude-myLatitude))*value<=25
--myLongitude,myLatitude是中心的坐标,value是经纬度差换算成距离km的数值,没有研究过,我认为距离比较近,精度要求不高的情况下是可以这样算的
select * from table_name where ((testLongitude-myLongitude)*(testLongitude-myLongitude)+(testLatitude-myLatitude)*(testLatitude-myLatitude))*value<=25
--myLongitude,myLatitude是中心的坐标,value是经纬度差换算成距离km的数值,没有研究过,我认为距离比较近,精度要求不高的情况下是可以这样算的
这样不对吧?
#获取某经纬度附近5km的用户信息
DROP PROCEDURE IF EXISTS pro_getNearUser
CREATE PROCEDURE pro_getNearUser(IN mylon DOUBLE,IN mylat DOUBLE,IN beginId INT,IN counts INT,IN dist INT)
BEGIN
DECLARE mylon DOUBLE; DECLARE mylat DOUBLE;
DECLARE lon1 FLOAT; DECLARE lon2 FLOAT;
DECLARE lat1 FLOAT; DECLARE lat2 FLOAT;
-- calculate lon and lat for the rectangle:
SET lon1 = mylon-dist/ABS(COS(RADIANS(mylat))*69);
SET lon2 = mylon+dist/ABS(COS(RADIANS(mylat))*69);
SET lat1 = mylat-(dist/69);
SET lat2 = mylat+(dist/69);
-- run the query:
SELECT merchantId,
3956 * 2 * ASIN(SQRT( POWER(SIN((orig.userLon - dest.userLat) * PI()/180 / 2), 2) +
COS(orig.userLat * PI()/180) * COS(dest.userLat * PI()/180) * POWER(SIN((orig.userLon -dest.userLon) * PI()/180 / 2), 2) )) AS
distance FROM users dest, users orig
WHERE dest.userLon BETWEEN lon1 AND lon2
AND dest.userLon BETWEEN lat1 AND lat2
HAVING distance < dist ORDER BY distance LIMIT beginId,counts;
END
#--------------------------------------------------------------------------------
#获取某经纬度附近5km的用户信息
DROP PROCEDURE IF EXISTS pro_getNearUser
CREATE PROCEDURE pro_getNearUser(IN mylon DOUBLE,IN mylat DOUBLE,IN beginId INT,IN counts INT,IN dist INT)
BEGIN
DECLARE mylon DOUBLE; DECLARE mylat DOUBLE;
DECLARE lon1 FLOAT; DECLARE lon2 FLOAT;
DECLARE lat1 FLOAT; DECLARE lat2 FLOAT;
-- calculate lon and lat for the rectangle:
SET lon1 = mylon-dist/ABS(COS(RADIANS(mylat))*69);
SET lon2 = mylon+dist/ABS(COS(RADIANS(mylat))*69);
SET lat1 = mylat-(dist/69);
SET lat2 = mylat+(dist/69);
-- run the query:
SELECT merchantId,
3956 * 2 * ASIN(SQRT( POWER(SIN((orig.userLon - dest.userLat) * PI()/180 / 2), 2) +
COS(orig.userLat * PI()/180) * COS(dest.userLat * PI()/180) * POWER(SIN((orig.userLon -dest.userLon) * PI()/180 / 2), 2) )) AS
distance FROM users dest, users orig
WHERE dest.userLon BETWEEN lon1 AND lon2
AND dest.userLon BETWEEN lat1 AND lat2
HAVING distance < dist ORDER BY distance LIMIT beginId,counts;
END
#--------------------------------------------------------------------------------以上存储过程针对的不是TestTab表,而是用户表。