/*! \page ogr_sql OGR SQL
The OGRDataSource supports executing commands against a datasource via the
OGRDataSource::ExecuteSQL() method. While in theory any sort of command could
be handled this way, in practice the mechanism is used to provide a subset of
SQL SELECT capability to applications. This page discusses the generic SQL
implementation implemented within OGR, and issue with driver specific SQL
support.
\section ogr_sql_select SELECT
The SELECT statement is used to fetch layer features (analygous to table
rows in an RDBMS) with the result of the query represented as a temporary layer
of features. The layers of the datasource are analygous to tables in an
RDBMS and feature attributes are analygous to column values. The simpliest
form of OGR SQL SELECT statement looks like this:
\code
SELECT * FROM polylayer
\endcode
In this case all features are fetched from the layer named "polylayer", and
all attributes of those features are returned. This is essentially
equivelent to accessing the layer directly. In this example the "*"
is the list of fields to fetch from the layer, with "*" meaning that all
fields should be fetched.
This slightly more sophisticated form still pulls all features from the layer
but the schema will only contain the EAS_ID and PROP_VALUE attributes. Any
other attributes would be discarded.
\code
SELECT eas_id, prop_value FROM polylayer
\endcode
A much more ambitious SELECT, restricting the features fetched with a
WHERE clause, and sorting the results might look like:
\code
SELECT * from polylayer WHERE prop_value > 220000.0 ORDER BY prop_value DESC
\endcode
This select statement will produce a table with just one feature, with one
attribute (named something like "count_eas_id") containing the number of
distinct values of the eas_id attribute.
\code
SELECT COUNT(DISTINCT eas_id) FROM polylayer
\endcode
\subsection ogr_sql_flist_ops Field List Operators
The field list is a comma separate list of the fields to be carried into
the output features from the source layer. They will appear on output features
in the order they appear on in the field list, so the field list may be used
to re-order the fields.
A special form of the field list uses the DISTINCT keyword. This returns a
list of all the distinct values of the named attribute. When the DISTINCT
keyword is used, only one attribute may appear in the field list. The DISTINCT
keyword may be used against any type of field. Currently the distinctness
test against a string value is case insensitive in OGR SQL. The result of
a SELECT with a DISTINCT keyword is a layer with one column (named the same
as the field operated on), and one feature per distinct value. Geometries
are discarded. The distinct values are assembled in memory, so alot of
memory may be used for datasets with a large number of distinct values.
\code
SELECT DISTINCT areacode FROM polylayer
\endcode
There are also several summarization operators that may be applied to columns.
When a summarization operator is applied to any field, then all fields must
have summarization operators applied. The summarization operators are
COUNT (a count of instances), AVG (numerical average), SUM (numericla sum),
MIN (lexical or numerical minimum), and MAX (lexical or numerical maximum).
This example produces a variety of sumarization information on parcel
property values:
\code
SELECT MIN(prop_value), MAX(prop_value), AVG(prop_value), SUM(prop_value),
COUNT(prop_value) FROM polylayer WHERE prov_name = "Ontario"
\endcode
As a special case, the COUNT() operator can be given a "*" argument instead
of a field name which is a short form for count all the records though it
would get the same result as giving it any of the column names. It is
also possible to apply the COUNT() operator to a DISTINCT SELECT to get
a count of distinct values, for instance:
\code
SELECT COUNT(DISTINCT areacode) FROM polylayer
\endcode
Field names can also be prefixed by a table name though this is only
really meaningful when performing joins. It is further demonstrated in
the JOIN section.
\subsubsection ogr_sql_flist_limits Field List Limitations
-
Field arithmetic, and other binary operators are not supported, so you can't
do something like:
\code
SELECT prop_value / area FROM invoices
\endcode
-
There is no mechanism to rename a column. In fact, I don't even know how this
is done in real SQL.
- Lots of operators are missing.
\subsection ogr_sql_where WHERE
The argument to the WHERE clause is a fairly simplistic logical expression
used select records to be selected from the source layer. In addition to its
use within the WHERE statement, the WHERE clause handling is also used for
OGR attribute queries on regular layers.
A WHERE clause consists of
a set of attribute tests. Each basic test is of the form fieldname
operator value. The fieldname is any of the fields in the source
layer. The operator is one of
=,
!=,
<>,
<,
>,
<=,
>=,
LIKE and
ILIKE and
IN.
Most of the operators are self explanitory, but is is worth nothing that
!= is the same as <>, the string equality is
case insensitive, but the <, >, <= and >= operators are case sensitive. Both the LIKE and ILIKE operators are case insensitive.
The value argument to the LIKE operator is a pattern against which
the value string is matched. In this pattern percent (%) matches any number of
characters, and underscore ( _ ) matches any one character.
\code
String Pattern Matches?
------ ------- --------
Alberta ALB% Yes
Alberta _lberta Yes
St. Alberta _lberta No
St. Alberta %lberta Yes
Robarts St. %Robarts% Yes
12345 123%45 Yes
123.45 12?45 No
N0N 1P0 %N0N% Yes
L4C 5E2 %N0N% No
\endcode
The IN takes a list of values as it's argument and tests the attribute
value for membership in the provided set.
\code
Value Value Set Matches?
------ ------- --------
321 IN (456,123) No
"Ontario" IN ("Ontario","BC") Yes
"Ont" IN ("Ontario","BC") No
1 IN (0,2,4,6) No
\endcode
In addition to the above binary operators, there are additional operators
for testing if a field is null or not. These are the IS NULL
and IS NOT NULL operators.
Basic field tests can be combined in more complicated predicates using logical
operators include AND, OR, and the unary logical NOT.
Subexpressions should be bracketed to make precidence
clear. Some more complicated predicates are:
\code
SELECT * FROM poly WHERE (prop_value >= 100000) AND (prop_value < 200000)
SELECT * FROM poly WHERE NOT (area_code LIKE "N0N%")
SELECT * FROM poly WHERE (prop_value IS NOT NULL) AND (prop_value < 100000)
\endcode
\subsection ogr_sql_where_limits WHERE Limitations
- The left of any comparison operator must be a field name, and the right
must be a literal value. Fields cannot currently be compared to fields.
- Fields must all come from the primary table (the one listed in the FROM
clause, and must not have any table prefix ... they must just be the field
name.
- No arithmetric operations are supported. You can't test
"WHERE (a+b) < 10" for instance.
- All string comparisons are case insensitive except for <, >, <= and >=.
\subsection ogr_sql_order_by ORDER BY
The ORDER BY clause is used force the returned features to be reordered
into sorted order (ascending or descending) on one of the field values.
Ascending (increasing) order is the default if neither the ASC or DESC keyword
is provided. For example:
\code
SELECT * FROM property WHERE class_code = 7 ORDER BY prop_value DESC
SELECT * FROM property ORDER BY prop_value
SELECT * FROM property ORDER BY prop_value ASC
SELECT DISTINCT zip_code FROM property ORDER BY zip_code
\endcode
Note that ORDER BY clauses cause two passes through the feature set. One to
build an in-memory table of field values corresponded with feature ids, and
a second pass to fetch the features by feature id in the sorted order. For
formats which cannot efficiently randomly read features by feature id this can
be a very expensive operation.
Sorting of string field values is case sensitive, not case insensitive like in
most other parts of OGR SQL.
\subsection ogr_sql_joins JOINs
OGR SQL supports a limited form of one to one JOIN. This allows records from
a secondary table to be looked up based on a shared key between it and the
primary table being queried. For instance, a table of city locations might
include a nation_id column that can be used as a reference into a
secondary nation table to fetch a nation name. A joined query might
look like:
\code
SELECT city.*, nation.name FROM city
LEFT JOIN nation ON city.nation_id = nation.id
\endcode
This query would result in a table with all the fields from the city table,
and an additional "nation.name" field with the nation name pulled from the
nation table by looking for the record in the nation table that has the "id"
field with the same value as the city.nation_id field.
Joins introduce a number of additional issues. One is the concept of table
qualifiers on field names. For instance, referring to city.nation_id instead
of just nation_id to indicate the nation_id field from the city layer. The
table name qualifiers may only be used in the field list, and within the
ON clause of the join.
Wildcards are also somewhat more involved. All fields from the primary table
(city in this case) and the secondary table (nation in this
case) may be selected using the usual * wildcard. But the fields of
just one of the primary or secondary table may be selected by prefixing the
asterix with the table name.
The field names in the resulting query layer will be qualified by the table
name, if the table name is given as a qualifier in the field list. In addition
field names will be qualified with a table name if they would conflict with
earlier fields. For instance, the following select would result might result
in a results set with a name, nation_id, nation.nation_id and
nation.name field if the city and nation tables both have the
nation_id and name fieldnames.
\code
SELECT * FROM city LEFT JOIN nation ON city.nation_id = nation.nation_id
\endcode
On the other hand if the nation table had a continent_id field, but
the city table did not, then that field would not need to be qualified in
the result set. However, if the selected instead looked like the following
statement, all result fields would be qualified by the table name.
\code
SELECT city.*, nation.* FROM city
LEFT JOIN nation ON city.nation_id = nation.nation_id
\endcode
In the above examples, the nation table was found in the same
datasource as the city table. However, the OGR join support
includes the ability to join against a table in a different data source,
potentially of a different format. This is indicated by qualifying the
secondary table name with a datasource name. In this case the secondary
datasource is opened using normal OGR semantics and utilized to access the
secondary table untill the query result is no longer needed.
\code
SELECT * FROM city
LEFT JOIN '/usr2/data/nation.dbf'.nation ON city.nation_id = nation.nation_id
\endcode
While not necessarily very useful, it is also possible to introduce table
aliases to simplify some SELECT statements. This can also be useful to
disambiguate situations where ables of the same name are being used from
different data sources. For instance, if the actual
tables names were messy we might want to do something like:
\code
SELECT c.name, n.name FROM project_615_city c
LEFT JOIN '/usr2/data/project_615_nation.dbf'.project_615_nation n
ON c.nation_id = n.nation_id
\endcode
It is possible to do multiple joins in a single query.
\code
SELECT city.name, prov.name, nation.name FROM city
LEFT JOIN province ON city.prov_id = province.id
LEFT JOIN nation ON city.nation_id = nation.id
\endcode
\subsection ogr_sql_join_limits JOIN Limitations
- Joins can be very expensive operations if the secondary table is not
indexed on the key field being used.
- Joined fields may not be used in WHERE clauses, or ORDER BY clauses
at this time. The join is essentially evaluated after all primary table
subsetting is complete, and after the ORDER BY pass.
- Joined fields may not be used as keys in later joins. So you could not
use the province id in a city to lookup the province record, and then use a
nation id from the province id to lookup the nation record. This is a sensible
thing to want and could be implemented, but is not currently supported.
- Datasource names for joined tables are evaluated relative to the
current processes working directory, not the path to the primary datasource.
- These are not true LEFT or RIGHT joins in the RDBMS sense. Whether
or not a secondary record exists for the join key or not, one and only one
copy of the primary record is returned in the result set. If a secondary
record cannot be found, the secondary derived fields will be NULL. If more
than one matching secondary field is found only the first will be used.
\section ogr_sql_special_fields SPECIAL FIELDS
The OGR SQL query processor treats some of the attributes of the features as
built-in special fields can be used in the SQL statements likewise the
other fields. These fields can be placed in the select list, the WHERE clause
and the ORDER BY clause respectively. The special field will not be included
in the result by default but it may be explicitly included by adding it to
the select list.
When accessing the field values the special fields will take pecedence over
the other fields with the same names in the data source.
\subsection ogr_sql_fid FID
Normally the feature id is a special property of a feature and not treated
as an attribute of the feature. In some cases it is convenient to be able to
utilize the feature id in queries and result sets as a regular field. To do
so use the name FID. The field wildcard expansions will not include
the feature id, but it may be explicitly included using a syntax like:
\code
SELECT FID, * FROM nation
\endcode
\subsection ogr_sql_geometry OGR_GEOMETRY
Some of the data sources (like MapInfo tab) can handle geometries of different
types within the same layer. The OGR_GEOMETRY special field represents
the geometry type returned by OGRGeometry::getGeometryName() and can be used to
distinguish the various types. By using this field one can select particular
types of the geometries like:
\code
SELECT * FROM nation WHERE OGR_GEOMETRY='POINT' OR OGR_GEOMETRY='POLYGON'
\endcode
\subsection ogr_sql_geom_wkt OGR_GEOM_WKT
The Well Known Text representation of the geometry can also be used as
a special field. To select the WKT of the geometry OGR_GEOM_WKT
might be included in the select list, like:
\code
SELECT OGR_GEOM_WKT, * FROM nation
\endcode
Using the OGR_GEOM_WKT and the LIKE operator in the WHERE
clause we can get similar effect as using OGR_GEOMETRY:
\code
SELECT OGR_GEOM_WKT, * FROM nation WHERE OGR_GEOM_WKT
LIKE 'POINT%' OR OGR_GEOM_WKT LIKE 'POLYGON%'
\endcode
\subsection ogr_sql_style OGR_STYLE
The OGR_STYLE special field represents the style string of the feature
returned by OGRFeature::GetStyleString(). By using this field and the
LIKE operator the result of the query can be filtered by the style.
For example we can select the annotation features as:
\code
SELECT * FROM nation WHERE OGR_STYLE LIKE 'LABEL%'
\endcode
\section ogr_sql_create_index CREATE INDEX
Some OGR SQL drivers support creating of attribute indexes. Currently
this includes the Shapefile driver. An index accelerates very simple
attribute queries of the form fieldname = value, which is what
is used by the JOIN capability. To create an attribute index on
the nation_id field of the nation table a command like this would be used:
\code
CREATE INDEX ON nation USING nation_id
\endcode
\subsection ogr_sql_index_limits Index Limitations
- Indexes are not maintained dynamically when new features are added to or
removed from a layer.
- Very long strings (longer than 256 characters?) cannot currently be
indexed.
- To recreate an index it is necessary to drop all indexes on a layer and
then recreate all the indexes.
- Indexes are not used in any complex queries. Currently the only
query the will accelerate is a simple "field = value" query.
\section ogr_sql_drop_index DROP INDEX
The OGR SQL DROP INDEX command can be used to drop all indexes on a particular
table, or just the index for a particular column.
\code
DROP INDEX ON nation USING nation_id
DROP INDEX ON nation
\endcode
\section ogr_sql_exec_sql ExecuteSQL()
SQL is executed against an OGRDataSource, not against a specific layer. The
call looks like this:
\code
OGRLayer * OGRDataSource::ExecuteSQL( const char *pszSQLCommand,
OGRGeometry *poSpatialFilter,
const char *pszDialect );
\endcode
The pszDialect argument is in theory intended to allow for support of
different command languages against a provider, but for now applications
should always pass an empty (not NULL) string to get the default dialect.
The poSpatialFilter argument is a geometry used to select a bounding rectangle
for features to be returned in a manner similar to the
OGRLayer::SetSpatialFilter() method. It may be NULL for no special spatial
restriction.
The result of an ExecuteSQL() call is usually a temporary OGRLayer representing
the results set from the statement. This is the case for a SELECT statement
for instance. The returned temporary layer should be released with
OGRDataSource::ReleaseResultsSet() method when no longer needed. Failure
to release it before the datasource is destroyed may result in a crash.
\section ogr_sql_non_ogr_sql Non-OGR SQL
The Oracle (OCI) and PostGIS (PG) drivers both override the ExecuteSQL()
implementation, and pass the SQL statements directly through to the underlying
RDBMS. In these cases the SQL syntax varies in some particulars from OGR SQL.
Also, anything possible in SQL can then be accomplished for these particular
databases. Only the result of SQL WHERE statements will be returned as
layers.
*/