[Qgis-user] Spatialite VIEW in QGIS question

[Bernd Vogelgesang]

Am 21.02.2014, 22:17 Uhr, schrieb Bo Victor Thomsen  
<bo.victor.thomsen at gmail.com>:

> 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

Hi Bo,
yes, I misunderstood that obviously, or i was "hoping" that those things  
were already easier in the year of 2014 ;)
Anyway, found a working solution my collegue is happy with. I also tested  
with master yesterday after reading the link about qgis relations, but  
trying this only crashed the system.

But we all are awaiting a bright future with easy to establish 1:n  
relations, I'm sure of that, and then nothing will stop me ;)

Thanx
Bernd

>
> 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 itCreating a table instead of a view. ("... SQL VIEWs  
>> are rather extravagant and whimsical objects, and they couldeasily  
>> 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 ASSELECT 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 staFROM 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 ASSELECT rowid AS old_rowid, Geometry,  
>> ora_nachweis_id, zahl, jahr, art, staFROM 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 yesWhats the trick?if noIs QGIS just not able to show the  
>>> views table correctly?if noWhats 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
>



-- 
Bernd Vogelgesang
Siedlerstraße 2
91083 Baiersdorf/Igelsdorf
Tel: 09133-825374
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20140222/a323d6ed/attachment.html>