Not only is it 5.1, but there's a special branch that has improved GIS functions not found in the regular MySQL. I'm not sure if/when they're planning on rolling them back into mysql:
http://downloads.mysql.com/forge/mysql-5.1.35-gis/ If it's not possible to use that version, then you can still implement a Distance function yourself as a stored procedure or UDF. Just google for mysql+haversine or something similar. The important part though is the MBRContains, which does an efficient box cull and uses the spatial index. Oops, I forgot to change a couple occurances of "line_segment" to "coordinates" line_segment was just the column name I was using in my original query. Regards, Gavin Towey -----Original Message----- From: René Fournier [mailto:m...@renefournier.com] Sent: Thursday, December 17, 2009 8:54 AM To: Gavin Towey Cc: mysql Subject: Re: Spatial extensions Awesome, this is what I was trying to find, as you succinctly wrote it. I *really* appreciate getting pointed in the right direction, since I haven't found a lot of MySQL's GIS tutorials directed at what I'm trying to do. Still, a couple questions, the Distance() function you included, that must require 5.1 or higher right? 5.0.88 on my box throws an error: "Function places.Distance does not exist" Also, where does line_segment come from in the below query? Thanks. ...Rene On 2009-12-17, at 8:45 AM, Gavin Towey wrote: > 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:m...@renefournier.com] > 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=gto...@ffn.com > > > 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 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub...@renefournier.com > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
