List:General Discussion« Previous MessageNext Message »
From:René Fournier Date:December 22 2009 9:45am
Subject:Re: Spatial extensions
View as plain text  
Hi Gavin,

OK, I finally tracked down the source of the trouble... My CREATE TABLE query included a
regular KEY index instead of SPATIAL KEY -- that's why the MBRCONTAINS wasn't working...
Works now!!

Thanks for your help.

On 2009-12-22, at 2:42 AM, Gavin Towey wrote:

> 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:m5@stripped]
> 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:m5@stripped]
> 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: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
> 
> 
> 
> 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
> 
> 
> ________________________________
> 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

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