List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:December 21 2009 6:42pm
Subject:RE: Spatial extensions
View as plain text  
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