EVOLUTION-MANAGER
Edit File: ogr_sql.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/> <meta http-equiv="X-UA-Compatible" content="IE=9"/> <meta name="generator" content="Doxygen 1.8.5"/> <title>OGR: OGR SQL</title> <link href="tabs.css" rel="stylesheet" type="text/css"/> <script type="text/javascript" src="jquery.js"></script> <script type="text/javascript" src="dynsections.js"></script> <link href="doxygen.css" rel="stylesheet" type="text/css" /> </head> <body> <div id="top"><!-- do not remove this div, it is closed by doxygen! --> <div id="titlearea"> <table cellspacing="0" cellpadding="0"> <tbody> <tr style="height: 56px;"> <td style="padding-left: 0.5em;"> <div id="projectname">OGR </div> </td> </tr> </tbody> </table> </div> <!-- end header part --> <!-- Generated by Doxygen 1.8.5 --> <div id="navrow1" class="tabs"> <ul class="tablist"> <li><a href="index.html"><span>Main Page</span></a></li> <li class="current"><a href="pages.html"><span>Related Pages</span></a></li> <li><a href="annotated.html"><span>Classes</span></a></li> <li><a href="files.html"><span>Files</span></a></li> </ul> </div> </div><!-- top --> <div class="header"> <div class="headertitle"> <div class="title">OGR SQL </div> </div> </div><!--header--> <div class="contents"> <div class="textblock"><p>The <a class="el" href="classOGRDataSource.html">OGRDataSource</a> supports executing commands against a datasource via the <a class="el" href="classOGRDataSource.html#aa6acc228db6513784a56ce12334a8c33" title="Execute an SQL statement against the data store. ">OGRDataSource::ExecuteSQL()</a> 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.</p> <p>Since GDAL/OGR 1.10, an alternate "dialect", the SQLite dialect, can be used instead of the OGRSQL dialect. Refer to the <b><a href="ogr_sql_sqlite.html">SQLite SQL dialect</a></b> page for more details.</p> <p>The <a class="el" href="classOGRLayer.html">OGRLayer</a> class also supports applying an attribute query filter to features returned using the <a class="el" href="classOGRLayer.html#acb2c6cc5fa3577df5be538284c1b0dde" title="Set a new attribute query. ">OGRLayer::SetAttributeFilter()</a> method. The syntax for the attribute filter is the same as the WHERE clause in the OGR SQL SELECT statement. So everything here with regard to the WHERE clause applies in the context of the SetAttributeFilter() method.</p> <p>NOTE: OGR SQL has been reimplemented for GDAL/OGR 1.8.0. Many features discussed below, notably arithmetic expressions, and expressions in the field list, were not support in GDAL/OGR 1.7.x and earlier. See RFC 28 for details of the new features in GDAL/OGR 1.8.0.</p> <h1><a class="anchor" id="ogr_sql_select"></a> SELECT</h1> <p>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 SQL SELECT statement looks like this:</p> <div class="fragment"><div class="line">SELECT * FROM polylayer</div> </div><!-- fragment --><p>In this case all features are fetched from the layer named "polylayer", and all attributes of those features are returned. This is essentially equivalent 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.</p> <p>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.</p> <div class="fragment"><div class="line">SELECT eas_id, prop_value FROM polylayer</div> </div><!-- fragment --><p>A much more ambitious SELECT, restricting the features fetched with a WHERE clause, and sorting the results might look like:</p> <div class="fragment"><div class="line">SELECT * from polylayer WHERE prop_value > 220000.0 ORDER BY prop_value DESC</div> </div><!-- fragment --><p>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.</p> <div class="fragment"><div class="line">SELECT COUNT(DISTINCT eas_id) FROM polylayer</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_flist_ops"></a> Field List Operators</h2> <p>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.</p> <p>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.</p> <div class="fragment"><div class="line">SELECT DISTINCT areacode FROM polylayer</div> </div><!-- fragment --><p>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 (numerical sum), MIN (lexical or numerical minimum), and MAX (lexical or numerical maximum). This example produces a variety of sumarization information on parcel property values:</p> <div class="fragment"><div class="line">SELECT MIN(prop_value), MAX(prop_value), AVG(prop_value), SUM(prop_value), </div> <div class="line"> COUNT(prop_value) FROM polylayer WHERE prov_name = "Ontario"</div> </div><!-- fragment --><p>It is also possible to apply the COUNT() operator to a DISTINCT SELECT to get a count of distinct values, for instance:</p> <div class="fragment"><div class="line">SELECT COUNT(DISTINCT areacode) FROM polylayer</div> </div><!-- fragment --><p>Note: prior to OGR 1.9.0, null values were counted in COUNT(column_name) or COUNT(DISTINCT column_name), which was not conformant with the SQL standard. Since OGR 1.9.0, only non-null values are counted.</p> <p>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.</p> <div class="fragment"><div class="line">SELECT COUNT(*) FROM polylayer</div> </div><!-- fragment --><p>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.</p> <p>Field definitions can also be complex expressions using arithmetic, and functional operators. However, the DISTINCT keyword, and summarization operators like MIN, MAX, AVG and SUM may not be applied to expression fields.</p> <div class="fragment"><div class="line">SELECT cost+tax from invoice</div> </div><!-- fragment --><p>or</p> <div class="fragment"><div class="line">SELECT CONCAT(owner_first_name,<span class="charliteral">' '</span>,owner_last_name) from properties</div> </div><!-- fragment --><h3><a class="anchor" id="ogr_sql_functions"></a> Functions</h3> <p>Starting with OGR 1.8.2, the SUBSTR function can be used to extract a substring from a string. Its syntax is the following one : SUBSTR(string_expr, start_offset [, length]). It extracts a substring of string_expr, starting at offset start_offset (1 being the first character of string_expr, 2 the second one, etc...). If start_offset is a negative value, the substring is extracted from the end of the string (-1 is the last character of the string, -2 the character before the last character, ...). If length is specified, up to length characters are extracted from the string. Otherwise the remainder of the string is extracted.</p> <p>Note: for the time being, the character as considered to be equivalent to bytes, which may not be appropriate for multi-byte encodings like UTF-8.</p> <div class="fragment"><div class="line">SELECT SUBSTR(<span class="stringliteral">'abcdef'</span>,1,2) FROM xxx --> 'ab'</div> <div class="line">SELECT SUBSTR('abcdef',4) FROM xxx --> 'def'</div> <div class="line">SELECT SUBSTR('abcdef',-2) FROM xxx --> 'ef'</div> </div><!-- fragment --><h3><a class="anchor" id="ogr_sql_fname_alias"></a> Using the field name alias</h3> <p>OGR SQL supports renaming the fields following the SQL92 specification by using the AS keyword according to the following example:</p> <div class="fragment"><div class="line">SELECT *, OGR_STYLE AS STYLE FROM polylayer</div> </div><!-- fragment --><p>The field name alias can be used as the last operation in the column specification. Therefore we cannot rename the fields inside an operator, but we can rename whole column expression, like these two:</p> <div class="fragment"><div class="line">SELECT COUNT(areacode) AS 'count' FROM polylayer</div> <div class="line">SELECT dollars/100.0 AS cents FROM polylayer</div> </div><!-- fragment --><h3><a class="anchor" id="ogr_sql_ftype_cast"></a> Changing the type of the fields</h3> <p>Starting with GDAL 1.6.0, OGR SQL supports changing the type of the columns by using the SQL92 compliant CAST operator according to the following example:</p> <div class="fragment"><div class="line">SELECT *, CAST(OGR_STYLE AS character(255)) FROM rivers</div> </div><!-- fragment --><p>Currently casting to the following target types are supported:</p> <ol> <li> character(field_length). By default, field_length=1. </li> <li> float(field_length) </li> <li> numeric(field_length, field_precision) </li> <li> integer(field_length) </li> <li> date(field_length) </li> <li> time(field_length) </li> <li> timestamp(field_length) </li> <li> geometry, geometry(geometry_type), geometry(geometry_type,epsg_code) </li> </ol> <p>Specifying the field_length and/or the field_precision is optional. An explicit value of zero can be used as the width for character() to indicate variable width. Conversion to the 'integer list', 'double list' and 'string list' OGR data types are not supported, which doesn't conform to the SQL92 specification.</p> <p>While the CAST operator can be applied anywhere in an expression, including in a WHERE clause, the detailed control of output field format is only supported if the CAST operator is the "outer most" operators on a field in the field definition list. In other contexts it is still useful to convert between numeric, string and date data types.</p> <p>Starting with OGR 1.11, casting a WKT string to a geometry is allowed. geometry_type can be POINT[Z], LINESTRING[Z], POLYGON[Z], MULTIPOINT[Z], MULTILINESTRING[Z], MULTIPOLYGON[Z], GEOMETRYCOLLECTION[Z] or GEOMETRY[Z].</p> <h2><a class="anchor" id="ogr_sql_where"></a> WHERE</h2> <p>The argument to the WHERE clause is a logical expression used select records 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 via <a class="el" href="classOGRLayer.html#acb2c6cc5fa3577df5be538284c1b0dde" title="Set a new attribute query. ">OGRLayer::SetAttributeFilter()</a>.</p> <p>In addition to the arithmetic and other functional operators available in expressions in the field selection clause of the SELECT statement, in the WHERE context logical operators are also available and the evaluated value of the expression should be logical (true or false).</p> <p>The available logical operators are <b>=</b>, <b>!=</b>, <b><></b>, <b><</b>, <b>></b>, <b><=</b>, <b>>=</b>, <b>LIKE</b> and <b>ILIKE</b>, <b>BETWEEN</b> and <b>IN</b>. Most of the operators are self explanitory, but is is worth nothing that <b> !=</b> is the same as <b><></b>, the string equality is case insensitive, but the <b><</b>, <b>></b>, <b><=</b> and <b>>=</b> operators <em>are</em> case sensitive. Both the LIKE and ILIKE operators are case insensitive.</p> <p>The value argument to the <b>LIKE</b> 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. An optional ESCAPE escape_char clause can be added so that the percent or underscore characters can be searched as regular characters, by being preceded with the escape_char.</p> <div class="fragment"><div class="line">String Pattern Matches?</div> <div class="line">------ ------- --------</div> <div class="line">Alberta ALB% Yes</div> <div class="line">Alberta _lberta Yes</div> <div class="line">St. Alberta _lberta No</div> <div class="line">St. Alberta %lberta Yes</div> <div class="line">Robarts St. %Robarts% Yes</div> <div class="line">12345 123%45 Yes</div> <div class="line">123.45 12?45 No</div> <div class="line">N0N 1P0 %N0N% Yes</div> <div class="line">L4C 5E2 %N0N% No</div> </div><!-- fragment --><p>The <b>IN</b> takes a list of values as it's argument and tests the attribute value for membership in the provided set.</p> <div class="fragment"><div class="line">Value Value Set Matches?</div> <div class="line">------ ------- --------</div> <div class="line">321 IN (456,123) No</div> <div class="line">"Ontario" IN ("Ontario","BC") Yes</div> <div class="line">"Ont" IN ("Ontario","BC") No</div> <div class="line">1 IN (0,2,4,6) No</div> </div><!-- fragment --><p>The syntax of the <b>BETWEEN</b> operator is "field_name BETWEEN value1 AND value2" and it is equivalent to "field_name >= value1 AND field_name <= value2".</p> <p>In addition to the above binary operators, there are additional operators for testing if a field is null or not. These are the <b>IS NULL</b> and <b>IS NOT NULL</b> operators.</p> <p>Basic field tests can be combined in more complicated predicates using logical operators include <b>AND</b>, <b>OR</b>, and the unary logical <b>NOT</b>. Subexpressions should be bracketed to make precedence clear. Some more complicated predicates are:</p> <div class="fragment"><div class="line">SELECT * FROM poly WHERE (prop_value >= 100000) AND (prop_value < 200000)</div> <div class="line">SELECT * FROM poly WHERE NOT (area_code LIKE "N0N%")</div> <div class="line">SELECT * FROM poly WHERE (prop_value IS NOT NULL) AND (prop_value < 100000)</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_where_limits"></a> WHERE Limitations</h2> <ol> <li> Fields must all come from the primary table (the one listed in the FROM clause). </li> <li> All string comparisons are case insensitive except for <b><</b>, <b>></b>, <b><=</b> and <b>>=</b>. </li> </ol> <h2><a class="anchor" id="ogr_sql_order_by"></a> ORDER BY</h2> <p>The <b>ORDER BY</b> 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:</p> <div class="fragment"><div class="line">SELECT * FROM <span class="keyword">property</span> WHERE class_code = 7 ORDER BY prop_value DESC</div> <div class="line">SELECT * FROM <span class="keyword">property</span> ORDER BY prop_value </div> <div class="line">SELECT * FROM <span class="keyword">property</span> ORDER BY prop_value ASC</div> <div class="line">SELECT DISTINCT zip_code FROM <span class="keyword">property</span> ORDER BY zip_code</div> </div><!-- fragment --><p>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.</p> <p>Sorting of string field values is case sensitive, not case insensitive like in most other parts of OGR SQL.</p> <h2><a class="anchor" id="ogr_sql_joins"></a> JOINs</h2> <p>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 <em>nation_id</em> column that can be used as a reference into a secondary <em>nation</em> table to fetch a nation name. A joined query might look like:</p> <div class="fragment"><div class="line">SELECT city.*, nation.name FROM city </div> <div class="line"> LEFT JOIN nation ON city.nation_id = nation.id</div> </div><!-- fragment --><p>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.</p> <p>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 <b>ON</b> clause of the join.</p> <p>Wildcards are also somewhat more involved. All fields from the primary table (<em>city</em> in this case) and the secondary table (<em>nation</em> in this case) may be selected using the usual <b>*</b> wildcard. But the fields of just one of the primary or secondary table may be selected by prefixing the asterix with the table name.</p> <p>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 <em>name, nation_id, nation.nation_id</em> and <em> nation.name</em> field if the city and nation tables both have the <em>nation_id</em> and <em>name</em> fieldnames.</p> <div class="fragment"><div class="line">SELECT * FROM city LEFT JOIN nation ON city.nation_id = nation.nation_id</div> </div><!-- fragment --><p>On the other hand if the nation table had a <em>continent_id</em> 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.</p> <div class="fragment"><div class="line">SELECT city.*, nation.* FROM city </div> <div class="line"> LEFT JOIN nation ON city.nation_id = nation.nation_id</div> </div><!-- fragment --><p>In the above examples, the <em>nation</em> table was found in the same datasource as the <em>city</em> 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 until the query result is no longer needed.</p> <div class="fragment"><div class="line">SELECT * FROM city </div> <div class="line"> LEFT JOIN <span class="stringliteral">'/usr2/data/nation.dbf'</span>.nation ON city.nation_id = nation.nation_id</div> </div><!-- fragment --><p>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 tables 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:</p> <div class="fragment"><div class="line">SELECT c.name, n.name FROM project_615_city c</div> <div class="line"> LEFT JOIN <span class="stringliteral">'/usr2/data/project_615_nation.dbf'</span>.project_615_nation n </div> <div class="line"> ON c.nation_id = n.nation_id</div> </div><!-- fragment --><p>It is possible to do multiple joins in a single query.</p> <div class="fragment"><div class="line">SELECT city.name, prov.name, nation.name FROM city</div> <div class="line"> LEFT JOIN province ON city.prov_id = province.id</div> <div class="line"> LEFT JOIN nation ON city.nation_id = nation.id</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_join_limits"></a> JOIN Limitations</h2> <ol> <li> Joins can be very expensive operations if the secondary table is not indexed on the key field being used. </li> <li> 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. </li> <li> 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. </li> <li> Datasource names for joined tables are evaluated relative to the current processes working directory, not the path to the primary datasource. </li> <li> 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. </li> </ol> <h1><a class="anchor" id="ogr_sql_union_all"></a> UNION ALL</h1> <p>(OGR >= 1.10.0)</p> <p>The SQL engine can deal with several SELECT combined with UNION ALL. The effect of UNION ALL is to concatenate the rows returned by the right SELECT statement to the rows returned by the left SELECT statement.</p> <div class="fragment"><div class="line">[(] SELECT field_list FROM first_layer [WHERE where_expr] [)]</div> <div class="line">UNION ALL [(] SELECT field_list FROM second_layer [WHERE where_expr] [)]</div> <div class="line">[UNION ALL [(] SELECT field_list FROM third_layer [WHERE where_expr] [)]]*</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_union_all_restrictions"></a> UNION ALL restrictions</h2> <p>The processing of UNION ALL in OGR differs from the SQL standard, in which it accepts that the columns from the various SELECT are not identical. In that case, it will return a super-set of all the fields from each SELECT statement.</p> <p>There is also a restriction : ORDER BY can only be specified for each SELECT, and not at the level of the result of the union.</p> <h1><a class="anchor" id="ogr_sql_special_fields"></a> SPECIAL FIELDS</h1> <p>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 precedence over the other fields with the same names in the data source.</p> <h2><a class="anchor" id="ogr_sql_fid"></a> FID</h2> <p>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 <b>FID</b>. The field wildcard expansions will not include the feature id, but it may be explicitly included using a syntax like:</p> <div class="fragment"><div class="line">SELECT FID, * FROM nation</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_geometry"></a> OGR_GEOMETRY</h2> <p>Some of the data sources (like MapInfo tab) can handle geometries of different types within the same layer. The <b>OGR_GEOMETRY</b> special field represents the geometry type returned by <a class="el" href="classOGRGeometry.html#aba109e1c53ce1452942f85eae66d88b8" title="Fetch WKT name for geometry type. ">OGRGeometry::getGeometryName()</a> and can be used to distinguish the various types. By using this field one can select particular types of the geometries like:</p> <div class="fragment"><div class="line">SELECT * FROM nation WHERE OGR_GEOMETRY=<span class="stringliteral">'POINT'</span> OR OGR_GEOMETRY=<span class="stringliteral">'POLYGON'</span></div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_geom_wkt"></a> OGR_GEOM_WKT</h2> <p>The Well Known Text representation of the geometry can also be used as a special field. To select the WKT of the geometry <b>OGR_GEOM_WKT</b> might be included in the select list, like:</p> <div class="fragment"><div class="line">SELECT OGR_GEOM_WKT, * FROM nation</div> </div><!-- fragment --><p>Using the <b>OGR_GEOM_WKT</b> and the <b>LIKE</b> operator in the WHERE clause we can get similar effect as using OGR_GEOMETRY:</p> <div class="fragment"><div class="line">SELECT OGR_GEOM_WKT, * FROM nation WHERE OGR_GEOM_WKT</div> <div class="line"> LIKE <span class="stringliteral">'POINT%'</span> OR OGR_GEOM_WKT LIKE <span class="stringliteral">'POLYGON%'</span></div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_geom_area"></a> OGR_GEOM_AREA</h2> <p>(Since GDAL 1.7.0)</p> <p>The <b>OGR_GEOM_AREA</b> special field returns the area of the feature's geometry computed by the <a class="el" href="classOGRSurface.html#a3b2c3125ec8c0b3a986e43cd1056f9e4" title="Get the area of the surface object. ">OGRSurface::get_Area()</a> method. For <a class="el" href="classOGRGeometryCollection.html">OGRGeometryCollection</a> and <a class="el" href="classOGRMultiPolygon.html">OGRMultiPolygon</a> the value is the sum of the areas of its members. For non-surface geometries the returned area is 0.0.</p> <p>For example, to select only polygon features larger than a given area:</p> <div class="fragment"><div class="line">SELECT * FROM nation WHERE OGR_GEOM_AREA > 10000000<span class="stringliteral">'</span></div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_style"></a> OGR_STYLE</h2> <p>The <b>OGR_STYLE</b> special field represents the style string of the feature returned by <a class="el" href="classOGRFeature.html#a54c179e53eb6eddb657dd392a87cccab" title="Fetch style string for this feature. ">OGRFeature::GetStyleString()</a>. By using this field and the <b>LIKE</b> operator the result of the query can be filtered by the style. For example we can select the annotation features as:</p> <div class="fragment"><div class="line">SELECT * FROM nation WHERE OGR_STYLE LIKE <span class="stringliteral">'LABEL%'</span></div> </div><!-- fragment --><h1><a class="anchor" id="ogr_sql_create_index"></a> CREATE INDEX</h1> <p>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 <em>fieldname = value</em>, which is what is used by the <b>JOIN</b> capability. To create an attribute index on the nation_id field of the nation table a command like this would be used:</p> <div class="fragment"><div class="line">CREATE INDEX ON nation USING nation_id</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_index_limits"></a> Index Limitations</h2> <ol> <li> Indexes are not maintained dynamically when new features are added to or removed from a layer. </li> <li> Very long strings (longer than 256 characters?) cannot currently be indexed. </li> <li> To recreate an index it is necessary to drop all indexes on a layer and then recreate all the indexes. </li> <li> Indexes are not used in any complex queries. Currently the only query the will accelerate is a simple "field = value" query. </li> </ol> <h1><a class="anchor" id="ogr_sql_drop_index"></a> DROP INDEX</h1> <p>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.</p> <div class="fragment"><div class="line">DROP INDEX ON nation USING nation_id</div> <div class="line">DROP INDEX ON nation</div> </div><!-- fragment --><h1><a class="anchor" id="ogr_sql_alter_table"></a> ALTER TABLE</h1> <p>(OGR >= 1.9.0)</p> <p>The following OGR SQL ALTER TABLE commands can be used.</p> <ol> <li> "ALTER TABLE tablename ADD [COLUMN] columnname columntype" to add a new field. Supported if the layer declares the OLCCreateField capability. </li> <li> "ALTER TABLE tablename RENAME [COLUMN] oldcolumnname TO newcolumnname" to rename an existing field. Supported if the layer declares the OLCAlterFieldDefn capability. </li> <li> "ALTER TABLE tablename ALTER [COLUMN] columnname TYPE columntype" to change the type of an existing field. Supported if the layer declares the OLCAlterFieldDefn capability. </li> <li> "ALTER TABLE tablename DROP [COLUMN] columnname" to delete an existing field. Supported if the layer declares the OLCDeleteField capability. </li> </ol> <p>The columntype value follows the syntax of the types supported by the CAST operator described above.</p> <div class="fragment"><div class="line">ALTER TABLE nation ADD COLUMN myfield integer</div> <div class="line">ALTER TABLE nation RENAME COLUMN myfield TO myfield2</div> <div class="line">ALTER TABLE nation ALTER COLUMN myfield2 TYPE character(15)</div> <div class="line">ALTER TABLE nation DROP COLUMN myfield2</div> </div><!-- fragment --><h1><a class="anchor" id="ogr_sql_drop_table"></a> DROP TABLE</h1> <p>(OGR >= 1.9.0)</p> <p>The OGR SQL DROP TABLE command can be used to delete a table. This is only supported on datasources that declare the ODsCDeleteLayer capability.</p> <div class="fragment"><div class="line">DROP TABLE nation</div> </div><!-- fragment --><h1><a class="anchor" id="ogr_sql_exec_sql"></a> ExecuteSQL()</h1> <p>SQL is executed against an <a class="el" href="classOGRDataSource.html">OGRDataSource</a>, not against a specific layer. The call looks like this:</p> <div class="fragment"><div class="line"><a class="code" href="classOGRLayer.html">OGRLayer</a> * <a class="code" href="classOGRDataSource.html#aa6acc228db6513784a56ce12334a8c33">OGRDataSource::ExecuteSQL</a>( <span class="keyword">const</span> <span class="keywordtype">char</span> *pszSQLCommand,</div> <div class="line"> <a class="code" href="classOGRGeometry.html">OGRGeometry</a> *poSpatialFilter,</div> <div class="line"> <span class="keyword">const</span> <span class="keywordtype">char</span> *pszDialect );</div> </div><!-- fragment --><p>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.</p> <p>The poSpatialFilter argument is a geometry used to select a bounding rectangle for features to be returned in a manner similar to the <a class="el" href="classOGRLayer.html#a0b4ab45cf97cbc470f0d60474d3e4169" title="Set a new spatial filter. ">OGRLayer::SetSpatialFilter()</a> method. It may be NULL for no special spatial restriction.</p> <p>The result of an ExecuteSQL() call is usually a temporary <a class="el" href="classOGRLayer.html">OGRLayer</a> 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.</p> <h1><a class="anchor" id="ogr_sql_non_ogr_sql"></a> Non-OGR SQL</h1> <p>All OGR drivers for database systems: <a href="/ogr/drv_mysql.html">MySQL</a>, PostgreSQL and PostGIS (<a href="/ogr/drv_pg.html">PG</a>), Oracle (<a href="/ogr/drv_oci.html">OCI</a>), <a href="/ogr/drv_sqlite.html">SQLite</a>, <a href="/ogr/drv_odbc.html">ODBC</a>, ESRI Personal Geodatabase (<a href="/ogr/drv_pgeo.html">PGeo</a>) and MS SQL Spatial (<a href="/ogr/drv_mssqlspatial.html">MSSQLSpatial</a>), override the <a class="el" href="classOGRDataSource.html#aa6acc228db6513784a56ce12334a8c33" title="Execute an SQL statement against the data store. ">OGRDataSource::ExecuteSQL()</a> function with dedicated implementation and, by default, pass the SQL statements directly 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. </p> </div></div><!-- contents --> <hr> Generated for GDAL by <a href="http://www.doxygen.org/index.html"><img src="doxygen.png" alt="doxygen" align="middle" border="0"></a> 1.8.5. </body> </html>