使用php在距当前位置一定距离内选择餐厅表条目

问题描述

我有一个数据库表，其中包含餐馆的列表，包括餐馆的名称，纬度和经度.我想选择距离我当前位置一定距离内的所有餐厅.当前位置是在php文件中确定的(现在我只是使用静态lat&lng).我找到了计算距离的代码:

function findDist($lat, $lng){
    $currLat = 33.777563;
    $currLng = -84.389959;

    $currLat = deg2rad ($currLat);
    $sincurrLat = sin ($currLat);
    $lat = deg2rad ($lat);
    $currLng = deg2rad ($currLng);
    $lng = deg2rad ($lng);

    return round((7926 - 26 * $sincurrLat) * asin (min (1, 0.707106781186548 * sqrt ((1 - (sin ($lat) * $sincurrLat) - cos ($currLat) * cos ($lat) * cos ($lng - $currLng))))),4);
}

但是如何将其合并到我的选择查询中?我试过了:

$query = "SELECT *
FROM
    eateries E
WHERE
    EXISTS
    (
        SELECT *
        FROM
            eateries_hours EH, eateries_type ET
        WHERE
            EH.eateries_id = E.id AND ET.eateries_id = E.id
            AND findDist(E.lat, E.lng) <= .5
    )";

但是当然不起作用，因为它无法识别该功能.我可以先对纬度和经度做一个单独的查询，计算距离，然后以某种方式将其与上述查询结合起来吗?有什么想法吗?

谢谢.

解决方案

看看一个问题.您可以在MySQL(或其他数据库)中创建一个存储函数，前提是您具有经度和纬度.

DELIMITER $$

DROP FUNCTION IF EXISTS `FindDist` $$
CREATE FUNCTION `FindDist` (lt1 DOUBLE,lg1 DOUBLE,lt2 DOUBLE,lg2 DOUBLE) RETURNS DOUBLE
DETERMINISTIC
BEGIN
    DECLARE dist,eradius DOUBLE;

    SET eradius=3963.1;
    SET dist=Acos(Cos(lt1) * Cos(lg1) * Cos(lt2) * Cos(lg2) + Cos(lt1) * Sin(lg1) * Cos(lt2) * Sin(lg2) + Sin(lt1) * Sin(lt2)) * eradius;
    RETURN dist;
END $$

DELIMITER ;

i have a database table with a list of restaurants including their names, latitudes, and longitudes. i would like to select all the restaurants that are within a certain distance from my current location. the current location is determined in the php file (right now i'm just using a static lat & lng). i found code for calculating the distance:

function findDist($lat, $lng){
    $currLat = 33.777563;
    $currLng = -84.389959;

    $currLat = deg2rad ($currLat);
    $sincurrLat = sin ($currLat);
    $lat = deg2rad ($lat);
    $currLng = deg2rad ($currLng);
    $lng = deg2rad ($lng);

    return round((7926 - 26 * $sincurrLat) * asin (min (1, 0.707106781186548 * sqrt ((1 - (sin ($lat) * $sincurrLat) - cos ($currLat) * cos ($lat) * cos ($lng - $currLng))))),4);
}

but how do i incorporate that into my select query? i tried this:

$query = "SELECT *
FROM
    eateries E
WHERE
    EXISTS
    (
        SELECT *
        FROM
            eateries_hours EH, eateries_type ET
        WHERE
            EH.eateries_id = E.id AND ET.eateries_id = E.id
            AND findDist(E.lat, E.lng) <= .5
    )";

but of course that doesn't work because it's not recognizing the function. can i do a separate query just for lats and lngs at first, calculate the distances, and then join that with the above query somehow? any ideas?

thanks.

解决方案

Take a look at a question that I previously answered. You can create a stored function in MySQL (or other databases) that does this provided that you have the latitudes and longitudes.

DELIMITER $$

DROP FUNCTION IF EXISTS `FindDist` $$
CREATE FUNCTION `FindDist` (lt1 DOUBLE,lg1 DOUBLE,lt2 DOUBLE,lg2 DOUBLE) RETURNS DOUBLE
DETERMINISTIC
BEGIN
    DECLARE dist,eradius DOUBLE;

    SET eradius=3963.1;
    SET dist=Acos(Cos(lt1) * Cos(lg1) * Cos(lt2) * Cos(lg2) + Cos(lt1) * Sin(lg1) * Cos(lt2) * Sin(lg2) + Sin(lt1) * Sin(lt2)) * eradius;
    RETURN dist;
END $$

DELIMITER ;

这篇关于使用php在距当前位置一定距离内选择餐厅表条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！