Best practice: Finding the closest point -- POINT (51 -114)

Just wondering with MySQL 5.0, if using the spatial extensions provides =
any real performance advantage in the following scenario:

Table with ~1 million rows:

CREATE TABLE `places` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`),
KEY `coord` (`coordinates`(25))
) ENGINE=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_general_ci;

Given a point, say, 51=BA, -114=BA -- what's the fastest way to select =
the closest 20 places from the above table? As you can see, the table =
already has two indexes. I'm currently using the first one, with the =
following query=20

$sql =3D "SELECT *, SQRT( POW( 69.1 * ( latitude - ".$lat.") , 2 ) + =
POW( 69.1 * ( ".$lng." - longitude ) * COS( latitude / 57.3 ) , 2 ) ) AS =
distance FROM lsd WHERE latitude BETWEEN ".$sw[0]." AND ".$ne[0]." AND =
longitude BETWEEN ".$sw[1]." AND ".$ne[1]." ORDER BY distance ASC LIMIT =
0,20";

(So it pre-calculate a rectangle of sorts -- select all the points =
within them, then sort.)

But with Spatial extensions, it should be much easier, and much faster =
than this right? That's what I thought, except that still in 5.0 MySQL =
seems to lack very basic GIS operations.

....Rene=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg