User Tools

Site Tools


doc:sqlwhere

SQL filter syntax

Documentation | Other Specifications | SQL filter

In a number of cases in code, such as shape lopping, 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 a DK we implemented this part of SQL:

country LIKE 'fra%'

Syntax

Strings

Strings are referred in a single quote.

Good:

'this is a s string'

Bad:

"this is a s string"

Numbers

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

Good:

1123.456

Bad:

1123,456

Fields referring

To refer an attribute field simple use it name with query like

country LIKE 'fra%'

It works properly if field names in 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

Number of DK computed fields is available for SQL query:

  • GIS_UID
    Numeric. Provides shape UID. It will be automatically replaced by proper identifier upon pass-trough queries
  • GIS_SELECTED
    Logical. True if shape is selected.
  • GIS_HIDDEN
    Logical. True if shape is hidden.
  • GIS_AREA
    Float. Shape area expressed in squared meters. Valid only of proper coordinate system has been applied.
  • GIS_LENGTH
    Float. Line length or area circumference expressed in meters.Valid only of 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.

Build-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 formats that is 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
  • -
    Substract
  • *
    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-trough

If layer on which SQL filer was 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, 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, Developer Kernel will:

  • Change any use of GIS_UID to 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 applied only for or local queries. Pass-through queries are limited only by database syntax.

2017/03/20 20:31