Spatial extensions

Spatial extensions

am 17.12.2009 01:31:43 von m5

I have table with 2 million rows of geographic points (latitude, =
longitude).=20
Given a location -- say, 52=BA, -113.9=BA -- 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Spatial extensions

am 17.12.2009 01:45:13 von Gavin Towey

Yes, spatial indexes are very fast:

Query would be something like:

SET @center =3D GeomFromText('POINT(37.372241 -122.021671)');

SET @radius =3D 0.005;

SET @bbox =3D 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=E9 Fournier [mailto:m5@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=BA, -113.9=BA -- what's the fastest way to quer=
y the 10 closest points (records) from that table? Currently, I'm using a s=
imple 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 COLLATE=3Dla=
tin1_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 u=
p 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 COLLATE=3Dla=
tin1_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=3Dgtowey@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 th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Spatial extensions

am 17.12.2009 17:54:29 von m5

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:
>=20
> Query would be something like:
>=20
> SET @center =3D GeomFromText('POINT(37.372241 -122.021671)');
>=20
> SET @radius =3D 0.005;
>=20
> SET @bbox =3D 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, '))')
> );
>=20
> select id, astext(coordinates), Distance(@center,line_segment) as dist
> FROM places where MBRContains(@bbox, line_segment) order by dist limit =
10;
>=20
> Regards,
> Gavin Towey
>=20
>=20
> -----Original Message-----
> From: Ren=E9 Fournier [mailto:m5@renefournier.com]
> Sent: Wednesday, December 16, 2009 4:32 PM
> To: mysql
> Subject: Spatial extensions
>=20
> I have table with 2 million rows of geographic points (latitude, =
longitude).
> Given a location -- say, 52=BA, -113.9=BA -- 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:
>=20
> 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_general_ci;
>=20
> My current query is fairly quick:
>=20
> SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN =
51.98228037384 AND 52.033153677 AND longitude BETWEEN -113.94770681881 =
AND -113.86685484296;
>=20
> But I wonder a couple things:
>=20
> 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)?
>=20
> 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_general_ci;
>=20
> 2. How would I write the query?
>=20
> ...Rene
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
>=20
>=20
> 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
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dm5@renefournier.com
>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Spatial extensions

am 17.12.2009 18:03:29 von Jim Ginn

------=_20091217120329_52566
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 8bit





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

>

------=_20091217120329_52566--

RE: Spatial extensions

am 17.12.2009 19:21:19 von Gavin Towey

Not only is it 5.1, but there's a special branch that has improved GIS func=
tions not found in the regular MySQL. I'm not sure if/when they're plannin=
g 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 Di=
stance function yourself as a stored procedure or UDF. Just google for mys=
ql+haversine or something similar.

The important part though is the MBRContains, which does an efficient box c=
ull and uses the spatial index. Oops, I forgot to change a couple occuranc=
es 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=E9 Fournier [mailto:m5@renefournier.com]
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 =3D GeomFromText('POINT(37.372241 -122.021671)');
>
> SET @radius =3D 0.005;
>
> SET @bbox =3D 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=E9 Fournier [mailto:m5@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, longitud=
e).
> Given a location -- say, 52=BA, -113.9=BA -- what's the fastest way to qu=
ery 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 COLLATE=3D=
latin1_general_ci;
>
> My current query is fairly quick:
>
> SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN 51.98228037384 A=
ND 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 COLLATE=3D=
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=3Dgtowey@ffn.com
>
>
> This message contains confidential information and is intended only for t=
he individual named. If you are not the named addressee, you are notified =
that reviewing, disseminating, disclosing, copying or distributing this e-m=
ail 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 yo=
ur system. E-mail transmission cannot be guaranteed to be secure or error-f=
ree as information could be intercepted, corrupted, lost, destroyed, arrive=
late or incomplete, or contain viruses. The sender therefore does not acce=
pt liability for any loss or damage caused by viruses or errors or omission=
s in the contents of this message, which arise as a result of e-mail transm=
ission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 9408=
9, USA, FriendFinder.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dm5@renefournier.c=
om
>


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Spatial extensions

am 19.12.2009 09:42:23 von m5

--Apple-Mail-9--562898272
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=iso-8859-1

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) |=20
| 2 | POINT(49.01415809 -110.01615511) |=20
| 3 | POINT(49.01424023 -110.00508075) |=20
| 4 | POINT(48.99978158 -110.01617366) |=20
| 5 | POINT(48.99978996 -110.00507794) |=20
| 6 | POINT(49.00683419 -110.02751996) |=20
| 7 | POINT(49.01402057 -110.03861578) |=20
| 8 | POINT(49.01407281 -110.02750442) |=20
| 9 | POINT(48.99974667 -110.0386263) |=20
| 10 | POINT(48.9997718 -110.0275421) |=20
+----+----------------------------------+
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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_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:
>=20
> http://downloads.mysql.com/forge/mysql-5.1.35-gis/
>=20
> 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.
>=20
> 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.
>=20
> Regards,
> Gavin Towey
>=20
> -----Original Message-----
> From: Ren=E9 Fournier [mailto:m5@renefournier.com]
> Sent: Thursday, December 17, 2009 8:54 AM
> To: Gavin Towey
> Cc: mysql
> Subject: Re: Spatial extensions
>=20
> 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.
>=20
> 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:
>=20
> "Function places.Distance does not exist"
>=20
> Also, where does line_segment come from in the below query?
> Thanks.
>=20
> ...Rene
>=20
> On 2009-12-17, at 8:45 AM, Gavin Towey wrote:
>=20
>> Yes, spatial indexes are very fast:
>>=20
>> Query would be something like:
>>=20
>> SET @center =3D GeomFromText('POINT(37.372241 -122.021671)');
>>=20
>> SET @radius =3D 0.005;
>>=20
>> SET @bbox =3D 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, '))')
>> );
>>=20
>> select id, astext(coordinates), Distance(@center,line_segment) as =
dist
>> FROM places where MBRContains(@bbox, line_segment) order by dist =
limit 10;
>>=20
>> Regards,
>> Gavin Towey
>>=20
>>=20
>> -----Original Message-----
>> From: Ren=E9 Fournier [mailto:m5@renefournier.com]
>> Sent: Wednesday, December 16, 2009 4:32 PM
>> To: mysql
>> Subject: Spatial extensions
>>=20
>> I have table with 2 million rows of geographic points (latitude, =
longitude).
>> Given a location -- say, 52=BA, -113.9=BA -- 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:
>>=20
>> 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_general_ci;
>>=20
>> My current query is fairly quick:
>>=20
>> SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN =
51.98228037384 AND 52.033153677 AND longitude BETWEEN -113.94770681881 =
AND -113.86685484296;
>>=20
>> But I wonder a couple things:
>>=20
>> 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)?
>>=20
>> 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_general_ci;
>>=20
>> 2. How would I write the query?
>>=20
>> ...Rene
>>=20
>>=20
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
>>=20
>>=20
>> 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
>>=20
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dm5@renefournier.com
>>=20
>=20
>=20
> 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
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dm5@renefournier.com
>=20


--Apple-Mail-9--562898272--

RE: Spatial extensions

am 21.12.2009 19:42:59 von Gavin Towey

--_000_30B3DF511CEC5C4DAE4D0D29050475341B1C1923A7AAApmgiloca l_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

How did you populate the point column? Should be something like:

UPDATE places SET point=3D =3DGeomFromText(CONCAT("Point(",longitude," ",la=
titude,")"));

You also should have a spatial index:

ALTER TABLE places MODIFY coordinates point NOT NULL, add spatial index (co=
ordinates);

You can check the data with the ASTEXT() function:

SELECT longitude, latitude, ASTEXT(coordinates) FROM places;


Hope this helps
-Gavin Towey

From: Ren=E9 Fournier [mailto:m5@renefournier.com]
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 i=
s how to use MBRContains on a table with a POINT column. For example, this =
works:

mysql> select id, astext(coordinates) FROM places where MBRContains(GeomFro=
mText('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(GeomFro=
mText('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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 COLLATE=3Dla=
tin1_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 func=
tions not found in the regular MySQL. I'm not sure if/when they're plannin=
g 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 Di=
stance function yourself as a stored procedure or UDF. Just google for mys=
ql+haversine or something similar.

The important part though is the MBRContains, which does an efficient box c=
ull and uses the spatial index. Oops, I forgot to change a couple occuranc=
es 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=E9 Fournier [mailto:m5@renefournier.com]
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 =3D GeomFromText('POINT(37.372241 -122.021671)');

SET @radius =3D 0.005;

SET @bbox =3D 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=E9 Fournier [mailto:m5@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=BA, -113.9=BA -- what's the fastest way to quer=
y the 10 closest points (records) from that table? Currently, I'm using a s=
imple 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 COLLATE=3Dla=
tin1_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 u=
p 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 COLLATE=3Dla=
tin1_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=3Dgtowey@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 th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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=3Dm5@renefournier.com



This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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=3Dm5@renefournier.com


________________________________
This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified tha=
t 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 s=
ystem. E-mail transmission cannot be guaranteed to be secure or error-free =
as information could be intercepted, corrupted, lost, destroyed, arrive lat=
e or incomplete, or contain viruses. The sender therefore does not accept l=
iability 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 transmissi=
on. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, U=
SA, FriendFinder.com

--_000_30B3DF511CEC5C4DAE4D0D29050475341B1C1923A7AAApmgiloca l_--

Re: Spatial extensions

am 22.12.2009 10:45:11 von m5

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:
>=20
> UPDATE places SET point=3D =3DGeomFromText(CONCAT("Point(",longitude," =
",latitude,")"));
>=20
> You also should have a spatial index:
>=20
> ALTER TABLE places MODIFY coordinates point NOT NULL, add spatial =
index (coordinates);
>=20
> You can check the data with the ASTEXT() function:
>=20
> SELECT longitude, latitude, ASTEXT(coordinates) FROM places;
>=20
>=20
> Hope this helps
> -Gavin Towey
>=20
> From: Ren=E9 Fournier [mailto:m5@renefournier.com]
> Sent: Saturday, December 19, 2009 12:42 AM
> To: Gavin Towey
> Cc: mysql
> Subject: Re: Spatial extensions
>=20
> Thanks Gavin.
>=20
> 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:
>=20
> 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:
>=20
> 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_general_ci;
>=20
> On 2009-12-18, at 2:21 AM, Gavin Towey wrote:
>=20
>=20
> 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:
>=20
> http://downloads.mysql.com/forge/mysql-5.1.35-gis/
>=20
> 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.
>=20
> 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.
>=20
> Regards,
> Gavin Towey
>=20
> -----Original Message-----
> From: Ren=E9 Fournier [mailto:m5@renefournier.com]
> Sent: Thursday, December 17, 2009 8:54 AM
> To: Gavin Towey
> Cc: mysql
> Subject: Re: Spatial extensions
>=20
> 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.
>=20
> 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:
>=20
> "Function places.Distance does not exist"
>=20
> Also, where does line_segment come from in the below query?
> Thanks.
>=20
> ...Rene
>=20
> On 2009-12-17, at 8:45 AM, Gavin Towey wrote:
>=20
>=20
> Yes, spatial indexes are very fast:
>=20
> Query would be something like:
>=20
> SET @center =3D GeomFromText('POINT(37.372241 -122.021671)');
>=20
> SET @radius =3D 0.005;
>=20
> SET @bbox =3D 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, '))')
> );
>=20
> select id, astext(coordinates), Distance(@center,line_segment) as dist
> FROM places where MBRContains(@bbox, line_segment) order by dist limit =
10;
>=20
> Regards,
> Gavin Towey
>=20
>=20
> -----Original Message-----
> From: Ren=E9 Fournier [mailto:m5@renefournier.com]
> Sent: Wednesday, December 16, 2009 4:32 PM
> To: mysql
> Subject: Spatial extensions
>=20
> I have table with 2 million rows of geographic points (latitude, =
longitude).
> Given a location -- say, 52=BA, -113.9=BA -- 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:
>=20
> 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_general_ci;
>=20
> My current query is fairly quick:
>=20
> SELECT SQL_NO_CACHE * FROM places WHERE latitude BETWEEN =
51.98228037384 AND 52.033153677 AND longitude BETWEEN -113.94770681881 =
AND -113.86685484296;
>=20
> But I wonder a couple things:
>=20
> 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)?
>=20
> 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=3DMyISAM AUTO_INCREMENT=3D50 DEFAULT CHARSET=3Dlatin1 =
COLLATE=3Dlatin1_general_ci;
>=20
> 2. How would I write the query?
>=20
> ...Rene
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
>=20
>=20
> 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
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dm5@renefournier.com
>=20
>=20
>=20
> 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
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dm5@renefournier.com
>=20
>=20
> ________________________________
> 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=3Dgcdmg-mysql-2@m.gmane.o rg