[Qgis-user] Spatialite VIEW in QGIS question

[Alex Mandel]

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