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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
