List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:January 10 2006 3:35pm
Subject:Re: temporary table issue
View as plain text  
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(
     '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,


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:
> ('table' instead of 'tabel'; table name only once; backticks around 
> table name instead of quotes)
> Regards, Jigal.

Ben Clewett
+44(0)1923 460000
Project Manager
Road Tech Computer Systems Ltd
temporary table issueXiaobo Chen10 Jan
  • Re: temporary table issueJigal van Hemert10 Jan
    • Re: temporary table issueXiaobo Chen10 Jan
    • Re: temporary table issueBen Clewett10 Jan
      • Re: temporary table issueJigal van Hemert10 Jan
        • Re: Geogrphic AdviseBen Clewett10 Jan
  • Re: temporary table issueBill Dodson10 Jan