On 02/19/2014 10:43 AM, Bernd Vogelgesang wrote: > Am 19.02.2014 16:55, schrieb Alex Mandel: >> On 02/18/2014 12:29 PM, Bernd Vogelgesang wrote: >>> 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 >>> >>> > >> I usually cheat, and in making my View I do a left join to the attribute >> table with the geometries. It ends up duplicating the geometries for >> each match which can be inefficient on a large dataset but works >> perfectly reasonable most of the time. >> >> Then the regular info and select tools return multiple records. > Thank you Alex for your answer. > > I think this combination was the only one I haven't tried yet. > > But ... the outcome is as depressing as all my other, or even worse. > Instead of 37 features i now have 9725, and the attribute table has the > same amount of entries instead of some 700. > > When loading the attribute table, there are only ERROR in the fields, > and it took some minutes till I was able to switch to "show only > selected". And ... surprise surprise, it again showed just the entry of > the first species in all of the rows instead of all the individual > observations. > > Guys, this is a very sad topic and I really can't understand how people > can work with 1:n data even on the most basic level under these > circumstances ... > Or doesn't anyone work with 1:n data? Well, the the world I'm living in > is full of that ... > > After nearly one week of trial and error, I am giving up now. > > Cheers > Bernd >
I rarely need to visually explore the 1:n without aggregation. What I love about spatial databases is that I can easily query whatever I want including 1:n but I don't need to click on an individual. What attribute about the second table do you need to know? How many, average x, or even just a list of ids? If you want the Birds per polygon check out the group concat from sqlite: CREATE View birdlists as SELECT group_concat(species), count(species) FROM table1 JOIN table2 ON table1.id = table2.id GROUP BY polygon This gives you the list of birds per polygon as a view of the polygon layer. Yes occasionally you want to click and get a list, I've done this in a web page where you take the id, then query the db to return the rows related. Like I mentioned before a python plugin should be able to do this. Thanks, Alex _______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user