List:General Discussion« Previous MessageNext Message »
From:dan Date:May 3 2010 4:56am
Subject:Re: mysql spatial functions (Was: Best index for searching on lat / long
data i.e. decimal vs. float)
View as plain text  
Ok... I am close.... I forgot an extra () in my POLYGON statement:

UPDATE `grid` SET lsd_poly = GeomFromText(CONCAT('POLYGON((',n,' ',e,',
',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,'))'));

(I also created a new GEOMETRY lsd_poly column rather than the poly POLYGON
one).

Now I need to figure out the rest of it...

Dan

On Sun, 2 May 2010 14:00:16 -0700, Ted Yu <yuzhihong@stripped> wrote:
> Have you declared poly to be of spatial type ?
> Cheers
> 
> On Sun, May 2, 2010 at 1:03 PM, dan <dan@stripped> 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 <yuzhihong@stripped> wrote:
>> > Have you tried replacing GeomFromText in place of PolygonFromText ?
>> >
>> > On Sun, May 2, 2010 at 10:59 AM, dan <dan@stripped> 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
> <baron@stripped>
>> >> 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 <dan@stripped> 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
> <yuzhihong@stripped>
>> 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 <dan@stripped>
> 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=1
>> >> >>>>
>> >> >>>>
>> >> >>
>> >> >> --
>> >> >> MySQL General Mailing List
>> >> >> For list archives: http://lists.mysql.com/mysql
>> >> >> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > 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=1
>> >>
>> >>
>>
Thread
mysql spatial functions (Was: Best index for searching on lat / long datai.e. decimal vs. float)dan2 May
Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)dan2 May
  • Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)Baron Schwartz2 May
    • Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)dan2 May
Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)dan2 May
Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)dan3 May
Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)dan3 May
  • Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)Rob Wultsch3 May
    • Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)dan3 May