Posted by Hendri Kurniawan on 01/05/07 00:40

Assumption:
1. long/lat of kennels are already stored in database
2. Using MySQL database
3. ##SQRT(bla bla bla)## is the formula to find the distance

MySQL actually provide mathematical functions. So why not use it to your
advantage.
SELECT kennel.*, ##SQRT(bla bla bla)##
FROM kennel
WHERE ##SQRT(bla bla bla)## < 500
ORDER BY ##SQRT(bla bla bla)##

Albeit this will take some processing time for MySQL.
Unless if there is a MySQL buff here that knows how to optimize this further

Hope that helps

Hendri Kurniawan



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

[Back to original message]