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
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user
