📄 ogr_sql.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1"><title>OGR: OGR SQL</title><link href="doxygen.css" rel="stylesheet" type="text/css"><link href="tabs.css" rel="stylesheet" type="text/css"></head><body><!-- Generated by Doxygen 1.5.1 --><div class="tabs"> <ul> <li><a href="index.html"><span>Main Page</span></a></li> <li><a href="annotated.html"><span>Classes</span></a></li> <li><a href="files.html"><span>Files</span></a></li> <li><a href="dirs.html"><span>Directories</span></a></li> <li><a href="pages.html"><span>Related Pages</span></a></li> </ul></div><h1><a class="anchor" name="ogr_sql">OGR SQL</a></h1>The <a class="el" href="classOGRDataSource.html">OGRDataSource</a> supports executing commands against a datasource via the <a class="el" href="classOGRDataSource.html#a6acc228db6513784a56ce12334a8c33">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.<h2><a class="anchor" name="ogr_sql_select">SELECT</a></h2>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:<p><div class="fragment"><pre class="fragment">SELECT * FROM polylayer</pre></div><p>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.<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"><pre class="fragment">SELECT eas_id, prop_value FROM polylayer</pre></div><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"><pre class="fragment">SELECT * from polylayer WHERE prop_value > 220000.0 ORDER BY prop_value DESC</pre></div><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"><pre class="fragment">SELECT COUNT(DISTINCT eas_id) FROM polylayer</pre></div><h3><a class="anchor" name="ogr_sql_flist_ops">Field List Operators</a></h3>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>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"><pre class="fragment">SELECT DISTINCT areacode FROM polylayer</pre></div><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 (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:<p><div class="fragment"><pre class="fragment">SELECT MIN(prop_value), MAX(prop_value), AVG(prop_value), SUM(prop_value), COUNT(prop_value) FROM polylayer WHERE prov_name = "Ontario"</pre></div><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 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:<p><div class="fragment"><pre class="fragment">SELECT COUNT(DISTINCT areacode) FROM polylayer</pre></div><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.<h4><a class="anchor" name="ogr_sql_flist_limits">Field List Limitations</a></h4><ol><li>Field arithmetic, and other binary operators are not supported, so you can't do something like:<p><div class="fragment"><pre class="fragment">SELECT prop_value / area FROM invoices</pre></div><p></li><li>There is no mechanism to rename a column. In fact, I don't even know how this is done in real SQL.<p></li><li>Lots of operators are missing.<p></li></ol><h3><a class="anchor" name="ogr_sql_where">WHERE</a></h3>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.<p>A WHERE clause consists of a set of attribute tests. Each basic test is of the form <b>fieldname operator value</b>. The <b>fieldname</b> is any of the fields in the source layer. The operator is one of <b>=</b>, <b>!=</b>, <b><></b>, <b><</b>, <b>></b>, <b><=</b>, <b>>=</b>, <b>LIKE</b> and <b>ILIKE</b> and <b>IN</b>.<p>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>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.<p><div class="fragment"><pre class="fragment"> 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</pre></div><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"><pre class="fragment"> 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</pre></div><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>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 precidence clear. Some more complicated predicates are:<p><div class="fragment"><pre class="fragment">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)</pre></div><h3><a class="anchor" name="ogr_sql_where_limits">WHERE Limitations</a></h3><ol><li>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. </li><li>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. </li><li>No arithmetric operations are supported. You can't test "WHERE (a+b) < 10" for instance. </li><li>All string comparisons are case insensitive except for <b><</b>, <b>></b>, <b><=</b> and <b>>=</b>. </li></ol><h3><a class="anchor" name="ogr_sql_order_by">ORDER BY</a></h3>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"><pre class="fragment">SELECT * FROM property WHERE class_code = 7 ORDER BY prop_value DESCSELECT * FROM property ORDER BY prop_value SELECT * FROM property ORDER BY prop_value ASCSELECT DISTINCT zip_code FROM property ORDER BY zip_code⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -