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
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 wult...@gmail.com wrote:
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
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
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
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,',
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 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
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
On Sat, 01 May 2010 15:28:46 -0500, mos mo...@fastmail.fm 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
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
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
On Fri, 30 Apr 2010 17:14:06 -0500, mos mo...@fastmail.fm 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;
13 matches
Mail list logo