User Tools

Site Tools


doc:sqlwhere

SQL filter syntax

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%'

Syntax

Strings

Strings are referred to in a single quote.

Good:

'this is a s string'

Bad:

"this is a s string"

Numbers

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

Good:

1123.456

Bad:

1123,456

Fields referring

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%'

Built-in fields

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.

Built-in functions

  • 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.

Operators

Logical
  • =
    Equal
  • <>
    Not equal
  • <
    Less then

  • Less-equal then
  • >
    Greater then
  • >=
    Greater-equal then
Arithmetic
  • +
    Add
  • -
    Subtract
  • *
    Multiply
  • /
    Divide
  • %
    Modulo
Other
  • LIKE
    Like case insensitive operator
  • IN
    In operator
  • NOT
    Negation
  • IS NULL
    Test if field is null/not assigned.
Sample
( population / GIS_AREA ) > 2.5
  country LIKE '%land'
  code IN( 'US', 'CA', MX')
  NOT GIS_SELECTED


Pass-through

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.

Limitation

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.

2021/09/24 09:03

Page Tools