poly is a polygon but I have not added a spatial index yet.
Here's where it gets weird. I tried adding dummy data:
mysql> SET @bbox = 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))';
Query OK, 0 rows affected (0.01 sec)
mysql> update grid set poly = GeomFromText(@bbox);
Query OK, 7876282 rows affected (7 min 7.04 sec)
Rows matched: 7876282 Changed: 7876282 Warnings: 0
mysql> select AsText(poly) from grid limit 10;
+------------------------------------+
| AsText(poly) |
+------------------------------------+
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
| POLYGON((0 0,10 0,10 10,0 10,0 0)) |
+------------------------------------+
10 rows in set (0.20 sec)
So it works but not my index:
mysql> ALTER TABLE grid ADD SPATIAL INDEX(poly);
ERROR 1252 (42000): All parts of a SPATIAL index must be NOT NULL
On Sun, 2 May 2010 14:00:16 -0700, Ted Yu <yuzhih...@gmail.com> wrote:
> Have you declared poly to be of spatial type ?
> Cheers
>
> On Sun, May 2, 2010 at 1:03 PM, dan <d...@tappin.ca> wrote:
>
>>
>> Tried it but no luck:
>>
>> mysql> UPDATE `grid` SET poly = GeomFromText(CONCAT('POLYGON(',n,'
',e,',
>> ',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));
>> Query OK, 0 rows affected (2 min 3.86 sec)
>> Rows matched: 7876282 Changed: 0 Warnings: 0
>>
>> mysql> select poly from grid limit 10;
>> +------+
>> | poly |
>> +------+
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> +------+
>> 10 rows in set (0.01 sec)
>>
>>
>>
>> On Sun, 2 May 2010 12:54:07 -0700, Ted Yu <yuzhih...@gmail.com> wrote:
>> > Have you tried replacing GeomFromText in place of PolygonFromText ?
>> >
>> > On Sun, May 2, 2010 at 10:59 AM, dan <d...@tappin.ca> wrote:
>> >
>> >>
>> >> I am still lost... I tried this:
>> >>
>> >> UPDATE `grid` SET poly = PolygonFromText(CONCAT('POLYGON(',n,' ',e,',
>> >> ',s,'
>> >> ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));
>> >>
>> >> I had my delimiters mixed up and I know my CONCAT worked:
>> >>
>> >> mysql> select CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,',
>> >> ',n,'
>> >> ',w,', ',n,' ',e,')') from grid limit 3;
>> >>
>> >>
>>
>>
+-------------------------------------------------------------------------------------------------------------------------------------------+
>> >> | CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,' ',w,',
>> >> ',n,'
>> >> ',e,')') |
>> >>
>> >>
>>
>>
+-------------------------------------------------------------------------------------------------------------------------------------------+
>> >> | POLYGON(49.07756615 101.36211395, 49.07390213 101.36211395,
>> 49.07390213
>> >> 101.36764145, 49.07756615 101.36764145, 49.07756615 101.36211395) |
>> >> | POLYGON(49.08123016 101.36211395, 49.07756615 101.36211395,
>> 49.07756615
>> >> 101.36764145, 49.08123016 101.36764145, 49.08123016 101.36211395) |
>> >> | POLYGON(49.08489418 101.36211395, 49.08123016 101.36211395,
>> 49.08123016
>> >> 101.36764145, 49.08489418 101.36764145, 49.08489418 101.36211395) |
>> >>
>> >>
>>
>>
+-------------------------------------------------------------------------------------------------------------------------------------------+
>> >>
>> >> But after my UPDATE my poly column is still full of NULL values.
>> >>
>> >> Dan
>> >>
>> >> On Sun, 2 May 2010 13:34:14 -0400, Baron Schwartz <ba...@xaprb.com>
>> >> wrote:
>> >> > Dan,
>> >> >
>> >> > I think you are trying to create a polygon based on the values in
>> >> > other columns in the same row. I think these other columns are
>> >> > named
>> >> > `n` and so on.
>> >> >
>> >> > Your mistake is that you are creating a text string,
>> >> > "POLYGON(......)"
>> >> > and embedding column names inside it. That won't work. Those
>> >> > column
>> >> > names are just part of a string. They are not literal values that
>> >> > the
>> >> > POLYGON() function can interpret. You will need to use CONCAT() or
>> >> > similar to build a string that POLYGON() can interpret.
>> >> >
>> >> > On Sun, May 2, 2010 at 11:15 AM, dan <d...@tappin.ca> wrote:
>> >> >>
>> >> >> I have seen that but I am stuck at just populating my POLYGON
>> >> >> column
>> >> >> (poly). I have tried this:
>> >> >>
>> >> >> UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s`
`e`,
>> >> >> `s`
>> >> >> `w`, `n` `w`, `n` `e`)');
>> >> >>
>> >> >> but my poly column just reports back NULL.
>> >> >>
>> >> >> the n, e, s & w columns are decimal lat / long data.
>> >> >>
>> >> >> Dan
>> >> >>
>> >> >> On Sun, 2 May 2010 06:43:13 -0700, Ted Yu <yuzhih...@gmail.com>
>> wrote:
>> >> >>> I think you may have seen this:
>> >> >>>
>> http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html
>> >> >>>
>> >> >>> On Sat, May 1, 2010 at 11:12 PM, dan <d...@tappin.ca> wrote:
>> >> >>>
>> >> >>>>
>> >> >>>> Can any one help me with understanding the mysql spatial
>> >> >>>> functions?
>> >> I
>> >> >>>> can
>> >> >>>> only seem to find bits and pieces of how-to's etc.
>> >> >>>>
>> >> >>>> I have an existing table of lat / long data representing unique
>> >> >>>> boundaries
>> >> >>>> i.e. rectangles and I want to search the table to find the
>> rectangle
>> >> >> that
>> >> >>>> bounds a specific point.
>> >> >>>>
>> >> >>>> Dan
>> >> >>>>
>> >> >>>> --
>> >> >>>> MySQL General Mailing List
>> >> >>>> For list archives: http://lists.mysql.com/mysql
>> >> >>>> To unsubscribe:
>> >> >> http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com
>> >> >>>>
>> >> >>>>
>> >> >>
>> >> >> --
>> >> >> MySQL General Mailing List
>> >> >> For list archives: http://lists.mysql.com/mysql
>> >> >> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=ba...@xaprb.com
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Baron Schwartz
>> >> > Percona Inc <http://www.percona.com/>
>> >> > Consulting, Training, Support & Services for MySQL
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com
>> >>
>> >>
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
