我有经纬度,我想从数据库中提取记录,它有最近的经纬度,如果这个距离比指定的长,就不检索它。

表结构:

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

其他回答

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]是开始测量距离的位置。

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

试试这个,它显示最近的点提供的坐标(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/

Mysql查询搜索坐标的距离限制和条件

 SELECT id, ( 3959 * acos( cos( radians('28.5850154') ) * cos( radians(latitude) ) * cos( radians( longitude ) - radians('77.07207489999999') ) + sin( radians('28.5850154') ) * sin( radians( latitude ) ) ) ) AS distance FROM `vendors` HAVING distance < 5;

这个问题一点也不难,但是如果你需要优化它,它就会变得更加复杂。

我的意思是,你的数据库中有100个地点还是1亿个?这有很大的不同。

如果位置的数量很小,只需执行->,就可以将它们从SQL中取出并放入代码中

Select * from Location

一旦你把它们转换成代码,用哈弗辛公式计算出每一个纬度/长度与原始值之间的距离,然后排序。