OK, I tried the SQLite command line tool 3.7.17 and inserted all 1 million records without any error. Then I cleared the table, ran the same SQL again in SQLite Studio and *bang* same error. Seems to be a problem of SQLite Studio. Anyway, I'm glad thats no bug in SQLite and my problem is solved.
@Yan: The area size depends on the time. I collect some raw sensor data for about 30sec in a BLOB linked to the area. This can be a quite large or small area depending if you're moving or standing still. Later i have to find time chunks collected in a defined area. Thanks all! 2013/6/27 Yan Seiner <y...@seiner.com> > 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<http://www.ridgelineconsultingllc.com> > > > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
