How to make a Spatialite View (with geometries) for QGIS based on two table

Question

There are several posts (1), 2)) and manuals to create a spatial view within a Spatialite Database and to work with it in QGIS.

The important thing is to register the view in geometry_columns table:

INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column)
VALUES ('italy', 'geometry', 'ROWID', 'local_councils', 'geometry');

All examples are about views which were created from one table.

How I have to register this view?

CREATE VIEW photo_object AS
SELECT a.id, MakeLine(b.geom, a.geom) AS geom
FROM object a, photo b
    WHERE a.id=b.id

Answer 1

What you try is not supported. As you can see from the structure of the views_geometry_columns table the f_table_name and f_geometry_column fields are referencing one single table and one single geometry column. You can't register your computed geometry MakeLine(b.geom, a.geom) into spatial views.

You must convert your view into a real table with CREATE TABLE AS even it means wasting some disk space. You can do that with ogr2ogr which should automatically take care of updating the metadata tables as well.

ogr2ogr -f sqlite -update -sql "SELECT a.rowid, MakeLine(b.geometry, a.geometry) AS geometry FROM foo1 a, foo2 b WHERE a.ogc_fid=b.ogc_fid;" test.sqlite test.sqlite -nln bar -nlt linestring

Answer 2

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 views_geometry_columns table.

---

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,
    SHAPE MULTIPOLYGON
)

2 - Set the adequate metadata for the geometry column of the dummy table:

SELECT RecoverGeometryColumn('dummy_etrs_pl','shape',3763,'MULTIPOLYGON',2)

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:

1. avoids creating "duplicated" data in the database
2. assures that the data in the resulting layer is up-to-date (as it is an actual view of the input tables)
3. the dummy table can be used as a referenced to several spatial views, if they have the same geometry type and SRID