Hi Gavin, OK, I finally tracked down the source of the trouble... My CREATE TABLE query included a regular KEY index instead of SPATIAL KEY -- that's why the MBRCONTAINS wasn't working... Works now!!
Thanks for your help. On 2009-12-22, at 2:42 AM, Gavin Towey wrote: > How did you populate the point column? Should be something like: > > UPDATE places SET point= =GeomFromText(CONCAT("Point(",longitude," > ",latitude,")")); > > You also should have a spatial index: > > ALTER TABLE places MODIFY coordinates point NOT NULL, add spatial index > (coordinates); > > You can check the data with the ASTEXT() function: > > SELECT longitude, latitude, ASTEXT(coordinates) FROM places; > > > Hope this helps > -Gavin Towey > > From: René Fournier [mailto:m...@renefournier.com] > Sent: Saturday, December 19, 2009 12:42 AM > To: Gavin Towey > Cc: mysql > Subject: Re: Spatial extensions > > Thanks Gavin. > > I've got part your query working... sort of. Something I can't figure out is > how to use MBRContains on a table with a POINT column. For example, this > works: > > mysql> select id, astext(coordinates) FROM places where > MBRContains(GeomFromText('POLYGON((48.6 -116.4,53.4 -116.4,53.4 -111.6,48.6 > -111.6,48.6 -116.4))'), GeomFromText('Point(49 -114)')) limit 10; > +----+----------------------------------+ > | id | astext(coordinates) | > +----+----------------------------------+ > | 1 | POINT(49.00701238 -110.00507933) | > | 2 | POINT(49.01415809 -110.01615511) | > | 3 | POINT(49.01424023 -110.00508075) | > | 4 | POINT(48.99978158 -110.01617366) | > | 5 | POINT(48.99978996 -110.00507794) | > | 6 | POINT(49.00683419 -110.02751996) | > | 7 | POINT(49.01402057 -110.03861578) | > | 8 | POINT(49.01407281 -110.02750442) | > | 9 | POINT(48.99974667 -110.0386263) | > | 10 | POINT(48.9997718 -110.0275421) | > +----+----------------------------------+ > 10 rows in set (0.00 sec) > But when I try to use the table's POINT column, nothing is returned: > mysql> select id, astext(coordinates) FROM places where > MBRContains(GeomFromText('POLYGON((48.6 -116.4,53.4 -116.4,53.4 -111.6,48.6 > -111.6,48.6 -116.4))'), (coordinates)) limit 10; > Empty set (0.00 sec) > What am I missing? For clarity, here's the table schema: > > 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; > > On 2009-12-18, at 2:21 AM, Gavin Towey wrote: > > > 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...@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