/ 猿问

# 在mysql中使用经度和纬度查找两点之间的距离

2019-11-14 09:32:28

--------------------------------------------

|  id  |  city  |  Latitude  |  Longitude  |

--------------------------------------------

|  1   |   3    |   34.44444 |   84.3434   |

--------------------------------------------

|  2   |   4    | 42.4666667 | 1.4666667   |

--------------------------------------------

|  3   |   5    |  32.534167 | 66.078056   |

--------------------------------------------

|  4   |   6    |  36.948889 | 66.328611   |

--------------------------------------------

|  5   |   7    |  35.088056 | 69.046389   |

--------------------------------------------

|  6   |   8    |  36.083056 |   69.0525   |

--------------------------------------------

|  7   |   9    |  31.015833 | 61.860278   |

--------------------------------------------

SELECT `locations`.`city`, ( 3959 * acos ( cos ( radians(31.589167) ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(64.363333) ) + sin ( radians(31.589167) ) * sin( radians( Latitude ) ) ) ) AS `distance` FROM `locations` HAVING (distance < 50)

## 3 回答

www说

SELECT a.city AS from_city, b.city AS to_city,

111.111 *

FROM city AS a

JOIN city AS b ON a.id <> b.id

WHERE a.city = 3 AND b.city = 7

http://sqlfiddle.com/#!2/abcc8/4/0

HAVING distance_in_km < 10.0    /* slow ! */

ORDER BY distance_in_km DESC

MySQL查询：-

SELECT (6371 * acos(

* cos( radians( lat1 ) )

* sin( radians( lat1 ) )

) ) as distance from your_table

Mysql功能：-

DELIMITER \$\$

CREATE FUNCTION `getDistance`(`lat1` VARCHAR(200), `lng1` VARCHAR(200), `lat2` VARCHAR(200), `lng2` VARCHAR(200)) RETURNS varchar(10) CHARSET utf8

begin

declare distance varchar(10);

set distance = (select (6371 * acos(

* cos( radians( lat1 ) )

* sin( radians( lat1 ) )

) ) as distance);

if(distance is null)

then

return '';

else

return distance;

end if;

end\$\$

DELIMITER ;

SELECT getDistance(lat1,lng1,\$lat2,\$lng2) as distance

FROM your_table.

DELIMITER \$\$

CREATE FUNCTION \`haversine\`(

lat1 FLOAT, lon1 FLOAT,

lat2 FLOAT, lon2 FLOAT

) RETURNS float

NO SQL

DETERMINISTIC

COMMENT 'Returns the distance in degrees on the Earth between two known points of latitude and longitude. To get miles, multiply by 3961, and km by 6373'

BEGIN

RETURN DEGREES(ACOS(

));

END;

DELIMITER;

• 3 回答
• 0 关注
• 318 浏览

0/150