ogr_sql.html

gdal库的学习文档

</pre></div><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>
Sorting of string field values is case sensitive, not case insensitive like in most other parts of OGR SQL.<h3><a class="anchor" name="ogr_sql_joins">
JOINs</a></h3>
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"><pre class="fragment">SELECT city.*, nation.name FROM city 
     LEFT JOIN nation ON city.nation_id = nation.id
</pre></div><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>
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>
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>
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"><pre class="fragment">SELECT * FROM city LEFT JOIN nation ON city.nation_id = nation.nation_id
</pre></div><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"><pre class="fragment">SELECT city.*, nation.* FROM city 
    LEFT JOIN nation ON city.nation_id = nation.nation_id
</pre></div><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 untill the query result is no longer needed.<p>
<div class="fragment"><pre class="fragment">SELECT * FROM city 
  LEFT JOIN '/usr2/data/nation.dbf'.nation ON city.nation_id = nation.nation_id
</pre></div><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 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:<p>
<div class="fragment"><pre class="fragment">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
</pre></div><p>
It is possible to do multiple joins in a single query.<p>
<div class="fragment"><pre class="fragment">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
</pre></div><h3><a class="anchor" name="ogr_sql_join_limits">
JOIN Limitations</a></h3>
<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>
<h2><a class="anchor" name="ogr_sql_special_fields">
SPECIAL FIELDS</a></h2>
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.<h3><a class="anchor" name="ogr_sql_fid">
FID</a></h3>
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"><pre class="fragment">SELECT FID, * FROM nation
</pre></div><h3><a class="anchor" name="ogr_sql_geometry">
OGR_GEOMETRY</a></h3>
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#b9c97ddc4876f1aa563cc4c075bfb251">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"><pre class="fragment">SELECT * FROM nation WHERE OGR_GEOMETRY='POINT' OR OGR_GEOMETRY='POLYGON'
</pre></div><h3><a class="anchor" name="ogr_sql_geom_wkt">
OGR_GEOM_WKT</a></h3>
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"><pre class="fragment">SELECT OGR_GEOM_WKT, * FROM nation
</pre></div><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"><pre class="fragment">SELECT OGR_GEOM_WKT, * FROM nation WHERE OGR_GEOM_WKT
   LIKE 'POINT%' OR OGR_GEOM_WKT LIKE 'POLYGON%'
</pre></div><h3><a class="anchor" name="ogr_sql_style">
OGR_STYLE</a></h3>
The <b>OGR_STYLE</b> special field represents the style string of the feature returned by <a class="el" href="classOGRFeature.html#54c179e53eb6eddb657dd392a87cccab">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"><pre class="fragment">SELECT * FROM nation WHERE OGR_STYLE LIKE 'LABEL%'
</pre></div><h2><a class="anchor" name="ogr_sql_create_index">
CREATE INDEX</a></h2>
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"><pre class="fragment">CREATE INDEX ON nation USING nation_id
</pre></div><h3><a class="anchor" name="ogr_sql_index_limits">
Index Limitations</a></h3>
<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>
<h2><a class="anchor" name="ogr_sql_drop_index">
DROP INDEX</a></h2>
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"><pre class="fragment">DROP INDEX ON nation USING nation_id
DROP INDEX ON nation
</pre></div><h2><a class="anchor" name="ogr_sql_exec_sql">
ExecuteSQL()</a></h2>
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"><pre class="fragment"><a class="code" href="classOGRLayer.html">OGRLayer</a> * <a class="code" href="classOGRDataSource.html#a6acc228db6513784a56ce12334a8c33">OGRDataSource::ExecuteSQL</a>( <span class="keyword">const</span> <span class="keywordtype">char</span> *pszSQLCommand,
                                      <a class="code" href="classOGRGeometry.html">OGRGeometry</a> *poSpatialFilter,
                                      <span class="keyword">const</span> <span class="keywordtype">char</span> *pszDialect );
</pre></div><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>
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#0b4ab45cf97cbc470f0d60474d3e4169">OGRLayer::SetSpatialFilter()</a> method. It may be NULL for no special spatial restriction.<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.<h2><a class="anchor" name="ogr_sql_non_ogr_sql">
Non-OGR SQL</a></h2>
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.<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.5.1.
</body>
</html>