[SpatiaLite-Users] No views visible in QGIS when loading a SpatiaLite layer?

[Stefan Keller]

Has anybody ever managed to load and display a SpatiaLite/SQLite layer
into QGIS when the source is a VIEW instead of a TABLE?

Yours
-S.

P.S. Here what I did:

-- Given the following famous Towns table from the SpatiaLite tutorial:
CREATE TABLE Towns (
PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT,
Peoples INTEGER,
LocalCounc INTEGER,
County INTEGER,
Region INTEGER, "Geometry" POINT
)
SELECT * from towns;

-- let's create a view...
-- containing at least a PK (of type integer) and a geometry
-- as well as a calculated column
CREATE VIEW IF NOT EXISTS towns_test_v AS
SELECT PK_UID, Name, Peoples + 1, Geometry from towns;

SELECT * FROM towns_test_v;

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To post to this group, send email to spatiali...@googlegroups.com.
To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.

[a.furieri]

Hi Stefan,

VIEWs (and Spatial VIEWs) are fully supported by SpatiaLite:
and they are supported as well by the SpatiaLite's data
provider for QGIS.

Anyway, you cannot simply use "CREATE VIEW": you need to
apply a more sophisticated approach.
In a very few words: simply using "CREATE VIEW" will miss to
update as expected the spatial-metadata tables used by
SpatiaLite (and by the QGIS data-provider).
So this 'trivial' VIEW is not a fully-qualified *spatial* table,
and accordingly to this will be completely ignored.

Please read the following doc to get any further detail:
http://www.gaia-gis.it/spatialite-2.4.0/Using-Views-Basic.pdf
http://www.gaia-gis.it/spatialite-2.4.0/Using-Views-Advanced.pdf

bye Sandro

[Stefan Keller]

Thanks, Sandro, for the hint. But there's a related problem:

Neither the alias column name nor the casted type are shown when
choosing "Show columns" in spatialite_gui (see attachment showing the
result of "PRAGMA table_info("towns_test_v")"). The column gets
labeled with "Peoples + 1" (instead "PeoplesPlus1") and there is no
column type shown. Any ideas?

Yours, -S.

P.S. View with CAST:

CREATE VIEW IF NOT EXISTS "towns_test_v" AS

SELECT PK_UID, Name, CAST(Peoples + 1 AS INTEGER) AS "PeoplesPlus1",
Geometry from towns;



2010/5/3 a.furieri <a.fu...@lqt.it>:

[Stefan Keller]

I solved the problem why column alias is not shown:
One obviously has to DROP the view before re-defining it.
What remains to be solved is the problem that the type of
a calculated column is not accessible out of a view.

Yours, -S.

2010/5/3 Stefan Keller <sfke...@gmail.com>:

[a.furieri]

Hi Stefan,

I've performed some testing on this problem:
it looks like if SQLite simply copies the
column type from the original table into the
corresponding view column.

If a view column represents a Function result
(or the result of some computation), then the
column type is NULL.
I see no way to circumvent this SQLite behaviour.

Anyway SQLite implements a very peculiar 'loose
column typing', so this one doesn't seems to be
a real issue.

In other words: a data type declaration at
column level in SQLite simply plays a 'cosmetic'
role, but doesn't has any 'functional' implication,
because each single cell can actually contain
*any* data-type:
http://www.sqlite.org/datatype3.html

[Stefan Keller]

Thank you,

I've posted my question to sqlite-users and got no useful response yet.

The application (QGIS) which reads from this view needs that
all columns are typed - even if the value types deviate from it - and
I think this is a logical assumption. To me it seems like an
inconsistency (or bug) when TABLES allow
declaration of types but VIEWS only eventually.

The only solution I've found so far is to create a temporary table
("CREATE TEMP TABLE..."). But this only works if the information
need is read-only (with VIEWs I would have uses TRIGGERs).

-S.

2010/5/3 a.furieri <a.fu...@lqt.it>: