Documentation | Other Specifications | SQL filter

In a number of cases in code, such as shape looping, Developer Kernel rendering uses SQL syntax. This is not any sort of full SQL implementation; it implements only a WHERE clause.

Suppose you have a full SQL query:

SELECT * 
  FROM my TABLE
  WHERE country LIKE 'fra%'
  ORDER BY population.

In the DK we implemented this part of SQL:

country LIKE 'fra%'

Strings are referred to in a single quote.

Good:

'this is a s string'

Bad:

"this is a s string"

Float numbers should always be expressed with a decimal separator using a dot.

Good:

1123.456

Bad:

1123,456

To refer to an attribute field, simply use its name with a query like:

country LIKE 'fra%'

It works properly if field names in the layer (especially in database based layers) are case-insensitive, ASCII based, and single-worded. In other cases you can use:

"County Name" LIKE 'fra%'
[County Name] LIKE 'fra%'

A number of DK computed fields are available for SQL query:

• GIS_UID
  Numeric. It provides shape UID. It will be automatically replaced by a proper identifier upon pass-through queries
• GIS_SELECTED
  Logical. True if a shape is selected.
• GIS_HIDDEN
  Logical. True if shape is hidden.
• GIS_AREA
  Float. The shape area is expressed in square meters. Valid only if a proper coordinate system has been applied.
• GIS_LENGTH
  Float. Line length or length of polygon circumference is expressed in meters. Valid only if a proper coordinate system has been applied.
• GIS_COORD_Z
  Z Coordinate for 3D Shapes.
• GIS_COORD_M
  M Coordinate for 3D Shapes.
• GIS_NOW
  Current date and time.

New built-in fields available since v11.33.0:

• GIS_MIN_X, GIS_MIN_Y, GIS_MIN_Z, GIS_MIN_M
  Float. Minimum shape coordinates.
• GIS_MAX_X, GIS_MAX_Y, GIS_MAX_Z, GIS_MAX_M
  Float. Maximum shape coordinates.
• GIS_CENTER_X, GIS_CENTER_Y, GIS_CENTER_Z, GIS_CENTER_M
  Float. Coordinates of the shape’s center (center of the extent).
• GIS_CENTROID_X, GIS_CENTROID_Y
  Float. Coordinates of the shape’s centroid.
• GIS_NUM_POINTS
  Numeric. Number of the shape’s points (vertices).
• GIS_NUM_PARTS
  Numeric. Number of the shape’s parts.

• date(year [,month] [,day])
  Returns date in a format suitable for comparing with date attribute fields.
• time(hours [,minutes] [,seconds] [,milliseconds])
  Returns time in a format suitable for comparing with date attribute fields.
• datetime(year [,month] [,day] [,minutes] [,seconds] [,milliseconds]))
  Returns date and time in a format suitable for comparing with date attribute fields.
• now()
  Returns current date and time in a format suitable for comparing with date attribute fields.
• today()
  Returns current date in a format suitable for comparing with date attribute fields.

• =
  Equal
• <>
  Not equal
• <
  Less then
• ⇐
  Less-equal then
• >
  Greater then
• >=
  Greater-equal then

• +
  Add
• -
  Subtract
• *
  Multiply
• /
  Divide
• %
  Modulo

• LIKE
  Like case insensitive operator
• IN
  In operator
• NOT
  Negation
• IS NULL
  Test if field is null/not assigned.

( population / GIS_AREA ) > 2.5
  country LIKE '%land'
  code IN( 'US', 'CA', MX')
  NOT GIS_SELECTED


If the layer on which a SQL filter is applied is a normal flat file (like SHP), TatukGIS is responsible for the whole parsing because there is no SQL back-end we can use. However, if the opened layer resides in a SQL database like TGIS_LayerSqlOgisSqlite, the Developer Kernel will try to send the query straight to a database.

DK will send to the database:

• query string with Loop enumerators
• layer.Filter

DK will parse locally all things related to params like:

• layer.Params.Query
• layer.Params.Expression

Upon passing through to a database, the Developer Kernel will:

• Change any use of GIS_UID to a database-specific field name (like FEA.UID)
• If a layer detects any predefined field like GIS_AREA or a join prefix (db.), the whole query will be computed locally.

Only fields within the same record can be compared. This limitation applies only to local queries. Pass-through queries are limited only by database syntax.