searching lat long with mysql with spatial

Question

Using mysql db. I have a column "latlong" which is a point type with a spatial index. I would like to get the closest locations 4 miles closest to the latitude and longitude.

So far I have this...

SELECT `postcode`,county, ( 3959 * acos( cos( radians(51.585738) ) * cos( radians( x(GeomFromText(astext(latlong))) ) ) * cos( radians( y(GeomFromText(astext(latlong))) ) - radians(-0.260878) ) + sin( radians(51.585738) ) * sin( radians( x(GeomFromText(astext(latlong))) ) ) ) ) AS distance  
FROM uk_p HAVING distance < 4 ORDER BY distance LIMIT 0 , 20

I only have 2900 records, and it takes approximately 0.0277 secs. Is there anyway of optimising this query, as I am worried that as the database grows, the slower this query will be...

Answer 1

You query is fine for 2900 records. For large dataset, you may want to use range queries with a bounding box surrounding the center. See the answer in this question.

Answer 2

You can take one of two approaches: one is to pre-calculate the radian conversions and trig functions and store the results in the new columns in your table - this will save you doing some of the work that doesn't change for every postal code. You'll still have to calculate the distance to your target and there's nothing you can do about that, since you probably want it to work with any arbitrary target.

The other approach is to be less exact. If you are working with a reasonably small area that isn't near one of the poles, you can assume the world is flat (seriously) and use cartesian coordinate math instead of spherical trig.

Or, to combine the two approaches, you could do a one time mapping of all your postal codes into a rectilinear grid and trust that that approximation is good enough for your purposes. That would work best if you're dealing with a relatively small geographic area.

In other words, you don't need a general solution (which you are using now) if you can make some assumptions about your specific domain.