Get all data where distance of coordinates are not more than 12

Question

I was trying to solve this but seems like i cant get the right thing to do in WHERE SQL. HERE's my code. PLease help me solve this problem

$url = "https://maps.googleapis.com/maps/api/geocode/json?address=$string&key=AIzaSyAdk7l9UtPkZsm2FzL465yMXeFDATEubp4";

$result_string = file_get_contents($url);
 $result = json_decode($result_string, true);


$latitudeFrom = $result['results'][0]['geometry']['location']['lat'];
$longitudeFrom = $result['results'][0]['geometry']['location']['lng'];


$theta = $longitudeFrom - $longitudeTo;
$dist = sin(deg2rad($latitudeFrom)) * sin(deg2rad($latitudeTo)) +  cos(deg2rad($latitudeFrom)) * cos(deg2rad($latitudeTo)) * cos(deg2rad($theta));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist * 60 * 1.1515;

$distance = ($miles * 1.609344).' km';





$query->select('*')->from('#__bbe_listing');


>>>> Below is where i get wrong for sure..


$query->where((($dist =sin(deg2rad($latitudeFrom)) * sin(deg2rad('latitude')) + cos(deg2rad($latitudeFrom)) * cos(deg2rad('latitude')) * cos(deg2rad($longitudeFrom - 'longitude'))).($dist = acos($dist)).($dist = rad2deg($dist)) ($miles = $dist * 60 * 1.1515).($distance = ($miles * 1.609344))). $distance < 12);

Why not do the haversine in the SQL? e.g. https://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula — user3783243, Sep 26 '19 at 01:51
this was dynamic. my problem only here is declaring variable inside Where. im newbie — JEROLD COQUILLA, Sep 26 '19 at 01:52
Protip: Start with a simple bounding box, _then_ do the expensive haversine.Better yet, [use the builtin](https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html). — Sammitch, Sep 26 '19 at 02:00
FWIW, I store haversine as a simple function in MySQL. It makes for tidier queries. — Strawberry, Sep 26 '19 at 07:05

score 0 · Answer 1 · answered Mar 08 '20 at 09:16

You can create haversine store fucntion in database

DELIMITER $$
DROP FUNCTION IF EXISTS haversine$$

CREATE FUNCTION haversine(
        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
     ) RETURNS FLOAT
    NO SQL DETERMINISTIC
    COMMENT 'Returns the distance in degrees on the Earth
             between two known points of latitude and longitude'
BEGIN
    RETURN DEGREES(ACOS(
              COS(RADIANS(lat1)) *
              COS(RADIANS(lat2)) *
              COS(RADIANS(lon2) - RADIANS(lon1)) +
              SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
            ));
END$$

DELIMITER ;

Usage:

To use this function successfully, you need to know the following things:

the latitude and longitude of a starting point
the latitude and longitude of an ending point
the number of kilometers (111.045), statute miles (69), or nautical miles (60) in a degree of latitude.

For example, you can use this stored haversine function to look in a zip code table and find the fifteen closest points to a particular point.

SELECT zip, primary_city, latitude, longitude,
       111.045*haversine(latitude,longitude,latpoint, longpoint) AS distance_in_km
 FROM zip
 JOIN (
     SELECT  42.81  AS latpoint,  -70.81 AS longpoint
   ) AS p
 ORDER BY distance_in_km
 LIMIT 15

Notice that the value of the haversine function is returned in degrees of longitude. Multiplying that value by 111.045 converts it to kilometers.

In your case:


$url = "https://maps.googleapis.com/maps/api/geocode/json?address=$string&key=AIzaSyAdk7l9UtPkZsm2FzL465yMXeFDATEubp4";

$result_string = file_get_contents($url);
 $result = json_decode($result_string, true);


$latitudeFrom = $result['results'][0]['geometry']['location']['lat'];
$longitudeFrom = $result['results'][0]['geometry']['location']['lng'];


$query->select('*')->from('#__bbe_listing');


$query->where(60*haversine($latitudeFrom,$longitudeFrom,latitude,longitude) < 12);

Get all data where distance of coordinates are not more than 12

1 Answers1