Spatialite db with generic geometry column added through Add Spatialite Layer added as non-spatial table

[robintw]

Describe the bug I have a Spatialite database with a table containing a generic GEOMETRY column (ie. not POINT or POLYGON, but generic GEOMETRY). When adding this database to QGIS through the Layer->Add Layer->Add Spatialite Layer, it gets added as a non-spatial table. However, when I add it through Layer->Add Layer->Add Vector Layer, I get a dialog showing the 'sub-layers' (one for points and one for lines), and it allows me to add both of those as spatial layers.

How to Reproduce

1. Download this example Spatialite database (see notes below for how to create from scratch if you'd prefer).
2. Open QGIS.
3. Go to Layer->Add Layer->Add Spatialite Layer.
4. Connect to the database file you just downloaded.
5. You'll see that the table geometries is listed with the GEOMETRY type.
6. Select this table and add it. It will be added as a non-spatial table:
7. Now go to Layer->Add Layer->Add Vector Layer, and select the database file. Choose Add.
8. You will get a dialog offering two layers with different geometry types:
9. Selecting both and adding them will add two new spatial layers to the map, which display correctly:

To create the database from scratch, create a new SQLite database (eg via the command-line sqlite3 tool), load the Spatialite extension (eg .load mod_spatialite.dll) and run the following SQL:

SELECT
    InitSpatialMetaData();

CREATE TABLE Geometries (
    geometry_id INTEGER NOT NULL
);

SELECT
    AddGeometryColumn("Geometries", "geometry", 4326, 'GEOMETRY');

INSERT INTO
    Geometries (geometry_id, geometry)
VALUES
    (1, ST_GeomFromText("POINT(50.5 -1.5)", 4326));

INSERT INTO
    Geometries (geometry_id, geometry)
VALUES
    (2, ST_GeomFromText("POINT(50.51 -1.51)", 4326));

INSERT INTO
    Geometries (geometry_id, geometry)
VALUES
    (3, ST_GeomFromText("LINESTRING (50.5 -1.5, 50.6 -1.6, 50.7 -1.4)", 4326));

QGIS and OS versions

QGIS version	3.14.0-Pi	QGIS code revision	9f7028fd23
Compiled against Qt	5.12.3	Running against Qt	5.12.3
Compiled against GDAL/OGR	2.4.1	Running against GDAL/OGR	2.4.1
Compiled against GEOS	3.7.2-CAPI-1.11.2	Running against GEOS	3.7.2-CAPI-1.11.2 b55d2125
Compiled against SQLite	3.28.0	Running against SQLite	3.28.0
PostgreSQL Client Version	11.3	SpatiaLite Version	4.3.0a
QWT Version	6.1.4	QScintilla2 Version	2.11.1
Compiled against PROJ	5.2.0	Running against PROJ	Rel. 5.2.0, September 15th, 2018
OS Version	macOS High Sierra (10.13)
Active python plugins	vector_tiles_reader; quick_map_services; qgis2web; pointsamplingtool; processing; db_manager; MetaSearch

[elpaso]

The reason is that the two methods of adding layers are using two different providers: spatialite and ogr, spatialite provider does not handle mixed geometry tables correctly while ogr (GDAL) does.

[robintw]

Ah ok.

Is there a way to point this out in the UI if a mixed geometry table is found? At the moment it seems to recognise the table correctly as it says 'GEOMETRY' in the Type column. Could this be changed to something like GEOMETRY (Unsupported) or something?

[elpaso]

Yeah, I didn't dig into the code but there maybe a better solution by exposing multiple rows (one for each geometry type) in the spatialite source select dialog. This would slow down the dialog though, because we would need to scan the whole table to find out the actual geometry types.

Generally speaking, GEOMETRY type should be avoided like plague, QGIS internally supports only one geometry type for each layer anyway.

[blaenk]

Sorry to bump a very old issue, but I ran into this last night and I wasn't sure how/where would be best to ask about this:

Generally speaking, GEOMETRY type should be avoided like plague

Would you be kind enough to give some detail on this? Are you specifically referring to avoiding the GEOMETRY type in QGIS? Or SpatiaLite? Both? I was thinking about using it to be able to accept all kinds of geometry not just MULTIPOLYGON.

Is using GEOMETRY inefficient somehow compared to just picking one?

I tried finding information on this on Spatialite's documentation but haven't been able to find anything on the subject so far.

[Pedro-Murteira]

@robintw Hello, is this issue still valid on recent releases?

[robintw]

Yes @Pedro-Murteira, this still occurs on 3.24.2.