Invalid spatial index

[Fabian]

Hello,

I have the problem that I cannot create a valid spatial index on my geometry column. The problem is persistent from 5.0.1 until the latest head revision.

I configure spatialite like this (using the current head): 

./configure --enable-examples=no --disable-rttopo --disable-gcp --enable-freexl=no --enable-libxml2=no --enable-minizip=no --enable-xlsxwriter=no --enable-lz4=no --enable-zstd=no --
enable-openjpeg=no --enable-webp=no --with-geosconfig=/usr/bin/geos-config

Corresponding versions at runtime:

select sqlite_version(), spatialite_version(), geos_version();
3.44.0|5.1.1-rc0|3.12.0-CAPI-1.18.0

The geometry column & index is created like this:

SELECT AddGeometryColumn('feature_geometry', 'center', 3857, 'POINT', 'XY');

SELECT CreateSpatialIndex('feature_geometry', 'center');

After that I start filling my table with data.

However, for some reasons the index is not valid and also cannot get rebuilt.

sqlite> SELECT CheckSpatialIndex('feature_geometry', 'center');
0

sqlite> SELECT RecoverSpatialIndex('feature_geometry', 'center');
1
sqlite> SELECT CheckSpatialIndex('feature_geometry', 'center');
0
sqlite> select count(*) from feature_geometry fg where IsValid(center) = false;
0
sqlite> SELECT DisableSpatialIndex('feature_geometry', 'center');
1
sqlite> drop table idx_feature_geometry_center;
sqlite> SELECT CreateSpatialIndex('feature_geometry', 'center');
1
sqlite> SELECT CheckSpatialIndex('feature_geometry', 'center');
0

At this stage I'm clueless whats the problem here.

Best regards Fabian

[Dane christian Neilson]

DIRECT SENDER IS HERE LETS DEAL.

Dane Christian Neilson




MT103/202 DIRECT WIRE TRANSFER
PAYPAL TRANSFER
CASHAPP TRANSFER
ZELLE TRANSFER
TRANSFER WISE
WESTERN UNION TRANSFER
BITCOIN FLASHING
BANK ACCOUNT LOADING/FLASHING
IBAN TO IBAN TRANSFER
MONEYGRAM TRANSFER
SLBC PROVIDER
CREDIT CARD TOP UP
SEPA TRANSFER
WIRE TRANSFER
GLOBALPAY INC US

Thanks.


NOTE; ONLY SERIOUS / RELIABLE RECEIVERS CAN CONTACT.

DM ME ON WHATSAPP FOR A SERIOUS DEAL.

+14234753476

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

On Mon, 27 Nov 2023 20:16:22 -0800 (PST), Fabian wrote:
> Hello,
>
> I have the problem that I cannot create a valid spatial index on my
> geometry column. The problem is persistent from 5.0.1 until the
> latest
> head revision.
>
> I configure spatialite like this (using the current head): 
>
> ./configure --enable-examples=no --disable-rttopo --disable-gcp
> --enable-freexl=no --enable-libxml2=no --enable-minizip=no
> --enable-xlsxwriter=no --enable-lz4=no --enable-zstd=no --
> enable-openjpeg=no --enable-webp=no
> --with-geosconfig=/usr/bin/geos-config
>

Hi Fabian,

the problem arises from the fact that you are disabling too
many dependencies.

note: some of them are not libspatialite dependencies at all
and ./configure reports a warning:

-----------------
configure: WARNING: unrecognized options: --enable-xlsxwriter,
--enable-lz4, --enable-zstd, --enable-openjpeg, --enable-webp
-----------------

the real critical point is that you are disabling the RtTopo
library, but this way it then becomes impossible to create
all the metadata tables essential for the correct functioning
of SpatiaLite.

Maybe you haven't noticed, but you should have seen error
messages about the absence of the "geometry_column" table,
which is fundamental for all operations on the Spatial Index.

how to fix the issue:
1. build librttopo (it's really simple and easy)
2. then configure again as follows:

./configure --enable-examples=no --disable-gcp \
--enable-freexl=no --enable-libxml2=no --enable-minizip=no \
--with-geosconfig=/usr/bin/geos-config

bye Sandro

[Fabian]

Hello Sandro,

thank you very much for your support!

I once planned to bundle various sqlite extensions, to make it easier to ship it with an android app. However, it was an experiment back then and is not required anymore. 

That's why the configure was messed up a bit. I remember I was a bit scared of including RtTopo, because the bundled library will become GPL mandatory.

I followed your advice and used your provided configuration to recompile spatialite. 

RtTopo is now available which I can confirm via:

sqlite> SELECT rttopo_version();
1.1.0

I'm also able to use InitSpatialMetaDataFull(1); now, which previously wasn't due to the lack of RtTopo.

However, I still got the same problem.

I decided to roll up everything from scratch and spin up a fresh debian docker container to use the official libsqlite3-mod-spatialite package, just to be sure.

I am able to reproduce the same problem with the official package.

I pushed everything on a github repo: https://github.com/FaFre/spatialite_index/

The *.gz is the compressed .dump of the table.

db.db is the database that has been built using the following commands:

# sqlite3 db.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> SELECT load_extension('/usr/lib/x86_64-linux-gnu/mod_spatialite.so');

sqlite> select InitSpatialMetaDataFull(1);
1
sqlite> .read /db/database/dump.sql
sqlite> SELECT RecoverGeometryColumn('geo', 'center', 3857, 'POINT', 'XY');
1
sqlite> SELECT CreateSpatialIndex('geo', 'center');
1
sqlite> SELECT CheckSpatialIndex('geo', 'center');
0
sqlite>

It is really strange and I suspect something is getting caused by some specific points. 

I tried to execute some random inserts on an empty table and the index stays consistent.

Unfortunately I don't have the tools for C debugging ready at the moment.

Best regards Fabian

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

On Tue, 28 Nov 2023 04:18:14 -0800 (PST), Fabian wrote:
> # sqlite3 db.db
> SQLite version 3.40.1 2022-12-28 14:03:47
> Enter ".help" for usage hints.
> sqlite> SELECT
> load_extension('/usr/lib/x86_64-linux-gnu/mod_spatialite.so');
>
> sqlite> select InitSpatialMetaDataFull(1);
> 1
> sqlite> .read /db/database/dump.sql
> sqlite> SELECT RecoverGeometryColumn('geo', 'center', 3857, 'POINT',
> 'XY');
> 1
> sqlite> SELECT CreateSpatialIndex('geo', 'center');
> 1
> sqlite> SELECT CheckSpatialIndex('geo', 'center');
> 0
> sqlite>
>
> It is really strange and I suspect something is getting caused by
> some
> specific points. 
>

Hi Fabian,

CheckSpatialIndex() is expected to return 0 (=corrupted) only if the
number of valid geometries into the table and the number of rows
into the R*Tree differ.
on any other error it will return -1

you can easily check by yourself by executing the same sql queries
automatically performed by the source code.

SELECT 'rtree', Count(*)
FROM <name of your R*Tree Virtual Table>
UNION
SELECT 'table', Count(*)
FROM <name of your data table>
WHERE ST_GeometryType(geom) IS NOT NULL;

if you get two different numbers the SpatialIndex is obiously
corrupted and urgently needs to be repaired.

bye Sandro

[Dane christian Neilson]

[Fabian]

Hello Sandro,

the following query returns the same count for both tables (=377089 entries, which is also equal to: select count(*) from geo):

SELECT 'rtree', Count(*)

FROM idx_geo_center

UNION

SELECT 'table', Count(*)

FROM geo

WHERE ST_GeometryType(center) IS NOT NULL;

while

SELECT CheckSpatialIndex('geo', 'center');

is still returning 0.

The query has been executed on the database that I uploaded in the github repo.

Is there anything else I can verify?

Best regards Fabian

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

On Tue, 28 Nov 2023 09:19:30 -0800 (PST), Fabian wrote:
> The query has been executed on the database that I uploaded in the
> github repo.
>

Hi Fabian,

after performing a debug session on your data it finally turned
out that there is a bug in CheckSpatialIndex()
but to explain where the problem is we have to start from afar:

1. coordinates in SpatiaLite are represented as 64-bit DOUBLE
precision floating point values.

2. but coordinates in the R*Tree are stored as 32-bit FLOAT
values, and this implying some rounding.

3. but that's not all: both SQLite and SpatiaLite deliberately
alter the values ​​that are inserted into the RTree in such
a way as to ensure that the MBR present in the RTree is
always a litlle bit larger than the actual one.
the reason should be obvious; given that the RTree is
ultimately just a coarse filter, it's unacceptable to
lose even a single feature due to rounding problems,
while obtaining a few false positives does not create
any serious issue.

4. drawing conclusions: the coordinate values ​​in the table
and in the RTree are never exactly the same, there is
always some small difference, and precisely for this
reason CheckSpatialIndex() must rely on an approximate
comparison.

5. unfortunately your geometries have SRID=3875 (Web Mercator),
which is very popular among Web developers but has a very
bad reputation among professional geographers.
This very particular SRID presents an unusual characteristic:
the values ​​of its coordinates (especially the X) are unusually
large thus throwing the approximate comparison criterion
adopted by CheckSpatialIndex() into haywire.

--------------

a quick and dirty fix for your issue:

a) open the spatialite.c source into src/spatialite

b) find the function body of eval_rtree_entry()

c) change "if (diff >= 1.5)" as "if (diff > 2.0)"

d) rebuild and install: problem solved :-D

(obviously it's NOT a robust solution, but it works)

bye Sandro

[Dane christian Neilson]

MT103/202 DIRECT WIRE TRANSFER
PAYPAL TRANSFER
CASHAPP TRANSFER
ZELLE TRANSFER

LOAN DEAL

TRANSFER WISE
WESTERN UNION TRANSFER
BITCOIN FLASHING
BANK ACCOUNT LOADING/FLASHING
IBAN TO IBAN TRANSFER
MONEYGRAM TRANSFER

IPIP/DTC

SLBC PROVIDER
CREDIT CARD TOP UP

DUMPS/ PINS
SEPA TRANSFER
WIRE TRANSFER
BITCOIN TOP UP
GLOBALPAY INC US
SKRILL USA
UNIONPAY RECEIVER

Thanks.


NOTE; ONLY SERIOUS / RELIABLE RECEIVERS CAN CONTACT.

DM ME ON WHATSAPP

+14234753476

[Fabian]

Hello Sandro,

thank you a lot for helping me out in this case and taking your time to explain how things work under the hood. 

That's really interesting and I have a better understanding of how the index works now.

I'm considering now to switch to 4326 for storing my geometries, and avoid issues like that in the future.

Thank you for keeping this project up over all those years, I'm really happy to be able to use this extension.

Wish you a nice day,
Fabian