Re: [Qgis-developer] Aggregates within expression engine
Hi, Hi, I remember having had such a discussion with Hugo and matthias when choosing what could be the best way for doing aggregates and relational queries in QGIS. At that time, we chose to test virtual layers path because all database query planner stuff seemed too much work to recode in QGIS core, and because demand from French ministry was to allow users to do advanced relational queries without having a full DB (many users are not granted to do that in by organisations). Anyway, I agree with Paolo that query layers certainly are for advanced SQL users, and are clearly not a straightfull path just to compute a max value in an expression engine.. I have two concerns: 1 - Having aggregate functions in expressions would be very handy. What I fear is that having expressions syntaxes between two worlds could be strange for new comers. "CASE WHEN" instead of "if(condition, value)" but aggregate expression instead of GROUP BY... Don't you feel so? Should we offer both syntax for SQL fans and spreadsheet fans (with some info in documentation panel pointing that both expressions are equivalent) ? Anyway, any of the two syntaxes wil still be clearer than having to put a VB code bloc like in Arcgis :) 2 -Question for the future: Is aggregates with expression the beginning of a relational engine in QGIS? If yes, what should be the future for virtual layers? Still use SQLITE with its pros and cons, or switch to a pure core API (less caveats, but lot of work for sure)? Thoughts? Cheers Régis -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Aggregates-within-expression-engine-tp5256639p5257591.html Sent from the Quantum GIS - Developer mailing list archive at Nabble.com. ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
> > So, thoughts and feedback welcome, no matter how off-the-cuff they are! ;) > > Nyall > Given that using SQL is maybe "harder", and that Mr. Paolo also have a good point aiming to ease to use: i would like just to remind what the very nature of a G.I.S. is, namely a Database, usually with an extra column that happens to be a geometry. I can understand that inexperienced users can be a little confused by the SQL syntax but if they want to do some *real work they will have to use QGIS as a database* so i would think of a syntax that is closer to SQL than to anything else because sooner or later they will have to learn SQL if they want to grow up. I think that using anything else would in fact add an extra layer of complexity, something pointless to learn and that conceals what's going on under the hood. just my 2c, best regards, Roy. ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
+1
Luigi Pirelli
**
* Boundless QGIS Support/Development: lpirelli AT boundlessgeo DOT com
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Mastering QGIS:
https://www.packtpub.com/application-development/mastering-qgis
**
On 16 March 2016 at 09:15, Hugo Mercier wrote:
> Hi,
>
> Just for me to understand: why not considering improving a bit the
> virtual layers ?
>
> There is already a support for user defined aggregate functions. Caching
> of the computed aggregate is already done by the engine (I guess). And
> we could add some functions to restrict the query to the selected
> features of a layer or deal with relations ...
>
> And using something like the "query builder" found in db manager (and
> inspired by mapinfo), it may ease to deal with the SQL syntax ...
>
> On 16/03/2016 08:30, Neumann, Andreas wrote:
>> Hi Paolo,
>>
>> Thank you for your feedback.
>>
>> Why do you think SQL aggregate syntax (e.g. as outlined
>> at http://www.postgresql.org/docs/9.5/interactive/functions-aggregate.html)
>> is a no-go? Can you explain this in detail? QGIS is much closer to a
>> database then it is to a spreadsheet - in fact for most serious QGIS
>> work you store your data in a SQL database.
>>
>> It was my impression that QGIS tries to maintain expression syntax
>> compatible with SQL wherever possible. I think it would be a good thing.
>>
>> The question is how we can specify grouping. I'd like to see support for
>> the following grouping mechanisms:
>>
>>
>>
>> * whole record set
>> * given grouping field or expression
>> * current selection
>> * grouping based on relations (very important to my employer) - also
>> our initial trigger for funding this work
>> * current set of symbology rules or classes (not so important to me,
>> but maybe useful to some)
>>
>>
>>
>> Andreas
>>
>> On 2016-03-16 08:11, Paolo Cavallini wrote:
>>
>>> Hi Nyall,
>>>
>>> Il 16/03/2016 07:17, Nyall Dawson ha scritto:
>>>
I'm also torn regarding the best syntax to use for aggregates within
expressions. I'm unsure if the traditional SQL "group by" clauses
would be a good fit within the existing QGIS expression syntax (eg
"sum("some_field") group by "some_other_field"). To me it doesn't fit
with the existing functional approach that the expressions take. But
on the other hand, trying to implement this as functions would result
in some very clumsy expressions: "aggregate('sum', "some_field",
"some_other_field")" or "sum("some_field", "some_other_field") ". Has
anyone got any other ideas for syntax which would be a good fit?
>>>
>>> Good news, thanks for sharing.
>>> IMHO having a syntax that is easy to use for the average user is a
>>> crucial point. I think using SQL is a no go. Why not using the
>>> spreadsheet syntax? Not that I find it particularly attractive, but at
>>> least everybody is more or less familiar with it.
>>> All the best.
>>
>>
>>
>>
>>
>>
>> ___
>> Qgis-developer mailing list
>> Qgis-developer@lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
>>
>
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Hi, Yes, the use cases that triggered this project are: * print composer * atlas printing * tables and forms (virtual fields): aggregates on related tables (e.g. show all owners of a parcel, or the count or mean age of all trees in a parcel) Who knows, it could even be useful in labelling, symbology, etc. - e.g. using the above example with the trees: color the parcels based on the mean age of all trees in the parcels. I guess there are many more use cases we don't even think about currently. Andreas On 2016-03-16 09:21, Nyall Dawson wrote: > On 16 March 2016 at 19:15, Hugo Mercier wrote: > >> Hi, >> >> Just for me to understand: why not considering improving a bit the >> virtual layers ? >> >> There is already a support for user defined aggregate functions. Caching >> of the computed aggregate is already done by the engine (I guess). And >> we could add some functions to restrict the query to the selected >> features of a layer or deal with relations ... >> >> And using something like the "query builder" found in db manager (and >> inspired by mapinfo), it may ease to deal with the SQL syntax ... > > I think the use case is quite different. Having aggregates in > expressions is useful for things like embedding the values in > composers and atlas prints, or in data defined overrides. > > Nyall ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
On 16/03/2016 09:21, Nyall Dawson wrote: > On 16 March 2016 at 19:15, Hugo Mercier wrote: >> Hi, >> >> Just for me to understand: why not considering improving a bit the >> virtual layers ? >> >> There is already a support for user defined aggregate functions. Caching >> of the computed aggregate is already done by the engine (I guess). And >> we could add some functions to restrict the query to the selected >> features of a layer or deal with relations ... >> >> And using something like the "query builder" found in db manager (and >> inspired by mapinfo), it may ease to deal with the SQL syntax ... > > I think the use case is quite different. Having aggregates in > expressions is useful for things like embedding the values in > composers and atlas prints, or in data defined overrides. > Sure, but "extending" a layer by a virtual layer that adds new columns computed by aggregate functions could also be a solution. ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
On 16 March 2016 at 19:15, Hugo Mercier wrote:
> Hi,
>
> Just for me to understand: why not considering improving a bit the
> virtual layers ?
>
> There is already a support for user defined aggregate functions. Caching
> of the computed aggregate is already done by the engine (I guess). And
> we could add some functions to restrict the query to the selected
> features of a layer or deal with relations ...
>
> And using something like the "query builder" found in db manager (and
> inspired by mapinfo), it may ease to deal with the SQL syntax ...
I think the use case is quite different. Having aggregates in
expressions is useful for things like embedding the values in
composers and atlas prints, or in data defined overrides.
Nyall
>
> On 16/03/2016 08:30, Neumann, Andreas wrote:
>> Hi Paolo,
>>
>> Thank you for your feedback.
>>
>> Why do you think SQL aggregate syntax (e.g. as outlined
>> at http://www.postgresql.org/docs/9.5/interactive/functions-aggregate.html)
>> is a no-go? Can you explain this in detail? QGIS is much closer to a
>> database then it is to a spreadsheet - in fact for most serious QGIS
>> work you store your data in a SQL database.
>>
>> It was my impression that QGIS tries to maintain expression syntax
>> compatible with SQL wherever possible. I think it would be a good thing.
>>
>> The question is how we can specify grouping. I'd like to see support for
>> the following grouping mechanisms:
>>
>>
>>
>> * whole record set
>> * given grouping field or expression
>> * current selection
>> * grouping based on relations (very important to my employer) - also
>> our initial trigger for funding this work
>> * current set of symbology rules or classes (not so important to me,
>> but maybe useful to some)
>>
>>
>>
>> Andreas
>>
>> On 2016-03-16 08:11, Paolo Cavallini wrote:
>>
>>> Hi Nyall,
>>>
>>> Il 16/03/2016 07:17, Nyall Dawson ha scritto:
>>>
I'm also torn regarding the best syntax to use for aggregates within
expressions. I'm unsure if the traditional SQL "group by" clauses
would be a good fit within the existing QGIS expression syntax (eg
"sum("some_field") group by "some_other_field"). To me it doesn't fit
with the existing functional approach that the expressions take. But
on the other hand, trying to implement this as functions would result
in some very clumsy expressions: "aggregate('sum', "some_field",
"some_other_field")" or "sum("some_field", "some_other_field") ". Has
anyone got any other ideas for syntax which would be a good fit?
>>>
>>> Good news, thanks for sharing.
>>> IMHO having a syntax that is easy to use for the average user is a
>>> crucial point. I think using SQL is a no go. Why not using the
>>> spreadsheet syntax? Not that I find it particularly attractive, but at
>>> least everybody is more or less familiar with it.
>>> All the best.
>>
>>
>>
>>
>>
>>
>> ___
>> Qgis-developer mailing list
>> Qgis-developer@lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
>>
>
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Hi,
Just for me to understand: why not considering improving a bit the
virtual layers ?
There is already a support for user defined aggregate functions. Caching
of the computed aggregate is already done by the engine (I guess). And
we could add some functions to restrict the query to the selected
features of a layer or deal with relations ...
And using something like the "query builder" found in db manager (and
inspired by mapinfo), it may ease to deal with the SQL syntax ...
On 16/03/2016 08:30, Neumann, Andreas wrote:
> Hi Paolo,
>
> Thank you for your feedback.
>
> Why do you think SQL aggregate syntax (e.g. as outlined
> at http://www.postgresql.org/docs/9.5/interactive/functions-aggregate.html)
> is a no-go? Can you explain this in detail? QGIS is much closer to a
> database then it is to a spreadsheet - in fact for most serious QGIS
> work you store your data in a SQL database.
>
> It was my impression that QGIS tries to maintain expression syntax
> compatible with SQL wherever possible. I think it would be a good thing.
>
> The question is how we can specify grouping. I'd like to see support for
> the following grouping mechanisms:
>
>
>
> * whole record set
> * given grouping field or expression
> * current selection
> * grouping based on relations (very important to my employer) - also
> our initial trigger for funding this work
> * current set of symbology rules or classes (not so important to me,
> but maybe useful to some)
>
>
>
> Andreas
>
> On 2016-03-16 08:11, Paolo Cavallini wrote:
>
>> Hi Nyall,
>>
>> Il 16/03/2016 07:17, Nyall Dawson ha scritto:
>>
>>> I'm also torn regarding the best syntax to use for aggregates within
>>> expressions. I'm unsure if the traditional SQL "group by" clauses
>>> would be a good fit within the existing QGIS expression syntax (eg
>>> "sum("some_field") group by "some_other_field"). To me it doesn't fit
>>> with the existing functional approach that the expressions take. But
>>> on the other hand, trying to implement this as functions would result
>>> in some very clumsy expressions: "aggregate('sum', "some_field",
>>> "some_other_field")" or "sum("some_field", "some_other_field") ". Has
>>> anyone got any other ideas for syntax which would be a good fit?
>>
>> Good news, thanks for sharing.
>> IMHO having a syntax that is easy to use for the average user is a
>> crucial point. I think using SQL is a no go. Why not using the
>> spreadsheet syntax? Not that I find it particularly attractive, but at
>> least everybody is more or less familiar with it.
>> All the best.
>
>
>
>
>
>
> ___
> Qgis-developer mailing list
> Qgis-developer@lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Hi Paolo On 03/16/2016 09:08 AM, Paolo Cavallini wrote: > Hi Matthias, > > Il 16/03/2016 08:47, Matthias Kuhn ha scritto: > >> Sorry, I'm not familiar with the spreadsheet syntax. Can you make some >> examples so we can compare the advantages and drawbacks in detail? > > best if you have a look to LibreOffice help itself. > BTW, we already used the LO help for the description of many basic > functions, which are essentially the same. > https://help.libreoffice.org/Calc/AGGREGATE_function Are you sure that =AGGREGATE(4;1;A2:A9) is easy to write and understand? Matthias ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Hi Matthias, Il 16/03/2016 08:47, Matthias Kuhn ha scritto: > Sorry, I'm not familiar with the spreadsheet syntax. Can you make some > examples so we can compare the advantages and drawbacks in detail? best if you have a look to LibreOffice help itself. BTW, we already used the LO help for the description of many basic functions, which are essentially the same. -- Paolo Cavallini - www.faunalia.eu QGIS & PostGIS courses: http://www.faunalia.eu/training.html ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Il 16/03/2016 08:53, Neumann, Andreas ha scritto: > And what would be the alternative syntax that is easier than SQL syntax? as said, everyone knows at least some spreadsheet syntax > Please try to be more specific rather than just saying SQL syntax is > complicated. I'm not saying it is complicated. I like it and use it a lot. What I'm saying is that for our average users, based on my sample of over 350 courses * ~15 participants, it is out of reach. I'm happy to be proved wrong. All the best. -- Paolo Cavallini - www.faunalia.eu QGIS & PostGIS courses: http://www.faunalia.eu/training.html ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
On Wed, Mar 16, 2016 at 6:17 AM, Nyall Dawson wrote:
> I'm also torn regarding the best syntax to use for aggregates within
> expressions. I'm unsure if the traditional SQL "group by" clauses
> would be a good fit within the existing QGIS expression syntax (eg
> "sum("some_field") group by "some_other_field"). To me it doesn't fit
> with the existing functional approach that the expressions take. But
> on the other hand, trying to implement this as functions would result
> in some very clumsy expressions: "aggregate('sum', "some_field",
> "some_other_field")" or "sum("some_field", "some_other_field") ". Has
> anyone got any other ideas for syntax which would be a good fit?
>
In R (stats programming language/package) these things are done with
functions, so you can get expressions like:
means = summarize(group_by(filter(data,"Outcome"=="fatal"),"Disease"),
mean=mean("Age"))
where "Disease" is the name of a column in the "data" dataset. This
expression would return a vector (like a python "list") of the mean
ages for people who died with each disease. Now, once you get a few
more expressions in these things get messy, so people implemented a
new operator to express these like a pipeline. The above can be
written:
means = data %>% filter("Outcome"=="fatal") %>% group_by("Disease")
%>% summarise(mean=mean("Age"))
which reads much more naturally from left to right than trying to
unwrap nested function calls. (Note the ugly-looking operator with
%-signs is because user-defined R operators have to be demarcated in %
signs). So if I wanted to know the range of the mean ages by disease
I'd just add:
%>% range()
on the end of the above expression, rather than sticking `range(` at
the start and then going to the end and putting the closing `)` next
to, quite probably, a bunch of uncountable closing brackets.
The operator works quite simply by rewriting the expression `foo %>%
bar(x,y,z)` as `bar(foo,x,y,z)`.
This pipe operator has divided R users a bit, and it has been
overused and abused greatly. Its also slower than direct function
evaluation because of the re-arrangement of the expression, but its
unlikely that any pipeline chain will spend much of its time doing
that compared to doing the actual work of the pipeline.
Anyway, if something like that might be useful in QGIS, take a look
at the documentation for R's `dplyr` package, or tutorials with it.
Barry
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Hi, just my 2 ct: I agree with Paolo that normal users do not know SQL, on the other hand there might be users that do not know spreadsheet syntax either (e.g. me :) which spreadsheet BTW: Excel? LO? or do they use the same syntax? SQL would be a standardized approach (and is the syntax already used in QGIS expressions). On the other hand if it is possible to "click together" the expression it doesn't matter which syntax is used, good documentation and direct help is a must here. If you e.g. choose a SUM function a GROUP BY woould be added automatically at the end (if not already there) SQL is used at so many places in QGIS: layer filters, labelling, rule-based renderer... I would not mix this with different syntax. Bernhard Am 16.03.2016 um 08:36 schrieb Paolo Cavallini: Il 16/03/2016 08:30, Neumann, Andreas ha scritto: Why do you think SQL aggregate syntax (e.g. as outlined at http://www.postgresql.org/docs/9.5/interactive/functions-aggregate.html) is a no-go? Can you explain this in detail? QGIS is much closer to a database then it is to a spreadsheet - in fact for most serious QGIS work you store your data in a SQL database. It was my impression that QGIS tries to maintain expression syntax compatible with SQL wherever possible. I think it would be a good thing. Hi Andreas, based on my lengthy experience with courses, I'm pretty sure SQL commands are out of reach for most "normal" GIS users. It is fine to have access to SQL for power users, but aggregate functions should IMHO be available to anyone. All the best. __ Information from ESET Mail Security, version of virus signature database 13185 (20160316) __ The message was checked by ESET Mail Security. http://www.eset.com ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Hi Paolo, And what would be the alternative syntax that is easier than SQL syntax? Please try to be more specific rather than just saying SQL syntax is complicated. Syntax that allows to use aggregate functions and grouping for the following use cases: Aggregate functions: All data types: * Count * Count distinct * Count missing values (null) Text data types: * string_agg/join: with delimiter and sort option Numeric data types: * Sum * Mean * Median * Minimum * Maximum * Range * St dev(pop/sample) * Minority * Majority * Variety * Q1 * Q3 * IQR Date/Time types: * Minimum date/time * Maximum date/time * Range between minimum/maximum date/time -- Grouping options: * All records in the attribute table * A given grouping field or expression * Currently Selected features * Selection based on table relations (e.g. aggregation of all owners of a parcel) * Optional: have the same function grouped by each symbol category or rule Thanks, Andreas On 2016-03-16 08:36, Paolo Cavallini wrote: > Il 16/03/2016 08:30, Neumann, Andreas ha scritto: > >> Why do you think SQL aggregate syntax (e.g. as outlined >> at http://www.postgresql.org/docs/9.5/interactive/functions-aggregate.html) >> is a no-go? Can you explain this in detail? QGIS is much closer to a >> database then it is to a spreadsheet - in fact for most serious QGIS >> work you store your data in a SQL database. >> >> It was my impression that QGIS tries to maintain expression syntax >> compatible with SQL wherever possible. I think it would be a good thing. > > Hi Andreas, > based on my lengthy experience with courses, I'm pretty sure SQL > commands are out of reach for most "normal" GIS users. > It is fine to have access to SQL for power users, but aggregate > functions should IMHO be available to anyone. > All the best. ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Hi Paolo, On 03/16/2016 08:36 AM, Paolo Cavallini wrote: > Il 16/03/2016 08:30, Neumann, Andreas ha scritto: > >> Why do you think SQL aggregate syntax (e.g. as outlined >> at http://www.postgresql.org/docs/9.5/interactive/functions-aggregate.html) >> is a no-go? Can you explain this in detail? QGIS is much closer to a >> database then it is to a spreadsheet - in fact for most serious QGIS >> work you store your data in a SQL database. >> >> It was my impression that QGIS tries to maintain expression syntax >> compatible with SQL wherever possible. I think it would be a good thing. > > Hi Andreas, > based on my lengthy experience with courses, I'm pretty sure SQL > commands are out of reach for most "normal" GIS users. > It is fine to have access to SQL for power users, but aggregate > functions should IMHO be available to anyone. > All the best. > Sorry, I'm not familiar with the spreadsheet syntax. Can you make some examples so we can compare the advantages and drawbacks in detail? Thank you Matthias ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Il 16/03/2016 08:30, Neumann, Andreas ha scritto: > Why do you think SQL aggregate syntax (e.g. as outlined > at http://www.postgresql.org/docs/9.5/interactive/functions-aggregate.html) > is a no-go? Can you explain this in detail? QGIS is much closer to a > database then it is to a spreadsheet - in fact for most serious QGIS > work you store your data in a SQL database. > > It was my impression that QGIS tries to maintain expression syntax > compatible with SQL wherever possible. I think it would be a good thing. Hi Andreas, based on my lengthy experience with courses, I'm pretty sure SQL commands are out of reach for most "normal" GIS users. It is fine to have access to SQL for power users, but aggregate functions should IMHO be available to anyone. All the best. -- Paolo Cavallini - www.faunalia.eu QGIS & PostGIS courses: http://www.faunalia.eu/training.html ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Hi Paolo,
Thank you for your feedback.
Why do you think SQL aggregate syntax (e.g. as outlined at
http://www.postgresql.org/docs/9.5/interactive/functions-aggregate.html)
is a no-go? Can you explain this in detail? QGIS is much closer to a
database then it is to a spreadsheet - in fact for most serious QGIS
work you store your data in a SQL database.
It was my impression that QGIS tries to maintain expression syntax
compatible with SQL wherever possible. I think it would be a good thing.
The question is how we can specify grouping. I'd like to see support for
the following grouping mechanisms:
* whole record set
* given grouping field or expression
* current selection
* grouping based on relations (very important to my employer) - also
our initial trigger for funding this work
* current set of symbology rules or classes (not so important to me,
but maybe useful to some)
Andreas
On 2016-03-16 08:11, Paolo Cavallini wrote:
> Hi Nyall,
>
> Il 16/03/2016 07:17, Nyall Dawson ha scritto:
>
>> I'm also torn regarding the best syntax to use for aggregates within
>> expressions. I'm unsure if the traditional SQL "group by" clauses
>> would be a good fit within the existing QGIS expression syntax (eg
>> "sum("some_field") group by "some_other_field"). To me it doesn't fit
>> with the existing functional approach that the expressions take. But
>> on the other hand, trying to implement this as functions would result
>> in some very clumsy expressions: "aggregate('sum', "some_field",
>> "some_other_field")" or "sum("some_field", "some_other_field") ". Has
>> anyone got any other ideas for syntax which would be a good fit?
>
> Good news, thanks for sharing.
> IMHO having a syntax that is easy to use for the average user is a
> crucial point. I think using SQL is a no go. Why not using the
> spreadsheet syntax? Not that I find it particularly attractive, but at
> least everybody is more or less familiar with it.
> All the best.
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Aggregates within expression engine
Hi Nyall,
Il 16/03/2016 07:17, Nyall Dawson ha scritto:
> I'm also torn regarding the best syntax to use for aggregates within
> expressions. I'm unsure if the traditional SQL "group by" clauses
> would be a good fit within the existing QGIS expression syntax (eg
> "sum("some_field") group by "some_other_field"). To me it doesn't fit
> with the existing functional approach that the expressions take. But
> on the other hand, trying to implement this as functions would result
> in some very clumsy expressions: "aggregate('sum', "some_field",
> "some_other_field")" or "sum("some_field", "some_other_field") ". Has
> anyone got any other ideas for syntax which would be a good fit?
Good news, thanks for sharing.
IMHO having a syntax that is easy to use for the average user is a
crucial point. I think using SQL is a no go. Why not using the
spreadsheet syntax? Not that I find it particularly attractive, but at
least everybody is more or less familiar with it.
All the best.
--
Paolo Cavallini - www.faunalia.eu
QGIS & PostGIS courses: http://www.faunalia.eu/training.html
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer
