67,513
社区成员
发帖
与我相关
我的任务
分享
/*表: TestTab*/----------------------
/*列信息*/-----------
FIELD TYPE COLLATION NULL KEY DEFAULT Extra PRIVILEGES COMMENT
-------------- ------------- --------------- ------ ------ ------- -------------- ------------------------------- -------
testId INT(11) (NULL) NO PRI (NULL) AUTO_INCREMENT SELECT,INSERT,UPDATE,REFERENCES 编号
testName VARCHAR(64) utf8_general_ci NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
testLongitude DOUBLE (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES 经度
testLatitude DOUBLE (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES 纬度
createTime DATETIME (NULL) NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
#-----------------------------------------------------------------------------
#获取某经纬度附近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
#--------------------------------------------------------------------------------
[/quote]
以上存储过程针对的不是TestTab表,而是用户表。#-----------------------------------------------------------------------------
#获取某经纬度附近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
#--------------------------------------------------------------------------------
select * from table_name where ((testLongitude-myLongitude)*(testLongitude-myLongitude)+(testLatitude-myLatitude)*(testLatitude-myLatitude))*value<=25
--myLongitude,myLatitude是中心的坐标,value是经纬度差换算成距离km的数值,没有研究过,我认为距离比较近,精度要求不高的情况下是可以这样算的
[/quote]
这样不对吧?
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-Latitude)*(testLatitude-Latitude))*value<=25