From: René Fournier (m5renefournier.com)
Date: Sun Dec 13 2009 - 09:34:10 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Given a point, say, 51º, -114º -- 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

$sql = "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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]