Solved
searching closest postcode using latitude longitude with mysql spatial type
Posted on 2012-03-27
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...
Or does anyone know of any other approach that will be alot quicker?