Tobias Kolb wrote:
Hi,
I'm currently experimenting with the R*Tree support in SQLite. I have to
collect some log data over a timespan and save it linked to the area (not
the exact path, just a minimum bounding rect around) it was collected
(min/max latitude and min/max longitude). Based on this data I want to do
range queries like "get all records within a area (rectangle)". R*Trees
seems perfect for this use case.
So I've created my log data table (but that doesn't matter) and a virtual
rtree table:
CREATE VIRTUAL TABLE log_chunks_geoindex USING rtree(
id,
latitude_min,
latitude_max,
longitude_min,
longitude_max
);
The latitude/longitude values are stored as floating point values (e.g.
49.12345, 8.67890). I generated a million records for testing and tried
inserting them in the virtual table. A few hundrets inserted fine, then I
got stucked with this one:
insert into log_chunks_geoindex (id, latitude_min, latitude_max,
longitude_min, longitude_max)
values(473, 49.000472, 49.000473, 8.000472, 8.000473);
Error: constraint failed
Which constraint is affected in this case? The only constraint I know is
that max value has to be greater or equal than min value. But this is the
case! I guess it has something to do with that SQLite stores R*Tree values
only as single precision (32bit) floating point values. But the
documentation says that if the inserted value cannot be represented by a
32bit floating point the value is rounded down for lower-bound coordinated
and rounded up for upper-bound coordinates. So the constraint (min<=max)
should always be met in this case. If I change latitude_max from 49.000473
to 49.0004731 it inserts well.
At a guess you might be running into some rounding error. Why are your
bounding areas so small? Can you make them bigger?
I use a much larger area (about 10 miles square in my case) and then do
a brute force search on all results returned to find the one I want.
This works quite reliably.
--
Project Management Consulting and Training
http://www.ridgelineconsultingllc.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
