Posted by bill on 01/05/07 13:38

bill wrote:
> I am about to start on a module that will accept a location from a user,
> use Google geolocation services to get the lat/lon and then compute the
> distance from the site visitor to about 100 kennels (could grow to 1000
> eventually).
>
> Once I have the distance I need to sort the kennels by distance and
> present those within 500 miles.
>
> Approach 1: read the kennel lat/lon from the mysql kennel record
> compute the distance
> write the distance back into the kennel record
> when done,
> using the distance as an index, read back
> the kennel data until I get > 500 miles.
> If I use this approach would I need to lock the database to prevent
> a second site visitor from possibly colliding ?
>
> Approach 2: read the kennel lat/lon from the mysql kennel record
> compute the distance
> put the kennel ID (20 char) and distance into an
> associative array
> when done,
> sort the array
> look up kennels by kennel ID that are within 500mi
>
> Approach 3: read the kennel lat/lon from the mysql kennel record
> compute the distance
> put the data back into the $rows array, proceed as in
> approach 2.
> I don't know if one can add a column to an already
> established array and/or re-use a dummy variable in the array.
>
> I ask for suggestions on the best approach.
> The kennel record has a row size of 1.118 bytes
>
> bill


The record size 1,118 bytes was just information.

Thanks to all who made suggestions.
Are the MySql spatial extensions part of 4.1.2 (which is what my
provider provides) or an extension that I need to install ?

the formula for distance is:

// Convert lattitude/longitude (degrees) to radians for
calculations
$lat1 = deg2rad($lat1);
$lon1 = deg2rad($lon1);
$lat2 = deg2rad($lat2);
$lon2 = deg2rad($lon2);

// Find the deltas
$delta_lat = $lat2 - $lat1;
$delta_lon = $lon2 - $lon1;

// Find the Great Circle distance
$temp = pow(sin($delta_lat/2.0),2) + cos($lat1) *
cos($lat2) * pow(sin($delta_lon/2.0),2);
$distance = 3956 * 2 * atan2(sqrt($temp),sqrt(1-$temp));

[Back to original message]