• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

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?
0
Rakz0r
Asked:
Rakz0r
  • 4
2 Solutions
 
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
 
Rakz0rAuthor Commented:
my answer gave the best optimisation
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now