On 15 Jun 2011, at 3:56am, Hoang Linh Duong wrote:
> I have tried R-Tree, the execution time is even longer.
>
> For a trigger to calculate the distance, is it too much a burden if the
> query is executed very frequently (the table Location is of more than 600k
> records). I suspect that it may take even longer time if we need to do
> update & query.
>
> After trying two ways to optimize (use standard query on 1 table, use
> R-Tree), i still cannot improve execution time (still >1min).
>
> Is there any other ways?
I would like to check that you are calculating the distance to different points
most times. So the example you gave is just one example, and the next time you
do the calculation you will calculate the distance to a different place.
After that I notice that you are interested only in the closest hundred points.
Since you have 600K records, you can reject most of them very quickly if they
are a long way away from your target place. So what you might do is divide up
your area into sectors. Make a table of sectors, and another table which says
which sectors are nearby (in case the target is near the edge of a sector. So
for example if your sectors are
ABCDE
FGHIJ
KLMNO
PQRST
Then if your target point is in sector D, then you only need to look for points
in sectors CDEHIJ. You can store a 'closeSectors' column in your Location
TABLE like this
name type category x y closeSectors
ABFG
ABCFGH
BCDGHI
… …
Every time you save a new row in Location, work out the sectors which are close
to it (you could look them up in another table) and save the 'closeSectors'
column too. Then when you come to do your SELECT, add in this clause:
... WHERE category = 17 AND closeSectors LIKE '%C%' ...
You can calculate the '%C%' first from another table with maximum and minimum X
and Y coordinates in.
For details on 'LIKE' expressions see half way down
<http://www.sqlite.org/lang_expr.html>
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
