473,324 Members | 2,531 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Suggestions for best practice: mysql vs array sorting

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
Jan 4 '07 #1
6 3097
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

bill wrote:
Once I have the distance I need to sort the kennels by distance
and present those within 500 miles.
[...]
I ask for suggestions on the best approach.
First, do use MySQL's spatial extensions to store the lat-long coordinates
of every kennel.

Second, do use spatial operators to filter and sort by distance. It should
go like this:

select name,Distance(GeomFromText(''),kennels.position) as dist from kennels
where dist < 1500 sort by dist asc;

Easy, huh? I think that letting the hard work to MySQL is the fastest way.

However, you may run on a problem: the earth is flat, and calculating the
distance on the surface of a (quasi-)sphere is not trivial. You may
research on earth projection models (mercator, etc) to "flatten" out the
earth, so MySQL returns a nice cartesian distance.
The kennel record has a row size of 1.118 bytes
Why? You can't change that or what?

- --
- ----------------------------------
Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-

Now listening to: Shirley Bassey - The Remix Album ... Diamonds Are
Forever0 - [9] Light My Fire (Twelftree's Lady Mix) (4:04) (95%)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFnUQhR56dWuhgxGgRAkICAJ93I7DECfOJ3plsEqE6kZ AInt/QYQCgn0CE
DPiFh9QN3iUK6DqmeQd7PKE=
=l9d2
-----END PGP SIGNATURE-----
Jan 4 '07 #2
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.
js*******@attglobal.net
==================
Jan 4 '07 #3
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
Jan 5 '07 #4
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jerry Stuckle wrote:
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
No, you shouldn't do that. If you're using spatial extensions, then define a
bounding box (with GeomFromText('POLYGON blahblahblah') ), then use a
spatial MBR intersection function to filter the data.

The result will be the same (filtering the points by a rectangle centered on
a point). However, by *only* using spatial functions, the Q-trees
algorithms can kick in, and filter the data faster than using four
non-spatial comparisons. After all, that's why Q-trees and spatial stuff
was invented.

- --
- ----------------------------------
Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-

Well, if you're not running Windows, how the hell else are you supposed to
get memory leaks? They don't just grow on B-Trees, y'know!
-- Tackhead, on Slashdot.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFncO5R56dWuhgxGgRAvvoAJ9D2WBkImlUvuiXYBc8q7 6ryiai9ACfVfcv
vG4d6yRqt9IldEAiqDrwJ60=
=8y+S
-----END PGP SIGNATURE-----
Jan 5 '07 #5
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));
Jan 5 '07 #6
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

bill wrote:
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 spatial extensions are avaiable by default, in MySQL 4.1 and higher.

- --
- ----------------------------------
Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-

Afternoon very favorable for romance. Try a single person for a change.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFnmW6R56dWuhgxGgRAjZgAJ9PNrbBfiqhxCYqQKpmI/6dqr+rGQCfdPvg
cbP6I46fbViv5xeyrTh46pc=
=OV7v
-----END PGP SIGNATURE-----
Jan 5 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: John Hicks | last post by:
Is there an accepted best practice on whether to store decimal currency amounts (e.g. dollars and cents) in MySQL decimal column types? Certainly, the most straightforward way is to use decimal...
0
by: Matt W | last post by:
Hi all, I'm planning to use MySQL's full-text search for my forum system (possibly 5+ million posts). I've been playing with it a lot lately to see the performance and functionality and have...
11
by: DrUg13 | last post by:
In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in...
3
by: PWalker | last post by:
Hi, I have written code that I would like to optimize. I need to push it to the limit interms of speed as the accuracy of results are proportional to runtime. First off, would anyone know any...
193
by: Michael B. | last post by:
I was just thinking about this, specifically wondering if there's any features that the C specification currently lacks, and which may be included in some future standardization. Of course, I...
5
by: Dan | last post by:
I wonder if anyone has suggestions for reducing the amount of time it would take to search my array using the function that I have written. I want to find a position in the array of an item that...
21
by: Imran | last post by:
I have a vector of integers, such as and I want to find out the number which occurs most frequently.what is the quick method. My array size is huge. what I am doing is 1. find out the...
4
by: al jones | last post by:
I need to create a collection of data and I'm not sure what approach to take - any suggestions appreciated. For example, using mp3's, If I collect: 1) song title 2) file name 3) producer 4)...
10
by: Tammy | last post by:
Hello all, I am wondering what is the best way to declare a struct to be used in other c and c++ files. Such as for a C API that will be used by others. 1. Declaring the typedef and the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.