• 0 Vote(s) - 0 Average
• 1
• 2
• 3
• 4
• 5
 Near Latitide&Longitide; for given postcode El Forum Guest   06-04-2012, 05:35 AM [eluser]veledrom[/eluser] Hi, Apologised if not relevant to CI. I am using http://codeigniter.com/wiki/Google_Maps_API_V3_Library for my purposes. Let me explain what I need with an example. I have a postcode NW1 5LR and its LATITUDE:51.546069024064 LONGITUDE:-0.235949008191785 coming from user entry. I also have many other postcodes and their Latitide&Longitide; values in database. What formula should I use to get only those postcodes which are in 1 mil away maximum? Thanks El Forum Guest   06-04-2012, 05:53 AM [eluser]Learn CodeIgniter[/eluser] You have a whole table full of places with latitude and longitude’s associated with them. Just replace the \$lat and \$lon with the center point you want to find distances from. You can also change the distance => 1 to a number you want to search from. This will limit your results to all results that are greater then 1 miles from the starting point. You can change the distance for what you need. Code:```\$sql = "SELECT ((ACOS(SIN(\$lat * PI() / 180) * SIN(lat * PI() / 180) + COS(\$lat * PI() / 180) * COS(lat * PI() / 180) * COS((\$lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `table_name` HAVING `distance` => '1' ORDER BY `distance` ASC"; \$result = \$this->db->query(\$sql);``` El Forum Guest   06-04-2012, 06:08 AM [eluser]veledrom[/eluser] LOVELY. This works perfectly fine. One more question: What if I want distance in KMs instead of MILEs? Code:```SELECT postcode, ((ACOS(SIN('51.546069024064' * PI() / 180) * SIN(latitude * PI() / 180) + COS('51.546069024064' * PI() / 180) * COS(latitude * PI() / 180) * COS(('-0.235949008191785' - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `postcodes` HAVING `distance` <= '1' ORDER BY `distance` ASC``` El Forum Guest   06-04-2012, 10:54 AM [eluser]veledrom[/eluser] I don't understand how this query knows that value 1 in HAVING clause represents MILE? Is it really 1 MILE or 1 KM? El Forum Guest   06-04-2012, 11:12 AM [eluser]veledrom[/eluser] This is in KMs. Code:```SELECT      postcode,      (((ACOS(sin(('51.546069024064' * PI() / 180)) * SIN((latitude * PI() / 180)) +      COS(('51.546069024064' * PI() / 180)) * COS((latitude * PI() / 180)) *      COS((('-0.235949008191785' - longitude) * PI() / 180)))) * 180 / PI()) * 60 * 1.1515 * 1.609344) AS Distance     FROM postcodes     HAVING Distance <= '1'     ORDER BY Distance ASC```