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

Question

I read in radius and mysql, and I'm very confused about how to accurately implement code with multiple search variables, so can someone break it for me.

My database has the following tables:

idx | type | price | item_desc | s_lat | s_long | date of creation

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

All I have found so far says:

 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'm confused by the way I implement it for $search_origin_radius and $search_dest_radius . Basically, what I'm trying to do is find everything that sells in the price range and radius around two cities.

Example. I want to find everything that costs $ 100 around Oklahoma City, OK within 200 miles and Kansas City, Missouri, within 100 miles.

EDIT ** As suggested, this is added 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 a new query but nothing is displayed:

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

Answer 1

Oklahoma City and Kansas City are approximately 300 miles apart.

There is very little land that could be counted less than 200 miles from Oklahoma City AND less than 100 miles from Kansas City, so I'm not surprised that your query returns no results.

Maybe you need 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 can 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 am inclined to the second, since it is easy to build in code and gives you in which city the product was found to be neat.

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

Answer 2

Write a stored function that takes two pairs lat and lng and returns the 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 say the expression twice.

Answer 3

If you are using MySQL version 5.7, you can use its Spatial Convenience Functions and more specifically ST_Distance_Sphere . Here you will have the result in meters, and from there you can easily calculate it in miles.

If your version of MySQL is 5.6.1, you can use ST_Distance as 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 first will convert the result of ST_Distance to miles. The second (when added) will convert it to kilometers. In the above example, ST_Distance used to get the distance from a geographic point from Oklahoma City. If you want to add another city to your SELECT query, something like this will 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;