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?
 
Rakz0rConnect With a Mentor Author 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
 
HainKurtConnect With a Mentor Sr. 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:
my answer gave the best optimisation
0
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.

All Courses

From novice to tech pro — start learning today.