[Qgis-user] Spatialite VIEW in QGIS question

Fri Feb 21 13:17:32 PST 2014

Bernd -

I think, that you have misunderstood some of the basic tenets of 
relational database technology:

  * If you establish a straight 1:n relationships in a relational
    database (as SQLite, SpatiaLite or Postgres/PostGis) you'll get at
    least n rows in the resulting view; if you want it otherwise you'll
    have to use aggregate functions to group and aggregate your results
  * If you want to select 1 row in a main table and afterwards have a
    look at n rows in a sub-table that's related to the main table by
    some common field(s)  -  that's  the responsibility of the
    presentation layer meaning Access, Excel or QGIS . But QGIS hasn't
    this functionality before ver. 2.2 (which isn't released yet).

If you want the above mentioned function in QGIS, you have to wait for 
QGIS 2.2 or download some bleeding edge QGIS like QGIS Weekly 
(http://qgis.org/downloads/weekly/) . Look at this article: 
http://blog.vitu.ch/10112013-1201/qgis-relations

Regards
Bo Victor Thomsen
Aestas-GIS
Denmark

Den 21-02-2014 18:23, Bernd Vogelgesang skrev:
> Dear folks,
> thanks a lot for all who tried to help.
> I have a working solution now, which I will hopefuly be able to forge 
> into some script, so all steps for many layers will run more automatic.
>
> I maybe had other working solutions before, but I made the mistake and 
> didn't see that I had selected the "one object only" selection mode in 
> QGIS ... so of course it only picked the most up laying polygon and 
> couldn't show me more info than just one row in the attribute table 
> ... too bad.
>
> Sandro Furieri was so kind to help me along as well 
> https://mail.google.com/mail/u/0/?shva=1#inbox/144507c0ecb4c392
>
> The things that were important: not to have a ROWID in the joined 
> table, but rename it
> Creating a table instead of a view. ("... SQL VIEWs are rather 
> extravagant and whimsical objects, and they could
> easily introduce many hard-to-be-solved undesired side effects. ...")
>
> So as a sample for one of my layers, it looks like this now:
>
> CREATE VIEW View_PUNKTE AS
> SELECT a.ROWID AS ROWID, a.Geometry AS Geometry, b.ora_nachweis_id AS 
> ora_nachweis_id, b.zahl AS zahl, b.jahr AS jahr, b.art AS art, b.sta 
> AS sta
> FROM ASK_PUNKTE AS a JOIN ask_art AS b USING (id);
>
> INSERT INTO views_geometry_columns (view_name, view_geometry, 
> view_rowid, f_table_name, f_geometry_column, read_only) VALUES 
> ('view_punkte', 'geometry', 'rowid', 'ask_punkte', 'geometry',1);
>
> CREATE TABLE Abfrage_PUNKTE AS
> SELECT rowid AS old_rowid, Geometry, ora_nachweis_id, zahl, jahr, art, 
> sta
> FROM View_PUNKTE;
>
> SELECT RecoverGeometryColumn('Abfrage_PUNKTE', 'geometry', 31468, 
> 'MULTIPOINT', 'XY');
> SELECT CreateSpatialIndex('Abfrage_PUNKTE', 'geometry');
>
> I have now finally realized/accepted, that there is NO WAY to avoid 
> duplication of geometries (very very sad) for 1:n relations in QGIS, 
> so this really bloats the layers ... but thats life.
>
> Maybe I could skip some of this and make a table directly from a 
> select, but I'm happy with at least one working solution.
>
> Now i will try and put all this into a batch script that will generate 
> me those layers automagically cause filling in all the separate 
> commands for several layers is boring, time consuming an error prone.
>
> So thanx mates,
> Bernd
>
>
> Am 18.02.2014 21:29, schrieb Bernd Vogelgesang:
>> Hi folks,
>>
>> I'm quite desperate, cause I do not seem to understand what I'm doing 
>> wrong, or if it's just not possible to do.
>>
>> I have a polygon layer in my spatialite database and a normal table 
>> with bird observations. There are many observation entries for each 
>> item in the polygons.
>> They share the simple field "id".
>>
>> I created dozens of view, following strictly 
>> http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html 
>> (and i REALLY can't find any other tutorials).
>>
>> When i query "SELECT * FROM "test17"" in Spatialite GUI, it shows all 
>> the lines with different observation entries for each polygon id, 
>> when i load the VIEW in QGIS, it doesn't but duplicates the first 
>> matching observation for one polygon over and over.
>>
>> The idea is to quickly identify all observations when selecting a 
>> polygon, and then go to the attribute table to see which species are 
>> there.
>>
>> Is it possible to create a one-to-many spatial VIEW with Spatialite 
>> GUI  ?
>> if yes
>> Whats the trick?
>> if no
>> Is QGIS just not able to show the views table correctly?
>> if no
>> Whats the trick?
>>
>> Wasted many days on that now, and time is running away.
>>
>> Please, someone, heeeeelp
>>
>> Bernd
>>
>>
>
>>
>> _______________________________________________
>> 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/20140221/b28dc528/attachment.html>