I know this is an old question with an already accepted answer, but I found this post when searching a solution for my problem and I had a different approach to get my spatial view registered.
So, in my case, I had to reproject the original geometries into another CRS, so I also couldn't use the reference to the original geometry table to insert the attributes of the spatial view into
To resolve this I've created a 'dummy' table just with the sole purpose of creating a reference table with the adequate geometry type and CRS for the metadata of the spatial view I wanted to create.
1 - First I created the dummy table with the adequate geometry type:
CREATE TABLE dummy_etrs_pl (
OBJECTID integer primary key autoincrement not null,
2 - Set the adequate metadata for the geometry column of the dummy table:
3 - Inserted the metadata for the spatial view into
views_geometry_columns table referencing the dummy table for the f_table_name and f_geometry_column attributes:
INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
VALUES ('etrs_freg_pl', 'shape', 'objectid', 'dummy_etrs_pl', 'shape', 1)
The resulting spatial view was correctly recognized in SpatialLite_GUI and QGIS.
As the dummy table is set with no spatial index, the spatial view is recognized as having no spatial index as well, which is good, since the spatial index would have no correspondence with the existing geometries in the spatial view.
This approach has advantages aggainst the solution of creating a real table with the results of the query:
- avoids creating "duplicated" data in the database
- assures that the data in the resulting layer is up-to-date (as it is an actual view of the input tables)
- the dummy table can be used as a referenced to several spatial views, if they have the same geometry type and SRID