ST_Covers gives incorrect result with geometry collection.

[Shijie li]

Considering following queries:

DROP table if EXISTS t1;
DROP table if EXISTS t2;
CREATE TABLE t1(geom geometry NOT NULL);
CREATE TABLE t2(geom geometry NOT NULL);

INSERT INTO t1 (geom) VALUES(ST_GeomFromText('GEOMETRYCOLLECTION (POLYGON((0 0, 2 0, 2 2, 0 2, 0 0)))'));
INSERT INTO t1 (geom) VALUES(ST_GeomFromText('MULTIPOLYGON (((0 0, 2 0, 2 2, 0 2, 0 0)))'));
INSERT INTO t1 (geom) VALUES(ST_GeomFromText('POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0))'));

INSERT INTO t2 (geom) VALUES(ST_GeomFromText('GEOMETRYCOLLECTION (POINT (0 0),LINESTRING (0.5 0.5, 1 1))'));

SELECT ST_Covers(t1.geom, t2.geom) FROM t1, t2;

--expected:{t}{t}{t}

--actual:    :{f},{f},{t}

SELECT ST_Equals(ST_GeomFromText('GEOMETRYCOLLECTION (POLYGON((0 0, 2 0, 2 2, 0 2, 0 0)))'),ST_GeomFromText('MULTIPOLYGON (((0 0, 2 0, 2 2, 0 2, 0 0)))'));

--result:{t}

SELECT ST_Equals(ST_GeomFromText('POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0))'),ST_GeomFromText('MULTIPOLYGON (((0 0, 2 0, 2 2, 0 2, 0 0)))'));

--result:{t}

The calculation results of the cover predicate for the same geometric object are different for three equally spaced geometric objects.

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

On Sun, 29 Dec 2024 22:59:47 -0800 (PST), Shijie li wrote:
> SELECT ST_Covers(t1.geom, t2.geom) FROM t1, t2;
> --expected:{t}{t}{t}
>
> --actual:    :{f},{f},{t}
>

Hi,

once again I cannot confirm what you report.

testing the above query on my own PC using the most
recenti versione of SpatiaLite GUI returns three
TRUE rows in the resultset.

It's starting to get pretty boring having to waste
time checking fake or inaccurate reports.

To avoid further messes I invite you to always
check your spatial queries using the latest
SpatiaLite GUI; It will definitely save you a
lot of misunderstandings.

bye Sandro

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

On Sun, 29 Dec 2024 22:59:47 -0800 (PST), Shijie li wrote:

> Considering following queries:

> CREATE TABLE t1(geom geometry NOT NULL);
> CREATE TABLE t2(geom geometry NOT NULL);
>

just for the sake of clarity, this is not at all the
correct way to create a genuine Spatial Table on
SpatiaLite.

This way you'll simply obtain an ordinary SQLite's
Table containing generic BLOBs which could also
possibly be Geometries, but which will not benefit
from any of those special treatments that you expect
for a real SpatialLite's Table with Geometries.

Just to say, QGIS will never be able to understand
that it's a Spatial Table.

the correct mechanism always requires two distinct
steps:

1. first of all it's necessary to create the base table
with all its columns of a type other than Geometry.

2. after which all the requested Geometry columns are
then added one at a time using the SQL function
AddGeometryColumn()

example:
---------------------------------
CREATE TABLE my_geoms (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL);

SELECT AddGeometryColumn('my_geoms', 'geom', 4326, 'POLYGON', 'XY');
---------------------------------

Only in this way will you be guaranteed that all the
TRIGGERs required will be installed to ensure the full
consistency of the Geometries that can be inserted in
that specific column, in addition to the fact that all
the system tables allowing to recognize a genuine
Spatial Table will be correctly updated.

HINT: always defining a PRIMARY KEY of the INTEGER
tyoe is not strictly essential, but it's a wise
precaution that will avoid many headaches, especially
with the use of Spatial Indexes.

> DROP table if EXISTS t1;
> DROP table if EXISTS t2;
>

Additional Corollary:
even to drop a Spatial Table it is not enough to call
DROP TABLE directly.
Indeed, it's a very dangerous operation that can lead
to a DB being seriously corrupted to the point of
being unusable.

the only safe way is to call the SQL function
DropTable() as in:

SELECT DropTable('my_geoms');

This way thus not only the Base Table alone but
also all the TRIGGERs and any Spatial Index
connected to the table will be removed, in addition
to the fact that all the references present in the
System Tables will be deleted as well.

bye Sandro

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

One last question worthy of some clarification.

SpatiaLite entirely delegates all the functions of evaluating
relationships between Geometries to the GEOS standard library.

In all cases in which intersections, overlaps etc need to be
evaluated, SpatiaLite simply limits itself to pass data to
GEOS then returning its results as they are.

There are excellent reasons to do so given that GEOS is
used by the main open source software in the GIS field,
from PostGIS to QGIS an so on.
Quite obviously the intention is precisely to offer results
that are as consistent as possible regardless of the specific
implementation.

Conclusion: it does not make much sense to report to SpatiaLite's
develpers all the cases in which it's believed that GEOS provides
erroneous results.
If there really are problems, the defect should be reported to
the GEOS developers.

bye Sandro