[Qgis-user] Spatialite VIEW in QGIS question

[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


2014-02-20 16:51 GMT+01:00 Bernd Vogelgesang <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>:
>>
>> 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
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>>
>> _______________________________________________
>> Qgis-user mailing list
>> 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/de9f05df/attachment.html>