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

qgisspatial-viewspatialite

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

Best Answer

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
Related Question