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]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
