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

Fri May 30 17:33:04 PDT 2014

Hi !

Great thread :-)

Just a thought : if we are able to load shapefiles and other non-db formats
through spatialite, is there a point in still being able to load them the
old way ? Won't this just cause confusion, and force us to maintain an
artificial and less powerful query language ?

And what about database data providers, couldn't we load all of them
through spatialite's virtual ogr, so to allow an unified and full featured
spatial query language on ALL layers ? Wouldn't this also open the door for
caching data of distant servers ?

How cool it would be to be able to create real cross table views with
functions such as buffer, intersect, etc. !!! (without having to import all
data to a database first, which is indeed a real combo-breaker)

(sorry if I missed the point, I almost know nothing about how data
providers work in QGIS)

Best,

Olivier

2014-05-30 12:30 GMT+02:00 Nathan Woodrow <madmanwoo at gmail.com>:

> 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
>>
>
>
> _______________________________________________
> 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/20140531/09c55f33/attachment.html>