[Qgis-developer] Spatialite views not working in QGIS

[a.furieri at lqt.it]

On Fri, 29 Apr 2011 15:32:05 +0200, Stefan Keller wrote
> > c) accordingly to all this, SpatiaLite defines any
> >  Spatial VIEW on VIEWS_GEOMETRY_COLUMNS (not on
> >  GEOMETRY_COLUMNS): and this allows to fully support
> >  Spatial Index for VIEWs as well
> 
> Cool! So, what can I do?
> 
> Just replace this statement:
> << INSERT INTO GEOMETRY_COLUMNS VALUES ('Towns_v1', 'Geometry',
> 'POINT', 2, 32632, 0);
> to this?
> >> INSERT INTO VIEWS_GEOMETRY_COLUMNS VALUES ('Towns_v1', 'Geometry',
'POINT', 2, 32632, 0);
> 

Hi Stefan,

just a simple practical example:

CREATE TABLE a_test (
  code INTEGER PRIMARY KEY,
  category TEXT);
  
CREATE TABLE b_test (
  id INTEGER PRIMARY KEY,
  code INTEGER,
  name TEXT);
SELECT AddGeometryColumn(
  'b_test', 'geom', 4326, 'POINT', 'XY');

CREATE VIEW vw_test AS
SELECT b.id AS pk_id, b.code AS code,
  a.category AS category, 
  b.name AS name, b.geom AS the_geom
FROM b_test AS b
JOIN a_test AS a ON (a.code = b.code);

INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid,
  f_table_name, f_geometry_column)
VALUES ('vw_test', 'the_geom', 'pk_id',
  'b_test', 'geom');

bye Sandro