Posted by Iván Sánchez Ortega on 01/04/07 18:14

-----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)

iD8DBQFFnUQhR56dWuhgxGgRAkICAJ93I7DECfOJ3plsEqE6kZAInt/QYQCgn0CE
DPiFh9QN3iUK6DqmeQd7PKE=
=l9d2
-----END PGP SIGNATURE-----

• Next in forum: Re: online word processors?
• Prev in forum: Re: CGI PHP vs. FastCGI vs. mod_php vs. application server?
• Thread view: Re: Suggestions for best practice: mysql vs array sorting

[Reply to this message]