[Qgis-user] Spatialite VIEW in QGIS question

[Bernd Vogelgesang]

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