EVOLUTION-MANAGER
Edit File: ogr_sql_sqlite.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: SQLite SQL dialect</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">SQLite SQL dialect </div> </div> </div><!--header--> <div class="contents"> <div class="textblock"><p>Since GDAL/OGR 1.10, the SQLite "dialect" can be used as an alternate SQL dialect to the <b><a href="ogr_sql.html">OGR SQL dialect</a></b>. This assumes that GDAL/OGR is built with support for SQLite (>= 3.6), and preferably with <a href="http://www.gaia-gis.it/spatialite/">Spatialite</a> support too to benefit from spatial functions.</p> <p>The SQLite dialect may be used with any OGR datasource, like the OGR SQL dialect. It is available through the <a class="el" href="classOGRDataSource.html#aa6acc228db6513784a56ce12334a8c33" title="Execute an SQL statement against the data store. ">OGRDataSource::ExecuteSQL()</a> method by specifying the pszDialect to "SQLITE". For the <a href="../ogrinfo.html">ogrinfo</a> or <a href="../ogr2ogr.html">ogr2ogr</a> utility, you must specify the "-dialect SQLITE" option.</p> <p>This is mainly aimed to execute SELECT statements, but, for datasources that support update, INSERT/UPDATE/DELETE statements can also be run.</p> <p>The syntax of the SQL statements is fully the one of the SQLite SQL engine. You can refer to the following pages: </p> <ul> <li> <a href="http://www.sqlite.org/lang_select.html">SELECT</a> documentation </li> <li> <a href="http://www.sqlite.org/lang_insert.html">INSERT</a> documentation </li> <li> <a href="http://www.sqlite.org/lang_update.html">UPDATE</a> documentation </li> <li> <a href="http://www.sqlite.org/lang_delete.html">DELETE</a> documentation </li> </ul> <h1><a class="anchor" id="ogr_sql_sqlite_select"></a> SELECT statement</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 SQLITE SELECT statement looks like this:</p> <div class="fragment"><div class="line">SELECT * FROM polylayer</div> </div><!-- fragment --><p>More complex statements can of course be used, including WHERE, JOIN, USING, GROUP BY, ORDER BY, sub SELECT, ...</p> <p>The table names that can be used are the layer names available in the datasource on which the ExecuteSQL() method is called.</p> <p>Similarly to OGRSQL, it is also possible to refer to layers of other datasources with the following syntax : "other_datasource_name"."layer_name".</p> <div class="fragment"><div class="line">SELECT p.*, NAME FROM poly p JOIN <span class="stringliteral">"idlink.dbf"</span>.<span class="stringliteral">"idlink"</span> il USING (eas_id)</div> </div><!-- fragment --><p>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...</p> <p>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.</p> <h2><a class="anchor" id="ogr_sql_sqlite_geometry"></a> Geometry field</h2> <p>The <b>GEOMETRY</b> special field represents the geometry of the feature returned by <a class="el" href="classOGRFeature.html#acc966ce8c10ae3ddf9f14c2736fdce9a" title="Fetch pointer to feature geometry. ">OGRFeature::GetGeometryRef()</a>. It can be explicitly specified in the result column list of a SELECT, and is automatically selected if the wildcard is used.</p> <p>For OGR layers that have a non-empty geometry column name (generally for RDBMS datasources), as returned by <a class="el" href="classOGRLayer.html#af68036c23622c954ce3a91861f22b724" title="This method returns the name of the underlying database column being used as the geometry column...">OGRLayer::GetGeometryColumn()</a>, the name of the geometry special field in the SQL statement will be the name of the geometry column of the underlying OGR layer.</p> <div class="fragment"><div class="line">SELECT EAS_ID, GEOMETRY FROM poly</div> <div class="line"></div> <div class="line">returns:</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):0</div> <div class="line"> EAS_ID (Real) = 168</div> <div class="line"> POLYGON ((479819.84375 4765180.5,479690.1875 4765259.5,[...],479819.84375 4765180.5))</div> </div><!-- fragment --><div class="fragment"><div class="line">SELECT * FROM poly</div> <div class="line"></div> <div class="line">returns:</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):0</div> <div class="line"> AREA (Real) = 215229.266</div> <div class="line"> EAS_ID (Real) = 168</div> <div class="line"> PRFEDEA (String) = 35043411</div> <div class="line"> POLYGON ((479819.84375 4765180.5,479690.1875 4765259.5,[...],479819.84375 4765180.5))</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_sqlite_style"></a> OGR_STYLE special field</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 --><h2><a class="anchor" id="ogr_sql_sqlite_spatialite"></a> Spatialite SQL functions</h2> <p>When GDAL/OGR is build with support for the <a href="http://www.gaia-gis.it/spatialite/">Spatialite</a> library, a lot of <a href="http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html">extra SQL functions</a>, in particular spatial functions, can be used in results column fields, WHERE clauses, etc....</p> <div class="fragment"><div class="line">SELECT EAS_ID, ST_Area(GEOMETRY) AS area FROM poly WHERE</div> <div class="line"> ST_Intersects(GEOMETRY, BuildCircleMbr(479750.6875,4764702.0,100))</div> <div class="line"></div> <div class="line">returns:</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):0</div> <div class="line"> EAS_ID (Real) = 169</div> <div class="line"> area (Real) = 101429.9765625</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):1</div> <div class="line"> EAS_ID (Real) = 165</div> <div class="line"> area (Real) = 596610.3359375</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):2</div> <div class="line"> EAS_ID (Real) = 170</div> <div class="line"> area (Real) = 5268.8125</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_sqlite_datasource_function"></a> OGR datasource SQL functions</h2> <p>The <b>ogr_datasource_load_layers(datasource_name[, update_mode[, prefix]])</b> function can be used to automatically load all the layers of a datasource as <a href="http://gdal.org/ogr/drv_sqlite.html">VirtualOGR tables</a>.</p> <div class="fragment"><div class="line">sqlite> SELECT load_extension(<span class="stringliteral">'libgdal.so'</span>);</div> <div class="line"></div> <div class="line">sqlite> SELECT load_extension(<span class="stringliteral">'libspatialite.so'</span>);</div> <div class="line"></div> <div class="line">sqlite> SELECT ogr_datasource_load_layers(<span class="stringliteral">'poly.shp'</span>);</div> <div class="line">1</div> <div class="line">sqlite> SELECT * FROM sqlite_master;</div> <div class="line">table|poly|poly|0|CREATE VIRTUAL TABLE <span class="stringliteral">"poly"</span> USING VirtualOGR(<span class="stringliteral">'poly.shp'</span>, 0, <span class="stringliteral">'poly'</span>)</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_sqlite_layer_function"></a> OGR layer SQL functions</h2> <p>The following SQL functions are available and operate on a layer name : <b>ogr_layer_Extent()</b>, <b>ogr_layer_SRID()</b>, <b>ogr_layer_GeometryType()</b> and <b>ogr_layer_FeatureCount()</b></p> <div class="fragment"><div class="line">SELECT ogr_layer_Extent(<span class="stringliteral">'poly'</span>), ogr_layer_SRID(<span class="stringliteral">'poly'</span>) AS srid,</div> <div class="line"> ogr_layer_GeometryType('poly') AS geomtype, ogr_layer_FeatureCount('poly') AS count</div> <div class="line"></div> <div class="line">returns:</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):0</div> <div class="line"> srid (Integer) = 40004</div> <div class="line"> geomtype (String) = POLYGON</div> <div class="line"> count (Integer) = 10</div> <div class="line"> POLYGON ((478315.53125 4762880.5,481645.3125 4762880.5,481645.3125 4765610.5,478315.53125 4765610.5,478315.53125 4762880.5))</div> </div><!-- fragment --><h2><a class="anchor" id="ogr_sql_sqlite_compression_functions"></a> OGR compression functions</h2> <p><b>ogr_deflate(text_or_blob[, compression_level])</b> returns a binary blob compressed with the ZLib deflate algorithm. See <a class="el" href="cpl__conv_8h.html#a09f1dd41a8fa1ebc36bc6c34c7c224a3" title="Compress a buffer with ZLib DEFLATE compression. ">CPLZLibDeflate()</a></p> <p><b>ogr_inflate(compressed_blob)</b> 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 <a class="el" href="cpl__conv_8h.html#a066abbb8471f29f521d19f2252e9959a" title="Uncompress a buffer compressed with ZLib DEFLATE compression. ">CPLZLibInflate()</a>.</p> <h2><a class="anchor" id="ogr_sql_sqlite_ogr_geocode_function"></a> OGR geocoding functions</h2> <p>The following SQL functions are available : <b>ogr_geocode(...)</b> and <b>ogr_geocode_reverse(...)</b>.</p> <p><b>ogr_geocode(name_to_geocode [, field_to_return [, option1 [, option2, ...]]])</b> 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 <a class="el" href="ogr__geocoding_8h.html#a2b0382ceb7e3935d7fe1da2adada564e" title="Runs a geocoding request. ">OGRGeocode()</a>. 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 <a class="el" href="ogr__geocoding_8h.html#ac3e12320a8046248b992fd0ce4731903" title="Creates a session handle for geocoding requests. ">OGRGeocodeCreateSession()</a> or <a class="el" href="ogr__geocoding_8h.html#a2b0382ceb7e3935d7fe1da2adada564e" title="Runs a geocoding request. ">OGRGeocode()</a>.</p> <p>This function internally uses the <a class="el" href="ogr__geocoding_8h.html#a2b0382ceb7e3935d7fe1da2adada564e" title="Runs a geocoding request. ">OGRGeocode()</a> API. Refer to it for more details.</p> <div class="fragment"><div class="line">SELECT ST_Centroid(ogr_geocode(<span class="stringliteral">'Paris'</span>))</div> <div class="line"></div> <div class="line">returns:</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):0</div> <div class="line"> POINT (2.342878767069653 48.85661793020374)</div> </div><!-- fragment --><div class="fragment"><div class="line">ogrinfo cities.csv -dialect sqlite -sql <span class="stringliteral">"SELECT *, ogr_geocode(city, 'country') AS country, ST_Centroid(ogr_geocode(city)) FROM cities"</span></div> <div class="line"></div> <div class="line">returns:</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):0</div> <div class="line"> id (Real) = 1</div> <div class="line"> city (String) = Paris</div> <div class="line"> country (String) = France métropolitaine</div> <div class="line"> POINT (2.342878767069653 48.85661793020374)</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):1</div> <div class="line"> id (Real) = 2</div> <div class="line"> city (String) = London</div> <div class="line"> country (String) = United Kingdom</div> <div class="line"> POINT (-0.109369427546499 51.500506667319407)</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):2</div> <div class="line"> id (Real) = 3</div> <div class="line"> city (String) = Rennes</div> <div class="line"> country (String) = France métropolitaine</div> <div class="line"> POINT (-1.68185153381778 48.111663929761093)</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):3</div> <div class="line"> id (Real) = 4</div> <div class="line"> city (String) = Strasbourg</div> <div class="line"> country (String) = France métropolitaine</div> <div class="line"> POINT (7.767762859150757 48.571233274141846)</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):4</div> <div class="line"> id (Real) = 5</div> <div class="line"> city (String) = New York</div> <div class="line"> country (String) = United States of America</div> <div class="line"> POINT (-73.938140243499049 40.663799577449979)</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):5</div> <div class="line"> id (Real) = 6</div> <div class="line"> city (String) = Berlin</div> <div class="line"> country (String) = Deutschland</div> <div class="line"> POINT (13.402306623451983 52.501470321410636)</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):6</div> <div class="line"> id (Real) = 7</div> <div class="line"> city (String) = Beijing</div> <div class="line"> country (String) = 中华人民共和国</div> <div class="line"> POINT (116.391195 39.9064702)</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):7</div> <div class="line"> id (Real) = 8</div> <div class="line"> city (String) = Brasilia</div> <div class="line"> country (String) = Brasil</div> <div class="line"> POINT (-52.830435216371839 -10.828214867369699)</div> <div class="line"></div> <div class="line"><a class="code" href="classOGRFeature.html">OGRFeature</a>(SELECT):8</div> <div class="line"> id (Real) = 9</div> <div class="line"> city (String) = Moscow</div> <div class="line"> country (String) = Российская Федерация</div> <div class="line"> POINT (37.367988106866868 55.556208255649558)</div> </div><!-- fragment --><p><b>ogr_geocode_reverse(longitude, latitude, field_to_return [, option1 [, option2, ...]])</b> where longitude, latitude is the coordinate to query. field_to_return must be a field name of the layer returned by <a class="el" href="ogr__geocoding_8h.html#abd49f38501af49461928469495861617" title="Runs a reverse geocoding request. ">OGRGeocodeReverse()</a> (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 <a class="el" href="ogr__geocoding_8h.html#ac3e12320a8046248b992fd0ce4731903" title="Creates a session handle for geocoding requests. ">OGRGeocodeCreateSession()</a> or <a class="el" href="ogr__geocoding_8h.html#abd49f38501af49461928469495861617" title="Runs a reverse geocoding request. ">OGRGeocodeReverse()</a>.</p> <p><b>ogr_geocode_reverse(geometry, field_to_return [, option1 [, option2, ...]])</b> is also accepted as an alternate syntax where geometry is a (Spatialite) point geometry.</p> <p>This function internally uses the <a class="el" href="ogr__geocoding_8h.html#abd49f38501af49461928469495861617" title="Runs a reverse geocoding request. ">OGRGeocodeReverse()</a> API. Refer to it for more details.</p> <h2><a class="anchor" id="ogr_sql_sqlite_spatial_index"></a> Spatialite spatial index</h2> <p>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.</p> <p>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 :</p> <div class="fragment"><div class="line">SELECT city_name, region_name FROM cities, regions WHERE</div> <div class="line"> ST_Area(ST_Intersection(cities.geometry, regions.geometry)) > 0 AND</div> <div class="line"> regions.rowid IN (</div> <div class="line"> SELECT pkid FROM idx_regions_geometry WHERE </div> <div class="line"> xmax >= MbrMinX(cities.geometry) AND xmin <= MbrMaxX(cities.geometry) AND</div> <div class="line"> ymax >= MbrMinY(cities.geometry) AND ymin <= MbrMaxY(cities.geometry))</div> </div><!-- fragment --><p>or more elegantly :</p> <div class="fragment"><div class="line">SELECT city_name, region_name FROM cities, regions WHERE</div> <div class="line"> ST_Area(ST_Intersection(cities.geometry, regions.geometry)) > 0 AND</div> <div class="line"> regions.rowid IN (</div> <div class="line"> SELECT rowid FROM SpatialIndex WHERE</div> <div class="line"> f_table_name = <span class="stringliteral">'regions'</span> AND search_frame = cities.geometry)</div> </div><!-- fragment --> </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>