<div dir="ltr"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span style="font-family:arial,sans-serif;font-size:13px">Hi Olivier,<br>
</span><span style="font-family:arial,sans-serif;font-size:13px">thank you for trying to help,<br></span><span style="font-family:arial,sans-serif;font-size:13px">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. </span></blockquote>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">OK I misunderstood your problem, I though you had trouble loading the view, which is not that straightforward...</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">As Bo Victor said, your view has the problem that it does not have unique IDs which are necessary to have working spatial views.</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">But still, it seems your doing something wrong : if the idea is to have all of the observations as an attribute for each polygon, you must return each polygon in only one row, and aggregate the related data in that row. If you don't, you'll end up with duplicated polygons like Bo Victor said, which will probably be quite unusable...</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">Maybe you rather need a view looking like :</span></div><div><span style="font-family:arial,sans-serif;font-size:13px"><br>
</span></div>CREATE VIEW "Testview23" AS<br>SELECT "a"."ROWID" AS "ROWID", "a"."Geometry" AS "Geometry",<br>GROUP_CONCAT("b"."ora_nachweis_id") AS "ora_nachweis_id",<br>
GROUP_CONCAT("b"."zahl") AS "zahl",<br>GROUP_CONCAT("b"."jahr") AS "jahr",<br>GROUP_CONCAT("b"."art") AS "art",<br>GROUP_CONCAT("b"."sta") AS "sta"<br>
FROM "ASK_VOEGEL" AS "a"<br>JOIN "ask_art" AS "b" USING ("id")<br>GROUP BY a.ROWID<div><br></div><div>This way you'll get all of the species (comma separated) as attribute of the polygon.</div>
<div><br></div><div>Hope it helps this time...<br></div><div><div><br></div><div>Olivier</div><div><br></div><div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div></div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">2014-02-20 19:34 GMT+01:00 Bernd Vogelgesang <span dir="ltr"><<a href="mailto:bernd.vogelgesang@gmx.de" target="_blank">bernd.vogelgesang@gmx.de</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
<div>Am 20.02.2014 18:13, schrieb Olivier
Dalang:<br>
</div><div class="">
<blockquote type="cite">
<div dir="ltr">Bernd,<br>
<br>
Here's a procedure which works here on QGIS master :<br>
<br>
1. Create a spatialite layer "countries" with fields "name" and
a geometry column "geom" of type polygon.<br>
<br>
2. Add some features to that layer.<br>
<br>
3. In the DB manager, create the view:<br>
CREATE VIEW countries_influence AS<br>
SELECT rowid as rowid, name, Buffer(geom,1) as geom FROM
countries<br>
<br>
4. In the DB manager, register the view:<br>
INSERT INTO views_geometry_columns VALUES
('countries_influence','geom','rowid','countries','geom',1)<br>
<br>
5. Reload the database in the DB manager and right-click : "add
to the canvas".... It does NOT work.<br>
<br>
6. From the "layer" menu, choose "add a spatialite layer",
navigate to your db, choose the view, and, tadaaam, it WORKS !<br>
<br>
Let me know if it works...
<div>It seems the bugs comes from that "add to the canvas"
function, which sets the source to</div>
<div><br>
<div>path/SpatialiteView.sqlite' table="countries_influence"
() sql=</div>
<div> instead of</div>
<div>path/SpatialiteView.sqlite' table="countries_influence"
(geom) sql=<br>
<br>
Regards,</div>
<div><br>
</div>
<div>Olivier<br>
<br>
</div>
</div>
</div>
</blockquote>
<br></div>
Hi Olivier,<br>
thank you for trying to help,<br>
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. <br>
<br>
I uploaded my testdb to dropbox.<br>
<a href="https://www.dropbox.com/s/8b1g97qu3us15tq/asktest3.sqlite" target="_blank">https://www.dropbox.com/s/8b1g97qu3us15tq/asktest3.sqlite</a><br>
<br>
There is only layer, a view and the table.<br>
<br>
Could anyone try it out? <br>
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.<br>
<br>
Cheers<span class="HOEnZb"><font color="#888888"><br>
Bernd</font></span><div><div class="h5"><br>
<br>
<br>
<blockquote type="cite">
<div dir="ltr">
<div>
<div><br>
2014-02-20 16:51 GMT+01:00 Bernd Vogelgesang <<a href="mailto:bernd.vogelgesang@gmx.de" target="_blank">bernd.vogelgesang@gmx.de</a>>:<br>
><br>
> Am 19.02.2014 20:02, schrieb Olivier Dalang:<br>
><br>
> Dear Bernd,<br>
><br>
> 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).<br>
><br>
> I have no time to test now, but from what I recall,
this does not work :<br>
> CREATE VIEW AS SELECT name, geometry FROM table<br>
> while this works :<br>
> CREATE VIEW AS SELECT ROWID, name, geometry FROM table<br>
><br>
> 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.<br>
><br>
> 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.<br>
><br>
><br>
> Hi Olivier,<br>
> added ROWID to the VIEW. Same result in QGIS.<br>
><br>
> 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.<br>
><br>
> So, this is my select: Anything important missing?<br>
><br>
> SELECT "a"."ROWID" AS "ROWID", "a"."Geometry" AS
"Geometry",<br>
> "b"."zahl" AS "zahl", "b"."jahr" AS "jahr",
"b"."art" AS "art",<br>
> "b"."sta" AS "sta"<br>
> FROM "ASK_VOEGEL" AS "a"<br>
> JOIN "ask_art" AS "b" USING ("id")<br>
><br>
><br>
> Thanx for your time<br>
> Bernd<br>
><br>
><br>
> Good luck !<br>
><br>
> Olivier<br>
><br>
><br>
><br>
><br>
><br>
> 2014-02-19 19:54 GMT+01:00 Bernd Vogelgesang <<a href="mailto:bernd.vogelgesang@gmx.de" target="_blank">bernd.vogelgesang@gmx.de</a>>:<br>
>><br>
>> Am 19.02.2014 19:14, schrieb Steve G:<br>
>><br>
>>> This does not directly answer your question
about spatialite views, but you<br>
>>> might be able to use relationships directly in
QGIS in the near future (I<br>
>>> think this is a 2.1 update). See:<br>
>>><br>
>>> <a href="http://blog.vitu.ch/10112013-1201/qgis-relations" target="_blank">http://blog.vitu.ch/10112013-1201/qgis-relations</a><br>
>>><br>
>>><br>
>>> -Steve<br>
>><br>
>><br>
>> Hi Steve,<br>
>> 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.<br>
>> Anyway ...<br>
>> 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.<br>
>> Searched the web for nearly an hour about any info
but to no avail, so many thanks for sharing the link!!<br>
>><br>
>> Will have a look at it ASAP, maybe thats kind of a
solution.<br>
>><br>
>> Cheers<br>
>> Bernd<br>
>><br>
>><br>
>><br>
>>> --<br>
>>> View this message in context: <a href="http://osgeo-org.1560.x6.nabble.com/Spatialite-VIEW-in-QGIS-question-tp5104565p5104775.html" target="_blank">http://osgeo-org.1560.x6.nabble.com/Spatialite-VIEW-in-QGIS-question-tp5104565p5104775.html</a><br>
>>> Sent from the Quantum GIS - User mailing list
archive at Nabble.com.<br>
>>> _______________________________________________<br>
>>> Qgis-user mailing list<br>
>>> <a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a><br>
>>> <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
>><br>
>><br>
>> _______________________________________________<br>
>> Qgis-user mailing list<br>
>> <a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a><br>
>> <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
><br>
><br>
><br>
</div>
</div>
</div>
</blockquote>
<br>
</div></div></div>
</blockquote></div><br></div>