CODEWITHSUNDEEP
mysqlmathdistance
Roman Toasov
Roman Toasov

Reputation: 821

Distance by zip code formula

I found this formula and it works, however what i trying to do is to give ability to filter by distance from his ZIP code.

I found formula that calculates 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 filter on page that sends following info

$_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))))

Will output distance as number of miles 4 miles in this case.

How can i convert this formula for my goal to find places no longer than say 50 miles from coordinates entered? I know all need to be done is change of places in formula, but i am not good with school math.

SELECT place_name FROM places 
    WHERE place_latitude = ? AND place_longitude = ?

EDIT:

I have places table where i got 1000 records in format

id place_name latitude longitude
 1  xxx        432423   -43432
 2  yyy        523452   -54353
 3  zzz        553453   -53422
etc.

So the formula has to 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

Upvotes: 3

Views: 2737

Answers (2)

EngineerCoder
EngineerCoder

Reputation: 1455

This resouce and web service usefull, check it: http://www.codebump.com/services/PlaceLookup.asmx

function calc_distance($point1, $point2)
{
    $radius      = 3958;      // Earth's 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 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]

Upvotes: 0

Barmar
Barmar

Reputation: 782785

Put the distance formula into the WHERE clause:

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

Upvotes: 1

Related Questions