can i incorporate a large mathmatical function in my query

[justride]

I have a db of zip codes, towns, states lats and longs...
can i build a dynamci query that takes a zip code and only pulls out records that are within a certain criteria?

$db = new My_DB("SELECT *
FROM airports AS a
WHERE id IN ('$origin', '$destination')");

$db->next_record();
$a = deg2rad($db->f('latitude'));
$b = deg2rad($db->f('longitude'));
$origin_id = $db->f('id');
$origin_name = $db->f('name');

$db->next_record();
$c = deg2rad($db->f('latitude'));
$d = deg2rad($db->f('longitude'));
$destinati
$destinati


$r=3963.1; //radius of the earth in miles

//calculate the distance between the two points
$distance = acos(
(cos($a) * cos($b) * cos($c) * cos($d)) +
(cos($a) * sin($b) * cos($c) * sin($d)) +
(sin($a) * sin($c))
) * $r;

if($origin == $destination) {
echo "Distance = 0
"; 
} else {
echo "Origin: ($origin_id) $origin_name
";
echo "Destination: ($destination_id) $destination_name
";
echo "Distance in miles: ", round($distance,4), "
";
echo "Distance in kilometers: ", round(($distance*1.609),4), "
";
}

I found this code online, it is somewhat straighforward.

somehow incorporate the computation in the query so i dont have to do the checking a php through a while loop or something.

thanks
chris

 

[justride]

i also will need to convert the zip codes to lat and lon on the fly

 

[sleipnir214]

You should be able to add your distance calculations to the WHERE clause of a SELECT query. But for the conversion of latitude and longitude for a ZIP code, I think you should precalculate.

I recommend that you add columns to the ZIP code table to record the latitude and longitude of a ZIP code, then update the table to populate those new columns. That way, you reduce resource requirements during your SELECT queries.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[justride]

thanks, i think i got it

select *,Z2.ZIPCODE,
truncate(
acos(
        ( sin(Z1.LATITUDE * 0.017453293)*sin(Z2.LATITUDE * 0.017453293) )
    +
        ( cos(Z1.LATITUDE * 0.017453293) *
          cos(Z2.LATITUDE * 0.017453293) *
          cos((Z2.LONGITUDE*0.017453293)-(Z1.LONGITUDE*0.017453293))
        )
    ) * 3956,1) as DISTANCE
from DINEZIPCODES Z1, DINEZIPCODES Z2
where Z1.ZIPCODE = '02842' and Z2.ZIPCODE <> '02842' 
and acos(
        ( sin(Z1.LATITUDE * 0.017453293)*sin(Z2.LATITUDE * 0.017453293) )
    +
        ( cos(Z1.LATITUDE * 0.017453293) *
          cos(Z2.LATITUDE * 0.017453293) *
          cos((Z2.LONGITUDE*0.017453293)-(Z1.LONGITUDE*0.017453293))
        )
    ) * 3956 between 5 and 10

thanks