Poi based on latitude and longitude

Databases
1

Dear All,
I have a big table of poi’s based on latitude and longitude. So then based on a given latitude and longitude I would like to find the nearest point in my poi table. Can I use some mysql functionality to do to be best optimize way. The problem I am currently doing it in php using the standard method as below

$distance = (3958*3.1415926*sqrt(($lat-floatval($row['lat']))*($lat-floatval($row['lat'])) + cos($lat/57.29578)*cos(floatval($row['lat'])/57.29578)*($long-floatval($row['long']))*($long-floatval($row['long'])))/180);

Below is my table.

CREATE TABLE IF NOT EXISTS `poi` (
  `poiID` int(11) NOT NULL auto_increment,
  `type` varchar(50) NOT NULL,
  `locationName` varchar(200) NOT NULL,
  `state` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `lat` float NOT NULL,
  `long` float NOT NULL,
  PRIMARY KEY  (`poiID`),
  KEY `lat` (`lat`,`long`),
  KEY `lat_2` (`lat`),
  KEY `long` (`long`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=201046 ;
2

Nastyness, but it… should… work?


$sql = "SELECT type,locationName,state,city,lat,long, 
ATAN2(SQRT((SIN((lat-".$lat.")/2)*SIN((lat-".$lat.")/2))+(COS(".$lat.")*COS(lat)*(SIN((lon-".$lon.")/2)*SIN((lon-".$lon.")/2)))),SQRT(1-((SIN((lat-".$lat.")/2)*SIN((lat-".$lat.")/2))+(COS(".$lat.")*COS(lat)*(SIN((lon-".$lon.")/2)*SIN((lon-".$lon.")/2)))))) AS distance 
FROM poi
ORDER BY distance
LIMIT 1"

(Note: I chopped off the extraneous constants 2 and R, which make no difference to order)

3

Dear Starlion,
Wouldn’t this scan the whole table of the huge records? I am afraid that will again take a long time right?