我有经纬度,我想从数据库中提取记录,它有最近的经纬度,如果这个距离比指定的长,就不检索它。
表结构:
id
latitude
longitude
place name
city
country
state
zip
sealevel
我有经纬度,我想从数据库中提取记录,它有最近的经纬度,如果这个距离比指定的长,就不检索它。
表结构:
id
latitude
longitude
place name
city
country
state
zip
sealevel
当前回答
MS SQL版本在这里:
DECLARE @SLAT AS FLOAT
DECLARE @SLON AS FLOAT
SET @SLAT = 38.150785
SET @SLON = 27.360249
SELECT TOP 10 [LATITUDE], [LONGITUDE], SQRT(
POWER(69.1 * ([LATITUDE] - @SLAT), 2) +
POWER(69.1 * (@SLON - [LONGITUDE]) * COS([LATITUDE] / 57.3), 2)) AS distance
FROM [TABLE] ORDER BY 3
其他回答
查找离我最近的用户:
距离(米)
根据文森特提的公式
i有用户表:
+----+-----------------------+---------+--------------+---------------+
| id | email | name | location_lat | location_long |
+----+-----------------------+---------+--------------+---------------+
| 13 | xxxxxx@xxxxxxxxxx.com | Isaac | 17.2675625 | -97.6802361 |
| 14 | xxxx@xxxxxxx.com.mx | Monse | 19.392702 | -99.172596 |
+----+-----------------------+---------+--------------+---------------+
sql:
-- my location: lat 19.391124 -99.165660
SELECT
(ATAN(
SQRT(
POW(COS(RADIANS(users.location_lat)) * SIN(RADIANS(users.location_long) - RADIANS(-99.165660)), 2) +
POW(COS(RADIANS(19.391124)) * SIN(RADIANS(users.location_lat)) -
SIN(RADIANS(19.391124)) * cos(RADIANS(users.location_lat)) * cos(RADIANS(users.location_long) - RADIANS(-99.165660)), 2)
)
,
SIN(RADIANS(19.391124)) *
SIN(RADIANS(users.location_lat)) +
COS(RADIANS(19.391124)) *
COS(RADIANS(users.location_lat)) *
COS(RADIANS(users.location_long) - RADIANS(-99.165660))
) * 6371000) as distance,
users.id
FROM users
ORDER BY distance ASC
地球半径:6371000(单位:米)
简单的一个;)
SELECT * FROM `WAYPOINTS` W ORDER BY
ABS(ABS(W.`LATITUDE`-53.63) +
ABS(W.`LONGITUDE`-9.9)) ASC LIMIT 30;
把坐标换成你需要的坐标。这些值必须存储为double类型。这是一个工作中的MySQL 5。x的例子。
干杯
以防你像我一样懒,这里有一个解决方案,由这个和其他关于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/
+----+-----------------------+---------+--------------+---------------+
| 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");