CODEWITHSUNDEEP
phpsqlsearchlocationradius
Phill
Phill

Reputation: 51

MySQL find rows with lat/lng + radius within a search radius

does anyone know how to do a query which will allow a user to specify their lat / lng and a radius, and then search what could be a very large amount of locations which also have a lat lng + a radius and see if any overlap, and then allow for sorting by distance?

We've been asked to allow for companies to place markers on a map of suburbs that they operate in, with the ability to do a radius, but I can't find any queries on Google that do this.

I'm currently using the following query which does the job, 

(SELECT ( 6371.01 * acos( cos( radians( $lat ) ) * cos( radians( location.lat ) ) 
 cos( radians( location.lng ) - radians( $lng ) )
 sin( radians( $lat ) ) * sin( radians( location.lat ) ) ) ) AS distance
FROM location 
HAVING distance <= $radius 
ORDER BY distance ASC) as distance

But obviously this doesn't allow for the locations to have a radius.

The second part of this question, this search query is already part of a very large query.. are there any recommendations on optimizing this at all? Every time I google'd for examples on queries like the above there was always people warning about how expensive this query is. At the moment the only solution I can think of is to have a separate search server.

Upvotes: 0

Views: 796

Answers (1)

Robbie
Robbie

Reputation: 17710

Very simple answer: use the spacial geometry functions in mySQL (http://dev.mysql.com/doc/refman/5.5/en/spatial-extensions.html)

These have been around for a while, but got a nice upgrade in 5.6 that will make it easier to do your select. http://www.percona.com/blog/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/ has some details on it.

If you don't have 5.6 then use a "grid" system. Basically, when you store the point (x,y), you also store a grid identifier (X,Y) that covers a larger square. When searching other points nearby you know it's within grid (X-1 to X+1) and (Y-1 to Y+1); this reduces the number of points you need to search. Then check both the actual difference for x and y are within a square in mySQL, then, once you've reduced it massively, then do your cos/sin maths.

Upvotes: 1

Related Questions