CODEWITHSUNDEEP
phpmysqllatitude-longitude
Jayce
Jayce

Reputation: 842

How can I use the PHP radius function with multiple variables?

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.

My database has the following tables:

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

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.

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

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
";

Upvotes: 6

Views: 436

Answers (3)

user2560539
user2560539

Reputation:

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.

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

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. 

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;

Upvotes: 0

Arth
Arth

Reputation: 13110

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

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.

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.

Upvotes: 5

Rick James
Rick James

Reputation: 142296

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

$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.

Upvotes: 0

Related Questions