Distance by zip code formula
I found this formula and it works, however what I am trying to do is enable it to filter by distance from its zip code.
I found a formula that calculates the distance between two latitude and longitude coordinates.
(3956 * ACOS(COS(RADIANS(start_lat)) * COS(RADIANS(end_lat)) * COS(RADIANS(end_lon) - RADIANS(start_lon)) + SIN(RADIANS(start_lat)) * SIN(RADIANS(end_lat))))
I have a filter on a page that sends the following information
$_POST["start_latitude"] = 34.023179;
$_POST["start_longitude"] = -118.303965;
$_POST["max_distance"] = 50;
If i do
SELECT (3956 * ACOS(COS(RADIANS({$_POST["start_latitude"]})) * COS(RADIANS(34.018626))
* COS(RADIANS(-118.249978) - RADIANS({$_POST["start_longitude"]}))
+ SIN(RADIANS({$_POST["start_latitude"]})) * SIN(RADIANS(34.018626))))
In this case, the distance will be displayed as kilometers 4 miles.
How can I convert this formula to my target to find locations no more than 50 miles from the coordinates entered? I know that everything has to be done, this is a change of places in the formula, but I am not very good at school math.
SELECT place_name FROM places
WHERE place_latitude = ? AND place_longitude = ?
EDIT:
I have a table of places where I got 1000 records in the format
id place_name latitude longitude
1 xxx 432423 -43432
2 yyy 523452 -54353
3 zzz 553453 -53422
etc.
So the formula should do something like
SELECT place_name FROM places
(CALCULATE each place distance from
$_POST["start_latitude"] and $_POST["start_longitude"]
and select only ones that) < 50
source to share
Put the distance formula in the sentence WHERE
:
SELECT place_name
FROM places
WHERE (3956 * ACOS(COS(RADIANS(:start_latitude)) * COS(RADIANS(latitude)) * COS(RADIANS(-longitude) - RADIANS(:start_longitude)) + SIN(RADIANS(:start_latitude)) * SIN(RADIANS(latitude))))
< :max_distance
source to share
This resource and web service is helpful, check it out: http://www.codebump.com/services/PlaceLookup.asmx
function calc_distance($point1, $point2)
{
$radius = 3958; // Earth radius (miles)
$deg_per_rad = 57.29578; // Number of degrees/radian (for conversion)
$distance = ($radius * pi() * sqrt(
($point1['lat'] - $point2['lat'])
* ($point1['lat'] - $point2['lat'])
+ cos($point1['lat'] / $deg_per_rad) // Convert these to
* cos($point2['lat'] / $deg_per_rad) // radians for cos()
* ($point1['long'] - $point2['long'])
* ($point1['long'] - $point2['long'])
) / 180);
return $distance; // Returned using the units used for $radius.
}
EDIT
Check out the page: http://www.mssqltips.com/sqlservertip/2690/calculate-the-geographical-distance-between-two-cities-in-sql-server/
-
DECLARE @98001 GEOGRAPHY;
DECLARE @Patzip GEOGRAPHY;
SELECT @98001 = Coordinates FROM ZipCodeLKUP INNER JOIN
Facility ON ZipCodeLKUP.ZipCode = Facility.ZipCode
Where Facility.ZipCode=98001
SELECT @Patzip = Coordinates FROM ZipCodeLKUP INNER JOIN
HIDIMV_year ON ZipCodeLKUP.ZipCode = HIDIMV_year .Patzip
where PATZIP in ('98001', '98466','97202')
SELECT @98001.STDistance(@Patzip)/1000 AS [Distance in KM]
source to share