<!DOCTYPE html><html><head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type">
<style type="text/css">body { font-family:'DejaVu Sans Mono'; font-size:12px}</style>
</head>
<body text="#000000" bgcolor="#FFFFFF">Am 21.02.2014, 22:17 Uhr, schrieb Bo Victor Thomsen <bo.victor.thomsen@gmail.com>:<br><br><blockquote style="margin: 0 0 0.80ex; border-left: #0000FF 2px solid; padding-left: 1ex">
<div class="moz-cite-prefix">Bernd - <br>
<br>
I think, that you have misunderstood some of the basic tenets of
relational database technology:<br>
<ul>
<li>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<br>
</li>
<li>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).<br>
</li>
</ul>
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
(<a class="moz-txt-link-freetext" href="http://qgis.org/downloads/weekly/">http://qgis.org/downloads/weekly/</a>) . Look at this article:
<a class="moz-txt-link-freetext" href="http://blog.vitu.ch/10112013-1201/qgis-relations">http://blog.vitu.ch/10112013-1201/qgis-relations</a> <br>
</div></blockquote><div><br></div><div>Hi Bo,</div><div>yes, I misunderstood that obviously, or i was "hoping" that those things were already easier in the year of 2014 ;)</div><div>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.</div><div><br></div><div>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 ;)</div><div><br></div><div>Thanx</div><div>Bernd</div><div><br></div><blockquote style="margin: 0 0 0.80ex; border-left: #0000FF 2px solid; padding-left: 1ex"><div class="moz-cite-prefix"><br>
Regards<br>
Bo Victor Thomsen<br>
Aestas-GIS<br>
Denmark<br>
<br>
Den 21-02-2014 18:23, Bernd Vogelgesang skrev:<br>
</div>
<blockquote cite="mid:53078B7C.7090502@gmx.de" type="cite">Dear
folks,
<br>
thanks a lot for all who tried to help.
<br>
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.
<br>
<br>
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.
<br>
<br>
Sandro Furieri was so kind to help me along as well
<a class="moz-txt-link-freetext" href="https://mail.google.com/mail/u/0/?shva=1#inbox/144507c0ecb4c392">https://mail.google.com/mail/u/0/?shva=1#inbox/144507c0ecb4c392</a>
<br>
<br>
The things that were important: not to have a ROWID in the joined
table, but rename it
<br>
Creating a table instead of a view. ("... SQL VIEWs are rather
extravagant and whimsical objects, and they could
<br>
easily introduce many hard-to-be-solved undesired side effects.
...")
<br>
<br>
So as a sample for one of my layers, it looks like this now:
<br>
<br>
CREATE VIEW View_PUNKTE AS
<br>
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
<br>
FROM ASK_PUNKTE AS a JOIN ask_art AS b USING (id);
<br>
<br>
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);
<br>
<br>
CREATE TABLE Abfrage_PUNKTE AS
<br>
SELECT rowid AS old_rowid, Geometry, ora_nachweis_id, zahl, jahr,
art, sta
<br>
FROM View_PUNKTE;
<br>
<br>
SELECT RecoverGeometryColumn('Abfrage_PUNKTE', 'geometry', 31468,
'MULTIPOINT', 'XY');
<br>
SELECT CreateSpatialIndex('Abfrage_PUNKTE', 'geometry');
<br>
<br>
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.
<br>
<br>
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.
<br>
<br>
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.
<br>
<br>
So thanx mates,
<br>
Bernd
<br>
<br>
<br>
Am 18.02.2014 21:29, schrieb Bernd Vogelgesang:
<br>
<blockquote type="cite">Hi folks,
<br>
<br>
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.
<br>
<br>
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.
<br>
They share the simple field "id".
<br>
<br>
I created dozens of view, following strictly
<a class="moz-txt-link-freetext" href="http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html">http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html</a>
(and i REALLY can't find any other tutorials).
<br>
<br>
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.
<br>
<br>
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.
<br>
<br>
Is it possible to create a one-to-many spatial VIEW with
Spatialite GUI ?
<br>
if yes
<br>
Whats the trick?
<br>
if no
<br>
Is QGIS just not able to show the views table correctly?
<br>
if no
<br>
Whats the trick?
<br>
<br>
Wasted many days on that now, and time is running away.
<br>
<br>
Please, someone, heeeeelp
<br>
<br>
Bernd
<br>
<br>
<br>
</blockquote>
<br>
<blockquote type="cite">
<br>
_______________________________________________
<br>
Qgis-user mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a>
<br>
</blockquote>
<br>
_______________________________________________
<br>
Qgis-user mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a>
<br>
</blockquote>
<br>
</blockquote><br><br><br><div id="M2Signature"><div>-- </div><div>Bernd Vogelgesang<br>Siedlerstraße 2<br>91083 Baiersdorf/Igelsdorf<br>Tel: 09133-825374</div></div></body></html>