Lance - 1 year ago 54

SQL Question

I currently have a MySQL table that is structured as follows:

`id name lon lat`

----- ----- ----------- -----------

1 Mark -76.316528 40.036027

2 John -95.995102 41.25716

3 Paul -82.337036 29.645095

4 Dave -82.337036 29.645095

5 Chris -76.316528 40.036027

The way I currently query the DB to see if a user's location is within a certain mile radius of a given location is by doing this.

`function Haversine($lat_from, $lon_from, $lat_to, $lon_to) {`

$radius = 6371000;

$delta_lat = deg2rad($lat_to-$lat_from);

$delta_lon = deg2rad($lon_to-$lon_from);

$a = sin($delta_lat/2) * sin($delta_lat/2) +

cos(deg2rad($lat_from)) * cos(deg2rad($lat_to)) *

sin($delta_lon/2) * sin($delta_lon/2);

$c = 2*atan2(sqrt($a), sqrt(1-$a));

// Convert the distance from meters to miles

return ceil(($radius*$c)*0.000621371);

}

// Set the given location to NYC

$my_lon = -73.9844;

$my_lat = 40.7590;

// Query the DB for all of the users

$sql = "SELECT * FROM users";

$result = mysqli_query($con, $sql)or die(mysqli_error($con));

$count = mysqli_num_rows($result);

$i = 0;

while($row = mysqli_fetch_assoc($result)) {

$lon[$i] = $row['lon'];

$lat[$i] = $row['lat'];

$i++;

}

for($i=0;$i<$count;$i++) {

// Calculate the distance between each user's location and my location

$distance = Haversine($my_lat, $my_lon, $lat[$i], $lon[$i);

if($distance < 50) {

echo "Close enough";

}

}

This works well with only a few hundred rows in the table. But, now that I have tens of thousands of rows, checking so many rows has proven to be very time consuming. I'm wondering if there is a way to use the Haversine formula to only query rows that are within the 50 mile radius.

Answer Source

**Spherical Law of Cosines Formula**

(37 and -122 are the lat and lon of your radius center)

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

Features

- Fastest
- Precision similar to Harvesine Formula

**Haversine Formula**

```
SELECT id, 3956 * 2 * ASIN(SQRT(POWER(SIN((37 - abs(lat)) * pi()/180 / 2), 2)
+ COS(37 * pi()/180 ) * COS(abs(lat) * pi()/180)
* POWER(SIN((-122 – long) * pi()/180 / 2), 2) )) as distance
FROM myTable
HAVING distance < 50
ORDER BY distance
```

Features

- Fast
- More robust to floating point errors

Note that **3959 is the Earth radius in miles**. Earth radius in km: 6971

You can find more information here