.. only:: html
.. index:: Expressions
.. _vector_expressions:
************
Expressions
************
.. only:: html
.. contents::
:local:
Based on layer data and prebuilt or user defined functions, **Expressions**
offer a powerful way to manipulate attribute value, geometry and variables
in order to dynamically change the geometry style, the content or position
of the label, the value for diagram, the height of a layout item,
select some features, create virtual field ...
.. _expression_builder:
The Expression string builder
=============================
Main dialog to build expressions, the :guilabel:`Expression string builder`
is available from many parts in QGIS and, can particularly be accessed when:
* clicking the |expression| button;
* :ref:`selecting features ` with the |expressionSelect|
:sup:`Select By Expression...` tool;
* :ref:`editing attributes ` with e.g. the
|calculateField| :sup:`Field calculator` tool;
* manipulating symbology, label or layout item parameters with the |dataDefined|
:sup:`Data defined override` tool (see :ref:`data_defined`);
* building a :ref:`geometry generator ` symbol layer;
* doing some :ref:`geoprocessing `.
The Expression builder dialog offers access to the:
* :ref:`Expression tab ` which, thanks to a list of predefined
functions, helps to write and check the expression to use;
* :ref:`Function Editor tab ` which helps to extend the list of
functions by creating custom ones.
**Some use cases of expressions:**
* From Field Calculator, calculate a "pop_density" field using existing "total_pop"
and "area_km2" fields::
"total_pop" / "area_km2"
* Update the field "density_level" with categories according to the "pop_density" values::
CASE WHEN "pop_density" < 50 THEN 'Low population density'
WHEN "pop_density" >= 50 and "pop_density" < 150 THEN 'Medium population density'
WHEN "pop_density" >= 150 THEN 'High population density'
END
* Apply a categorized style to all the features according to whether their average house
price is smaller or higher than 10000€ per square metre::
"price_m2" > 10000
* Using the "Select By Expression..." tool, select all the features representing
areas of “High population density” and whose average house price is higher than
10000€ per square metre::
"density_level" = 'High population density' and "price_m2" > 10000
Likewise, the previous expression could also be used to define which features
should be labeled or shown in the map.
Using expressions offers you a lot of possibilities.
.. index:: Named parameters
single: Expressions; Named parameters
single: Functions; Named parameters
.. tip:: **Use named parameters to improve the expression reading**
Some functions require many parameters to be set. The expression engine supports the
use of named parameters. This means that instead of writing the cryptic expression
``clamp( 1, 2, 9)``, you can use ``clamp( min:=1, value:=2, max:=9)``. This also allows
arguments to be switched, e.g. ``clamp( value:=2, max:=9, min:=1)``. Using named parameters
helps clarify what the arguments for an expression function refer to, which is helpful
when you are trying to interpret an expression at a later date!
.. index:: Functions
.. _functions_list:
List of functions
=================
The :guilabel:`Expression` tab provides the main interface to write expressions
using functions, layer's fields and values. It contains following widgets:
* An expression editor area to type or paste expressions. Autocompletion is
available to speed expression writing:
* Corresponding variables, function names and field names to the input text
are shown below: use the :kbd:`Up` and :kbd:`Down` arrows to browse the
items and press :kbd:`Tab` to insert in the expression or simply click
on the wished item.
* Function parameters are shown while filling them.
QGIS also checks the expression rightness and highlights all the errors using:
* *Underline*: for unknown functions, wrong or invalid arguments;
* *Marker*: for every other error (eg, missing parenthesis, unexpected
character) at a single location.
.. tip:: **Document your expression with comments**
When using complex expression, it is good practice to add
text either as a multiline comment or inline comments to help you remember.
::
/*
Labels each region with its highest (in altitude) airport(s)
and altitude, eg 'AMBLER : 264m' for the 'Northwest Artic' region
*/
with_variable(
'airport_alti', -- stores the highest altitude of the region
aggregate(
'airports',
'max',
"ELEV", -- the field containing the altitude
-- and limit the airports to the region they are within
filter := within( $geometry, geometry( @parent ) )
),
aggregate( -- finds airports at the same altitude in the region
'airports',
'concatenate',
"NAME",
filter := within( $geometry, geometry( @parent ) )
and "ELEV" = @airport_alti
)
|| ' : ' || @airport_alti || 'm'
-- using || allows regions without airports to be skipped
)
* Under the expression editor, an :guilabel:`Output preview` displays the result
of the expression evaluated on the first feature of the layer. In case of
error, it indicates it and you can access details with the provided hyperlink.
* A function selector displays the list of functions, variables, fields...
organized in groups. A search box is available to filter the list and quickly
find a particular function or field.
Double-clicking an item adds it to the expression editor.
* A help panel displays help for each selected item in the function selector.
.. tip::
Press :kbd:`Ctrl+Click` when hovering a function name in an expression to
automatically display its help in the dialog.
* A field's values widget shown when a field is selected in the function selector
helps to fetch features attributes. Double-clicking a value adds it to the
expression editor.
.. tip::
The right panel, showing functions help or field values, can be
collapsed (invisible) in the dialog. Press the :guilabel:`Show Values`
or :guilabel:`Show Help` button to get it back.
.. _figure_expression_tab:
.. figure:: img/function_list.png
:align: center
The Expression tab
.. index:: Aggregates
.. _aggregates_function:
Aggregates Functions
--------------------
This group contains functions which aggregate values over layers and fields.
====================== =======================================================
Function Description
====================== =======================================================
aggregate Returns an aggregate value calculated using
features from another layer
array_agg Returns an array of aggregated values from a field
or expression
collect Returns the multipart geometry of aggregated
geometries from an expression
concatenate Returns the all aggregated strings from a field
or expression joined by a delimiter
count Returns the count of matching features
count_distinct Returns the count of distinct values
count_missing Returns the count of missing (null) values
iqr Returns the calculated inter quartile range from
a field or expression
majority Returns the aggregate majority of values (most
commonly occurring value) from a field or expression
max_length Returns the maximum length of strings from a field
or expression
maximum Returns the aggregate maximum value from a field
or expression
mean Returns the aggregate mean value from a field
or expression
median Returns the aggregate median value from a field
or expression
min_length Returns the minimum length of strings from a field
or expression
minimum Returns the aggregate minimum value from a field
or expression
minority Returns the aggregate minority of values (least
commonly occurring value) from a field or expression
q1 Returns the calculated first quartile from a field
or expression
q3 Returns the calculated third quartile from a field
or expression
range Returns the aggregate range of values (maximum -
minimum) from a field or expression
relation_aggregate Returns an aggregate value calculated using all
matching child features from a layer relation
stdev Returns the aggregate standard deviation value
from a field or expression
sum Returns the aggregate summed value from a field
or expression
====================== =======================================================
|
**Examples:**
* Return the maximum of the "passengers" field from features in the layer
grouped by "station_class" field::
maximum("passengers", group_by:="station_class")
* Calculate the total number of passengers for the stations inside the current
atlas feature::
aggregate('rail_stations','sum',"passengers",
intersects(@atlas_geometry, $geometry))
* Return the mean of the "field_from_related_table" field for all matching
child features using the 'my_relation' relation from the layer::
relation_aggregate('my_relation', 'mean', "field_from_related_table")
or::
relation_aggregate(relation:='my_relation', aggregate := 'mean',
expression := "field_from_related_table")
.. index:: Array, List data structure
.. _array_functions:
Array Functions
---------------
This group contains functions to create and manipulate arrays (also known as
list data structures). The order of values within the array matters, unlike the
:ref:`'map' data structure `, where the order of key-value pairs
is irrelevant and values are identified by their keys.
====================== =======================================================
Function Description
====================== =======================================================
array Returns an array containing all the values passed
as parameter
array_append Returns an array with the given value added at the end
array_cat Returns an array containing all the given arrays concatenated
array_contains Returns true if an array contains the given value
array_distinct Returns an array containing distinct values of the given array
array_filter Returns an array with only the items for which an expression
evaluates to true
array_find Returns the index (0 for the first one) of a value
within an array. Returns -1 if the value is not found.
array_first Returns the first value of an array
array_foreach Returns an array with the given expression evaluated on each item
array_get Returns the Nth value (0 for the first one) of an array
array_insert Returns an array with the given value added at the
given position
array_intersect Returns true if any element of array_1 exists in array_2
array_last Returns the last element of an array
array_length Returns the number of elements of an array
array_prepend Returns an array with the given value added at the beginning
array_remove_all Returns an array with all the entries of the given
value removed
array_remove_at Returns an array with the given index removed
array_reverse Returns the given array with array values in reversed order
array_slice Returns the values of the array from the start_pos argument up
to and including the end_pos argument
array_to_string Concatenates array elements into a string separated by
a delimiter and using optional string for empty values
generate_series Creates an array containing a sequence of numbers
regexp_matches Returns an array of all strings captured by capturing
groups, in the order the groups themselves appear in
the supplied regular expression against a string
string_to_array Splits string into an array using supplied delimiter
and optional string for empty values
====================== =======================================================
Color Functions
----------------
This group contains functions for manipulating colors.
============================== ==========================================================
Function Description
============================== ==========================================================
color_cmyk Returns a string representation of a color based on
its cyan, magenta, yellow and black components
color_cmyka Returns a string representation of a color based on
its cyan, magenta, yellow, black and alpha (transparency)
components
color_grayscale_average Applies a grayscale filter and returns a string
representation from a provided color
color_hsl Returns a string representation of a color based on
its hue, saturation, and lightness attributes
color_hsla Returns a string representation of a color based on its
hue, saturation, lightness and alpha (transparency)
attributes
color_hsv Returns a string representation of a color based on
its hue, saturation, and value attributes
color_hsva Returns a string representation of a color based on
its hue, saturation, value and alpha (transparency)
attributes
color_mix_rgb Returns a string representing a color mixing the red,
green, blue, and alpha values of two provided colors
based on a given ratio
color_part Returns a specific component from a color string,
eg the red component or alpha component
color_rgb Returns a string representation of a color based on
its red, green, and blue components
color_rgba Returns a string representation of a color based on
its red, green, blue, and alpha (transparency) components
create_ramp Returns a gradient ramp from a map of color strings and steps
darker Returns a darker (or lighter) color string
lighter Returns a lighter (or darker) color string
project_color Returns a color from the project's color scheme
ramp_color Returns a string representing a color from a color ramp
set_color_part Sets a specific color component for a color string,
eg the red component or alpha component
============================== ==========================================================
Conditional Functions
---------------------
This group contains functions to handle conditional checks in expressions.
===================================== =========================================
Function Description
===================================== =========================================
CASE WHEN ... THEN ... END Evaluates an expression and returns a
result if true. You can test multiple
conditions
CASE WHEN ... THEN ... ELSE ... END Evaluates an expression and returns a
different result whether it's true or
false. You can test multiple conditions
coalesce Returns the first non-NULL value from
the expression list
if Tests a condition and returns a
different result depending on the
conditional check
===================================== =========================================
|
**Some example:**
* Send back a value if the first condition is true, else another value::
CASE WHEN "software" LIKE '%QGIS%' THEN 'QGIS' ELSE 'Other' END
.. _conversion_functions:
Conversions Functions
---------------------
This group contains functions to convert one data type to another
(e.g., string to integer, integer to string).
================== ========================================================
Function Description
================== ========================================================
to_date Converts a string into a date object
to_datetime Converts a string into a datetime object
to_dm Converts a coordinate to degree, minute
to_dms Converts coordinate to degree, minute, second
to_int Converts a string to integer number
to_interval Converts a string to an interval type (can be used
to take days, hours, months, etc. of a date)
to_real Converts a string to a real number
to_string Converts number to string
to_time Converts a string into a time object
================== ========================================================
Custom Functions
-----------------
This group contains functions created by the user.
See :ref:`function_editor` for more details.
Date and Time Functions
------------------------
This group contains functions for handling date and time data.
============== ==============================================================
Function Description
============== ==============================================================
age Returns as an interval the difference between two dates
or datetimes
day Extracts the day from a date or datetime, or the number
of days from an interval
day_of_week Returns a number corresponding to the day of the week
for a specified date or datetime
epoch Returns the interval in milliseconds between the unix
epoch and a given date value
hour Extracts the hour from a datetime or time,
or the number of hours from an interval
minute Extracts the minute from a datetime or time,
or the number of minutes from an interval
month Extracts the month part from a date or datetime, or the
number of months from an interval
now Returns current date and time
second Extracts the second from a datetime or time,
or the number of seconds from an interval
week Extracts the week number from a date or datetime,
or the number of weeks from an interval
year Extracts the year part from a date or datetime,
or the number of years from an interval
============== ==============================================================
|
This group also shares several functions with the :ref:`conversion_functions` (
to_date, to_time, to_datetime, to_interval) and :ref:`string_functions`
(format_date) groups.
**Some examples:**
* Get today's month and year in the "month_number/year" format:
.. code-block:: sql
format_date(now(),'MM/yyyy')
-- Returns '03/2017'
Besides these functions, subtracting dates, datetimes or times using the
``-`` (minus) operator will return an interval.
Adding or subtracting an interval to dates, datetimes or times, using the
``+`` (plus) and ``-`` (minus) operators, will return a datetime.
* Get the number of days until QGIS 3.0 release:
.. code-block:: sql
to_date('2017-09-29') - to_date(now())
-- Returns
* The same with time:
.. code-block:: sql
to_datetime('2017-09-29 12:00:00') - to_datetime(now())
-- Returns
* Get the datetime of 100 days from now:
.. code-block:: sql
now() + to_interval('100 days')
-- Returns
.. note:: **Storing date and datetime and intervals on fields**
The ability to store *date*, *time* and *datetime* values directly on
fields may depend on the data source's provider (e.g., Shapefile accepts
*date* format, but not *datetime* or *time* format). The following are some
suggestions to overcome this limitation:
* *date*, *Datetime* and *time* can be stored in text type fields after
using the ``to_format()`` function.
* *Intervals* can be stored in integer or decimal type fields after using
one of the date extraction functions (e.g., ``day()`` to get the interval
expressed in days)
.. _fields_values:
Fields and Values
------------------
Contains a list of fields from the layer.
Double-click a field name to have it added to your expression. You can also
type the field name (preferably inside double quotes) or its :ref:`alias
`.
To retrieve fields values to use in an expression, select the appropriate field
and, in the shown widget, choose between :guilabel:`10 Samples` and :guilabel:`All
Unique`. Requested values are then displayed and you can use the :guilabel:`Search`
box at the top of the list to filter the result.
Sample values can also be accessed via right-clicking on a field.
To add a value to the expression you are writing, double-click on it in the list.
If the value is of a string type, it should be simple quoted, otherwise no quote
is needed.
Fuzzy Matching Functions
-------------------------
This group contains functions for fuzzy comparisons between values.
=========================== =================================================
Function Description
=========================== =================================================
hamming_distance Returns the number of characters at
corresponding positions within the input
strings where the characters are different
levensheim Returns the minimum number of character edits
(insertions, deletions or substitutions)
required to change one string to another.
Measure the similarity between two strings
longest_common_substring Returns the longest common substring between
two strings
soundex Returns the Soundex representation of a string
=========================== =================================================
General Functions
------------------
This group contains general assorted functions.
==================== =======================================================
Function Description
==================== =======================================================
env Gets an environment variable and returns its content
as a string. If the variable is not found, ``NULL``
will be returned.
eval Evaluates an expression which is passed in a string.
Useful to expand dynamic parameters passed as context
variables or fields
is_layer_visible Returns true if a specified layer is visible
layer_property Returns a property of a layer or a value of its
metadata. It can be layer name, crs, geometry type,
feature count...
var Returns the value stored within a specified
variable. See variable functions below
with_variable Creates and sets a variable for any expression code
that will be provided as a third argument. Useful to
avoid repetition in expressions where the same value
needs to be used more than once.
==================== =======================================================
.. _geometry_functions:
Geometry Functions
------------------
This group contains functions that operate on geometry objects (e.g., length, area).
+------------------------+---------------------------------------------------+
| Function | Description |
+========================+===================================================+
| $area | Returns the area size of the current feature |
+------------------------+---------------------------------------------------+
| $geometry | Returns the geometry of the current feature (can |
| | be used for processing with other functions) |
+------------------------+---------------------------------------------------+
| $length | Returns the length of the current line feature |
+------------------------+---------------------------------------------------+
| $perimeter | Returns the perimeter of the current polygon |
| | feature |
+------------------------+---------------------------------------------------+
| $x | Returns the X coordinate of the current feature |
+------------------------+---------------------------------------------------+
| $x_at(n) | Returns the X coordinate of the nth node of the |
| | current feature's geometry |
+------------------------+---------------------------------------------------+
| $y | Returns the Y coordinate of the current feature |
+------------------------+---------------------------------------------------+
| $y_at(n) | Returns the Y coordinate of the nth node of the |
| | current feature's geometry |
+------------------------+---------------------------------------------------+
| angle_at_vertex | Returns the bisector angle (average angle) to the |
| | geometry for a specified vertex on a linestring |
| | geometry. Angles are in degrees clockwise from |
| | north |
+------------------------+---------------------------------------------------+
| area | Returns the area of a geometry polygon feature. |
| | Calculations are in the Spatial Reference System |
| | of this geometry |
+------------------------+---------------------------------------------------+
| azimuth | Returns the north-based azimuth as the angle in |
| | radians measured clockwise from the vertical on |
| | point_a to point_b |
+------------------------+---------------------------------------------------+
| boundary | Returns the closure of the combinatorial boundary |
| | of the geometry (ie the topological boundary of |
| | the geometry - see also :ref:`qgisboundary`). |
+------------------------+---------------------------------------------------+
| bounds | Returns a geometry which represents the bounding |
| | box of an input geometry. Calculations are in |
| | the Spatial Reference System of this geometry |
| | (see also :ref:`qgisboundingboxes`) |
+------------------------+---------------------------------------------------+
| bounds_height | Returns the height of the bounding box of a |
| | geometry. Calculations are in the Spatial |
| | Reference System of this geometry |
+------------------------+---------------------------------------------------+
| bounds_width | Returns the width of the bounding box of a |
| | geometry. Calculations are in the Spatial |
| | Reference System of this geometry |
+------------------------+---------------------------------------------------+
| buffer | Returns a geometry that represents all points |
| | whose distance from this geometry is less than |
| | or equal to distance. Calculations are in the |
| | Spatial Reference System of this geometry |
| | (see also :ref:`qgisbuffer`) |
+------------------------+---------------------------------------------------+
| buffer_by_m | Creates a buffer along a line geometry where the |
| | buffer diameter varies according to the M values |
| | at the line vertices |
| | (see also :ref:`qgisbufferbym`) |
+------------------------+---------------------------------------------------+
| centroid | Returns the geometric center of a geometry |
| | (see also :ref:`qgiscentroids`) |
+------------------------+---------------------------------------------------+
| closest_point | Returns the point on a geometry that is closest |
| | to a second geometry |
+------------------------+---------------------------------------------------+
| combine | Returns the combination of two geometries |
+------------------------+---------------------------------------------------+
| contains(a,b) | Returns 1 (true) if and only if no points of b |
| | lie in the exterior of a, and at least one point |
| | of the interior of b lies in the interior of a |
+------------------------+---------------------------------------------------+
| convex_hull | Returns the convex hull of a geometry (this |
| | represents the minimum convex geometry that |
| | encloses all geometries within the set) |
| | (see also :ref:`qgisconvexhull`) |
+------------------------+---------------------------------------------------+
| crosses | Returns 1 (true) if the supplied geometries have |
| | some, but not all, interior points in common |
+------------------------+---------------------------------------------------+
| difference(a,b) | Returns a geometry that represents that part of |
| | geometry a that does not intersect with geometry b|
| | (see also :ref:`qgisdifference`) |
+------------------------+---------------------------------------------------+
| disjoint | Returns 1 (true) if the geometries do not share |
| | any space together |
+------------------------+---------------------------------------------------+
| distance | Returns the minimum distance (based on Spatial |
| | Reference System) between two geometries in |
| | projected units |
+------------------------+---------------------------------------------------+
| distance_to_vertex | Returns the distance along the geometry to a |
| | specified vertex |
+------------------------+---------------------------------------------------+
| end_point | Returns the last node from a geometry |
| | (see also :ref:`qgisextractspecificvertices`) |
+------------------------+---------------------------------------------------+
| extend | Extends the start and end of a linestring |
| | geometry by a specified amount |
| | (see also :ref:`qgisextendlines`) |
+------------------------+---------------------------------------------------+
| exterior_ring | Returns a line string representing the exterior |
| | ring of a polygon geometry, |
| | or null if the geometry is not a polygon |
+------------------------+---------------------------------------------------+
| extrude(geom,x,y) | Returns an extruded version of the input (Multi-) |
| | Curve or (Multi-)Linestring geometry with an |
| | extension specified by X and Y |
+------------------------+---------------------------------------------------+
| flip_coordinates | Returns a copy of the geometry with the X and Y |
| | coordinates swapped (see also :ref:`qgisswapxy`) |
+------------------------+---------------------------------------------------+
| geom_from_gml | Returns a geometry created from a GML |
| | representation of geometry |
+------------------------+---------------------------------------------------+
| geom_from_wkt | Returns a geometry created from a well-known text |
| | (WKT) representation |
+------------------------+---------------------------------------------------+
| geom_to_wkt | Returns the well-known text (WKT) representation |
| | of the geometry without SRID metadata |
+------------------------+---------------------------------------------------+
| geometry | Returns a feature's geometry |
+------------------------+---------------------------------------------------+
| geometry_n | Returns the nth geometry from a geometry |
| | collection, or null if the input geometry |
| | is not a collection |
+------------------------+---------------------------------------------------+
| hausdorff_distance | Returns basically a measure of how similar or |
| | dissimilar 2 geometries are, with a lower |
| | distance indicating more similar geometries |
+------------------------+---------------------------------------------------+
| inclination | Returns the inclination measured from the zenith |
| | (0) to the nadir (180) on point_a to point_b |
+------------------------+---------------------------------------------------+
| interior_ring_n | Returns the geometry of the nth interior ring |
| | from a polygon geometry, or null if the geometry |
| | is not a polygon |
+------------------------+---------------------------------------------------+
| intersection | Returns a geometry that represents the shared |
| | portion of two geometries |
| | (see also :ref:`qgisintersection`) |
+------------------------+---------------------------------------------------+
| intersects | Tests whether a geometry intersects another. |
| | Returns 1 (true) if the geometries spatially |
| | intersect (share any portion of space) |
| | and 0 if they don't |
+------------------------+---------------------------------------------------+
| intersects_bbox | Tests whether a geometry's bounding box overlaps |
| | another geometry's bounding box. Returns 1 (true) |
| | if the geometries spatially intersect (share any |
| | portion of space) their bounding box, |
| | or 0 if they don't |
+------------------------+---------------------------------------------------+
| is_closed | Returns true if a line string is closed |
| | (start and end points are coincident), false if |
| | a line string is not closed, or null if the |
| | geometry is not a line string |
+------------------------+---------------------------------------------------+
| length | Returns length of a line geometry feature |
| | (or length of a string) |
+------------------------+---------------------------------------------------+
| line_interpolate_angle | Returns the angle parallel to the geometry at a |
| | specified distance along a linestring geometry. |
| | Angles are in degrees clockwise from north. |
+------------------------+---------------------------------------------------+
| line_interpolate_point | Returns the point interpolated by a specified |
| | distance along a linestring geometry. |
| | (see also :ref:`qgisinterpolatepoint`) |
+------------------------+---------------------------------------------------+
| line_locate_point | Returns the distance along a linestring |
| | corresponding to the closest position the |
| | linestring comes to a specified point geometry. |
+------------------------+---------------------------------------------------+
| line_substring | Returns the portion of a line or curve geometry |
| | falling betweeen specified start and end distances|
| | (measured from the beginning of the line) |
| | (see also :ref:`qgislinesubstring`) |
+------------------------+---------------------------------------------------+
| line_merge | Returns a (Multi-)LineString geometry, where any |
| | connected LineStrings from the input geometry |
| | have been merged into a single linestring. |
+------------------------+---------------------------------------------------+
| m | Returns the M value of a point geometry |
+------------------------+---------------------------------------------------+
| make_circle | Creates a circular geometry based on center point |
| | and radius |
+------------------------+---------------------------------------------------+
| make_ellipse | Creates an elliptical geometry based on center |
| | point, axes and azimuth |
+------------------------+---------------------------------------------------+
| make_line | Creates a line geometry from a series of point |
| | geometries |
+------------------------+---------------------------------------------------+
| make_point(x,y,z,m) | Returns a point geometry from X and Y (and |
| | optional Z or M) values |
+------------------------+---------------------------------------------------+
| make_point_m(x,y,m) | Returns a point geometry from X and Y coordinates |
| | and M values |
+------------------------+---------------------------------------------------+
| make_polygon | Creates a polygon geometry from an outer ring and |
| | optional series of inner ring geometries |
+------------------------+---------------------------------------------------+
| make_regular_polygon | Creates a regular polygon |
+------------------------+---------------------------------------------------+
| make_triangle | Creates a triangle polygon |
+------------------------+---------------------------------------------------+
| minimal_circle | Returns the minimal enclosing circle of an input |
| | geometry (see also |
| | :ref:`qgisminimumenclosingcircle`) |
+------------------------+---------------------------------------------------+
| nodes_to_points | Returns a multipoint geometry consisting of every |
| | node in the input geometry |
| | (see also :ref:`qgisextractvertices`) |
+------------------------+---------------------------------------------------+
| num_geometries | Returns the number of geometries in a geometry |
| | collection, or null if the input geometry is not |
| | a collection |
+------------------------+---------------------------------------------------+
| num_interior_rings | Returns the number of interior rings in a polygon |
| | or geometry collection, or null if the input |
| | geometry is not a polygon or collection |
+------------------------+---------------------------------------------------+
| num_points | Returns the number of vertices in a geometry |
+------------------------+---------------------------------------------------+
| num_rings | Returns the number of rings (including exterior |
| | rings) in a polygon or geometry collection, or |
| | null if the input geometry is not a polygon or |
| | collection |
+------------------------+---------------------------------------------------+
| offset_curve | Returns a geometry formed by offsetting a |
| | linestring geometry to the side. Distances are in |
| | the Spatial Reference System of this geometry. |
| | (see also :ref:`qgisoffsetline`) |
+------------------------+---------------------------------------------------+
| order_parts | Orders the parts of a MultiGeometry by a given |
| | criteria |
+------------------------+---------------------------------------------------+
| oriented_bbox | Returns a geometry representing the minimal |
| | oriented bounding box of an input geometry |
| | (see also :ref:`qgisorientedminimumboundingbox`) |
+------------------------+---------------------------------------------------+
| overlaps | Tests whether a geometry overlaps another. |
| | Returns 1 (true) if the geometries share space, |
| | are of the same dimension, but are not completely |
| | contained by each other |
+------------------------+---------------------------------------------------+
| perimeter | Returns the perimeter of a geometry polygon |
| | feature. Calculations are in the Spatial |
| | Reference System of this geometry |
+------------------------+---------------------------------------------------+
| point_n | Returns a specific node from a geometry |
| | (see also :ref:`qgisextractspecificvertices`) |
+------------------------+---------------------------------------------------+
| point_on_surface | Returns a point guaranteed to lie on the surface |
| | of a geometry (see also :ref:`qgispointonsurface`)|
+------------------------+---------------------------------------------------+
| pole_of_inaccessibility| Calculates the approximate pole of inaccessibility|
| | for a surface, which is the most distant internal |
| | point from the boundary of the surface (see also |
| | :ref:`qgispoleofinaccessibility`) |
+------------------------+---------------------------------------------------+
| project | Returns a point projected from a start point |
| | using a distance and bearing (azimuth) in radians |
| | (see also :ref:`qgisprojectpointcartesian`) |
+------------------------+---------------------------------------------------+
| relate | Tests or returns the Dimensional Extended 9 |
| | Intersection Model (DE-9IM) representation of the |
| | relationship between two geometries |
+------------------------+---------------------------------------------------+
| reverse | Reverses the direction of a line string by |
| | reversing the order of its vertices |
| | (see also :ref:`qgisreverselinedirection`) |
+------------------------+---------------------------------------------------+
| segments_to_lines | Returns a multi line geometry consisting of a |
| | line for every segment in the input geometry |
| | (see also :ref:`qgisexplodelines`) |
+------------------------+---------------------------------------------------+
| shortest_line | Returns the shortest line joining two geometries. |
| | The resultant line will start at geometry 1 and |
| | end at geometry 2 |
+------------------------+---------------------------------------------------+
| simplify | Simplifies a geometry by removing nodes using a |
| | distance based threshold |
| | (see also :ref:`qgissimplifygeometries`) |
+------------------------+---------------------------------------------------+
| simplify_vw | Simplifies a geometry by removing nodes using an |
| | area based threshold |
| | (see also :ref:`qgissimplifygeometries`) |
+------------------------+---------------------------------------------------+
| single_sided_buffer | Returns a geometry formed by buffering out just |
| | one side of a linestring geometry. Distances are |
| | in the Spatial Reference System of this geometry |
| | (see also :ref:`qgissinglesidedbuffer`) |
+------------------------+---------------------------------------------------+
| smooth | Smooths a geometry by adding extra nodes which |
| | round off corners in the geometry |
| | (see also :ref:`qgissmoothgeometry`) |
+------------------------+---------------------------------------------------+
| start_point | Returns the first node from a geometry |
| | (see also :ref:`qgisextractspecificvertices`) |
+------------------------+---------------------------------------------------+
| sym_difference | Returns a geometry that represents the portions |
| | of two geometries that do not intersect |
| | (see also :ref:`qgissymmetricaldifference`) |
+------------------------+---------------------------------------------------+
| tapered_buffer | Creates a buffer along a line geometry where the |
| | buffer diameter varies evenly over the length of |
| | the line (see also :ref:`qgistaperedbuffer`) |
+------------------------+---------------------------------------------------+
| touches | Tests whether a geometry touches another. |
| | Returns 1 (true) if the geometries have at least |
| | one point in common, but their interiors do not |
| | intersect |
+------------------------+---------------------------------------------------+
| transform | Returns the geometry transformed from the source |
| | CRS to the destination CRS |
| | (see also :ref:`qgisreprojectlayer`) |
+------------------------+---------------------------------------------------+
| translate | Returns a translated version of a geometry. |
| | Calculations are in the Spatial Reference System |
| | of the geometry |
| | (see also :ref:`qgistranslategeometry`) |
+------------------------+---------------------------------------------------+
| union | Returns a geometry that represents the point set |
| | union of the geometries |
+------------------------+---------------------------------------------------+
| wedge_buffer | Returns a wedge shaped buffer originating from a |
| | point geometry given an angle and radii |
| | (see also :ref:`qgiswedgebuffers`) |
+------------------------+---------------------------------------------------+
| within (a,b) | Tests whether a geometry is within another. |
| | Returns 1 (true) if geometry a is completely |
| | inside geometry b |
+------------------------+---------------------------------------------------+
| x | Returns the X coordinate of a point geometry, or |
| | the X coordinate of the centroid for a non-point |
| | geometry |
+------------------------+---------------------------------------------------+
| x_min | Returns the minimum X coordinate of a geometry. |
| | Calculations are in the Spatial Reference System |
| | of this geometry |
+------------------------+---------------------------------------------------+
| x_max | Returns the maximum X coordinate of a geometry. |
| | Calculations are in the Spatial Reference System |
| | of this geometry |
+------------------------+---------------------------------------------------+
| y | Returns the Y coordinate of a point geometry, or |
| | the Y coordinate of the centroid for a non-point |
| | geometry |
+------------------------+---------------------------------------------------+
| y_min | Returns the minimum Y coordinate of a geometry. |
| | Calculations are in the Spatial Reference System |
| | of this geometry |
+------------------------+---------------------------------------------------+
| y_max | Returns the maximum Y coordinate of a geometry. |
| | Calculations are in the Spatial Reference System |
| | of this geometry |
+------------------------+---------------------------------------------------+
| z | Returns the Z coordinate of a point geometry |
+------------------------+---------------------------------------------------+
|
**Some examples:**
* You can manipulate the current geometry with the variable $geometry to create
a buffer or get the point on surface::
buffer( $geometry, 10 )
point_on_surface( $geometry )
* Return the X coordinate of the current feature's centroid::
x( $geometry )
* Send back a value according to feature's area::
CASE WHEN $area > 10 000 THEN 'Larger' ELSE 'Smaller' END
Layout Functions
----------------
This group contains functions to manipulate print layout items properties.
================== ========================================================
Function Description
================== ========================================================
item_variables Returns a map of variables from a layout item inside
this print layout
================== ========================================================
|
**Some example:**
* Get the scale of the 'Map 0' in the current print layout::
map_get( item_variables('Map 0'), 'map_scale')
Map Layers
----------
This group contains a list of the available layers in the current project.
This offers a convenient way to write expressions referring to multiple layers,
such as when performing :ref:`aggregates `, :ref:`attribute
` or :ref:`spatial ` queries.
.. index:: Map data structure, Dictionary, Key-value pairs, Associative arrays
.. _maps_functions:
Maps Functions
--------------
This group contains functions to create or manipulate keys and values of map
data structures (also known as dictionary objects, key-value pairs, or associative
arrays). Unlike the :ref:`list data structure ` where values
order matters, the order of the key-value pairs in the map object is not relevant
and values are identified by their keys.
==================== =========================================================
Function Description
==================== =========================================================
hstore_to_map Creates a map from a hstore-formatted string
json_to_map Creates a map from a json-formatted string
map Returns a map containing all the keys and values passed
as pair of parameters
map_akeys Returns all the keys of a map as an array
map_avals Returns all the values of a map as an array
map_concat Returns a map containing all the entries of the given
maps. If two maps contain the same key, the value of
the second map is taken.
map_delete Returns a map with the given key and its corresponding
value deleted
map_exist Returns true if the given key exists in the map
map_get Returns the value of a map, given it's key
map_insert Returns a map with an added key/value
map_to_hstore Merges map elements into a hstore-formatted string
map_to_json Merges map elements into a json-formatted string
==================== =========================================================
Mathematical Functions
-----------------------
This group contains math functions (e.g., square root, sin and cos).
================= ==========================================================
Function Description
================= ==========================================================
abs Returns the absolute value of a number
acos Returns the inverse cosine of a value in radians
asin Returns the inverse sine of a value in radians
atan Returns the inverse tangent of a value in radians
atan2(y,x) Returns the inverse tangent of Y/X by using the signs
of the two arguments to determine the quadrant of the
result
azimuth(a,b) Returns the north-based azimuth as the angle in radians
measured clockwise from the vertical on point a
to point b
ceil Rounds a number upwards
clamp Restricts an input value to a specified range
cos Returns the cosine of a value in radians
degrees Converts from radians to degrees
exp Returns exponential of a value
floor Rounds a number downwards
inclination Returns the inclination measured from the zenith (0) to
the nadir (180) on point_a to point_b.
ln Returns the natural logarithm of the passed expression
log Returns the value of the logarithm of the passed
value and base
log10 Returns the value of the base 10 logarithm of the
passed expression
max Returns the largest not null value in a set of values
min Returns the smallest not null value in a set of values
pi Returns the value of pi for calculations
radians Converts from degrees to radians
rand Returns the random integer within the range specified
by the minimum and maximum argument (inclusive)
randf Returns the random float within the range specified
by the minimum and maximum argument (inclusive)
round Rounds to number of decimal places
scale_exp Transforms a given value from an input domain
to an output range using an exponential curve
scale_linear Transforms a given value from an input domain
to an output range using linear interpolation
sin Returns the sine of an angle
sqrt Returns the square root of a value
tan Returns the tangent of an angle
================= ==========================================================
Operators
----------
This group contains operators (e.g., +, -, \*).
Note that for most of the mathematical functions below,
if one of the inputs is NULL then the result is NULL.
=========================== ===================================================
Function Description
=========================== ===================================================
a + b Addition of two values (a plus b)
a - b Subtraction of two values (a minus b).
a * b Multiplication of two values (a multiplied by b)
a / b Division of two values (a divided by b)
a % b Remainder of division of a by b
(eg, 7 % 2 = 1, or 2 fits into 7 three times
with remainder 1)
a ^ b Power of two values (for example, 2^2=4 or 2^3=8)
a < b Compares two values and evaluates to 1 if the
left value is less than the right value
(a is smaller than b)
a <= b Compares two values and evaluates to 1 if the
left value isless than or equal to the right
value
a <> b Compares two values and evaluates to 1
if they are not equal
a = b Compares two values and evaluates to 1
if they are equal
a != b a and b are not equal
a > b Compares two values and evaluates to 1
if the left value is greater than the right
value (a is larger than b)
a >= b Compares two values and evaluates to 1
if the left value is greater than or equal to
the right value
a ~ b a matches the regular expression b
|| Joins two values together into a string.
If one of the values is NULL the result will
be NULL
'\\n' Inserts a new line in a string
LIKE Returns 1 if the first parameter matches the
supplied pattern
ILIKE Returns 1 if the first parameter matches
case-insensitive the supplied pattern (ILIKE
can be used instead of LIKE to make the match
case-insensitive)
a IS b Tests whether two values are identical.
Returns 1 if a is the same as b
a OR b Returns 1 when condition a or condition b is true
a AND b Returns 1 when conditions a and b are true
NOT Negates a condition
column name "column name" Value of the field column name, take care to
not be confused with simple quote, see below
'string' a string value, take care to not be confused
with double quote, see above
NULL null value
a IS NULL a has no value
a IS NOT NULL a has a value
a IN (value[,value]) a is below the values listed
a NOT IN (value[,value]) a is not below the values listed
=========================== ===================================================
|
.. note:: **About fields concatenation**
You can concatenate strings using either `||` or ``+``. The latter also means
sum up expression. So if you have an integer (field or numeric value) this can
be error prone. In this case, you should use `||`. If you concatenate two
string values, you can use both.
**Some examples:**
* Joins a string and a value from a column name::
'My feature''s id is: ' || "gid"
'My feature''s id is: ' + "gid" => triggers an error as gid is an integer
"country_name" + '(' + "country_code" + ')'
"country_name" || '(' || "country_code" || ')'
* Test if the "description" attribute field starts with the 'Hello' string
in the value (note the position of the % character)::
"description" LIKE 'Hello%'
.. _raster_functions:
Rasters Functions
-----------------
This group contains functions to operate on raster layer.
==================== =========================================================
Function Description
==================== =========================================================
raster_statistic Returns statistics from a raster layer
raster_value Returns the raster band value at the provided point
==================== =========================================================
.. _record_attributes:
Record and Attributes Functions
-------------------------------
This group contains functions that operate on record identifiers.
==================== =========================================================
Function Description
==================== =========================================================
$currentfeature Returns the current feature being evaluated.
This can be used with the 'attribute' function
to evaluate attribute values from the current feature.
$id Returns the feature id of the current row
attribute Returns the value of a specified attribute from a
feature
get_feature Returns the first feature of a layer matching a
given attribute value
get_feature_by_id Returns the feature of a layer matching the given
feature ID
is_selected Returns if a feature is selected
num_selected Returns the number of selected features on a given layer
represent_value Returns the configured representation value for a
field value (convenient with some :ref:`widget types
`)
uuid Generates a Universally Unique Identifier (UUID)
for each row. Each UUID is 38 characters long.
==================== =========================================================
|
**Some examples:**
* Return the first feature in layer "LayerA" whose field "id" has the same value
as the field "name" of the current feature (a kind of jointure)::
get_feature( 'layerA', 'id', attribute( $currentfeature, 'name') )
* Calculate the area of the joined feature from the previous example::
area( geometry( get_feature( 'layerA', 'id', attribute( $currentfeature, 'name') ) ) )
.. _string_functions:
String Functions
-----------------
This group contains functions that operate on strings
(e.g., that replace, convert to upper case).
===================== ======================================================
Function Description
===================== ======================================================
char Returns the character associated with a unicode code
concat Concatenates several strings to one
format Formats a string using supplied arguments
format_date Formats a date type or string into a custom
string format
format_number Returns a number formatted with the locale
separator for thousands (also truncates the
number to the number of supplied places)
left(string, n) Returns a substring that contains the n
leftmost characters of the string
length Returns length of a string
(or length of a line geometry feature)
lower converts a string to lower case
lpad Returns a string padded on the left to the specified
width, using the fill character
regexp_match Returns the first matching position matching a regular
expression within a string, or 0 if the substring is
not found
regexp_replace Returns a string with the supplied regular
expression replaced
regexp_substr Returns the portion of a string which matches
a supplied regular expression
replace Returns a string with the supplied string, array, or
map of strings replaced by a string, an array of strings
or paired values
right(string, n) Returns a substring that contains the n
rightmost characters of the string
rpad Returns a string padded on the right to the specified
width, using the fill character
strpos Returns the first matching position of a substring within
another string, or 0 if the substring is not found
substr Returns a part of a string
title Converts all words of a string to title
case (all words lower case with leading
capital letter)
trim Removes all leading and trailing white
space (spaces, tabs, etc.) from a string
upper Converts string a to upper case
wordwrap Returns a string wrapped to a maximum/
minimum number of characters
===================== ======================================================
.. _variables_functions:
Variables Functions
--------------------
This group contains dynamic variables related to the application, the project
file and other settings.
It means that some functions may not be available according to the context:
- from the |expressionSelect| :sup:`Select by expression` dialog
- from the |calculateField| :sup:`Field calculator` dialog
- from the layer properties dialog
- from the print layout
To use these functions in an expression, they should be preceded by @ character
(e.g, @row_number). Are concerned:
============================ =======================================================
Function Description
============================ =======================================================
algorithm_id Returns the unique ID of an algorithm
atlas_feature Returns the current atlas feature (as feature object)
atlas_featureid Returns the current atlas feature ID
atlas_featurenumber Returns the current atlas feature number in the layout
atlas_filename Returns the current atlas file name
atlas_geometry Returns the current atlas feature geometry
atlas_layerid Returns the current atlas coverage layer ID
atlas_layername Returns the current atlas coverage layer name
atlas_pagename Returns the current atlas page name
atlas_totalfeatures Returns the total number of features in atlas
canvas_cursor_point Returns the last cursor position on the canvas in the
project's geographical coordinates
cluster_color Returns the color of symbols within a cluster, or NULL
if symbols have mixed colors
cluster_size Returns the number of symbols contained within a cluster
current_feature Returns the feature currently being edited in the
attribute form or table row
current_geometry Returns the geometry of the feature currently being edited
in the form or the table row
geometry_part_count Returns the number of parts in rendered feature's geometry
geometry_part_num Returns the current geometry part number for feature being rendered
geometry_point_count Returns the number of points in the rendered geometry's part
geometry_point_num Returns the current point number in the rendered geometry's part
grid_axis Returns the current grid annotation axis
(eg, 'x' for longitude, 'y' for latitude)
grid_number Returns the current grid annotation value
item_id Returns the layout item user ID
(not necessarily unique)
item_uuid Returns the layout item unique ID
layer Returns the current layer
layer_id Returns the ID of current layer
layer_name Returns the name of current layer
layout_dpi Returns the composition resolution (DPI)
layout_name Returns the layout name
layout_numpages Returns the number of pages in the layout
layout_page Returns the page number of the current item in the layout
layout_pageheight Returns the active page height in the layout (in mm)
layout_pagewidth Returns the active page width in the layout (in mm)
map_crs Returns the Coordinate reference system of the current map
map_crs_definition Returns the full definition of the Coordinate reference
system of the current map
map_extent Returns the geometry representing the current extent of the map
map_extent_center Returns the point feature at the center of the map
map_extent_height Returns the current height of the map
map_extent_width Returns the current width of the map
map_id Returns the ID of current map destination.
This will be 'canvas' for canvas renders, and
the item ID for layout map renders
map_layer_ids Returns the list of map layer IDs visible in the map
map_layers Returns the list of map layers visible in the map
map_rotation Returns the current rotation of the map
map_scale Returns the current scale of the map
map_units Returns the units of map measurements
notification_message Content of the notification message sent by the provider
(available only for actions triggered by provider notifications).
parent Refers to the current feature in the parent layer,
providing access to its attributes and geometry when filtering
an :ref:`aggregate ` function
project_abstract Returns the project abstract, taken from project metadata
project_author Returns the project author, taken from project metadata
project_basename Returns the basename of current project's filename (without
path and extension)
project_creation_date Returns the project creation date, taken from project metadata
project_crs Returns the Coordinate reference system of the project
project_crs_definition Returns the full definition of the Coordinate reference
system of the project
project_filename Returns the filename of the current project
project_folder Returns the folder of the current project
project_home Returns the home path of the current project
project_identifier Returns the project identifier, taken from the project's metadata
project_keywords Returns the project keywords, taken from the project's metadata
project_path Returns the full path (including file name) of the current project
project_title Returns the title of current project
qgis_locale Returns the current language of QGIS
qgis_os_name Returns the current Operating system name,
eg 'windows', 'linux' or 'osx'
qgis_platform Returns QGIS platform, eg 'desktop' or 'server'
qgis_release_name Returns current QGIS release name
qgis_short_version Returns current QGIS version short string
qgis_version Returns current QGIS version string
qgis_version_no Returns current QGIS version number
snapping_results Gives access to snapping results while digitizing a
feature (only available in add feature)
symbol_angle Returns the angle of the symbol used to render
the feature (valid for marker symbols only)
symbol_color Returns the color of the symbol used to render
the feature
user_account_name Returns the current user's operating system
account name
user_full_name Returns the current user's operating system
user name
row_number Stores the number of the current row
value Returns the current value
with_variable Allows setting a variable for usage within an expression
and avoid recalculating the same value repeatedly
============================ =======================================================
|
**Some examples:**
* Return the X coordinate of a map item center to insert into a label in layout::
x( map_get( item_variables( 'map1'), 'map_extent_center' ) )
* Return for each feature in the current layer the number of overlapping airports
features::
aggregate( layer:='airport', aggregate:='count', expression:="code",
filter:=intersects( $geometry, geometry( @parent ) ) )
* Get the object_id of the first snapped point of a line::
with_variable(
'first_snapped_point',
array_first( @snapping_results ),
attribute(
get_feature_by_id(
map_get( @first_snapped_point, 'layer' ),
map_get( @first_snapped_point, 'feature_id' )
),
'object_id'
)
)
Recent Functions
-----------------
This group contains recently used functions. Depending on the context of its
usage (feature selection, field calculator, generic), any applied expression
is added to the corresponding list (up to ten expressions), sorted from the
more recent to the less one.
This helps to quickly retrieve and reapply any previously used expression.
.. index:: Custom functions
.. _function_editor:
Function Editor
===============
With the :guilabel:`Function Editor` tab, you are able to write your own functions
in Python language. This provides a handy and comfortable way to address
particular needs that would not be covered by the predefined functions.
.. _figure_expression_function:
.. figure:: img/function_editor.png
:align: center
The Function Editor tab
To create a new function:
#. Press the |signPlus| :sup:`New File` button.
#. Enter a name to use in the form that pops up and press :guilabel:`OK`.
A new item of the name you provide is added in the left panel of the
:guilabel:`Function Editor` tab; this is a Python :file:`.py` file based on
QGIS template file and stored in the :file:`/python/expressions` folder
under the active :ref:`user profile ` directory.
#. The right panel displays the content of the file: a python script template.
Update the code and its help according to your needs.
#. Press the |start| :guilabel:`Save and Load Functions` button.
The function you wrote is added to the functions tree in the :guilabel:`Expression`
tab, by default under the ``Custom`` group.
#. Enjoy your new function.
#. If the function requires improvements, enable the :guilabel:`Function Editor`
tab, do the changes and press again the |start| :guilabel:`Save and Load
Functions` button to make them available in the file, hence in any expression
tab.
Custom Python functions are stored under the user profile directory, meaning that at
each QGIS startup, it will auto load all the functions defined with the current user
profile. Be aware that new functions are only saved in the :file:`/python/expressions`
folder and not in the project file.
If you share a project that uses one of your custom functions you will need to also
share the :file:`.py` file in the :file:`/python/expressions` folder.
Here's a short example on how to create your own functions:
.. code-block:: python
from qgis.core import *
from qgis.gui import *
@qgsfunction(args='auto', group='Custom')
def my_sum(value1, value2, feature, parent):
"""
Calculates the sum of the two parameters value1 and value2.
Example usage:
- my_sum(5, 8) -> 13
- my_sum("field1", "field2") -> 42
"""
return value1 + value2
The short example creates a function ``my_sum`` that will give you a function
with two values.
When using the ``args='auto'`` function argument the number of function
arguments required will be calculated by the number of arguments the function
has been defined with in Python (minus 2 - ``feature``, and ``parent``).
This function can then be used in expressions:
.. _figure_expression_custom_function:
.. figure:: img/customFunction.png
:align: center
Custom Function added to the Expression tab
Further information about creating Python code can be found in the
:ref:`PyQGIS-Developer-Cookbook`.
.. Substitutions definitions - AVOID EDITING PAST THIS LINE
This will be automatically updated by the find_set_subst.py script.
If you need to create a new substitution manually,
please add it also to the substitutions.txt file in the
source folder.
.. |calculateField| image:: /static/common/mActionCalculateField.png
:width: 1.5em
.. |dataDefined| image:: /static/common/mIconDataDefine.png
:width: 1.5em
.. |expression| image:: /static/common/mIconExpression.png
:width: 1.5em
.. |expressionSelect| image:: /static/common/mIconExpressionSelect.png
:width: 1.5em
.. |signPlus| image:: /static/common/symbologyAdd.png
:width: 1.5em
.. |start| image:: /static/common/mActionStart.png
:width: 1.5em