Ben Carey Ben Carey - 1 year ago 247
SQL Question

Find records within Geofence (circle) using MySQL Spatial fields

The Issue

I am currently storing the
values for a location using the MySQL
field type in the format:

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.

My Research

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

Please see the following:

My Question

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:

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

I took the above code from another answer but given this answer was posted 2 years ago, I have assumed it it out of date and therefore this may not be the most efficient method anymore...

Answer Source

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

select * from locations where 
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:

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.