10

How can I get automatically a list of all functions available for usage in QGIS's Virtual Layer?

From my own understanding/experience and the QGIS Documentation, QGIS's Virtual Layer employes a mixture of functions provided in PostGIS, SQLite, SpatiaLite and the Functions of QGIS expressions. (I hope I did not miss anything vital.)

I have seen several topics What spatial functions are available to shapefiles as “virtual layers” in QGIS DB Manager? and Virtual layers for QGIS (GitHub), unfortunately, in none of them, I could find the solution to my concern.

12

You can access directly to the Virtual Layers with the power of Python introspection and well formated QGIS Python modules with this code below executed in the QGIS console:

from db_manager.db_plugins.vlayers import sql_dictionary

print(f"Functions: {sql_dictionary.functions}")
print(f"Math functions: {sql_dictionary.math_functions}")
print(f"String functions: {sql_dictionary.string_functions}")
print(f"Aggregate functions: {sql_dictionary.aggregate_functions}")
print(f"Spatialite functions: {sql_dictionary.spatialite_functions}")
print(f"QGIS functions: {sql_dictionary.qgis_functions}")

Results in:

Functions: ['changes', 'coalesce', 'glob', 'ifnull', 'hex', 'last_insert_rowid', 'nullif', 'quote', 'random', 'randomblob', 'replace', 'round', 'soundex', 'total_change', 'typeof', 'zeroblob', 'date', 'datetime', 'julianday', 'strftime']
Math functions: ['Abs', 'ACos', 'ASin', 'ATan', 'Cos', 'Cot', 'Degrees', 'Exp', 'Floor', 'Log', 'Log2', 'Log10', 'Pi', 'Radians', 'Round', 'Sign', 'Sin', 'Sqrt', 'StdDev_Pop', 'StdDev_Samp', 'Tan', 'Var_Pop', 'Var_Samp']
String functions: ['Length', 'Lower', 'Upper', 'Like', 'Trim', 'LTrim', 'RTrim', 'Replace', 'Substr']
Aggregate functions: ['Max', 'Min', 'Avg', 'Count', 'Sum', 'Group_Concat', 'Total', 'Var_Pop', 'Var_Samp', 'StdDev_Pop', 'StdDev_Samp']
Spatialite functions: ['*iszipblob', '*ispdfblob', '*isgifblob', '*ispngblob', '*isjpegblob', '*isexifblob', '*isexifgpsblob', '*geomfromexifgpsblob', 'MakePoint', 'BuildMbr', '*buildcirclembr', 'ST_MinX', 'ST_MinY', 'ST_MaxX', 'ST_MaxY', 'ST_GeomFromText', '*pointfromtext', '*geomfromwkb', '*pointfromwkb', 'ST_AsText', 'ST_AsBinary', '*assvg', '*asfgf', '*geomfromfgf', 'ST_Dimension', 'ST_GeometryType', 'ST_Srid', 'ST_SetSrid', 'ST_isEmpty', 'ST_isSimple', 'ST_isValid', 'ST_Boundary', 'ST_Envelope', 'ST_X', 'ST_Y', 'ST_StartPoint', 'ST_EndPoint', 'ST_Length', 'ST_isClosed', 'ST_isRing', 'ST_Simplify', '*simplifypreservetopology', 'ST_NumPoints', 'ST_PointN', 'ST_Centroid', 'ST_PointOnSurface', 'ST_Area', 'ST_ExteriorRing', 'ST_InteriorRingN', 'ST_NumGeometries', 'ST_GeometryN', 'MbrEqual', 'MbrDisjoint', 'MbrTouches', 'MbrWithin', 'MbrOverlaps', 'MbrIntersects', 'MbrContains', 'ST_Equals', 'ST_Disjoint', 'ST_Touches', 'ST_Within', 'ST_Overlaps', 'ST_Crosses', 'ST_Intersects', 'ST_Contains', 'ST_Relate', 'ST_Distance', 'ST_Intersection', 'ST_Difference', 'ST_Union', 'ST_SymDifference', 'ST_Buffer', 'ST_ConvexHull', 'ST_Transform', '*initspatialmetadata', '*addgeometrycolumn', '*recovergeometrycolumn', '*discardgeometrycolumn', '*createspatialindex', '*creatembrcache', '*disablespatialindex', '*checkspatialmetadata', '*autofdostart', '*autofdostop', '*initfdospatialmetadata', '*addfdogeometrycolumn', '*recoverfdogeometrycolumn', '*discardfdogeometrycolumn', '*filtermbrwithin', '*filtermbrcontains', '*filtermbrintersects', '*buildmbrfilter']
QGIS functions: ['atan2', 'round', 'rand', 'randf', 'clamp', 'scale_linear', 'scale_exp', '_pi', 'to_int', 'toint', 'to_real', 'toreal', 'to_string', 'tostring', 'to_datetime', 'todatetime', 'to_date', 'todate', 'to_time', 'totime', 'to_interval', 'tointerval', 'regexp_match', 'now', '_now', 'age', 'year', 'month', 'week', 'day', 'hour', 'minute', 'second', 'day_of_week', 'title', 'levenshtein', 'longest_common_substring', 'hamming_distance', 'wordwrap', 'regexp_replace', 'regexp_substr', 'concat', 'strpos', '_left', '_right', 'rpad', 'lpad', 'format', 'format_number', 'format_date', 'color_rgb', 'color_rgba', 'ramp_color', 'color_hsl', 'color_hsla', 'color_hsv', 'color_hsva', 'color_cmyk', 'color_cmyka', 'color_part', 'darker', 'lighter', 'set_color_part', 'point_n', 'start_point', 'end_point', 'nodes_to_points', 'segments_to_lines', 'make_point', 'make_point_m', 'make_line', 'make_polygon', 'x_min', 'xmin', 'x_max', 'xmax', 'y_min', 'ymin', 'y_max', 'ymax', 'geom_from_wkt', 'geomFromWKT', 'geom_from_gml', 'relate', 'intersects_bbox', 'bbox', 'translate', 'buffer', 'point_on_surface', 'reverse', 'exterior_ring', 'interior_ring_n', 'geometry_n', 'bounds', 'num_points', 'num_interior_rings', 'num_rings', 'num_geometries', 'bounds_width', 'bounds_height', 'is_closed', 'convex_hull', 'sym_difference', 'combine', '_union', 'geom_to_wkt', 'geomToWKT', 'transform', 'uuid', '_uuid', 'layer_property', 'var', '_specialcol_', 'project_color']
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.