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 ...
'polygon((x0 y0,x1 y0,x1 y1,x0 y1,x0 y0))'
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,
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.
> DROP TEMPORARY TABLE IF EXISTS `temp_a`;
> ('table' instead of 'tabel'; table name only once; backticks around
> table name instead of quotes)
> Regards, Jigal.
Road Tech Computer Systems Ltd