1:many relation enhancements

[matteo]

Hi all,
I'm working with the 1:many relation tool (what an amazing tool!!) and I
get (maybe) some enhancement ideas.


I'll explain myself with a small example:

I have a geometry layer (Europe countries extracted from natural earth
database) and a table (dbf file downloaded from the EU database[0]) of
waste produced for every country from 1995 to 2012.

I made a form mask for the identify feature tool and when I open the
relation tab I can see relation of the table of all the waste produced
for every year.
But what if I want to see just the year 2010 for example? Or what if I
have 2 waste types in the same column (say urban waste and recycled) and
I want to see just the recycled one?

Do you think it is possible/easy/smart to add a sort of auto-filter tool
(like in Calc) so that the user can filter, and then display, just by
the values he wants?

Moreover (and finally), do you think that it would be possible also to
render by the linked table values? I mean, graduate all the countries of
the Europe layer with the recycled waste values coming from the dbf file?

I don't know if I have been clear enough, but I hope so else, be free to
ask clarifications

PS. here [1] a dropbox link with a zip file containing the layers and
the project file.. if anyone wants to try..

Thanks! Cheers

Matteo

[0]http://appsso.eurostat.ec.europa.eu/nui/submitViewTableAction.do
[1]https://www.dropbox.com/s/7j9e7sq3gcphkcz/project.zip?dl=0

[Régis Haubourg]

Hi,
I just finished a project with custom UI made with QTcreator with qgis
widgets. I successfully added the 1-n relations editor widget, ans it is
really a great feature.

+1 with filter suggestion to find a relation . a spatial filter ( distance
or BBox index filtrer) would be the best filter.
I also saw some possible improvements :
- An option to choose default view (attributs or form) is welcome ans
should be exposed in qtcreator too.
- blocking the tools to add or delete relations is wishable when administor
do not want user to do it.
- a commit is made for each row that has been clicked on (red italic
confirmation on form view). this leads to commits even if no data has been
modified. is that possible to have detect commit status if data input is
made?

Mattheo, 1-n agregator are not yet in qgis, but it is easy in postgres or
SQLite to have a view agregate value to let qgis map it.
Cheers
Régis



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/1-many-relation-enhancements-tp5168023p5168512.html
Sent from the Quantum GIS - Developer mailing list archive at Nabble.com.

[Matthias Kuhn]

Hi,

Thank you for the feedback. It's nice to hear that this work is being
used and appreciated.

Matteo, the filter sounds like a good idea. The main problem I see with
that is that adding an additional filter column may be cluttering the
already limited space in the embedded form. Maybe a button is required
to toggle this.

The other idea - if I understood it correctly - is something I have been
considering for a while. It is generally known as "aggregated
functions", that is things like max/min/avg/count etc. over all (or even
only a subset of) related features. This would be best embedded in or
around the expression engine. It's an advanced topic, but it's
definitely on my todo list.

On 20.10.2014 21:21, Régis Haubourg wrote:
> Hi,
> I just finished a project with custom UI made with QTcreator with qgis
> widgets. I successfully added the 1-n relations editor widget, ans it is
> really a great feature.
>
> +1 with filter suggestion to find a relation . a spatial filter ( distance
> or BBox index filtrer) would be the best filter.
This is referring to a scenario where both involved layers contain
geometries and you would want the parent's feature available in the
expression to filter the childrens?
> I also saw some possible improvements :
> - An option to choose default view (attributs or form) is welcome ans
> should be exposed in qtcreator too.
That is available in QtCreator if you use the custom widgets plugin.
There is an option called "view mode" which can be set to form or table.
(Available in master / 2.6)
> - blocking the tools to add or delete relations is wishable when administor
> do not want user to do it.
Good idea.
> - a commit is made for each row that has been clicked on (red italic
> confirmation on form view). this leads to commits even if no data has been
> modified. is that possible to have detect commit status if data input is
> made?

This is likely not a problem of the relation widgets but the edit
widgets used on the embedded form. Maybe one of them is unsuitable (like
a value map that doesn't contain the original value or an edit widget
that does not match the widget defined in the .ui file).

If somebody is able and willing to fund these features or help otherwise
I am more than happy to see things improving in this area. I think there
is a lot of potential left to be explored with relations.

Kind regards,
Matthias

--
Help getting QGIS to the next level of quality before November 15!
http://blog.vitu.ch/10102014-1046/crowdfunding-initiative-automated-testing

[HAUBOURG]

Hi Matthias,
Agregate functions could be provided by virtual table feature (qep is coming I think), I suppose we should chose one common way for advanced relationnal capabilities.

About filtering, you're right, we can't assume children table to be geographic. I was thinking of a search bar on top of the dialog showing new children candidates. That search bar could have a qgsexpression widget on its right to enable a permanent filter + a field chooser to choose on what field apply the user entries of search bar. why not have parent geometry accessible in expression builder and let the power user make spatial filters?
..
Thanks for the tip on qtcreator, I worked on 2.4… now I know it's done ;-)
By now, no direct funding is possible for me (administrative blockers). I wish I could finance your work on unit tests first, but it is currently forbidden to french administrations..

cheers,
Regis


Matthias Kuhn <matthias.kuhn-***@public.gmane.org> wrote:


Hi,

Thank you for the feedback. It's nice to hear that this work is being
used and appreciated.

Matteo, the filter sounds like a good idea. The main problem I see with
that is that adding an additional filter column may be cluttering the
already limited space in the embedded form. Maybe a button is required
to toggle this.

The other idea - if I understood it correctly - is something I have been
considering for a while. It is generally known as "aggregated
functions", that is things like max/min/avg/count etc. over all (or even
only a subset of) related features. This would be best embedded in or
around the expression engine. It's an advanced topic, but it's
definitely on my todo list.

On 20.10.2014 21:21, Régis Haubourg wrote:
> Hi,
> I just finished a project with custom UI made with QTcreator with qgis
> widgets. I successfully added the 1-n relations editor widget, ans it is
> really a great feature.
>
> +1 with filter suggestion to find a relation . a spatial filter ( distance
> or BBox index filtrer) would be the best filter.
This is referring to a scenario where both involved layers contain
geometries and you would want the parent's feature available in the
expression to filter the childrens?
> I also saw some possible improvements :
> - An option to choose default view (attributs or form) is welcome ans
> should be exposed in qtcreator too.
That is available in QtCreator if you use the custom widgets plugin.
There is an option called "view mode" which can be set to form or table.
(Available in master / 2.6)
> - blocking the tools to add or delete relations is wishable when administor
> do not want user to do it.
Good idea.
> - a commit is made for each row that has been clicked on (red italic
> confirmation on form view). this leads to commits even if no data has been
> modified. is that possible to have detect commit status if data input is
> made?

This is likely not a problem of the relation widgets but the edit
widgets used on the embedded form. Maybe one of them is unsuitable (like
a value map that doesn't contain the original value or an edit widget
that does not match the widget defined in the .ui file).

If somebody is able and willing to fund these features or help otherwise
I am more than happy to see things improving in this area. I think there
is a lot of potential left to be explored with relations.

Kind regards,
Matthias

--
Help getting QGIS to the next level of quality before November 15!
http://blog.vitu.ch/10102014-1046/crowdfunding-initiative-automated-testing

[Matthias Kuhn]

Hi Régis

On 10/21/2014 09:44 PM, HAUBOURG wrote:
> Hi Matthias,
> Agregate functions could be provided by virtual table feature (qep is coming I think), I suppose we should chose one common way for advanced relationnal capabilities.

I agree that one common way is preferable, but actually this QEP itself
is already duplicating functionality (of QgsExpression) and nobody was
able yet to confirm that it is possible to optimize queries for
execution on the database with sqlite virtual tables. Something that is
a must from my point of view. I think it is a good initiative, but for
the aforementioned reasons I am not yet completely convinced, that it's
the one and only way.

>
> About filtering, you're right, we can't assume children table to be geographic.

... and neither for the parent ...

> I was thinking of a search bar on top of the dialog showing new children candidates. That search bar could have a qgsexpression widget on its right to enable a permanent filter + a field chooser to choose on what field apply the user entries of search bar. why not have parent geometry accessible in expression builder and let the power user make spatial filters?
Yes, once there are expressions available the parent feature should be
available for evaluation. Now I am not sure if I understood correctly.
Are you referring to a filter to show only a subset of the related
children or a filter to search for new children which you want to link
to the current feature?
I was also thinking of improving the search field to link additional
features (or change the parent of a feature) but that would mainly make
sense (in my scenarios) if this could be evaluated server-side and that
would require a project "expression compiler" for which I am currently
also looking for funders :-)
> ..
> Thanks for the tip on qtcreator, I worked on 2.4… now I know it's done ;-)
> By now, no direct funding is possible for me (administrative blockers). I wish I could finance your work on unit tests first, but it is currently forbidden to french administrations..
I thought this barrier mainly exists for "funding" as such (like in unit
tests, but thank you for mentioning it), but not for a particular
contract work with a requirements document for a new feature? But you
surely know better.

All the best,
Matthias

--
--------------------------------------

Please help taking QGIS to the next level of quality. Before November 15 !
http://blog.vitu.ch/10102014-1046/crowdfunding-initiative-automated-testing

[Hugo Mercier]

Le 22/10/2014 08:39, Matthias Kuhn a écrit :
> Hi Régis
>
> On 10/21/2014 09:44 PM, HAUBOURG wrote:
>> Hi Matthias,
>> Agregate functions could be provided by virtual table feature (qep is
>> coming I think), I suppose we should chose one common way for advanced
>> relationnal capabilities.
>
> I agree that one common way is preferable, but actually this QEP itself
> is already duplicating functionality (of QgsExpression) and nobody was
> able yet to confirm that it is possible to optimize queries for
> execution on the database with sqlite virtual tables. Something that is
> a must from my point of view. I think it is a good initiative, but for
> the aforementioned reasons I am not yet completely convinced, that it's
> the one and only way.

Hi,

Since I'm the author of the mentioned QEP
(https://github.com/qgis/QGIS-Enhancement-Proposals/pull/5), I have to
answer :)

What functionalities of expressions do you think it duplicates ?

I don't see anything that prevents virtual layer queries from being
optimized server-side. It has to be taken into account during the
implementation of virtual layers, of course, but it is hard to exhibit
the precise way it will work now without having an almost complete
implementation.
Anyway, it will try to complete my proposal with some more concrete
material about that when I find time.

[Matthias Kuhn]

Hi Hugo,

On 22.10.2014 09:13, Hugo Mercier wrote:
> Le 22/10/2014 08:39, Matthias Kuhn a écrit :
>> Hi Régis
>>
>> On 10/21/2014 09:44 PM, HAUBOURG wrote:
>>> Hi Matthias,
>>> Agregate functions could be provided by virtual table feature (qep is
>>> coming I think), I suppose we should chose one common way for advanced
>>> relationnal capabilities.
>> I agree that one common way is preferable, but actually this QEP itself
>> is already duplicating functionality (of QgsExpression) and nobody was
>> able yet to confirm that it is possible to optimize queries for
>> execution on the database with sqlite virtual tables. Something that is
>> a must from my point of view. I think it is a good initiative, but for
>> the aforementioned reasons I am not yet completely convinced, that it's
>> the one and only way.
> Hi,
>
> Since I'm the author of the mentioned QEP
> (https://github.com/qgis/QGIS-Enhancement-Proposals/pull/5), I have to
> answer :)
>
> What functionalities of expressions do you think it duplicates ?

Almost all (IN, LIKE, ILIKE, *, +, / just to name a few)
:)

>
> I don't see anything that prevents virtual layer queries from being
> optimized server-side. It has to be taken into account during the
> implementation of virtual layers, of course, but it is hard to exhibit
> the precise way it will work now without having an almost complete
> implementation.
I was referring to this discussion here.

https://github.com/qgis/QGIS-Enhancement-Proposals/pull/5#issuecomment-58148788

Sorry I did not respond again, but I don't think a complete optimization
is possible without having deep access to virtual table functionality in
sqlite (or having to parse the same SQL on our end and replace parts of
it which basically comes close to writing our own engine again).
But let's discuss this on the QEP PR instead of inside this ml thread.

> Anyway, it will try to complete my proposal with some more concrete
> material about that when I find time.
I'll make sure I'll have a look at it when I find time after you found
time :)

Best
Matthias

--
Help getting QGIS to the next level of quality before November 15!
http://blog.vitu.ch/10102014-1046/crowdfunding-initiative-automated-testing

[Even Rouault]

Le mercredi 22 octobre 2014 09:46:23, Matthias Kuhn a écrit :
> Hi Hugo,
>
> On 22.10.2014 09:13, Hugo Mercier wrote:
> > Le 22/10/2014 08:39, Matthias Kuhn a écrit :
> >> Hi Régis
> >>
> >> On 10/21/2014 09:44 PM, HAUBOURG wrote:
> >>> Hi Matthias,
> >>> Agregate functions could be provided by virtual table feature (qep is
> >>> coming I think), I suppose we should chose one common way for advanced
> >>> relationnal capabilities.
> >>
> >> I agree that one common way is preferable, but actually this QEP itself
> >> is already duplicating functionality (of QgsExpression) and nobody was
> >> able yet to confirm that it is possible to optimize queries for
> >> execution on the database with sqlite virtual tables. Something that is
> >> a must from my point of view. I think it is a good initiative, but for
> >> the aforementioned reasons I am not yet completely convinced, that it's
> >> the one and only way.
> >
> > Hi,
> >
> > Since I'm the author of the mentioned QEP
> > (https://github.com/qgis/QGIS-Enhancement-Proposals/pull/5), I have to
> > answer :)
> >
> > What functionalities of expressions do you think it duplicates ?
>
> Almost all (IN, LIKE, ILIKE, *, +, / just to name a few)
>
> :)
> :
> > I don't see anything that prevents virtual layer queries from being
> > optimized server-side. It has to be taken into account during the
> > implementation of virtual layers, of course, but it is hard to exhibit
> > the precise way it will work now without having an almost complete
> > implementation.
>
> I was referring to this discussion here.
>
> https://github.com/qgis/QGIS-Enhancement-Proposals/pull/5#issuecomment-5814
> 8788
>
> Sorry I did not respond again, but I don't think a complete optimization
> is possible without having deep access to virtual table functionality in
> sqlite (or having to parse the same SQL on our end and replace parts of
> it which basically comes close to writing our own engine again).
> But let's discuss this on the QEP PR instead of inside this ml thread.

Just to give you my feeback based on my implementation of SQLite SQL dialect
in OGR (
https://github.com/OSGeo/gdal/blob/trunk/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitevirtualogr.cpp
), I also doubt that you could easily inform the server that a join should be
done. AFAIR I could forward simple filtering to the server/backend (things like
"column_A >= 5 AND column_A <= 10 AND column_B != 10") by implementing the
xFilter virtual method, but Virtual Tables are not informed of joins happening
(the sqlite virtual table API hardy exposes SQL at all). So that would indeed
require analyzing the SQL on QGIS side.

AFAIR, I've just verified that JOIN like "t1.col1 = t2.col2" are seen by the
virtual table implementation like successive filters "t1.col1 = val1", "t1.col1
= val2", where val1, val2 are values from t2.col2.

And regarding xFilter, even filters with OR etc.. are hidden to the virtual
table implementation and evaluated only by SQLite.

Even

>
> > Anyway, it will try to complete my proposal with some more concrete
> > material about that when I find time.
>
> I'll make sure I'll have a look at it when I find time after you found
> time :)
>
> Best
> Matthias

--
Spatialys - Geospatial professional services
http://www.spatialys.com

[Hugo Mercier]

Le 22/10/2014 10:12, Even Rouault a écrit :
> Le mercredi 22 octobre 2014 09:46:23, Matthias Kuhn a écrit :
>> Hi Hugo,
>>
>> On 22.10.2014 09:13, Hugo Mercier wrote:
>>> Le 22/10/2014 08:39, Matthias Kuhn a écrit :
>>>> Hi Régis
>>>>
>>>> On 10/21/2014 09:44 PM, HAUBOURG wrote:
>>>>> Hi Matthias,
>>>>> Agregate functions could be provided by virtual table feature (qep is
>>>>> coming I think), I suppose we should chose one common way for advanced
>>>>> relationnal capabilities.
>>>>
>>>> I agree that one common way is preferable, but actually this QEP itself
>>>> is already duplicating functionality (of QgsExpression) and nobody was
>>>> able yet to confirm that it is possible to optimize queries for
>>>> execution on the database with sqlite virtual tables. Something that is
>>>> a must from my point of view. I think it is a good initiative, but for
>>>> the aforementioned reasons I am not yet completely convinced, that it's
>>>> the one and only way.
>>>
>>> Hi,
>>>
>>> Since I'm the author of the mentioned QEP
>>> (https://github.com/qgis/QGIS-Enhancement-Proposals/pull/5), I have to
>>> answer :)
>>>
>>> What functionalities of expressions do you think it duplicates ?
>>
>> Almost all (IN, LIKE, ILIKE, *, +, / just to name a few)

Well, QgsExpression has been designed to mimick the SQL WHERE clause, so
yes I agree.
My point is that if you want to extend expression to support joins,
aggregates, window functions, etc. you end up recreating SQL, but
probably a less powerful one.

The difference I can see between expressions and SQL is that functions
used in expressions may have no direct equivalent SQLite-side. But it
can be resolved by using user-defined functions in SQLite (that would
just call code from QgsExpression for instance), so that the SQL dialect
would be seen as an extension of the current QgsExpression, not
something different.

>>
>> :)
>> :
>>> I don't see anything that prevents virtual layer queries from being
>>> optimized server-side. It has to be taken into account during the
>>> implementation of virtual layers, of course, but it is hard to exhibit
>>> the precise way it will work now without having an almost complete
>>> implementation.
>>
>> I was referring to this discussion here.
>>
>> https://github.com/qgis/QGIS-Enhancement-Proposals/pull/5#issuecomment-5814
>> 8788
>>
>> Sorry I did not respond again, but I don't think a complete optimization
>> is possible without having deep access to virtual table functionality in
>> sqlite (or having to parse the same SQL on our end and replace parts of
>> it which basically comes close to writing our own engine again).
>> But let's discuss this on the QEP PR instead of inside this ml thread.
>
> Just to give you my feeback based on my implementation of SQLite SQL dialect
> in OGR (
> https://github.com/OSGeo/gdal/blob/trunk/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitevirtualogr.cpp
> ), I also doubt that you could easily inform the server that a join should be
> done. AFAIR I could forward simple filtering to the server/backend (things like
> "column_A >= 5 AND column_A <= 10 AND column_B != 10") by implementing the
> xFilter virtual method, but Virtual Tables are not informed of joins happening
> (the sqlite virtual table API hardy exposes SQL at all). So that would indeed
> require analyzing the SQL on QGIS side.
>
> AFAIR, I've just verified that JOIN like "t1.col1 = t2.col2" are seen by the
> virtual table implementation like successive filters "t1.col1 = val1", "t1.col1
> = val2", where val1, val2 are values from t2.col2.
>
> And regarding xFilter, even filters with OR etc.. are hidden to the virtual
> table implementation and evaluated only by SQLite.
>

Thanks Even for your feedback.

The idea under virtual layers is that SQLite may be exposed to advanced
users if they want to use a complex query on some data sources.

It is very interesting to use SQLite to replace existing features from a
code point of view because it simplifies implementation.
But exposing a SQL query for already existing features such as joins may
not be desirable for standard users.

So for joins, since the information comes from QGIS ("I want to join
tables t1 and t2 on this column predicate"), the SQL query can be
deduced and some other metadata is also known, like the fact that t1 and
t2 may be from the very same database.
In that case the virtual layer may be created with some metadata saying
that this is in fact a join that must be optimized. And then the virtual
layer provider can ask the underlying provider (say postgis) if it can
process the whole join query.