Re: [Qgis-developer] 1:many relation enhancements
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 ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] 1:many relation enhancements
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. ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] 1:many relation enhancements
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 ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] 1:many relation enhancements
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 ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] 1:many relation enhancements
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. ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] 1:many relation enhancements
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 ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] 1:many relation enhancements
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.k...@gmx.ch 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 ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] 1:many relation enhancements
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. ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
