[Qgis-user] Spatialite VIEW in QGIS question

[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.

I do see potential for a plugin that simply executes a filter on one
table based on the selection in another table for a pre-defined
relationship (maybe there is one already).

Thanks,
Alex