add support for RTree indices

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Try to use spatial index in database to speed up spatial queries. 

What is the expected output? What do you see instead?
(Note: writing from memory)
If I put on device database file with spatial index created by spatialite_gui 
and try to access it (I think on prepare(..) function) I get exception with 
error about "lacking rtree module".

What version of the product are you using? On what operating system?
b247b2ca607c device,simulator; kubuntu

Please provide any additional information below.
attached diff adds _partial_ support for indices - I got rid of "lacking rtree 
module" exceptions and I can access "raw" index virtual tables like this: 
SELECT * FROM roads 
WHERE ROWID IN (
  SELECT pkid FROM idx_roads_geometry
  WHERE xmin<%f AND xmax>%f AND ymin<%f AND ymax>%f)
so it is kind of usable. BUT the proper way is to use MATCH operator:
WHERE ROWID IN 
  (SELECT pkid FROM idx_roads_geometry 
   WHERE pkid MATCH RTreeDistWithin(lon, lat, d))
and it doesn't work.

external info: 
http://www.sqlite.org/rtree.html
http://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html#t8 (a bit 
outdated)

To give more detailed account of what still doens't work and why, I would have 
to revert changes (from the diff), recompile, write junit tests and supply db 
with spatial index. For now for me attached patch is good enough, but if You 
would like to improve it further I can supply more information. 

Original issue reported on code.google.com by mateuszz...@gmail.com on 18 Mar 2012 at 9:53

Attachments:

• rtree.diff

[GoogleCodeExporter]

More information would be great.  I think working rtrees is an important 
feature.

Original comment by efroh...@gmail.com on 18 Mar 2012 at 1:42

• Changed state: Accepted

[GoogleCodeExporter]

In attached testcase witch should work if everything were fine and the database 
I used. Patch I send above IS applied. 

When using database without spatial index:
* select createSpatialIndex(...) (currently commented out in test) returns 1 
(nice), but select CheckSpatialIndex() returns 0 (not nice)
When using db with spatial index:
* every prepare(..) function throws exception "no such module: rtree"
* every exec(..) in test case crashes
* strangely I couldn't replicate situation where I would access index virtual 
table without crash. This works in my project

Original comment by mateuszz...@gmail.com on 19 Mar 2012 at 10:15

Attachments:

• SpatialiteRTreeTest.java
• test-2.3_copy_withindex.sqlite

[GoogleCodeExporter]

Thank you for all your work on R-Trees.  I included your test cases into the 
project.  In addition to adding R-Tree support I enabled geo callbacks (for 
RTreeDistWithin).

To make creating a Spatialite database from scratch easier I add a new function 
to the Database class called spatialite_create().  This will create and 
initialize the require spatial tables.  Example usage is in unit tests.

Original comment by efroh...@gmail.com on 1 Apr 2012 at 11:40

• Changed state: Fixed

[GoogleCodeExporter]

At the end of the ndk build i get an error:
obj/local/armeabi/objs/jsqlite/javasqlite-20120209/native/sqlite_jni.o: In 
function `Java_jsqlite_Database_internal_1init':
jni/javasqlite-20120209/native/sqlite_jni.c:5004: undefined reference to 
`spatialite_init'
obj/local/armeabi/libjsqlite.so] Error 1

Original comment by dear...@gmail.com on 2 Apr 2012 at 1:47

[GoogleCodeExporter]

@dearwes seems to be unrelated to this issue.  Please open a new issue with 
more details.

Original comment by efroh...@gmail.com on 2 Apr 2012 at 4:15