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

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
  • Thank you. I see, I have to create a workaround: create table, update table with ogr2ogr, put these things in a QGIS Plugin (PyQGIS). – Stefan Mar 17 '17 at 9:23
  • Try the ogr2ogr solution that I added to my answer. – user30184 Mar 17 '17 at 10:28
  • Nice solution. I've had it done more cumbersome. One short question: I've to use -overwrite in addition to -update to "update" the table (otherwise Error 1: Layer test already exists). Doesn't take the -update this into account? – Stefan Mar 17 '17 at 11:29
  • Yes, -update is for datasource and in this case it means that the same Spatialite database is updated - otherwise it will be overwritten. Take a backup if you want to try. For re-creating a layer use -overwrite, for adding new records use -append. Options are mostly documented in gdal.org/ogr2ogr.html. – user30184 Mar 17 '17 at 12:15

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,

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.