finding out datasets in a certain distance of a zip code
| From: | Merlin Morgenstern | Date: | Tue, 27 Apr 2010 15:13:21 +0000 |
| Subject: | finding out datasets in a certain distance of a zip code | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-304659@lists.php.net to get a copy of this message | ||
Hi there,
I am trying to find mysql db entries inside a tabel with the help of php that are in the distance of a certain amount of kilometers from a given zip code.
Unfortunatelly something must be wrong, as I do not get the desired results. Has anybody experience with that?
Here is the code I wrote:
$plz = '79279';
$distance = 1000;
###################################################
# find geo data for this zip code
$stmt ="
SELECT
lat,
lang
FROM
test.zip
WHERE
zip = '$plz'
";
$row=db_get_row2($stmt);
$breite = deg2rad($row->lat);
$laenge = deg2rad($row->lang);
###################################################
if ($breite){ // only if results
###################################################
# search for the members
$stmt = "
SELECT SQL_CALC_FOUND_ROWS
cl.ID,
g.city,
g.area_1 AS quarter,
g.* ,
IFNULL( (
ACOS( (
SIN( $breite ) * SIN( RADIANS( lat ) ) ) + ( COS( $breite ) * COS( RADIANS( lat ) ) * COS( RADIANS( lang ) - $laenge ) ) ) *6371
), 0
) AS e
FROM
test.zip g,
test.cl cl
WHERE
IFNULL( (
ACOS( (
SIN( $breite ) * SIN( RADIANS( lat ) ) ) + ( COS( $breite ) * COS( RADIANS( lat ) ) * COS( RADIANS( lang ) - $laenge ) ) ) *6371
), 0
) < $distance
AND g.id = cl.zip_id
GROUP BY cl.ID
ORDER BY
e ASC
";
$result = execute_stmt($stmt, $link);
while ($row = mysql_fetch_object($result)){
$entfernung_km = round(($row->e*1.4),2);
echo 'cl: '.$row->cl_id.' '.$entfernung_km.'<br>';
}
$num_results = db_numrows($result);
###################################################
} // end if results
Thank you for any help!
Merlin