[Qgis-user] Spatialite VIEW in QGIS question

Thu Feb 20 10:34:26 PST 2014

Am 20.02.2014 18:13, schrieb Olivier Dalang:
> Bernd,
>
> Here's a procedure which works here on QGIS master :
>
> 1. Create a spatialite layer "countries" with fields "name" and a 
> geometry column "geom" of type polygon.
>
> 2. Add some features to that layer.
>
> 3. In the DB manager, create the view:
> CREATE VIEW countries_influence AS
> SELECT rowid as rowid, name, Buffer(geom,1) as geom FROM countries
>
> 4. In the DB manager, register the view:
> INSERT INTO views_geometry_columns VALUES 
> ('countries_influence','geom','rowid','countries','geom',1)
>
> 5. Reload the database in the DB manager and right-click : "add to the 
> canvas".... It does NOT work.
>
> 6. From the "layer" menu, choose "add a spatialite layer", navigate to 
> your db, choose the view, and, tadaaam, it WORKS !
>
> Let me know if it works...
> It seems the bugs comes from that "add to the canvas" function, which 
> sets the source to
>
> path/SpatialiteView.sqlite' table="countries_influence" () sql=
>  instead of
> path/SpatialiteView.sqlite' table="countries_influence" (geom) sql=
>
> Regards,
>
> Olivier
>

Hi Olivier,
thank you for trying to help,
but, ermm, actually I do not see the point performing this. I have not 
really a problem creating views, but more making these functional in 
QGIS. Your example, as far as i understand, lacks a join-part 
completely, but thats the culprit I fear.

I uploaded my testdb to dropbox.
https://www.dropbox.com/s/8b1g97qu3us15tq/asktest3.sqlite

There is only layer, a view and the table.

Could anyone try it out?
When you query the view with SELECT * FROM "Testview22" , I see the list 
of observation I would like also to see in the QGIS attribute table, but 
this just doesn't work. It also show 746 rows, but only 37 distinct sets 
of information.

Cheers
Bernd

>
> 2014-02-20 16:51 GMT+01:00 Bernd Vogelgesang <bernd.vogelgesang at gmx.de 
> <mailto:bernd.vogelgesang at gmx.de>>:
> >
> > Am 19.02.2014 20:02, schrieb Olivier Dalang:
> >
> > Dear Bernd,
> >
> > I encountered some similar problems with Spatialite views, but in 
> the end I had it working (sometimes) both in 2.0 and in master... I 
> used the manual method from the spatialite cookbook though (not the 
> Spatialite GUI).
> >
> > I have no time to test now, but from what I recall, this does not work :
> > CREATE VIEW AS SELECT name, geometry FROM table
> > while this works :
> > CREATE VIEW AS SELECT ROWID, name, geometry FROM table
> >
> > Maybe you want to give it a last try. I can testify I have some 
> working spatialite views here. Let us know if this works, if not I may 
> give it a better look when I have some time.
> >
> > Another method is running a SELECT query in the DB Manager in QGIS, 
> and then choosing "Add to map canvas". This is not an actual sqlite 
> view, but depending on what you need, it could be enough.
> >
> >
> > Hi Olivier,
> > added ROWID to the VIEW. Same result in QGIS.
> >
> > I also performed the select in the DB Manager as you proposed. When 
> executed, it perfectly show the results i would like to see in the 
> attribute table, but what "arrives" in the attribute table after 
> loading as layer is again the duplication of the the first entry for 
> ROWID of the polygon.
> >
> > So, this is my select: Anything important missing?
> >
> > SELECT "a"."ROWID" AS "ROWID", "a"."Geometry" AS "Geometry",
> >     "b"."zahl" AS "zahl", "b"."jahr" AS "jahr", "b"."art" AS "art",
> >     "b"."sta" AS "sta"
> > FROM "ASK_VOEGEL" AS "a"
> > JOIN "ask_art" AS "b" USING ("id")
> >
> >
> > Thanx for your time
> > Bernd
> >
> >
> > Good luck !
> >
> > Olivier
> >
> >
> >
> >
> >
> > 2014-02-19 19:54 GMT+01:00 Bernd Vogelgesang 
> <bernd.vogelgesang at gmx.de <mailto:bernd.vogelgesang at gmx.de>>:
> >>
> >> Am 19.02.2014 19:14, schrieb Steve G:
> >>
> >>> This does not directly answer your question about spatialite 
> views, but you
> >>> might be able to use relationships directly in QGIS in the near 
> future (I
> >>> think this is a 2.1 update).  See:
> >>>
> >>> http://blog.vitu.ch/10112013-1201/qgis-relations
> >>>
> >>>
> >>> -Steve
> >>
> >>
> >> Hi Steve,
> >> Actually I do not care by which means I reach my destination, but I 
> had the, maybe wrong; impression that using a spatial database would 
> do the trick.
> >> Anyway ...
> >> I already installed latest master cause I read about this relation 
> manager thing a while ago ... but I was left quite clueless about how 
> to use it.
> >> Searched the web for nearly an hour about any info but to no avail, 
> so many thanks for sharing the link!!
> >>
> >> Will have a look at it ASAP, maybe thats kind of a solution.
> >>
> >> Cheers
> >> Bernd
> >>
> >>
> >>
> >>> --
> >>> View this message in context: 
> http://osgeo-org.1560.x6.nabble.com/Spatialite-VIEW-in-QGIS-question-tp5104565p5104775.html
> >>> Sent from the Quantum GIS - User mailing list archive at Nabble.com.
> >>> _______________________________________________
> >>> Qgis-user mailing list
> >>> Qgis-user at lists.osgeo.org <mailto:Qgis-user at lists.osgeo.org>
> >>> http://lists.osgeo.org/mailman/listinfo/qgis-user
> >>
> >>
> >> _______________________________________________
> >> Qgis-user mailing list
> >> Qgis-user at lists.osgeo.org <mailto:Qgis-user at lists.osgeo.org>
> >> http://lists.osgeo.org/mailman/listinfo/qgis-user
> >
> >
> >

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20140220/ca340bd8/attachment.html>