Since GDAL/OGR 1.10, the SQLite "dialect" can be used as an alternate SQL dialect to the OGR SQL dialect. This assumes that GDAL/OGR is built with support for SQLite (>= 3.6), and preferably with Spatialite support too to benefit from spatial functions.
The SQLite dialect may be used with any OGR datasource, like the OGR SQL dialect. It is available through the GDALDataset::ExecuteSQL() method by specifying the pszDialect to "SQLITE". For the ogrinfo or ogr2ogr utility, you must specify the "-dialect SQLITE" option.
This is mainly aimed to execute SELECT statements, but, for datasources that support update, INSERT/UPDATE/DELETE statements can also be run.
The syntax of the SQL statements is fully the one of the SQLite SQL engine. You can refer to the following pages:
The SELECT statement is used to fetch layer features (analogous 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 analogous to tables in an RDBMS and feature attributes are analogous to column values. The simplest form of OGR SQLITE SELECT statement looks like this:
More complex statements can of course be used, including WHERE, JOIN, USING, GROUP BY, ORDER BY, sub SELECT, ...
The table names that can be used are the layer names available in the datasource on which the ExecuteSQL() method is called.
Similarly to OGRSQL, it is also possible to refer to layers of other datasources with the following syntax : "other_datasource_name"."layer_name".
The column names that can be used in the result column list, in WHERE, JOIN, ... clauses are the field names of the layers. Expressions, SQLite functions can also be used, spatial functions, etc...
The conditions on fields expressed in WHERE clauses, or in JOINs are translated, as far as possible, as attribute filters that are applied on the underlying OGR layers. Joins can be very expensive operations if the secondary table is not indexed on the key field being used.
The GEOMETRY special field represents the geometry of the feature returned by OGRFeature::GetGeometryRef(). It can be explicitly specified in the result column list of a SELECT, and is automatically selected if the wildcard is used.
For OGR layers that have a non-empty geometry column name (generally for RDBMS datasources), as returned by OGRLayer::GetGeometryColumn(), the name of the geometry special field in the SQL statement will be the name of the geometry column of the underlying OGR layer.
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:
The ogr_datasource_load_layers(datasource_name[, update_mode[, prefix]]) function can be used to automatically load all the layers of a datasource as VirtualOGR tables.
The following SQL functions are available and operate on a layer name : ogr_layer_Extent(), ogr_layer_SRID(), ogr_layer_GeometryType() and ogr_layer_FeatureCount()
ogr_deflate(text_or_blob[, compression_level]) returns a binary blob compressed with the ZLib deflate algorithm. See CPLZLibDeflate()
ogr_inflate(compressed_blob) returns the decompressed binary blob, from a blob compressed with the ZLib deflate algorithm. If the decompressed binary is a string, use CAST(ogr_inflate(compressed_blob) AS VARCHAR). See CPLZLibInflate().
Starting with OGR 2.0, the hstore_get_value() function can be used to extract a value associate to a key from a HSTORE string, formatted like "key=>value,other_key=>other_value,..."
The following SQL functions are available : ogr_geocode(...) and ogr_geocode_reverse(...).
ogr_geocode(name_to_geocode [, field_to_return [, option1 [, option2, ...]]]) where name_to_geocode is a literal or a column name that must be geocoded. field_to_return if specified can be "geometry" for the geometry (default), or a field name of the layer returned by OGRGeocode(). The special field "raw" can also be used to return the raw response (XML string) of the geocoding service. option1, option2, etc.. must be of the key=value format, and are options understood by OGRGeocodeCreateSession() or OGRGeocode().
This function internally uses the OGRGeocode() API. Refer to it for more details.
ogr_geocode_reverse(longitude, latitude, field_to_return [, option1 [, option2, ...]]) where longitude, latitude is the coordinate to query. field_to_return must be a field name of the layer returned by OGRGeocodeReverse() (for example 'display_name'). The special field "raw" can also be used to return the raw response (XML string) of the geocoding service. option1, option2, etc.. must be of the key=value format, and are options understood by OGRGeocodeCreateSession() or OGRGeocodeReverse().
ogr_geocode_reverse(geometry, field_to_return [, option1 [, option2, ...]]) is also accepted as an alternate syntax where geometry is a (Spatialite) point geometry.
This function internally uses the OGRGeocodeReverse() API. Refer to it for more details.
Spatialite spatial index mechanism can be triggered by making sure a spatial index virtual table is mentioned in the SQL (of the form idx_layername_geometrycolumn), or by using the more recent SpatialIndex from the VirtualSpatialIndex extension. In which case, a in-memory RTree will be built to be used to speed up the spatial queries.
For example, a spatial intersection between 2 layers, by using a spatial index on one of the layers to limit the number of actual geometry intersection computations :
or more elegantly :