|
|
Posted by Jerry Stuckle on 01/04/07 23:06
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
Bill,
The spatial extensions are good, but if you have a large database it
could take significant time to calculate the distance for every row.
Another option:
If you want kennels within 500 miles:
Compute the latitude 500 miles north of your user's location (nlat)
Repeat for 500 miles south (slat)
Do the same for east and west longitude (elong & wlong)
Now use the spacial extensions in MySQL, but also filter on the
longitude and latitude values you computed (in your WHERE clause), i.e.
WHERE long >= elong AND long <= wlong AND lat >= nlat AND lat <= slat
This can filter out a lot of the values before the computations (have an
index on long-lat for even better performance).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
[Back to original message]
|