如何使用具有多个变量的PHP radius函数?

I have been reading up on radius and mysql and I am very confused on how to exactly implement the code with multiple search variables, so could someone break it down for me.

我一直在研究radius和mysql，我对如何使用多个搜索变量来实现代码感到非常困惑，所以有人可以帮我把它分解一下。

My database has the following tables:

我的数据库有以下表:

idx | type | price | item_desc | s_lat | s_long | created date

idx |型|价格| item_desc | s_lat | s_long |创建日期

Here is my current code.

这是我当前的代码。

$search_origin_radius = "200";
$search_dest_radius = "100";
$search_origin_lat = "37.2629742";
$search_origin_long = "-98.286158";
$search_dest_lat = "37.2629742";
$search_dest_long = "-98.286158";
$type = "consumers";
$price = "100";

$sql = "SELECT * FROM products WHERE `price` = '$price'";

if($type && !empty($type))
{
    $sql .= " AND `type` = '$type'";    
}

Everything I have found so far says to use :

到目前为止，我所发现的一切都表明:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) ) 
    * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance 
FROM myTable
HAVING distance < 50
ORDER BY distance

But I am super confused on how I implement it for $search_origin_radius and $search_dest_radius. Basically what I am trying to do is find everything that is for sale within the price range and radius around the two cities.

但是我对如何为$search_origin_radius和$search_dest_radius实现它感到非常困惑。基本上，我要做的就是在两个城市的价格范围和半径范围内找到所有要出售的东西。

Example I want to find everything priced for $100 around Oklahoma City, OK within 200 miles and Kansas City, MO within 100 miles.

举个例子，我想在俄克拉荷马城周围找到所有100美元的东西，在200英里以内，堪萨斯城，密苏里州100英里以内。

EDIT** As suggested added this as a stored function.

如建议的，编辑**将其添加为一个存储函数。

function Calc_Distance($latitudeFrom, $longitudeFrom, $latitudeTo,         $longitudeTo, $earthRadius = 3959)
{
  // convert from degrees to radians
  $latFrom = deg2rad($latitudeFrom);
  $lonFrom = deg2rad($longitudeFrom);
  $latTo = deg2rad($latitudeTo);
  $lonTo = deg2rad($longitudeTo);

  $latDelta = $latTo - $latFrom;
  $lonDelta = $lonTo - $lonFrom;

  $angle = 2 * asin(sqrt(pow(sin($latDelta / 2), 2) +
    cos($latFrom) * cos($latTo) * pow(sin($lonDelta / 2), 2)));
  return $angle * $earthRadius;
}

Using new query but still nothing shows up:

使用新的查询，但仍然没有显示:

$sql = "
SELECT *
FROM products
WHERE Calc_Distance(s_lat, s_long, $search_origin_lat, $search_origin_long) < 200
  AND Calc_Distance(s_lat, s_long, $search_dest_lat,   $search_dest_long)   < 100
  AND price = $price
";

3 个解决方案

#1

Oklahoma City and Kansas City are pretty much 300 miles away from each other.

俄克拉荷马城和堪萨斯城相距300英里。

There is very little land that could be considered both under 200 miles from Oklahoma City AND under 100 miles from Kansas City, so I am not surprised your query returns no results.

离俄克拉荷马城不到200英里，离堪萨斯城不到100英里的土地很少，所以我不奇怪你的查询没有结果。

Perhaps you wanted OR logic here..

也许你想要或逻辑在这里。

SELECT id
  FROM products
 WHERE (*Oklahoma City distance calc miles* < 200
    OR *Kansas City distance calc miles* < 100)
   AND price = 100;

..or you could UNION ALL a SELECT for each city:

. .或者你可以为每个城市联合所有的选择:

SELECT id, 'Oklahoma City' as city  
  FROM products
 WHERE *Oklahoma City distance calc miles* < 200
   AND price = 100

 UNION ALL

SELECT id, 'Kansas City'
  FROM products
 WHERE *Kansas City distance calc miles* < 100
   AND price = 100;

Personally I'd lean towards the second as it is easy to build in code and gives you which city the product is found for neatly.

就我个人而言，我倾向于第二种方法，因为在代码中构建代码是很容易的，并且可以让您清楚地看到产品所在的城市。

Although as pointed out by @PaulSpiegel this (unmodified) could return some products more than once if the search areas intersect.

尽管正如@PaulSpiegel所指出的，如果搜索区域交叉，这个(未修改的)可能会不止一次地返回一些产品。

#2

Write a Stored Function that takes two pairs of lat and lng, and returns a distance.

编写一个存储函数，该函数需要两对lat和lng，并返回一个距离。

$sql = "
SELECT ...
    FROM MyTbl
    WHERE Distance(s_lat, s_long, $search_origin_lat, $search_origin_long) < 200
      AND Distance(s_lat, s_long, $search_dest_lat,   $search_dest_long)   < 100
      AND price = $price
";

Or spell out the expression twice.

或者拼两次。

#3

If you use MySQL version 5.7 you can use it's Spatial Convenience Functions and more specifically ST_Distance_Sphere. This way you will have a result in meters and from there you could easily calculate it in miles.

如果您使用MySQL version 5.7，您可以使用它的空间方便函数，特别是ST_Distance_Sphere。这样你会得到一个米的结果，从那里你可以很容易地计算出英里数。

If your MySQL version is 5.6.1 then you can use ST_Distance function like in this MySQL-Fiddle example

如果MySQL版本是5.6.1，那么可以使用ST_Distance函数，如这个MySQL- fiddle示例所示

SELECT *,ST_Distance(POINT(`s_lat`,`s_long`), POINT(35.5857,-97.4885))*68.925*1.60934 AS `exact_distance`
FROM `entries` WHERE `price`=100 
HAVING `exact_distance`<=200;

You will notice 2 constants (68.925 and 1.60934). The 1st one will convert the result of ST_Distance to miles The second one (when added) will convert it to kilometers In the above example ST_Distance is used to get the distance of a geography point from Oklahoma City. If you want to add another city in your SELECT query then something like this would be what you could use.

你会注意到两个常数(68.925和1.60934)。第1个将把ST_Distance的结果转换为miles，第二个(当添加的时候)将它转换为km，在上面的示例ST_Distance中，它被用于从俄克拉荷马城获得地理点的距离。如果您想在SELECT查询中添加另一个城市，那么您可以使用类似这样的东西。

SELECT *,ST_Distance(POINT(`s_lat`,`s_long`), POINT(35.5857,-97.4885))*68.925 AS `exact_distance_from_Oklahoma_City`,
ST_Distance(POINT(`s_lat`,`s_long`), POINT(39.127562,-94.598810))*68.925 AS `exact_distance_from_Kansas_City`
FROM `entries` WHERE `price`=100 
HAVING `exact_distance_from_Oklahoma_City`<=200
AND `exact_distance_from_Kansas_City`<=100;

#1