Best index for searching on lat / long data i.e. decimal vs. float
Best index for searching on lat / long data i.e. decimal vs. float
am 30.04.2010 23:54:07 von dan
I have a table with over 8 million rows of lat / long data all currently in
decimal(12,8) format (actually some in float(12,8)).
First question - should I have these all in decimal or float? I can't
think of any reason why I would actually do any math via mysql with this
data. I am just running queries and dumping the reults into a webapp.
Second question - my data is actual distinct rows with a north, east, south
and west lat / long for the boundary of a specific area. I want to search
based on a point lat / long (i.e. WHERE n > lat, s < lat, e > long, w <
long) to find the specific area that the point is located in.
I tried a simple index with my n,e,s & w but it still takes along time to
run such a query i.e. phpmyadmin times out. Any idea on the best structure
for such a query?
Thanks,
Dan T
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: Best index for searching on lat / long data i.e. decimal vs. float
am 01.05.2010 00:14:06 von mos
At 04:54 PM 4/30/2010, you wrote:
>I have a table with over 8 million rows of lat / long data all currently in
>decimal(12,8) format (actually some in float(12,8)).
>
>First question - should I have these all in decimal or float? I can't
>think of any reason why I would actually do any math via mysql with this
>data. I am just running queries and dumping the reults into a webapp.
I would use decimal because float may round the values slightly so it may
not show up properly in a search.
Example: Is it 55.05 or 55.049999?
>Second question - my data is actual distinct rows with a north, east, south
>and west lat / long for the boundary of a specific area. I want to search
>based on a point lat / long (i.e. WHERE n > lat, s < lat, e > long, w <
>long) to find the specific area that the point is located in.
Use Explain in front of your Select statement to see how many indexes it is
using.
You could shorten the sql to something like:
select .... from table where lat between s and n and long between w and e;
For me, this is simpler to read, although MySQL may optimize it to what you
had before.
(Assuming of course you have normalized the lat and long in the table and
don't need to specify east longitude or south latitude etc..)
>I tried a simple index with my n,e,s & w but it still takes along time to
>run such a query i.e. phpmyadmin times out. Any idea on the best structure
>for such a query?
What version of MySQL are you using? MySQL may be using only one index so
I'd recommend making a compound index of all 4 columns:
n,s,e,w columns. Now MySQL only has to traverse the single index and won't
have to access the data records to satisfy the query.
Mike
>Thanks,
>
>Dan T
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: Best index for searching on lat / long data i.e. decimal vs. float
am 01.05.2010 07:06:09 von dan
On Fri, 30 Apr 2010 17:14:06 -0500, mos wrote:
> At 04:54 PM 4/30/2010, you wrote:
>
> Use Explain in front of your Select statement to see how many indexes it
is
mysql> explain SELECT * FROM `grid` WHERE n > 49.012 AND s < 49.012 AND e >
110.0244 AND w < 110.0244;
+----+-------------+-------+------+---------------+------+-- -------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+------+---------------+------+-- -------+------+---------+-------------+
| 1 | SIMPLE | grid | ALL | section | NULL | NULL | NULL |
4155232 | Using where |
+----+-------------+-------+------+---------------+------+-- -------+------+---------+-------------+
> using.
> You could shorten the sql to something like:
>
> select .... from table where lat between s and n and long between w and
e;
Nope that results in an empty set. I don't think you can do that - it has
to be... n between 49 and 50.
> What version of MySQL are you using? MySQL may be using only one index so
> I'd recommend making a compound index of all 4 columns:
> n,s,e,w columns. Now MySQL only has to traverse the single index and
won't
> have to access the data records to satisfy the query.
v5.0.89
I have that index already named 'section'
This query works:
SELECT * FROM `grid` WHERE n > 49.012 AND s < 49.012 AND e > 110.0245 AND w
< 110.0245; but takes an average of 15 seconds
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: Best index for searching on lat / long data i.e. decimal vs. float
am 02.05.2010 02:20:47 von dan
On Sat, 01 May 2010 15:28:46 -0500, mos wrote:
>
> SELECT * FROM `grid`� force index(section) WHERE n > 49.012 AND s <
49.012
> AND e >
> 110.0244 AND w < 110.0244;
>
> It should give you the answer around 0.1 seconds. Give it a try. :-)
>
> Mike
It actually makes it worse by about 12 times!!
mysql> SELECT * FROM `grid` FORCE INDEX(section) WHERE n > 49.012 AND s <
49.012 AND e > 110.0244 AND w < 110.0244;
+------------+-------------+--------------+-------------+--- -----------+-------------+--------------+-------------+----- ---------+-------------+--------------+----------+
| lsd | n | e | s | w |
lat | lng | sec_n | sec_w | sec_s |
sec_e | province |
+------------+-------------+--------------+-------------+--- -----------+-------------+--------------+-------------+----- ---------+-------------+--------------+----------+
| 1301001014 | 49.01424023 | 110.02723089 | 49.01062631 | 110.02169300 |
49.01243327 | 110.02446195 | 49.01424026 | 110.02722931 | 48.99978638 |
110.00508118 | AB |
+------------+-------------+--------------+-------------+--- -----------+-------------+--------------+-------------+----- ---------+-------------+--------------+----------+
1 row in set (6 min 4.83 sec)
mysql> SELECT * FROM `grid` WHERE n > 49.012 AND s < 49.012 AND e >
110.0244 AND w < 110.0244;
+------------+-------------+--------------+-------------+--- -----------+-------------+--------------+-------------+----- ---------+-------------+--------------+----------+
| lsd | n | e | s | w |
lat | lng | sec_n | sec_w | sec_s |
sec_e | province |
+------------+-------------+--------------+-------------+--- -----------+-------------+--------------+-------------+----- ---------+-------------+--------------+----------+
| 1301001014 | 49.01424023 | 110.02723089 | 49.01062631 | 110.02169300 |
49.01243327 | 110.02446195 | 49.01424026 | 110.02722931 | 48.99978638 |
110.00508118 | AB |
+------------+-------------+--------------+-------------+--- -----------+-------------+--------------+-------------+----- ---------+-------------+--------------+----------+
1 row in set (33.26 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
mysql spatial functions (Was: Best index for searching on lat / long datai.e. decimal vs. float)
am 02.05.2010 08:12:54 von dan
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=gcdmg-mysql-2@m.gmane.org
Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)
am 02.05.2010 17:15:21 von dan
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 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 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=yuzhihong@gmail.com
>>
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: mysql spatial functions (Was: Best index for searching on lat /
am 02.05.2010 19:34:14 von Baron Schwartz
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 wrote:
>
> I have seen that but I am stuck at just populating my POLYGON column
> (poly). =A0I have tried this:
>
> UPDATE `grid` SET poly =3D 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 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 wrote:
>>
>>>
>>> Can any one help me with understanding the mysql spatial functions? =A0=
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=3Dyuzhihong@gmail.com
>>>
>>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dbaron@xaprb.c=
om
>
>
--=20
Baron Schwartz
Percona Inc
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=3Dgcdmg-mysql-2@m.gmane.o rg
Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)
am 02.05.2010 19:59:50 von dan
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 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 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 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 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=yuzhihong@gmail.com
>>>>
>>>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: Â Â http://lists.mysql.com/mysql?unsub=baron@xaprb.com
>>
>>
>
>
>
> --
> Baron Schwartz
> Percona Inc
> 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=gcdmg-mysql-2@m.gmane.org
Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)
am 02.05.2010 22:03:43 von dan
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 wrote:
> Have you tried replacing GeomFromText in place of PolygonFromText ?
>
> On Sun, May 2, 2010 at 10:59 AM, dan 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
>> 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 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
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 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=yuzhihong@gmail.com
>> >>>>
>> >>>>
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe: http://lists.mysql.com/mysql?unsub=baron@xaprb.com
>> >>
>> >>
>> >
>> >
>> >
>> > --
>> > Baron Schwartz
>> > Percona Inc
>> > 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=yuzhihong@gmail.com
>>
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)
am 03.05.2010 00:07:26 von dan
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 wrote:
> Have you declared poly to be of spatial type ?
> Cheers
>
> On Sun, May 2, 2010 at 1:03 PM, dan 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 wrote:
>> > Have you tried replacing GeomFromText in place of PolygonFromText ?
>> >
>> > On Sun, May 2, 2010 at 10:59 AM, dan 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
>> >> 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 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
>> 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 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=yuzhihong@gmail.com
>> >> >>>>
>> >> >>>>
>> >> >>
>> >> >> --
>> >> >> MySQL General Mailing List
>> >> >> For list archives: http://lists.mysql.com/mysql
>> >> >> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=baron@xaprb.com
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Baron Schwartz
>> >> > Percona Inc
>> >> > 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=yuzhihong@gmail.com
>> >>
>> >>
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)
am 03.05.2010 06:56:33 von dan
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 wrote:
> Have you declared poly to be of spatial type ?
> Cheers
>
> On Sun, May 2, 2010 at 1:03 PM, dan 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 wrote:
>> > Have you tried replacing GeomFromText in place of PolygonFromText ?
>> >
>> > On Sun, May 2, 2010 at 10:59 AM, dan 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
>> >> 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 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
>> 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 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=yuzhihong@gmail.com
>> >> >>>>
>> >> >>>>
>> >> >>
>> >> >> --
>> >> >> MySQL General Mailing List
>> >> >> For list archives: http://lists.mysql.com/mysql
>> >> >> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=baron@xaprb.com
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Baron Schwartz
>> >> > Percona Inc
>> >> > 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=yuzhihong@gmail.com
>> >>
>> >>
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: mysql spatial functions (Was: Best index for searching on lat /
am 03.05.2010 08:39:41 von Rob Wultsch
>>> >> >>> On Sat, May 1, 2010 at 11:12 PM, dan wrote:
>>> >> >>>
>>> >> >>>>
>>> >> >>>> Can any one help me with understanding the mysql spatial
>>> >> >>>> functions?
>>> >> =A0I
>>> >> >>>> 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
>>> >> >>>>
Please at least considered PostGIS.
In my limited experience all the good GIS people I know use PG. Also
spatial indexes are limited to MyISAM in MySQL, which is a significant
limitation for many users.
http://postgis.refractions.net/
--=20
Rob Wultsch
wultsch@gmail.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: mysql spatial functions (Was: Best index for searching on lat / longdata i.e. decimal vs. float)
am 03.05.2010 17:55:11 von dan
It works great for me. After working out the bugs and adding the spatial
index I am now searching in the 0.05 second timeframe vs. minutes
otherwise.
Dan
On Sun, 2 May 2010 23:39:41 -0700, Rob Wultsch wrote:
>>>> >> >>> On Sat, May 1, 2010 at 11:12 PM, dan 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
>>>> >> >>>>
>
>
> Please at least considered PostGIS.
>
> In my limited experience all the good GIS people I know use PG. Also
> spatial indexes are limited to MyISAM in MySQL, which is a significant
> limitation for many users.
>
> http://postgis.refractions.net/
>
>
>
>
> --
> Rob Wultsch
> wultsch@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org