Re: Spatial extensions
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:m...@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 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:m...@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 = 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
RE: Spatial extensions
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:m...@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 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:m...@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 = 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
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:m...@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 = 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
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: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=arch...@jab.org
Re: Spatial extensions
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
RE: Spatial extensions
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:m...@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 = 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
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
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=arch...@jab.org
RE: Spatial extensions
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=arch...@jab.org
Spatial Extensions in MySQL: Multidimensional Points?
MySQL offers Spacial Extensions to operate with points in a 2 dimensional space. For a project I'm working on, I need to work with points in an N dimensional space and measure the distance among them. Is there a clever do to solve the following problem?: Let's say I have 4 3D point (X,Y,Z): (0,0,0), (1,1,1), (4,4,2), (-2,30,18) And for a new point (3, -1, 1.5), I need to know which of my other points are closer and what the distance is. Any clues? Thank you in advance, Jose.
Re: Spatial Extensions in MySQL: Multidimensional Points?
Hello. For a pity, I do not have an exact answer on how to do your task in a best way with MySQL. Searching in the archives gave me these links: http://lists.mysql.com/mysql/185851 http://lists.mysql.com/mysql/185846 Perhaps, you will need the help of stored routines or UDFs: http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html Jose Antonio wrote: MySQL offers Spacial Extensions to operate with points in a 2 dimensional space. For a project I'm working on, I need to work with points in an N dimensional space and measure the distance among them. Is there a clever do to solve the following problem?: Let's say I have 4 3D point (X,Y,Z): (0,0,0), (1,1,1), (4,4,2), (-2,30,18) And for a new point (3, -1, 1.5), I need to know which of my other points are closer and what the distance is. Any clues? Thank you in advance, Jose. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial Extensions in MySQL: Multidimensional Points?
Use 3d pythagorean theorem. the distance d between point1 (x1,y1,z1) and point2 (x2,y2,z2) is... d = sqrt((x1-x2)^2 + (y1-y2)^2 + (z1-z2)^2) as for building an index for this? i have no idea. but that is how you find the distance between two 3D points. b Jose Antonio wrote: MySQL offers Spacial Extensions to operate with points in a 2 dimensional space. For a project I'm working on, I need to work with points in an N dimensional space and measure the distance among them. Is there a clever do to solve the following problem?: Let's say I have 4 3D point (X,Y,Z): (0,0,0), (1,1,1), (4,4,2), (-2,30,18) And for a new point (3, -1, 1.5), I need to know which of my other points are closer and what the distance is. Any clues? Thank you in advance, Jose. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial Extensions to make a Dealer Locator?
I'm in the midst of the age-old problem of finding the right SQL to make a Dealer Locator by ZIP code. Like Steffan's posts from a couple weeks ago, I have the ZIP code data in one table with latitudes and longitudes, and the table of Dealers with ZIP codes in a separate table. While researching this I came across a reference to MySQL's Spatial Extensions: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions-in-mysql.html This is WAY over my head. The reference I found said these extensions are immature. I'm not even exactly sure what an extension is: does it mean that my ISP (PowWeb) probably doesn't have it installed? Has anyone else who has tackled this application found the Spatial Extensions to be useful, or is it better to stick with the basics and go the route discussed in Steffan's recent thread? - Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial Extensions to make a Dealer Locator?
Brian I'm in the midst of the age-old problem of finding the right SQL to make a Dealer Locator by ZIP code. Like Steffan's posts from a couple weeks ago, I have the ZIP code data in one table with latitudes and longitudes, and the table of Dealers with ZIP codes in a separate table. For great circle distance given latitudes longitudes you may find (as we did) that the current MySQL implementation of OpenGIS is simultaneously overkill (because of complexity) and underkill ( for features maturity). There are several reliable formulas for great circle distance including a few quoted here. PB Brian Dunning wrote: I'm in the midst of the age-old problem of finding the right SQL to make a Dealer Locator by ZIP code. Like Steffan's posts from a couple weeks ago, I have the ZIP code data in one table with latitudes and longitudes, and the table of Dealers with ZIP codes in a separate table. While researching this I came across a reference to MySQL's Spatial Extensions: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions-in-mysql.html This is WAY over my head. The reference I found said these extensions are immature. I'm not even exactly sure what an extension is: does it mean that my ISP (PowWeb) probably doesn't have it installed? Has anyone else who has tackled this application found the Spatial Extensions to be useful, or is it better to stick with the basics and go the route discussed in Steffan's recent thread? - Brian -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.4/143 - Release Date: 10/19/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: spatial extensions - SRID
Andras Kende [EMAIL PROTECTED] wrote on 08/13/2005 10:32:07 PM: Hello, I have a html page with 70+ form fields some like 40 fields are only used for entering quantity numbers… Don’t want to do Mysql table with 70 fields… Is it a good idea to put this 50 fields of the form fields into a single text mysql field? Somehow process it with php before, put inside of some kind of xml structure? Thanks, Andras Kende http://www.kende.com You ask, Is it a good idea to put this 50 fields of the form fields into a single text mysql field?. IMHO, the answer is No. To do so is to defeat the purpose of using a database in the first place. You might as well be saving your data into a text file. In proper database design, every single logical item of information should be contained within a single row with the elements of that item of information all residing within their own columns in that row. To merge several fields of information into one larger field actually _removes_ useful information from your data and makes it harder to work with. It is quite common to have several dozen fields defined within a single table. More than seventy columns is definitely NOT the widest table I have ever seen (I have seen network monitoring tables that were hundreds of columns wide, for example). If you need help modelling your data into an efficient database schema, talk to us (the list) about it and I am sure we can help. You describe what you want to store and we can offer our opinions on how to store it. The more details you provide, the better we can help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: spatial extensions - SRID
At 16:27 -0400 8/12/05, [EMAIL PROTECTED] wrote: To the powers that be: Can we get a MySql GIS/spatial list?? This is going to be a very popular area (actually it is already). I am trying to use the Spatial extensions to MySql. To be honest PostGIS has many more features, but MySql is my favorite, and on top of that, the applications I am using only work with MySql. My problem is this. From the manual, all Geometry contains: Its SRID, or Spatial Reference Identifier. This value identifies the geometry's associated Spatial Reference System that describes the coordinate space in which the geometry object is defined. In MySQL, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry. This is a big issue. Will this be the same in version 5? Or will the SRIDs actually be used? I would like to just represent everything in lat/lon, not on a planar surface. Now, if the SRIDs are used in MySql 5, where is the list of acceptable values? As far as I know, it's the same in MySQL 5. Also, to developers on the list: perhaps it would be better if these were text values like 'NAD83'?? I think that is unlikely to happen. The OpenGIS spec defines the SRID to be an integer, and the SRID() function to be an integer-valued function. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spatial extensions - SRID
Paul DuBois wrote: At 16:27 -0400 8/12/05, [EMAIL PROTECTED] wrote: This is a big issue. Will this be the same in version 5? Or will the SRIDs actually be used? I would like to just represent everything in lat/lon, not on a planar surface. Now, if the SRIDs are used in MySql 5, where is the list of acceptable values? As far as I know, it's the same in MySQL 5. Thanks Paul. Is there any one out there using MySql for any serious GIS applications? If you are, I would like to know what types of things have you done with it? Also, could you please respond and tell how you address the issue of calculations in a planar space (such as Distance(point, point) ) for example? -- http://www.douglassdavis.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: spatial extensions - SRID
Hello, I have a html page with 70+ form fields some like 40 fields are only used for entering quantity numbers… Don’t want to do Mysql table with 70 fields… Is it a good idea to put this 50 fields of the form fields into a single text mysql field? Somehow process it with php before, put inside of some kind of xml structure? Thanks, Andras Kende http://www.kende.com -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 0.0.338 / Virus Database: 267.10.8/71 - Release Date: 8/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spatial extensions - SRID
Andras, Is it a good idea to put this 50 fields of the form fields into a single text mysql field? Somehow process it with php before, put inside of some kind of xml structure? Nothing wrong with 70 columns unless there are interdependencies /or groupings amongst the 70 values that require normalisation. Definitely, 70 numeric columns would be a much better idea than one text column with 70 numeric values embedded in it! Sounds as if you need to read a bit about normalisation. PB - Andras Kende wrote: Hello, I have a html page with 70+ form fields some like 40 fields are only used for entering quantity numbers… Don’t want to do Mysql table with 70 fields… Is it a good idea to put this 50 fields of the form fields into a single text mysql field? Somehow process it with php before, put inside of some kind of xml structure? Thanks, Andras Kende http://www.kende.com No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.8/71 - Release Date: 8/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
spatial extensions - SRID
To the powers that be: Can we get a MySql GIS/spatial list?? This is going to be a very popular area (actually it is already). I am trying to use the Spatial extensions to MySql. To be honest PostGIS has many more features, but MySql is my favorite, and on top of that, the applications I am using only work with MySql. My problem is this. From the manual, all Geometry contains: Its SRID, or Spatial Reference Identifier. This value identifies the geometry's associated Spatial Reference System that describes the coordinate space in which the geometry object is defined. In MySQL, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry. This is a big issue. Will this be the same in version 5? Or will the SRIDs actually be used? I would like to just represent everything in lat/lon, not on a planar surface. Now, if the SRIDs are used in MySql 5, where is the list of acceptable values? Also, to developers on the list: perhaps it would be better if these were text values like 'NAD83'?? -- http://www.douglassdavis.com
Re: Spatial extensions and GIS
In both cases you will have to write your own code. MySQL, so far, only can store Spatial data and do very simple (still, r-tree optimized) GIS queries (like: all objects inside rect). mirza Terry Pothecary wrote: Hi. This may be a daft question, so forgive me: I am just starting out with the Spatial extensions and I am wondering how I can use it to determine the distances between any 2 landmarks that have been entered as POINTs in the database. The POINTs are entered with longitude and lattitude coordinates. I have 2 problems: 1) The Distance nor the Buffer functions have not been implemented. 2) How can I use MySQL to help me convert from lat / long to distances in miles (or KM)? Thanks. Terry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial extensions and GIS
Hi. This may be a daft question, so forgive me: I am just starting out with the Spatial extensions and I am wondering how I can use it to determine the distances between any 2 landmarks that have been entered as POINTs in the database. The POINTs are entered with longitude and lattitude coordinates. I have 2 problems: 1) The Distance nor the Buffer functions have not been implemented. 2) How can I use MySQL to help me convert from lat / long to distances in miles (or KM)? Thanks. Terry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using spatial extensions
Hi is there an easy way (through e.g. VCL components) to use the spatial features which will be offerd by MySQL 4.1 from Delphi? Rainer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial Extensions in MySQL 4.1 - converting to SRID values...
I am new to the world of GIS and have an application where I would like to use MySQL's (found in 4.1 alpha currently) Spatial Extensions. I have a set of latitude and longitude for various locations and am trying to figure out how to convert to the SRID units. I have started going thru the tons of info on OpenGIS and other related map projection sites, but it currently seems overwhelming. Has anyone out there converted from lat/long to SRID units for point geometry type in MySQL?? If there are any sites or otherrefs for a beginning tutorial on GIS topics please forward. I have been looking at PROJ.4 lib for performing these conversions - Am I on the right track? -- Bill Hess -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
Hi It seems to me that combination from the subject just doesn't work. Every time I try to incorporate spatial column into Innodb table Mysql dies. The simplest case, easy to repeat is bellow. When I'm trying to create table with just one spatial column I'm getting following error: mysql create table t (p polygon not null, spatial index(p)) type=innodb; ERROR 2013: Lost connection to MySQL server during query In error log mysql writes: assertion 0 failed: file ha_innodb.cc, line 1547 030929 16:52:24 mysqld restarted I have two identical systems and both demonstrate behaviour described above. About systems config. Mysql: Version: '4.1.0-alpha' OS: FreeBSD 4.8-RELEASE So could someone test this and confirm or disconfirm above-mentioned abnormal behaviour. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
G B U [EMAIL PROTECTED] wrote: It seems to me that combination from the subject just doesn't work. Every time I try to incorporate spatial column into Innodb table Mysql dies. The simplest case, easy to repeat is bellow. When I'm trying to create table with just one spatial column I'm getting following error: mysql create table t (p polygon not null, spatial index(p)) type=innodb; ERROR 2013: Lost connection to MySQL server during query In error log mysql writes: assertion 0 failed: file ha_innodb.cc, line 1547 030929 16:52:24 mysqld restarted I have two identical systems and both demonstrate behaviour described above. About systems config. Mysql: Version: '4.1.0-alpha' OS: FreeBSD 4.8-RELEASE So could someone test this and confirm or disconfirm above-mentioned abnormal behaviour. Currently you can use spatial columns only in the MyISAM tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
At 19:16 +0300 9/29/03, Victoria Reznichenko wrote: G B U [EMAIL PROTECTED] wrote: It seems to me that combination from the subject just doesn't work. Every time I try to incorporate spatial column into Innodb table Mysql dies. The simplest case, easy to repeat is bellow. When I'm trying to create table with just one spatial column I'm getting following error: mysql create table t (p polygon not null, spatial index(p)) type=innodb; ERROR 2013: Lost connection to MySQL server during query In error log mysql writes: assertion 0 failed: file ha_innodb.cc, line 1547 030929 16:52:24 mysqld restarted I have two identical systems and both demonstrate behaviour described above. About systems config. Mysql: Version: '4.1.0-alpha' OS: FreeBSD 4.8-RELEASE So could someone test this and confirm or disconfirm above-mentioned abnormal behaviour. Currently you can use spatial columns only in the MyISAM tables. Also, the crash described above is fixed in MySQL 4.1.1. GIS features still are available only in MyISAM (as Victoria notes), but attempting to use them with InnoDB won't cause loss of connection or server restart. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
At 19:16 +0300 9/29/03, Victoria Reznichenko wrote:
G B U [EMAIL PROTECTED] wrote:
It seems to me that combination from the subject just doesn't work.
Every time I try to incorporate spatial column into Innodb table
Mysql dies. The simplest case, easy to repeat is bellow.
When I'm trying to create table with just one spatial column I'm
getting following error:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
ERROR 2013: Lost connection to MySQL server during query
In error log mysql writes:
assertion 0 failed: file ha_innodb.cc, line 1547
030929 16:52:24 mysqld restarted
I have two identical systems and both demonstrate behaviour
described above.
About systems config.
Mysql: Version: '4.1.0-alpha'
OS: FreeBSD 4.8-RELEASE
So could someone test this and confirm or disconfirm above-mentioned
abnormal behaviour.
Currently you can use spatial columns only in the MyISAM tables.
Also, the crash described above is fixed in MySQL 4.1.1. GIS features
still are available only in MyISAM (as Victoria notes), but attempting
to use them with InnoDB won't cause loss of connection or server restart.
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that all is working
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2 2, 1 2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
On Mon, Sep 29, 2003 at 09:33:29PM +0400, G B U wrote:
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that all is working
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2 2, 1 2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row in set (0.00 sec)
Is it *really* an InnoDB table?
--
Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED] | http://jeremy.zawodny.com/
MySQL 4.0.15-Yahoo-SMP: up 15 days, processed 553,853,916 queries (407/sec. avg)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
On Mon, Sep 29, 2003 at 09:33:29PM +0400, G B U wrote:
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that all is working
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2 2, 1 2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row in set (0.00 sec)
Is it *really* an InnoDB table?
Yes it is really InnoDB table and I'm really confused now.
mysql show table status like 't';
+--+++--++-+
-+--+---++-+-+--
--+---++--+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_le
ngth | Index_length | Data_free | Auto_increment | Create_time | Update_time | C
heck_time | Charset | Create_options | Comment |
+--+++--++-+
-+--+---++-+-+--
--+---++--+
| t| InnoDB | Dynamic|1 | 16384 | 16384 |
NULL |16384 | 0 | NULL | NULL| NULL| N
ULL | latin1_swedish_ci || InnoDB free: 4096 kB |
+--+++--++-+
-+--+---++-+-+--
--+---++--+
1 row in set (0.01 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
At 21:33 +0400 9/29/03, G B U wrote:
At 19:16 +0300 9/29/03, Victoria Reznichenko wrote:
G B U [EMAIL PROTECTED] wrote:
It seems to me that combination from the subject just doesn't work.
Every time I try to incorporate spatial column into Innodb table
Mysql dies. The simplest case, easy to repeat is bellow.
When I'm trying to create table with just one spatial column I'm
getting following error:
mysql create table t (p polygon not null, spatial index(p))
type=innodb;
ERROR 2013: Lost connection to MySQL server during query
In error log mysql writes:
assertion 0 failed: file ha_innodb.cc, line 1547
030929 16:52:24 mysqld restarted
I have two identical systems and both demonstrate behaviour
described above.
About systems config.
Mysql: Version: '4.1.0-alpha'
OS: FreeBSD 4.8-RELEASE
So could someone test this and confirm or disconfirm above-mentioned
abnormal behaviour.
Currently you can use spatial columns only in the MyISAM tables.
Also, the crash described above is fixed in MySQL 4.1.1. GIS features
still are available only in MyISAM (as Victoria notes), but attempting
to use them with InnoDB won't cause loss of connection or server restart.
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that all is working
See:
http://www.mysql.com/doc/en/Creating_spatial_columns.html
I'll add a note to the beginning of the chapter as well to make this
more obvious.
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2 2, 1
2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row in set (0.00 sec)
My guess is that the table type may not really be InnoDB. What does
SHOW CREATE TABLE t or SHOW TABLE STATUS LIKE 't' say?
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incompatibility: FreeBSD+MySQL 4.1.0+InnoDB+Spatial Extensions
At 22:05 +0400 9/29/03, G B U wrote:
On Mon, Sep 29, 2003 at 09:33:29PM +0400, G B U wrote:
Hmm... am I missing something?! IIRC there is nothing manual about Innodb
not supporting spatial extensions. And on windows it seems that
all is working
fine. This is from my Win2000 box with the same Mysql version:
mysql create table t (p polygon not null, spatial index(p)) type=innodb;
Query OK, 0 rows affected (0.00) sec
mysql insert into t (p) values (geomfromtext('polygon((1 1, 2
2, 1 2, 1 1))'));
Query OK, 1 row affected (0.02) sec
mysql select astext(p) from t;
++
| astext(p) |
++
| POLYGON((1 1,2 2,1 2,1 1)) |
++
1 row in set (0.00 sec)
Is it *really* an InnoDB table?
Yes it is really InnoDB table and I'm really confused now.
It's a bug. The Windows version of 4.1.0 will allow this, but it shouldn't.
You should change your table to MyISAM, because it probably will
be disallowed in 4.1.1.
mysql show table status like 't';
+--+++--++-+
-+--+---++-+-+--
--+---++--+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_le
ngth | Index_length | Data_free | Auto_increment | Create_time |
Update_time | C
heck_time | Charset | Create_options | Comment |
+--+++--++-+
-+--+---++-+-+--
--+---++--+
| t| InnoDB | Dynamic|1 | 16384 | 16384 |
NULL |16384 | 0 | NULL | NULL|
NULL| N
ULL | latin1_swedish_ci || InnoDB free: 4096 kB |
+--+++--++-+
-+--+---++-+-+--
--+---++--+
1 row in set (0.01 sec)
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with spatial extensions
Firstable thanks for all, now I have two more questions: The first, I would like to know if the JDBC driver supports the spatial extensions, and the second question is how much stable is version alpha 4.1 of MySQL? Thanks: Enrique ___ Yahoo! Messenger - Nueva versión GRATIS Super Webcam, voz, caritas animadas, y más... http://messenger.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with spatial extensions
Hi, I would like to have a column of a table of the type GeometryCollection, but when I try to type a simple example like the ones in the manual, I get this: mysql CREATE TABLE geom (g GEOMETRY); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'GEOMETRY)' at line 1 I'm running server mysqld-nt version 4.0.13 on a windows NT, the mysql client is the same version. Have I to install some plug-in or change some option in the configuration file? I need help. Thanks and excuse me by the english (I'm spanish). Enrique ___ Yahoo! Messenger - Nueva versión GRATIS Super Webcam, voz, caritas animadas, y más... http://messenger.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with spatial extensions
Enrique, Your English is great so don't apologise! Looking on the web site at http://www.mysql.com/doc/en/Spatial_extensions_in_MySQL.html , it appears that this feature was introduced in server version 4.1, so I'm afraid you'll have to upgrade your server. Best regards, Andy -Original Message- From: Enrique Andreu [mailto:[EMAIL PROTECTED] Sent: 05 August 2003 08:08 To: [EMAIL PROTECTED] Subject: Problems with spatial extensions Hi, I would like to have a column of a table of the type GeometryCollection, but when I try to type a simple example like the ones in the manual, I get this: mysql CREATE TABLE geom (g GEOMETRY); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'GEOMETRY)' at line 1 I'm running server mysqld-nt version 4.0.13 on a windows NT, the mysql client is the same version. Have I to install some plug-in or change some option in the configuration file? I need help. Thanks and excuse me by the english (I'm spanish). Enrique ___ Yahoo! Messenger - Nueva versión GRATIS Super Webcam, voz, caritas animadas, y más... http://messenger.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[MySQL Spatial Extensions] Questions?
Hi, As a newcomer to this mailing list -- and to the MySQL database environment -- my main field of interest is the use of Spatial data. Before I start dropping my questions (a have a few) all over this list, two quick ones to start with: 1) For now, is the [EMAIL PROTECTED] the place to discuss MySQL's Spatial Extensions? 2) Are there any plans to create a separate list (e.g. [EMAIL PROTECTED]) in the future? Kind regards, Gijsbert Noordam ISIS Benelux BV The Netherlands T +31 345-544 744 F +31 345-544 777
Re: [MySQL Spatial Extensions] Questions?
Gijsbert Noordam [EMAIL PROTECTED] wrote: As a newcomer to this mailing list -- and to the MySQL database environment -- my main field of interest is the use of Spatial data. Before I start dropping my questions (a have a few) all over this list, two quick ones to start with: 1) For now, is the [EMAIL PROTECTED] the place to discuss MySQL's Spatial Extensions? Yes. 2) Are there any plans to create a separate list (e.g. [EMAIL PROTECTED]) in the future? Nope. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
