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:[email protected]]
> 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:[email protected]]
>> 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/[email protected]
>>
>>
>> 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/[email protected]
>>
>
>
> 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/[email protected]
>
