3

I'm using Qgis 1.8.0, and spatialite 2.3 in a Windows Vista machine.

I have a database in Spatialite, and I have created a view from one of its tables. When I open it in QGIS, using add Spatialite Layer (and enabling non-spatial tables), the attribute table shows ERROR in all of its lines and rows, but the number of rows and attributes seems correct.

I have tried both with spatial and non-spatial views withou sucess and opening tables (spatial or not) works well.

My table and view is something like this:

CREATE TABLE "Accoes"(
pkuid integer primary key autoincrement,
"UnidadeID" text,
"Tipo" text,
"Descricao" text,
"Data" text,
"Responsavel" text)

CREATE VIEW AccoesPoltest AS
SELECT c.* FROM Accoes as c

I have already read all info provided in this related question: Can Qgis Read Spatialite Views?

I also found a very old bug ticket from QGIS, with the exact same issue, but it was closed as invalid: http://trac.osgeo.org/qgis/ticket/2178

This is one of my first approaches with Qgis + Spatialite, so probably something is missing. Any idea? Thanks

Alexandre Neto

Community
  • 1
Alexandre Neto
  • 14,212
  • 2
  • 58
  • 85
  • I have now realized that QGis 1.8.0-3 uses Spatialite 3.0.1 and not 2.3, could this be a problem? – Alexandre Neto Aug 31 '12 at 11:45
  • hi , I am an beginner with spatalite, nevertheless I would suggest to try loading into the free spatialitegui ( https://www.gaia-gis.it/fossil/spatialite_gui/index) and see what happens there. spatialitegui is from the developer of spatialite and needs no install-routine – Kurt Aug 31 '12 at 15:47
  • @kurt, I guess we are both beginners... I have no problems with the views in SpatialiteGUI, actually I have created them with it. – Alexandre Neto Aug 31 '12 at 21:26
  • @AlexandreNeto Was this ever resolved? I am having the same issue with SQLite/spatiaLite 3.7.9/3.0.1 and QGIS 1.8.0 compiled against spatiaLite 3.0.1. – Scro Oct 08 '12 at 20:51
  • 1
    It worked following the SpatiaLite Cookbook. I did not have a ROWID, and its a must. – Alexandre Neto Oct 08 '12 at 23:03
  • @AlexandreNeto: can you convert this comment to a answer and mark it as done? – BradHards Jan 05 '13 at 06:00
  • @BradHards, I have done it now. I also have changed the title name be be a actual question. Later I might edit my answer in order to include the spatial views too. – Alexandre Neto Jan 05 '13 at 13:40

1 Answers1

3

I got my answer by following the SpatiaLite Cookbook. I didn't have a ROWID in my view, and it seams to be must in order to open in QGIS.

Therefore, for my specific case, All I needed to do is assign "pkuid" key as ROWID, just like this:

CREATE TABLE "Accoes"(
pkuid integer primary key autoincrement,
"UnidadeID" text,
"Tipo" text,
"Descricao" text,
"Data" text,
"Responsavel" text)

CREATE VIEW AccoesPoltest as
SELECT c.pkuid as ROWID, c.* FROM Accoes as c
Alexandre Neto
  • 14,212
  • 2
  • 58
  • 85