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 ;