List:General Discussion« Previous MessageNext Message »
From:Jim Ginn Date:December 17 2009 5:03pm
Subject:Re: Spatial extensions
View as plain text  



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:m5@stripped]
>> 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=1
>>
>>
>> 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=1
>>
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To
unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>

> 

Thread
Spatial extensionsRené Fournier17 Dec
  • RE: Spatial extensionsGavin Towey17 Dec
    • Re: Spatial extensionsRené Fournier17 Dec
      • Re: Spatial extensionsJim Ginn17 Dec
      • RE: Spatial extensionsGavin Towey17 Dec
        • Re: Spatial extensionsRené Fournier19 Dec
          • RE: Spatial extensionsGavin Towey21 Dec
            • Re: Spatial extensionsRené Fournier22 Dec