MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:December 17 2009 12:45am
Subject:RE: Spatial extensions
View as plain text  
Yes, spatial indexes are very fast:

Query would be something like:

SET @center = GeomFromText('POINT(37.372241 -122.021671)');

SET @radius = 0.005;

SET @bbox = GeomFromText(CONCAT('POLYGON((',
  X(@center) - @radius, ' ', Y(@center) - @radius, ',',
  X(@center) + @radius, ' ', Y(@center) - @radius, ',',
  X(@center) + @radius, ' ', Y(@center) + @radius, ',',
  X(@center) - @radius, ' ', Y(@center) + @radius, ',',
  X(@center) - @radius, ' ', Y(@center) - @radius, '))')
  );

select id, astext(coordinates), Distance(@center,line_segment) as dist
FROM places where MBRContains(@bbox, line_segment) order by dist limit 10;

Regards,
Gavin Towey


-----Original Message-----
From: René Fournier [mailto:m5@stripped]
Sent: Wednesday, December 16, 2009 4:32 PM
To: mysql
Subject: Spatial extensions

I have table with 2 million rows of geographic points (latitude, longitude).
Given a location -- say, 52º, -113.9º -- what's the fastest way to query the 10
closest points (records) from that table? Currently, I'm using a simple two-column index
to speed up queries:

CREATE TABLE `places` (
 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `latitude` decimal(10,8) NOT NULL,
 `longitude` decimal(12,8) NOT NULL
 PRIMARY KEY (`id`),
 KEY `latlng` (`latitude`,`longitude`)
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

My current query is fairly quick:

SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN 51.98228037384 AND 52.033153677
AND longitude BETWEEN -113.94770681881 AND -113.86685484296;

But I wonder a couple things:

1. Would MySQL's [seemingly anemic] spatial extensions would speed things up if I added a
column of type POINT (and a corresponding spatial INDEX)?

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;

2. How would I write the query?

...Rene


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


This message contains confidential information and is intended only for the individual
named.  If you are not the named addressee, you are notified that reviewing,
disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. 
Please notify the sender immediately by e-mail if you have received this e-mail by mistake
and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be
secure or error-free as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore does not accept
liability for any loss or damage caused by viruses or errors or omissions in the contents
of this message, which arise as a result of e-mail transmission. [FriendFinder Networks,
Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
Thread
Spatial extensionsRené Fournier17 Dec
  • RE: Spatial extensionsGavin Towey17 Dec
    • Re: Spatial extensionsRené Fournier17 Dec
      • Re: Spatial extensionsJim Ginn17 Dec
      • RE: Spatial extensionsGavin Towey17 Dec
        • Re: Spatial extensionsRené Fournier19 Dec
          • RE: Spatial extensionsGavin Towey21 Dec
            • Re: Spatial extensionsRené Fournier22 Dec