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
GIS_SELECTED
GIS_HIDDEN
GIS_AREA
GIS_LENGTH
GIS_COORD_Z
GIS_COORD_M
GIS_NOW
New built-in fields available since v11.33.0:
GIS_MIN_X
, GIS_MIN_Y
, GIS_MIN_Z
, GIS_MIN_M
GIS_MAX_X
, GIS_MAX_Y
, GIS_MAX_Z
, GIS_MAX_M
GIS_CENTER_X
, GIS_CENTER_Y
, GIS_CENTER_Z
, GIS_CENTER_M
GIS_CENTROID_X
, GIS_CENTROID_Y
GIS_NUM_POINTS
GIS_NUM_PARTS
date(year [,month] [,day])
time(hours [,minutes] [,seconds] [,milliseconds])
datetime(year [,month] [,day] [,minutes] [,seconds] [,milliseconds]))
now()
today()
=
<>
<
⇐
>
>=
+
-
*
/
%
LIKE
IN
NOT
IS NULL
( 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:
DK will parse locally all things related to params like:
Upon passing through to a database, the Developer Kernel will:
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.