[Qgis-user] Spatialite VIEW in QGIS question

[Bernd Vogelgesang]

Am 19.02.2014 20:06, schrieb 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
>
>
Hi Alex,

we need to have the list of simply all birds that have once been 
observed there in the past.
I need the info of each observation what species, how many of that , 
year of observation and breeding status ... in short again: all lines 
from the table that match the polygon id.
Nothing to calculate, nothing to group. We need to have an overview what 
has happened in the past in these areas to be able to find 
"interesting"  species/year/status-combinations to be able to decide 
about actions.

Just tried to build some views manually within the GUI (so without the 
Query Composer) and these procedures here

http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html

do not work at all. Seem to be outdated or what, errors over errors and in the end I have geometryless geometries ... perfect.

I simply can't believe that none on this planet ever decided to post a simple and working spatialite view creation script for 1:n somewhere in the web as a template.

arrghh..