Prev: Weird while issue
Next: auto indentation
From: Merlin Morgenstern on 27 Apr 2010 11:13
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
From: Nathan Rixham on 27 Apr 2010 11:28
Merlin Morgenstern wrote:
> 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

1: You really could do with using the mysql spatial extensions
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

2: here's an old old post to this list that may help you
http://coding.derkeiler.com/Archive/PHP/php.general/2008-09/msg00531.html

Best,

Nathan
From: "Tommy Pham" on 27 Apr 2010 11:59
Hi Nathan,

WOW!! 4GB when rar'ed. That must too you a while to compile that
information.

Regards,
Tommy

 | 
Pages: 1
Prev: Weird while issue
Next: auto indentation