I have a spatialite database I created that spans several years of crime data. To facilitate a real-world scenario where I may simplify the interaction for a user by introducing a View that simply limits the time span or what types of crimes are returned, I want to know if it is possible for QGIS to read this View like it would any other spatial table in my database. The point here is that I obviously do not want to create a separate table for every possible View I may use, and I am trying to make it simple by not requiring the user to know the database and the appropriate SQL to subset the data themselves. A similar workflow in the ArcGIS world might be creating separate layer files that the user could open in ArcMap that access the database (say, SQL Server) and present only the limited data set.

I created a View in my database, but it does not show up in the list of feature classes I can import when I connect to the spatialite db in QGIS. Maybe Views aren't considered spatial? (This is something I'm going to look into later, but I thought I already did; I just don't recall the conclusion.) In any case, how might one replicate the sort of work flow I want or the similar ArcGIS alternative?

6 Answers 6


I think you have to register the view in geometry_columns table to be able to use it in QGIS.

A good resource is "Hand-writing your own Spatial VIEW" with the following example:

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

Anyway you must register this VIEW into the views_geometry_columns, so to make it become a real Spatial View.

  • That seems to be the issue. I created the view and checked the meta tables. Obviously, the geometry from the view isn't listed. How do I register the view's geometry? I tried the RecoverGeometryColumn but it's telling me the table doesn't exist. I thought I was on a 2.4 version, but apparently I'm working with the Spatialite 3.0 beta. Maybe I'm implementing the command wrong? Maybe it doesn't work on views? (It better!); for clarity, my command was SELECT RecoverGeometryColumn('tblName', 'SHAPE', 2226, 'POINT', 2); Commented May 9, 2012 at 1:35
  • 4
    There is a special table for handling geometry based views. Use the following syntax INSERT INTO VIEWS_GEOMETRY_COLUMNS VALUES ('{TABLE NAME}', '{GEOM COLUMN NAME}', '{GEOM TYPE}', 2, {SRID}, 0);
    – Nathan W
    Commented May 9, 2012 at 1:53
  • example: INSERT INTO VIEWS_GEOMETRY_COLUMNS VALUES ('Towns_v1', 'Geometry', 'POINT', 2, 32632, 0);
    – Nathan W
    Commented May 9, 2012 at 1:53
  • Use SELECT * FROM views_geometry_columns; to see what is needed to be inserted. More info at gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/…
    – Nathan W
    Commented May 9, 2012 at 1:55

You may also have to put a unique ID into the view, similar to what is discussed here: In PostGIS is it possible to create a View with a unique ID?

  • Unless I explicitly don't select the PK in the view creation, wouldn't a unique ID be carried along? At least for the simple select query I'm doing here. Commented May 9, 2012 at 1:30
  • @BryanGoodrich- Give it a try, but it doesn't work that way with PostGIS views, as far as I have found. Commented May 9, 2012 at 14:10
  • The view was created by "SELECT * FROM crime WHERE ..." and I subset by the where-clause. This returned the PK column (rowname, which I generated from the column names in the R table I used to populate the SQLite DB). The problem is as underdark pointed out: it needs a geometry reference in the meta tables. This is what QGIS uses to identify spatial tables, and the view doesn't have any record in those meta tables. Commented May 9, 2012 at 20:43
  • Good to know. Thanks for asking the question and getting a good answer Commented May 9, 2012 at 21:53

registering the view in geometry_columns nowadays (year 2015) seems to require an additional field 'read_only' like so:

INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only )
VALUES ('italy', 'geometry', 'ROWID', 'local_councils', 'geometry', 1);
  • 1
    Could you please add a link to supporting documentation? Thanks!
    – underdark
    Commented Jul 29, 2015 at 17:00

For SpatialLite 4.x, two changes to the original answer are required - the addition of the read_only column and changing all inserted values to lowercase. The updated SQL would look like this:

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

The read_only field accepts either 0 or 1. (See Sandro Furieri's comment in Google Groups for further clarification.)

More details on these and other 4.x changes can be found on the switching-to-4.0 wiki.

INSERT INTO views_geometry_columns VALUES
('nameOfView' , 'geometry', 'rowid', 'geometryTable', 'geometry', 1);

N.B: only replace: nameOfView and geometryTable


Nowadays (year 2024 / QGIS 3.28) there's simple workaround:

  1. in QGIS, open DB-Manager and connect to the spatialite / geopackage of your choice

  2. it's ok that QGIS doesn't list your view as a table in the left pane (it doesn't recognize it as a spatial table, for the reasons mentioned in this thread); just go to the query window and enter:

    select * from yourviewhere

  3. hit "Execute"; the db-manager will now display all the data of your view

  4. below the results, check checkbox "load as new layer"

  5. make sure you activate "column with unique values" (seemed to be important in my case; if I didn't do that, it wouldn't work in the end)

  6. make sure the correct geometry column is selected (checkbox activated too)

  7. enter layer name and press "load"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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