0

I have a table with more than 8 millions record (geonames). Inside this table there are 19 columns, 2 of them are latitude and longitude.

I wish to find nearest place from latitude and longitude values and I do this query:

SELECT * , ( 6371 * ACOS( COS( RADIANS( 40.8333333 ) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS( 14.25 ) ) + SIN( RADIANS( 40.8333333 ) ) * SIN( RADIANS( latitude ) ) ) ) AS distance
FROM geoname
WHERE fclass =  'P'
HAVING distance <25
ORDER BY distance
LIMIT 0 , 20

I set a btree index whit fclass, latitude and longitude.

The issue is that the query take 5.6027 seconds. too much. Is there a way to optimize it? I'm doing something wrong?

Thanks

Luca
  • 848
  • 1
  • 14
  • 33
  • Have a look at [**How to optimize SQL query with calculating distance by longitude and latitude?**](http://stackoverflow.com/questions/3093964/how-to-optimize-sql-query-with-calculating-distance-by-longitude-and-latitude) – M Khalid Junaid Dec 13 '13 at 19:19
  • 1
    The first thing is to discrimitate the maximum using a rectangle distance example : `WHERE latitude < X AND latitude > Y AND longitude < Z AND longitude > W` that you calculate one time. That will eliminate 90-95% of possibilities and refinement with real distance will take much less time. – Alexandre Lavoie Dec 13 '13 at 19:33
  • Thanks Alexandre. This solution speed up at 0.04sec the query! :) – Luca Dec 14 '13 at 14:34

2 Answers2

0

To the best of my knowledge, regular relational databases aren't meant to provide facilities for spatial approximate queries.

In your place, I'd either move my data to a spatial database, or insert my data into a metric tree (a kd-tree may be the best choice here) and issue the query against the tree.

Felipe Martins Melo
  • 1,323
  • 11
  • 15
0

You can try your luck with spatial indices. That'll come at the cost of using the MyISAM storage engine, though,

DaSourcerer
  • 6,288
  • 5
  • 32
  • 55