Am 21.02.2014, 22:17 Uhr, schrieb Bo Victor Thomsen <[email protected]>:
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 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
-- Bernd Vogelgesang
Siedlerstraße 2
91083 Baiersdorf/Igelsdorf
Tel: 09133-825374 |
