[Qgis-developer] have aggregate/window expressions ever been discussed?

[Nathan Woodrow]

Here is the way I currently see it.

Currently there is no really easy way to do cross layer queries. Lets
assume that the layers are not database based, which a lot of time they are
not, or are mixed data sources, which I deal with a lot. Currently your
only option (if you want to do query type stuff) is to upload everything to
a database format and run a query there but even that experience is broken,
or not an option.  Using SQLIte virtual tables here IMO would gives us a
nice quick high level query that we can run on any layer open in QGIS to
get some results.  Of course there needs to be some information warning the
user that there are limitations and if you run it on a large data set while
trying to do something complicated you are going to get crap performance,
however for the simple case of something like: SELECT cola,
buffer(geometry) FROM layer WHERE ID > 10, this would be fine and we could
just return a new layer with geom and cola.

Using SQLite here means we can be layer agnostic at the higher level. This
would also solve one of my longer standing issue of extracting just the
columns you need, currently this is a really painful process. With a query
model we just run query -> export like normal.

When it comes to QgsExpression translation to native query language, I
don't see any direct problem with this, in fact Martin designed
QgsExpression so we can just attach node visitors to the tree and do what
we need.  Each provider would just have their own expression tree visitor
class and do what it needs with each node in the tree.   The main problem
with this can come with translation when something doesn't quite match up,
or isn't supported, in which case you would just need to bail and run it in
QGIS.

My other idea is to allow the data providers to run a native query on the
connection/database and just return the result as a new layer.  This would
be a matter just passing a query string, running the query on the provider,
returning a new layer with the data.   The layers don't need to be opened
in QGIS.  Pretty much what DB Manager currently does but done at a provider
level, so we have it for each provider.

In my mind there are two ways here for running queries:

1) SQLite over open layers in QGIS
2) Data provider query

The query dialog would have both options and the user can pick which one
they want to do.  If you select SQLite then you get the warning about
performance, if you use the provider then you can select which provider and
connection to use.  This should just reuse the connections you already have
defined in the browser.

This is the general idea (stolen from OpenJUMP):



Regards,
Nathan


On Thu, May 29, 2014 at 6:12 PM, Matthias Kuhn <matthias.kuhn at gmx.ch> wrote:

>  Using the foreign key constraints to propose automatically created
> relations to the user would be a nice feature for any database. One problem
> here is, that the providers handle the database management themselves and
> only provide QGIS the information that there is a layer, but not from what
> database it comes. Maybe it would be possible to extract this information
> with the current architecture but for an optimal handling the provider
> architecture should be adapted to provide such information in a standard
> way.
>
> Matthias
>
>
> On 05/28/2014 10:54 PM, Bob and Deb wrote:
>
> How about querying the sqlite_master table to find the data type?  And why
> not use the constraints found in this query to set up the qgis relations?
>
> -Bob (aka cgs_bob on freenode)
> On May 27, 2014 6:04 AM, "Régis Haubourg" <
> regis.haubourg at eau-adour-garonne.fr> wrote:
>
>> Hugo Mercier wrote
>> > Or
>> >> calculated fields in view can't be explicitly cast, so QGIS should have
>> >> to
>> >> guess data type based on a data scan (a major unadressed issue of
>> sqlite)
>> >
>> > Hmmm I wasn't aware of this limitation in SQLITE views :(
>>
>> Yes, SQLITE does dynamic typing, so user or provider has to scan values to
>> guess the right type.
>> Here is a sqlite topic on that [0]
>> And here my initial post in qgis list [1]
>>
>> [0]
>>
>> http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-td56769.html#a56770
>>
>> [1]
>>
>> http://osgeo-org.1560.x6.nabble.com/Spatialite-can-t-type-fields-of-a-view-td5058436.html
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://osgeo-org.1560.x6.nabble.com/have-aggregate-window-expressions-ever-been-discussed-tp5142215p5142714.html
>> Sent from the Quantum GIS - Developer mailing list archive at Nabble.com.
>> _______________________________________________
>> Qgis-developer mailing list
>> Qgis-developer at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>>
>
>
> _______________________________________________
> Qgis-developer mailing listQgis-developer at lists.osgeo.orghttp://lists.osgeo.org/mailman/listinfo/qgis-developer
>
>
>
> _______________________________________________
> Qgis-developer mailing list
> Qgis-developer at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20140530/89c1234b/attachment-0001.html>