MySQL函数计算两个纬度和经度之间的距离 [英] MySQL Function to calculate distance between two latitudes and longitudes

问题描述

如果您的数据库中存储有经纬度，需要计算两个坐标之间的距离。如何使用MySQL函数计算它？

解决方案

经过四处寻找，我放弃了自己写的。我能够修改其他一些代码到以下MySQL函数中。

$ $ $ $ $ $ $ $ $ $ $ $

以度数表示两个纬度和经度。如果您想以弧度传递，则可以将转换注释掉。
计算以英里为单位的距离，以km为单位将半径改为地球半径以获得km。
* /

如果出现GETDISTANCE $$ DR

CREATE FUNCTION GETDISTANCE
（deg_lat1 FLOAT，deg_lng1 FLOAT，deg_lat2 FLOAT，deg_lng2 FLOAT）
RETURNS FLOAT
DETERMINISTIC
BEGIN
声明距离FLOAT;
DECLARE delta_lat FLOAT;
DECLARE delta_lng FLOAT;
DECLARE lat1 FLOAT;
DECLARE lat2 FLOAT;
声明FLOAT;

SET distance = 0;

/ *将度数转换为弧度并获得我需要的变量。* /
SET delta_lat =弧度（deg_lat2 - deg_lat1）;
SET delta_lng =弧度（deg_lng2 - deg_lng1）;
SET lat1 =弧度（deg_lat1）;
SET lat2 =弧度（deg_lat2）;

/ *在这里找到的公式：http://www.movable-type.co.uk/scripts/latlong.html*/
SET a = sin（delta_lat / 2.0）* sin （delta_lat / 2.0）+ sin（delta_lng / 2.0）* sin（delta_lng / 2.0）* cos（lat1）* cos（lat2）;
SET距离= 3956.6 * 2 * atan2（sqrt（a），sqrt（1-a））;

RETURN距离;
END $$
DELIMITER;

If you have a latitude and longitude stored in your database and need to calculate the distance between two coordinates. How can you calculate it using a MySQL function?

解决方案

After searching around for awhile, I gave up and wrote it myself. I was able to adapt some other code to the following MySQL function.

DELIMITER $$
/*
Takes two latitudes and longitudes in degrees. You could comment out the conversion if you want to pass as radians.
Calculate the distance in miles, change the radius to the earth's radius in km to get km.
*/

DROP FUNCTION IF EXISTS GETDISTANCE$$
CREATE FUNCTION GETDISTANCE 
  (deg_lat1 FLOAT, deg_lng1 FLOAT, deg_lat2 FLOAT, deg_lng2 FLOAT) 
  RETURNS FLOAT 
  DETERMINISTIC 
BEGIN 
  DECLARE distance FLOAT;
  DECLARE delta_lat FLOAT; 
  DECLARE delta_lng FLOAT; 
  DECLARE lat1 FLOAT; 
  DECLARE lat2 FLOAT;
  DECLARE a FLOAT;

  SET distance = 0;

  /*Convert degrees to radians and get the variables I need.*/
  SET delta_lat = radians(deg_lat2 - deg_lat1); 
  SET delta_lng = radians(deg_lng2 - deg_lng1); 
  SET lat1 = radians(deg_lat1); 
  SET lat2 = radians(deg_lat2); 

  /*Formula found here: http://www.movable-type.co.uk/scripts/latlong.html*/
  SET a = sin(delta_lat/2.0) * sin(delta_lat/2.0) + sin(delta_lng/2.0) * sin(delta_lng/2.0) * cos(lat1) * cos(lat2); 
  SET distance = 3956.6 * 2 * atan2(sqrt(a),  sqrt(1-a)); 

  RETURN distance;
END$$
DELIMITER ;

这篇关于MySQL函数计算两个纬度和经度之间的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！