searching closest postcode using latitude longitude with mysql spatial type

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?
Rakz0rAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
yes you can do this...
add a where condition and add

where
X(Geomfromtext(Astext(latlong))) between 51.585738 and 51.585738+4
and
Y(Geomfromtext(Astext(latlong))) between -0.260878 and -0.260878+4

what I think is, filter the data first before calculating the distance for all points...
hope it may improve it :)
0
Rakz0rAuthor Commented:
I added the filter (below), and it does not bring up all the results that I expected. Anything wrong with this? Was expecting 14 records, but instead got 5 after adding 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 where X(Geomfromtext(Astext(latlong))) between 51.585738 and 51.585738+4 and Y(Geomfromtext(Astext(latlong))) between -0.260878 and -0.260878+4
 HAVING distance < 4 ORDER BY distance LIMIT 0 , 20
0
Rakz0rAuthor Commented:
I changed it slightly, but this only shedded off 0.0028 secs off the result. Any other ways to optimise this? Am i getting the latitude and longitude value correctly from the latlong column using x( GeomFromText( astext( latlong ))) ???

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
WHERE ( 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 )))))))<4
ORDER BY distance
0
Rakz0rAuthor Commented:
ok I have managed to optimise it further.

instead of X(Geomfromtext(Astext(latlong))) to get the lat value from the point, simply use
x(latlong).

Original query was taking 0.0251 sec but now it takes 0.0035 sec with the query below.

So now I have...

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rakz0rAuthor Commented:
my answer gave the best optimisation
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.