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