[Qgis-developer] Spatialite views not working in QGIS

[Stefan Keller]

Hi,

I'm trying to use views from a Spatialite data source but get errors
and misbehaviour all over the place... This is what I did:

I first downloaded test-2.3.sqlite from
http://www.gaia-gis.it/spatialite/resources.html .

There is a table with point geometry called Towns in this sample database.

Based on this I created two views, called Towns_v1 and Towns_v2 using
spatialite-gui:

CREATE VIEW Towns_v1 AS
  SELECT t.PK_UID, Name, Peoples, t.Geometry from Towns t;
CREATE VIEW Towns_v2 AS
  SELECT t.PK_UID, Name, CAST(Peoples + 1 AS INTEGER) Peoples1,
t.Geometry from Towns t;

... and added the required info in "system" table geometry_columns:
INSERT INTO geometry_columns VALUES ('Towns_v1', 'Geometry', 'POINT',
2, 32632, 0);
INSERT INTO geometry_columns VALUES ('Towns_v2', 'Geometry', 'POINT',
2, 32632, 0);

Now looking at all field data types:
PRAGMA table_info("Towns_v2");
0 PK_UID   INTEGER  0 NULL  0
1 Name     TEXT     0 NULL  0
2 Peoples1          0 NULL  0
3 Geometry POINT    0 NULL  0

=> Problem/Error (from SQlite): Field Peoples1 from table Towns_v2
shows no data type! (although there is a cast!!). That's a SQlite
problem originating probably because of its "dynamic/lazy data type"
concept.


But then I started QGIS (1.7) and Spatialite data source opened
test-2.3.sqlite:

=> Problem/Error in QGIS (1): Doing a "Open Attribute Table" (from
right click menu) on Layer 'Towns_v1' and 'Towns_v2' shows ERROR
values all over the place. <<

=> Problem/Error in QGIS (2): Selection of a feature object Layer
'Towns_v1' and 'Towns_v2' does not work. <<

Any ideas?

Yours, Stefan