SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Poi based on latitude and longitude

    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

    PHP Code:
    $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.

    Code:
    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. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Nastyness, but it... should... work?

    PHP Code:
    $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. #3
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Starlion,
    Wouldn't this scan the whole table of the huge records? I am afraid that will again take a long time right?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •