sql server
查询已知坐标方圆500米的记录
test表名
id varchar(50) Unchecked
name nvarchar(50) Checked
jing varchar(50) Checked
wei varchar(50) Checked
已知点的经度 113.914619
已知点的纬度 22.50128
select * from test where sqrt(
(
((113.914619-jing)*PI()*12656*cos(((22.50128+wei)/2)*PI()/180)/180)
*
((113.914619-jing)*PI()*12656*cos (((22.50128+wei)/2)*PI()/180)/180)
)
+
(
((22.50128-wei)*PI()*12656/180)
*
((22.50128-wei)*PI()*12656/180)
)
)<500
oracle
自定义函数(获得弧度的函数)
CREATE OR REPLACE FUNCTION Radian(d number) RETURN NUMBER
is
PI number :=3.141592625;
begin
return d* PI/180.0;
end ;
自定义函数(根据经纬度计算距离)
CREATE OR REPLACE FUNCTION GetDistance(lat1 number,
lng1 number,
lat2 number,
lng2 number) RETURN NUMBER is
earth_padius number := 6378.137;
radLat1 number := Radian(lat1);
radLat2 number := Radian(lat2);
a number := radLat1 - radLat2;
b number := Radian(lng1) - Radian(lng2);
s number := 0;
begin
s := 2 *
Asin(Sqrt(power(sin(a / 2), 2) +
cos(radLat1) * cos(radLat2) * power(sin(b / 2), 2)));
s := s * earth_padius;
s := Round(s * 10000) / 10000;
return s;
end;
已知两个点的经纬度,求两点之间距离
select getdistance('123.421784','41.841746','123.421326','41.841785') from aa
aa表名
id varchar(50) Uncheckedname nvarchar(50) Checked
jing varchar(50) Checked
wei varchar(50) Checked
已知某点的经纬度,查询方圆500米的数据
select id,name,jing,wei,cc from(select id,name,jing,wei,GetDistance(jing,wei,'123.421302','41.841873') cc from aa) where cc<0.5;