• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Near Latitide&Longitide; for given postcode

#1
[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

#2
[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);

#3
[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

#4
[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?

#5
[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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.