Rene:
We've easily integrated GIS with MySQL into our
sites:
http://tenant.com/map-search.php
http://yearlyrentals.com
http://acnj.com/map.php
...
Thanks!
Jim Ginn
Visit My Work
(888)
546-4466 office
(609) 226-5709 cell
> 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
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To
unsubscribe: http://lists.mysql.com/mysql?unsub=...@oats.com
>
>
