From: Ben Clewett
Date: January 10 2006 3:35pm
Subject: Re: temporary table issue
List-Archive: http://lists.mysql.com/mysql/193733
Message-Id: <43C3D448.3060809@clewett.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks for the excellent advise.

In the mean while a friend Googled me an article suggesting that this 
can be done using the POINT and SPATIAL INDEX.  But I prefer your ideas.

I'll show what I found because I want to see if anybody has an opinion 
on problems with either method:

Here I Store coordinates as POINT data types.  Adding a SPATIAL INDEX on 
the field.  I can then select a rectangle against the index to include 
all points I want:

SELECT AsText(point_field) FROM ...
WHERE MBRContains(
   GeomFromText(
     'polygon((x0 y0,x1 y0,x1 y1,x0 y1,x0 y0))'
   ), point_field);

This does use the SPATIAL INDEX.  Therefore if my subset of points is in 
a massive table, this *should* be the most efficient way.

But the index does not seem nearly as fast as liner (normal) indexes. 
Hence using a less effective liner index may be better...

After this method I still need select a radius within these data points, 
more effort.


Another answer I got suggests a third method just to make things worse:

Divide the area into cells.  Give each cell an ID.  Store data against 
that ID.  A bit like a hash-table for 2D data.  For the area you want, 
calculate all the ID's you need and select against them.  Then sort the 
data afterwards.  Select a size of cell to suit most effective queries.


I will have to benchmark...

Thanks for the help,

Ben




Jigal van Hemert wrote:
> Xiaobo Chen wrote:
> 
>> Hi, all
>>
>> I am trying to use this with error:
>>
>> drop temporary tabel temp_a if exists 'temp_a';
>>
>> it said syntax error.
> 
> 
> Try:
> 
> DROP TEMPORARY TABLE IF EXISTS `temp_a`;
> 
> ('table' instead of 'tabel'; table name only once; backticks around 
> table name instead of quotes)
> 
> http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
> 
> Regards, Jigal.


-- 
Ben Clewett
+44(0)1923 460000
Project Manager
Road Tech Computer Systems Ltd
http://www.roadrunner.uk.com