MySQL Forums
Forum List  »  GIS

Poi based on lat itude and longitude
Posted by: newbie Shai
Date: May 24, 2011 08:07PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Poi based on lat itude and longitude
5058
May 24, 2011 08:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.