如何在我的 mysql 查询中使用函数? [英] How can i use function in my mysql query?

问题描述

我在 php 工作.

我想找出与地理纬度和经度相距给定距离的地方.

i want to find out the places which are with in a given distance from a geographical latitude and longitude.

我在mysql数据库中有一张表，名为places，其中有三列placeId、latitude和longitude.

i have one table in a mysql database called places, in which there are three columns placeId, latitude and longitude.

用户提供一个地点的纬度、经度和一个距离，然后使用以下公式:

user provide latitude, longitude of a place and a distance, then by using the following formula:

d=2*asin(sqrt((sin((lat1-lat2)/2))^2 +cos(lat1)cos(lat2)(sin((lon1-lon2)/2))^2));

d=2*asin(sqrt((sin((lat1-lat2)/2))^2 + cos(lat1)cos(lat2)(sin((lon1-lon2)/2))^2));

我可以检查这些地方是否在用户给定的距离内.

i can check whether these place are with in the distance given by the user or not.

我想知道如何编写查询和函数来实现此功能.

I want to know that how can i write a query and function to implement this functionality.

推荐答案

我在MYSQL中创建了下面的函数

I have created below function in MYSQL

DELIMITER $$    
DROP FUNCTION IF EXISTS `great_circle_distance`$$
    CREATE DEFINER=`root`@`localhost` FUNCTION `great_circle_distance`(
    lat1 DOUBLE(10,6),
    lon1 DOUBLE(10,6),
    lat2 DOUBLE(10,6),
    lon2 DOUBLE(10,6)
    ) RETURNS double(10,2)
        DETERMINISTIC
    BEGIN
                    DECLARE delta_lat DOUBLE(10,6);
                    DECLARE delta_lon DOUBLE(10,6);
                    DECLARE temp1 DOUBLE(10,6);
                    DECLARE EARTH_RADIUS DOUBLE(10,2);
                    DECLARE distance DOUBLE(10,2);
                    SET lat1 = RADIANS(lat1);
                    SET lon1 = RADIANS(lon1);
                    SET lat2 = RADIANS(lat2);
                    SET lon2 = RADIANS(lon2);
                    SET delta_lat = lat2 - lat1;
                    SET delta_lon = lon2 - lon1;

                    SET temp1 = pow(sin(delta_lat/2.0),2) + cos(lat1) * cos(lat2) * pow(sin(delta_lon/2.0),2);
                    SET EARTH_RADIUS = 3956.0;
                    SET distance = EARTH_RADIUS * 2 * atan2(sqrt(temp1),sqrt(1-temp1));
                    RETURN distance;
        END$$

    DELIMITER ;

将其用作

Select great_circle_distance(z.lat,z.log, 32.2342,-72.42342) AS Distance from tbl_abc AS z;

这篇关于如何在我的 mysql 查询中使用函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！