我有经纬度,我想从数据库中提取记录,它有最近的经纬度,如果这个距离比指定的长,就不检索它。
表结构:
id
latitude
longitude
place name
city
country
state
zip
sealevel
我有经纬度,我想从数据库中提取记录,它有最近的经纬度,如果这个距离比指定的长,就不检索它。
表结构:
id
latitude
longitude
place name
city
country
state
zip
sealevel
当前回答
以防你像我一样懒,这里有一个解决方案,由这个和其他关于SO的答案合并而成。
set @orig_lat=37.46;
set @orig_long=-122.25;
set @bounding_distance=1;
SELECT
*
,((ACOS(SIN(@orig_lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS(@orig_lat * PI() / 180) * COS(`lat` * PI() / 180) * COS((@orig_long - `long`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance`
FROM `cities`
WHERE
(
`lat` BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance)
AND `long` BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance)
)
ORDER BY `distance` ASC
limit 25;
其他回答
试试这个,它显示最近的点提供的坐标(50公里内)。它工作得很完美:
SELECT m.name,
m.lat, m.lon,
p.distance_unit
* DEGREES(ACOS(COS(RADIANS(p.latpoint))
* COS(RADIANS(m.lat))
* COS(RADIANS(p.longpoint) - RADIANS(m.lon))
+ SIN(RADIANS(p.latpoint))
* SIN(RADIANS(m.lat)))) AS distance_in_km
FROM <table_name> AS m
JOIN (
SELECT <userLat> AS latpoint, <userLon> AS longpoint,
50.0 AS radius, 111.045 AS distance_unit
) AS p ON 1=1
WHERE m.lat
BETWEEN p.latpoint - (p.radius / p.distance_unit)
AND p.latpoint + (p.radius / p.distance_unit)
AND m.lon BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
ORDER BY distance_in_km
只需更改<table_name>。<userLat>和<userLon>
你可以在这里阅读更多关于这个解决方案:http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
SELECT latitude, longitude, SQRT(
POW(69.1 * (latitude - [startlat]), 2) +
POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 25 ORDER BY distance;
其中[starlat]和[startlng]是开始测量距离的位置。
听起来你应该只使用PostGIS、SpatialLite、SQLServer2008或Oracle Spatial。它们都可以用空间SQL为您回答这个问题。
+----+-----------------------+---------+--------------+---------------+
| id | email | name | location_lat | location_long |
+----+-----------------------+---------+--------------+---------------+
| 7 | test@gmail.com | rembo | 23.0249256 | 72.5269697 |
| 25 | test1@gmail.com. | Rajnis | 23.0233221 | 72.5342112 |
+----+-----------------------+---------+--------------+---------------+
$lat = 23.02350629;
$long = 72.53230239;
DB:: 选择 (“ 选择 * 从 ( 选择 , ( (acos(sin)。决。”* pi(美元)/ 180)* sin (lat * pi(+) - 180)因为(”。拉丁语“圆周率(美元)/ 180)* cos (lat * pi () / 180) * cos(("。龙。”- long) * pi(美元)/ 180))* 180 / pi() * 60 * 1515 1。1 . 609344 ) 距离美国 从 \用户 ) \用户 在哪里 距离<= 2");
在极端情况下,这种方法会失败,但为了性能考虑,我跳过了三角函数,只是简单地计算对角线的平方。