Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Updates on expression functions help
fix examples and description
add/update examples to "cross-link" functions
  • Loading branch information
DelazJ authored and nyalldawson committed Aug 9, 2020
1 parent a4c8f33 commit 43a20fe
Show file tree
Hide file tree
Showing 17 changed files with 36 additions and 29 deletions.
6 changes: 3 additions & 3 deletions resources/function_help/json/CASE
Expand Up @@ -2,14 +2,14 @@
"name": "CASE",
"type": "expression",
"groups": ["Conditionals"],
"description": "CASE is used to evaluate a series of conditions and return a result for the first condition that is met. The conditions are evaluated sequentially, and if a condition is true, the evaluation will stop, and the corresponding result will be returned. If none of the conditions are true, the value in the ELSE clause is returned. Furthermore, if no ELSE clause is set and none of the conditions are met, NULL is returned.<br><pre>CASE<br>WHEN <i>condition</i> THEN <i>result</i><br>[ …n ]<br>[ ELSE <i>result</i> ]<br>END</pre>[ ] marks optional components<br>",
"description": "CASE is used to evaluate a series of conditions and return a result for the first condition met. The conditions are evaluated sequentially, and if a condition is true, the evaluation stops, and the corresponding result is returned. If none of the conditions are true, the value in the ELSE clause is returned. Furthermore, if no ELSE clause is set and none of the conditions are met, NULL is returned.<br><pre>CASE<br>WHEN <i>condition</i> THEN <i>result</i><br>[ …n ]<br>[ ELSE <i>result</i> ]<br>END</pre>[ ] marks optional components<br>",
"arguments": [
{"arg":"WHEN condition","description":"A condition expression to evaluate"},
{"arg":"THEN result","description":"If <i>condition</i> evaluates to True then <i>result</i> is evaluated and returned."},
{"arg":"ELSE result","description":"If none of the above conditions evaluated to True then <i>result</i> is evaluated and returned."}
],
"examples": [
{ "expression":"CASE WHEN \"name\" IS NULL THEN 'None' END", "returns":" Returns the string 'none' if the \"name\" field is NULL"},
{ "expression":"CASE WHEN $area > 10000 THEN 'Big property' WHEN $area > 5000 THEN 'Medium property' ELSE 'Small property' END", "returns":" Returns the string 'Big property' if the area is bigger than 10000, 'Medium property' if the area is between 5000 and 10000, and 'Small property' for others"}
{ "expression":"CASE WHEN \"name\" IS NULL THEN 'None' END", "returns":"Returns the string 'None' if the \"name\" field is NULL"},
{ "expression":"CASE WHEN $area > 10000 THEN 'Big property' WHEN $area > 5000 THEN 'Medium property' ELSE 'Small property' END", "returns":"Returns the string 'Big property' if the area is bigger than 10000, 'Medium property' if the area is between 5000 and 10000, and 'Small property' for others"}
]
}
9 changes: 5 additions & 4 deletions resources/function_help/json/aggregate
Expand Up @@ -8,14 +8,15 @@
{"arg":"aggregate", "description":"a string corresponding to the aggregate to calculate. Valid options are:<br /><ul><li>count</li><li>count_distinct</li><li>count_missing</li><li>min</li><li>max</li><li>sum</li><li>mean</li><li>median</li><li>stdev</li><li>stdevsample</li><li>range</li><li>minority</li><li>majority</li><li>q1: first quartile</li><li>q3: third quartile</li><li>iqr: inter quartile range</li><li>min_length: minimum string length</li><li>max_length: maximum string length</li><li>concatenate: join strings with a concatenator</li><li>concatenate_unique: join unique strings with a concatenator</li><li>collect: create an aggregated multipart geometry</li><li>array_agg: create an array of aggregated values</li></ul>"},
{"arg":"expression", "description":"sub expression or field name to aggregate"},
{"arg":"filter", "optional":true, "description":"optional filter expression to limit the features used for calculating the aggregate. Fields and geometry are from the features on the joined layer. The source feature can be accessed with the variable @parent."},
{"arg":"concatenator", "optional":true, "description":"optional string to use to join values for 'concatenate' aggregate"},
{"arg":"order_by", "optional":true, "description":"optional filter expression to order the features used for calculating the aggregate. Fields and geometry are from the features on the joined layer."}
{"arg":"concatenator", "optional":true, "default":"''", "description":"optional string to use to join values for 'concatenate' aggregate"},
{"arg":"order_by", "optional":true, "description":"optional filter expression to order the features used for calculating the aggregate. Fields and geometry are from the features on the joined layer. By default, no sort is done"}
],
"examples": [
{ "expression":"aggregate(layer:='rail_stations',aggregate:='sum',expression:=\"passengers\")", "returns":"sum of all values from the passengers field in the rail_stations layer"},
{ "expression":"aggregate('rail_stations','sum', \"passengers\"/7)", "returns":"calculates a daily average of \"passengers\" by dividing the \"passengers\" field by 7 before summing the values"},
{ "expression":"aggregate(layer:='rail_stations',aggregate:='sum',expression:=\"passengers\",filter:=\"class\">3)", "returns":"sums up all values from the \"passengers\" field from features where the \"class\" attribute is greater than 3 only"},
{ "expression":"aggregate(layer:='rail_stations',aggregate:='concatenate', expression:=\"name\", concatenator:=',')", "returns":"comma separated list of the name field for all features in the rail_stations layer"},
{ "expression":"aggregate(layer:='countries', aggregate:='max', expression:=\"code\", filter:=intersects( $geometry, geometry(@parent) ) )", "returns":"The country code of an intersecting country on the layer 'countries'"}
]
{ "expression":"aggregate(layer:='countries', aggregate:='max', expression:=\"code\", filter:=intersects( $geometry, geometry(@parent) ) )", "returns":"The country code of an intersecting country on the layer 'countries'"},
{ "expression":"aggregate(layer:='rail_stations',aggregate:='sum',expression:=\"passengers\",filter:=contains( @atlas_geometry, $geometry ) )", "returns":"sum of all values from the passengers field in the rail_stations within the current atlas feature"}
]
}
4 changes: 2 additions & 2 deletions resources/function_help/json/darker
Expand Up @@ -5,7 +5,7 @@
"description": "Returns a darker (or lighter) color string",
"arguments": [
{"arg":"color", "description":"a color string"},
{"arg":"factor", "description":"an integer corresponding to the darkening factor:<ul><li>if the factor is greater than 100, this functions returns a darker color (e.g., setting factor to 300 returns a color that has one-third the brightness);</li><li>if the factor is less than 100, the return color is lighter, but using the lighter() function for this purpose is recommended;</li><li>if the factor is 0 or negative, the return value is unspecified.</li></ul>"}
{"arg":"factor", "description":"an integer corresponding to the darkening factor:<ul><li>if the factor is greater than 100, this function returns a darker color (e.g., setting factor to 200 returns a color that is half the brightness);</li><li>if the factor is less than 100, the return color is lighter, but using the lighter() function for this purpose is recommended;</li><li>if the factor is 0 or negative, the return value is unspecified.</li></ul>"}
],
"examples": [ { "expression":"darker('200,10,30',300)", "returns":"'66,3,10,255'"}]
"examples": [ { "expression":"darker('200,10,30', 200)", "returns":"'100,5,15,255'"}]
}
2 changes: 1 addition & 1 deletion resources/function_help/json/format_date
Expand Up @@ -9,6 +9,6 @@
{"arg":"language","optional":true,"description":"language (lowercase, two- or three-letter, ISO 639 language code) used to format the date into a custom string"}
],
"examples": [ { "expression":"format_date('2012-05-15','dd.MM.yyyy')", "returns":"'15.05.2012'"},
{ "expression":"format_date('2012-05-15','d MMMM yyyy','fr')", "returns":"'15 juin 2012'"}
{ "expression":"format_date('2012-05-15','d MMMM yyyy','fr')", "returns":"'15 mai 2012'"}
]
}
4 changes: 2 additions & 2 deletions resources/function_help/json/hour
Expand Up @@ -7,12 +7,12 @@
{ "variant": "Time variant",
"variant_description": "Extracts the hour part from a time or datetime.",
"arguments": [ {"arg":"datetime","description":"a time or datetime value"}],
"examples": [ { "expression":"hour('2012-07-22T13:24:57')", "returns":"13"}] },
"examples": [ { "expression":"hour( to_datetime('2012-07-22 13:24:57') )", "returns":"13"}] },
{
"variant": "Interval variant",
"variant_description": "Calculates the length in hours of an interval.",
"arguments": [ {"arg":"interval","description":"interval value to return number of hours from"}],
"examples": [ { "expression":"hour(tointerval('3 hours'))", "returns":"3"},
"examples": [ { "expression":"hour(to_interval('3 hours'))", "returns":"3"},
{ "expression":"hour(age('2012-07-22T13:00:00','2012-07-22T10:00:00'))", "returns":"3"},
{ "expression":"hour(age('2012-01-01','2010-01-01'))", "returns":"17520"}]
}]
Expand Down
2 changes: 1 addition & 1 deletion resources/function_help/json/item_variables
Expand Up @@ -4,6 +4,6 @@
"groups": ["Layout"],
"description": "Returns a map of variables from a composer item inside this composition.",
"arguments": [ {"arg":"id","description":"composer item ID"}],
"examples": [ { "expression":"map_get(item_variables('main_map'), 'map_scale')", "returns":"2000"}
"examples": [ { "expression":"map_get( item_variables('Map 0'), 'map_scale')", "returns":"scale of the item 'Map 0' in the current print layout"}
]
}
4 changes: 2 additions & 2 deletions resources/function_help/json/lighter
Expand Up @@ -5,7 +5,7 @@
"description": "Returns a lighter (or darker) color string",
"arguments": [
{"arg":"color", "description":"a color string"},
{"arg":"factor", "description":"a integer number corresponding to the lightening factor:<ul><li>if the factor is greater than 100, this functions returns a lighter color (for e.g., setting factor to 150 returns a color that is 50% brighter);</li><li>if the factor is less than 100, the return color is darker, but using the darker() function for this purpose is recommended;</li><li>if the factor is 0 or negative, the return value is unspecified.</li></ul>"}
{"arg":"factor", "description":"an integer corresponding to the lightening factor:<ul><li>if the factor is greater than 100, this function returns a lighter color (e.g., setting factor to 150 returns a color that is 50% brighter);</li><li>if the factor is less than 100, the return color is darker, but using the darker() function for this purpose is recommended;</li><li>if the factor is 0 or negative, the return value is unspecified.</li></ul>"}
],
"examples": [ { "expression":"lighter('200,10,30',200)", "returns":"'255,158,168,255'"}]
"examples": [ { "expression":"lighter('200,10,30', 200)", "returns":"'255,158,168,255'"}]
}
4 changes: 3 additions & 1 deletion resources/function_help/json/map_get
Expand Up @@ -5,5 +5,7 @@
"description": "Returns the value of a map, given it's key.",
"arguments": [ {"arg":"map","description":"a map"},
{"arg":"key","description":"the key to lookup"}],
"examples": [ { "expression":"map_get(map('1','one','2','two'),'2')", "returns":"'two'"}]
"examples": [ { "expression":"map_get(map('1','one','2','two'),'2')", "returns":"'two'"},
{ "expression":"map_get( item_variables('Map 0'), 'map_scale')", "returns":"scale of the item 'Map 0' in the current print layout"}
]
}
4 changes: 2 additions & 2 deletions resources/function_help/json/minute
Expand Up @@ -7,12 +7,12 @@
{ "variant": "Time variant",
"variant_description": "Extracts the minutes part from a time or datetime.",
"arguments": [ {"arg":"datetime","description":"a time or datetime value"}],
"examples": [ { "expression":"minute('2012-07-22T13:24:57')", "returns":"24"}] },
"examples": [ { "expression":"minute( to_datetime('2012-07-22 13:24:57') )", "returns":"24"}] },
{
"variant": "Interval variant",
"variant_description": "Calculates the length in minutes of an interval.",
"arguments": [ {"arg":"interval","description":"interval value to return number of minutes from"}],
"examples": [ { "expression":"minute(tointerval('3 minutes'))", "returns":"3"},
"examples": [ { "expression":"minute(to_interval('3 minutes'))", "returns":"3"},
{ "expression":"minute(age('2012-07-22T00:20:00','2012-07-22T00:00:00'))", "returns":"20"},
{ "expression":"minute(age('2012-01-01','2010-01-01'))", "returns":"1051200"}]
}]
Expand Down
3 changes: 2 additions & 1 deletion resources/function_help/json/op_minus
Expand Up @@ -8,6 +8,7 @@
],
"examples": [
{ "expression":"5 - 4", "returns":"1"},
{ "expression":"5 - NULL", "returns":"NULL"}
{ "expression":"5 - NULL", "returns":"NULL"},
{ "expression":"to_datetime('2012-05-05 12:00:00') - to_interval('1 day 2 hours')", "returns":"2012-05-04T10:00:00"}
]
}
4 changes: 3 additions & 1 deletion resources/function_help/json/op_plus
Expand Up @@ -8,6 +8,8 @@
],
"examples": [
{ "expression":"5 + 4", "returns":"9"},
{ "expression":"5 + NULL", "returns":"NULL"}
{ "expression":"5 + NULL", "returns":"NULL"},
{ "expression":"'QGIS ' + 'ROCKS'", "returns":"'QGIS ROCKS'"},
{ "expression":"to_datetime('2020-08-01 12:00:00') + '1 day 2 hours'", "returns":"2020-08-02T14:00:00"}
]
}
5 changes: 3 additions & 2 deletions resources/function_help/json/second
Expand Up @@ -7,12 +7,13 @@
{ "variant": "Time variant",
"variant_description": "Extracts the seconds part from a time or datetime.",
"arguments": [ {"arg":"datetime","description":"a time or datetime value"}],
"examples": [ { "expression":"second('2012-07-22T13:24:57')", "returns":"57"}] },
"examples": [ { "expression":"second( to_datetime('2012-07-22 13:24:57') )", "returns":"57"}] },
{
"variant": "Interval variant",
"variant_description": "Calculates the length in seconds of an interval.",
"arguments": [ {"arg":"interval","description":"interval value to return number of seconds from"}],
"examples": [ { "expression":"second(age('2012-07-22T00:20:00','2012-07-22T00:00:00'))", "returns":"1200"},
"examples": [ { "expression":"second(to_interval('3 minutes'))", "returns":"180"},
{ "expression":"second(age('2012-07-22T00:20:00','2012-07-22T00:00:00'))", "returns":"1200"},
{ "expression":"second(age('2012-01-01','2010-01-01'))", "returns":"63072000"}]
}]
}
6 changes: 3 additions & 3 deletions resources/function_help/json/sqlite_fetch_and_increment
Expand Up @@ -2,7 +2,7 @@
"name": "sqlite_fetch_and_increment",
"type": "function",
"groups": ["Record and Attributes"],
"description": "Manage autoincrementing values in sqlite databases.<p>SQlite default values can only be applied on insert and not prefetched.</p><p>This makes it impossible to acquire an incremented primary key via AUTO_INCREMENT before creating the row in the database. Sidenote: with postgres, this works via the option <i>evaluate default values</i>.</p><p>When adding new features with relations, it is really nice to be able to already add children for a parent, while the parents form is still open and hence the parent feature uncommitted.</p><p>To get around this limitation, this function can be used to manage sequence values in a separate table on sqlite based formats like gpkg.</p><p>The sequence table will be filtered for a sequence id (filter_attribute and filter_value) and the current value of the id_field will be incremented by 1 and the incremented value returned.</p><p>If additional columns require values to be specified, the default_value map can be used for this purpose.</p><p><b>Note</b><br/>This function modifies the target sqlite table. It is intended for usage with default value configurations for attributes.</p><p>When the database parameter is a layer and the layer is in transaction mode, the value will only be retrieved once during the lifetime of a transaction and cached and incremented. This makes it unsafe to work on the same database from several processes in parallel.</p>",
"description": "Manage autoincrementing values in sqlite databases.<p>SQlite default values can only be applied on insert and not prefetched.</p><p>This makes it impossible to acquire an incremented primary key via AUTO_INCREMENT before creating the row in the database. Sidenote: with postgres, this works via the option <i>evaluate default values</i>.</p><p>When adding new features with relations, it is really nice to be able to already add children for a parent, while the parents form is still open and hence the parent feature uncommitted.</p><p>To get around this limitation, this function can be used to manage sequence values in a separate table on sqlite based formats like gpkg.</p><p>The sequence table will be filtered for a sequence id (filter_attribute and filter_value) and the current value of the id_field will be incremented by 1 and the incremented value returned.</p><p>If additional columns require values to be specified, the default_values map can be used for this purpose.</p><p><b>Note</b><br/>This function modifies the target sqlite table. It is intended for usage with default value configurations for attributes.</p><p>When the database parameter is a layer and the layer is in transaction mode, the value will only be retrieved once during the lifetime of a transaction and cached and incremented. This makes it unsafe to work on the same database from several processes in parallel.</p>",
"arguments": [
{"arg":"database", "description":"Path to the sqlite file or geopackage layer"},
{"arg":"table", "description":"Name of the table that manages the sequences"},
Expand All @@ -12,7 +12,7 @@
{"arg":"default_values", "description":"Map with default values for additional columns on the table. The values need to be fully quoted. Functions are allowed.", "optional": true}
],
"examples": [
{ "expression":"sqlite_fetch_and_increment(@layer, 'sequence_table', 'last_unique_id', 'sequence_id', 'global', map('last_change','date(''now'')','user','''' || @user_account_name || ''''))", "returns":"0"},
{ "expression":"sqlite_fetch_and_increment(layer_property(@layer, 'path'), 'sequence_table', 'last_unique_id', 'sequence_id', 'global', map('last_change','date(''now'')','user','''' || @user_account_name || ''''))", "returns":"0"}
{ "expression":"sqlite_fetch_and_increment(@layer, 'sequence_table', 'last_unique_id', 'sequence_id', 'global', map('last_change', 'date(''now'')', 'user', '''' || @user_account_name || ''''))", "returns":"0"},
{ "expression":"sqlite_fetch_and_increment(layer_property(@layer, 'path'), 'sequence_table', 'last_unique_id', 'sequence_id', 'global', map('last_change', 'date(''now'')', 'user', '''' || @user_account_name || ''''))", "returns":"0"}
]
}
2 changes: 1 addition & 1 deletion resources/function_help/json/to_dm
Expand Up @@ -2,7 +2,7 @@
"name": "to_dm",
"type": "function",
"groups": ["Conversions"],
"description": "Convert a coordinate to degree, minute.",
"description": "Converts a coordinate to degree, minute.",
"arguments": [
{"arg":"coordinate","description":"A latitude or longitude value."},
{"arg":"axis","description":"The axis of the coordinate. Either 'x' or 'y'."},
Expand Down
2 changes: 1 addition & 1 deletion resources/function_help/json/to_dms
Expand Up @@ -2,7 +2,7 @@
"name": "to_dms",
"type": "function",
"groups": ["Conversions"],
"description": "Convert a coordinate to degree, minute, second.",
"description": "Converts a coordinate to degree, minute, second.",
"arguments": [
{"arg":"coordinate","description":"A latitude or longitude value."},
{"arg":"axis","description":"The axis of the coordinate. Either 'x' or 'y'."},
Expand Down
2 changes: 1 addition & 1 deletion resources/function_help/json/to_interval
Expand Up @@ -2,7 +2,7 @@
"name": "to_interval",
"type": "function",
"groups": ["Conversions", "Date and Time"],
"description": "Converts a string to a interval type. Can be used to take days, hours, month, etc of a date.",
"description": "Converts a string to an interval type. Can be used to take days, hours, month, etc of a date.",
"arguments": [ {"arg":"string","description":"a string representing an interval. Allowable formats include {n} days {n} hours {n} months."}],
"examples": [ { "expression":"to_datetime('2012-05-05 12:00:00') - to_interval('1 day 2 hours')", "returns":"2012-05-04T10:00:00"}
]
Expand Down
2 changes: 1 addition & 1 deletion resources/function_help/json/uuid
Expand Up @@ -2,7 +2,7 @@
"name": "uuid",
"type": "function",
"groups": ["Record and Attributes"],
"description": "Generates a Universally Unique Identifier (UUID) for each row using the Qt <a href='http://qt-project.org/doc/qt-4.8/quuid.html#createUuid'>QUuid::createUuid</a> method. Each UUID is 38 characters long.",
"description": "Generates a Universally Unique Identifier (UUID) for each row using the Qt <a href='https://doc.qt.io/qt-5/quuid.html#createUuid'>QUuid::createUuid</a> method. Each UUID is 38 characters long.",
"examples": [ { "expression":"uuid()", "returns":"'{0bd2f60f-f157-4a6d-96af-d4ba4cb366a1}'"}
]
}

0 comments on commit 43a20fe

Please sign in to comment.