Corruption in spatial index

[Ben Harper]

Hi,
Has anybody ever seen anomalous behaviour on an R*Tree. Specifically,
false negatives?
I'm using Spatialite 2.4.0 RC.

What I end up with is a table into which I can insert new geometry,
but that geometry does not get retrieved by an appropriate RTree
search.
Indeed, even a search which spans all of my geometry does not yield
everything.

For example:
select count(rowid) from idx_elec_lv_cable_geometry where xmax >=
-9e30 AND xmin <= 9e30 AND ymax >= -9e90 AND ymin <= 9e30;
17235

select (count*) from elec_lv_cable;
17241

So I'm missing 6 records there.

I can't discern anything strange about the data, or my methods.
I'm curious to know whether anybody has seen this kind of thing
before?

Thanks,
Ben

ps. This message is cross-posted to sqlite-users mailing list.

[a.fu...@lqt.it]

Hi Ben,

"real" R*Tree corruption is the one explained here:
http://www.gaia-gis.it/spatialite-2.4.0-5/SpatialIndex-Update.pdf
but this doesn't seems to be your case.

So I strongly suspect this is a really subtle and
completely different problem.

The SQLite's own documentation is really clear:
http://www.sqlite.org/rtree.html

the R*Tree internally stores any coordinate as 32-bit
floating point numbers (aka single precision).
but SpatiaLite stores any coordinate as 64-bit
floating point numbers (aka double precision).

Some rounding or trumcation occurs for sure:
happily enough, it's usually so small to be
quite unnoticeable.

A shart tale of mine: I become quite crazy to identify the
cause explaining such "odd" issue the first time a sample
"missing one feature in several millions" was submitted
to my attention few weeks ago.
And at the end of this defatiguating debugging session
I duly discovered that it simply was due to some adverse
truncation side effect.

Resolving this issue is absolutely trivial: you simply
have to *enlarge* a little bit your query BBOX by a
quite infinitesimal amount, so to recover for any
possible undesired numeric rounding/truncation.

A little "curiosity": just by hazard I had some weeks
ago an interesting conversation with one of the PostGIS
developers.
So I learnt that PostGIS as well has to face this same
identical problem: 64 bit "double" values for Geometries,
but only 32 bit "single" values for the GIST spatial index.
And the PostGIS solution is exactly the above one:
they "silently" enlarge the query bounding box passed by
the user before actually searching into the GIST Spatial
Index :-)

bye Sandro

[Ben Harper]

Thanks for the details - and that PDF.
My case does appear strange indeed, since I do have "rowid" INTEGER
PRIMARY KEY AUTOINCREMENT" for this particular table.
But it's definitely not a floating point precision issue.
Even if I extend the bounds of my query to basically FLT_MAX, I still
don't get my fresh records in the result set.

I'll keep on hunting.

[Ben Harper]

Thanks for the details - and that PDF.
My case does appear strange indeed, since I do have "rowid" INTEGER
PRIMARY KEY AUTOINCREMENT" for this particular table.
But it's definitely not a floating point precision issue.
Even if I extend the bounds of my query to basically FLT_MAX, I still
don't get my fresh records in the result set.

I'll keep on hunting.

On Jul 5, 3:43 pm, a.furi...@lqt.it wrote: