Ben Carey - 10 months ago 163

SQL Question

I am currently storing the

`latitude`

`longitude`

`POINT`

`POINT(51.507351 -0.127758)`

I have never before used this kind of field type, and therefore do not have any experience with the queries, and how to actually, efficiently, use the data stored.

I have found many links that demonstrate various methods to search for items within a specified radius. However, most of these are using independent

`latitude`

`longitude`

Please see the following:

- Fastest Way to Find Distance Between Two Lat/Long Points
- http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
- Use MySQL spatial extensions to select points inside circle

I am trying to search for any records within a given radius (in metres). Based on the structure of my table, what is the best, and most efficient, method of searching my records and returning any items within the specified radius (circle not rectangle)?

This is what I have so far:

`SELECT`

*,

(

6373 * acos (

cos ( radians( PASSED_IN_LATITUDE ) )

* cos( radians( X(location) ) )

* cos( radians( Y(location) ) - radians( PASSED_IN_LONGITUDE ) )

+ sin ( radians( PASSED_IN_LATITUDE ) )

* sin( radians( X(location) )

)

) AS distance

FROM locations

HAVING distance < PASSED_IN_RADIUS

Answer

Assuming you have a spatial key on location, you can do something like this:

```
select * from locations where
contains(geomfromtext('polygon($bounding_rect_coords)'),location)
and earth_distance(location,point($lat,$lon)) < $radius
```

The bounding rectangle coordinates should be computed using the following formulas:

```
$deg_to_rad = $PI/180.0
$rad_to_deg = 1.0/$deg_to_rad
$delta_y = $rad_to_deg *($radius / ($earth_radius * cos($lat*$deg_to_rad))) // the length of the parallel = EARTH_R * cos(lat)
$delta_x = $rad_to_deg * ($radius/$earth_radius)
$x1 = $lat - $delta_x
$x2 = $lat + $delta_x
$y1 = $lon - $delta_y
$y2 = $lon + $delta_y
```

Then the rectangle is obtained with

```
geomfromtext('polygon(($x1 $y1,$x2 $y1,$x2 $y2, $x1 $y2, $x1 $y1))')
```

This is best done in the application to offload the database server.

This rectangle is actually a spherical rectangle, thus the use of the PI constant in its computation. The idea is simple. For the given parallel, convert the search radius into degrees of longitude. That is how many degrees east and west we need to go from the target to cover our candidate points. Then compute the same for degrees of latitude - unlike longitude, this will not be coordinate-dependent as all meridians have the same length. That is how many degrees we need to go north and south.

For `earth_distance()`

you have several options:

- Use my UDF (http://github.com/spachev/mysql_udf_bundle) (fastest, but you need to have the ability to install UDFs on your server)
- Write a MySQL store function. You can start with http://gist.github.com/aramonc/6259563 and adjust if needed (need ability to create functions).
- Just paste your distance computation above directly into the query (ugly, but requires no special setup or privileges)

Your computation itself is OK even though it is two years old - nothing revolutionary has been discovered in the last two years as far as measuring the distance between two points on the earth is concerned as far as I know.

Your original method would work as well except it will be inefficient. The addition of `contains`

clause allows us to reduce our search to a (hopefully) relatively small set that is guaranteed to be within the search radius very quickly. Then we take each candidate and filter out the ones that did not make the cut on `earth_distance()`

.

I must add a standard disclaimer that I inserted variables into SQL that potentially might not have been sanitized. Make sure to validate the resulting SQL queries for SQL injection attacks when writing the actual production code.