46
46
by passing <strong>"OGRSQL"</strong> string to the ExecuteSQL()
47
47
method, as name of the SQL dialect.</p>
49
<p>Starting with OGR 1.8.0, the OGR_SQLITE_SYNCHRONOUS configuration option has been added.
50
When set to OFF, this issues a 'PRAGMA synchronous = OFF' command to the SQLite database.
51
This has the advantage of speeding-up some write operations (e.g. on EXT4 filesystems), but
52
at the expense of data safety w.r.t system/OS crashes. So use it carefully in
53
production environments and read the SQLite
54
<a href="http://www.sqlite.org/pragma.html#pragma_synchronous">related documentation</a>.</p>
56
<h2>VSI Virtual File System API support</h2>
58
(Require OGR >= 1.9.0 and SQLite >= 3.6.0)<p>
60
The driver supports reading and writing to files managed by VSI Virtual File System API, which include
61
"regular" files, as well as files in the /vsimem/ (read-write), /vsizip/ (read-only), /vsigzip/ (read-only), /vsicurl/ (read-only) domains.<p>
63
Note: for regular files, the standard I/O operations provided by SQLite are used, in order to benefit
64
from its integrity guarantees.<p>
49
66
<h2>Using the SpatiaLite library (Spatial extension for SQLite)</h2>
51
68
(Starting with GDAL 1.7.0)<p>
53
The SQLite driver can read and write SpatiaLite databases without needing
54
to load the SpatiaLite library. But if you configure GDAL/OGR with explicit linking
55
to SpatiaLite library (version >= 2.3), you can take advantage of all the extension
56
functions provided by this library, such as spatial indexes, spatial functions, etc...<p>
70
The SQLite driver can read and write SpatiaLite databases. Creating or updating a spatialite database requires
71
explicit linking against SpatiaLite library (version >= 2.3.1). Explicit linking against SpatiaLite library also
72
provides access to functions provided by this library, such as spatial indexes, spatial functions, etc...<p>
60
# Duplicate the sample database provided with SpatiaLite (does not need explicit linking with SpatiaLite)
76
# Duplicate the sample database provided with SpatiaLite
61
77
ogr2ogr -f SQLite testspatialite.sqlite test-2.3.sqlite -dsco SPATIALITE=YES
63
# Add a spatial index on the geometry column of the Towns table (needs explicit linking with SpatiaLite)
64
ogrinfo testspatialite.sqlite -sql "SELECT CreateSpatialIndex('Towns', 'geometry')"
66
# Make a request with a spatial filter (needs explicit linking with SpatiaLite)
67
ogrinfo testspatialite.sqlite \
68
-sql "SELECT Name, asText(geometry) FROM Towns WHERE MBRWithin(geometry, BuildMBR(754000, 4692000, 770000, 4924000))"
72
# Will work faster with spatial filter and explicit linking with SpatiaLite
79
# Make a request with a spatial filter. Will work faster if spatial index has
80
# been created and explicit linking against SpatiaLite library.
73
81
ogrinfo testspatialite.sqlite Towns -spat 754000 4692000 770000 4924000
84
<h2>Opening with 'VirtualShape:'</h2>
86
(Require OGR >= 1.9.0 and Spatialite support)<p>
88
It is possible to open on-the-fly a shapefile as a VirtualShape with Spatialite. The syntax to use for the
89
datasource is "VirtualShape:/path/to/shapefile.shp" (the shapefile must be a "real" file).<p>
91
This gives the capability to use the spatial operations of Spatialite (note that spatial indexes on virtual
92
tables are not available).<p>
77
94
<h2>Creation Issues</h2>
79
96
<p>The SQLite driver supports creating new SQLite database files, or adding
91
108
<li> <b>SPATIALITE=yes/no</b>: (Starting with GDAL 1.7.0) Create the SpatiaLite flavour of the metadata
92
109
tables, which are a bit differ from the metadata used by this OGR driver and
93
from OGC specifications. Implies <b>METADATA=yes</b>.
110
from OGC specifications. Implies <b>METADATA=yes</b>.<br>
111
Please note: (Starting with GDAL 1.9.0) OGR must be linked against <i>libspatialite</i> in order to support
112
insert/write on SpatiaLite; if not, <i>read-only</i> mode is enforced.<br>
113
Attempting to perform any insert/write on SpatiaLite skipping the appropriate library support simply produces broken (corrupted) DB-files.<br>
114
Important notice: when the underlaying <i>libspatialite</i> is v.2.3.1 (or any previous
115
version) any Geometry will be casted to 2D [XY], because earlier versions of this library
116
are simply able to support 2D [XY] dimensions. Version 2.4.0 (or any subsequent) is required in order to support 2.5D [XYZ].
118
<li> <b>INIT_WITH_EPSG=yes/no</b>: (Starting with GDAL 1.8.0) Insert the content of the EPSG CSV files
119
into the spatial_ref_sys table. Defaults to no</b>.<br>
120
Please note: if <b>SPATIALITE=yes</b> and the underlaying <i>libspatialite</i> is v.2.4 (or any subsequent
121
version) <b>INIT_WITH_EPSG</b> is ignored anyway;
122
any recent version of this library will unconditionally load the EPSG
123
dataset into the spatial_ref_sys table when creating a new DB (<i>self-initialization</i>).
97
126
<h3>Layer Creation Options</h3>
114
143
of the SpatiaLite flavour, and if OGR is linked against libspatialite, this option
115
144
can be used to control if a spatial index must be created. Default to yes.
146
<li> <b>COMPRESS_GEOM=yes/no</b>: (Starting with GDAL 1.9.0) If the format of the
147
geometry BLOB is of the SpatiaLite flavour, this option can be used to control
148
if the compressed format for geometries (LINESTRINGs, POLYGONs) must be used. This
149
format is understood by Spatialite v2.4 (or any subsequent version). Default to no.
150
Note: when updating an existing Spatialite DB, the COMPRESS_GEOM configuration option
151
can be set to produce similar results for appended/overwritten features.
155
<h2>Performance hints</h2>
156
SQLite is a Transactional DBMS; while many INSERT statements are executed in close
157
sequence, BEGIN TRANSACTION and COMMIT TRANSACTION statements have to be invoked
158
appropriately in order to get optimal performance.
159
The default OGR behavior is to COMMIT a transaction every 200 inserted rows. This
160
value is surely too low for SQLite; and closing too much frequently the current
161
transaction causes severe performance degradation.
162
The <b>-gt</b> argument allows to explicitly set the number of rows for each transaction.
163
Explicitly defining <b>-gt 1024</b> usually ensures a noticeable performance boost;
164
defining an even bigger <b>-gt 65536</b> ensures optimal performance while
165
populating some table containing many hundredth thousand or million rows.<p>
167
SQLite usually has a very minimal memory foot-print; just about 20MB of RAM are
168
reserved to store the internal Page Cache [merely 2000 pages].
169
This value too may well be inappropriate under many circumstances, most notably when
170
accessing some really huge DB-file containing many tables related to a corresponding
172
Explicitly setting a much more generously dimensioned internal Page Cache may often
173
help to get a noticeably better performance.
174
Starting since GDAL 1.9.0 you can explicitly set the internal Page Cache size using the
175
configuration option <b>OGR_SQLITE_CACHE</b> <i>value</i> [<i>value</i> being measured in MB];
176
if your HW has enough available RAM, defining a Cache size as big as 512MB (or even 1024MB)
177
may sometimes help a lot in order to get better performance.
122
181
<li>Development of the OGR SQLite driver was supported by
123
182
<a href="http://www.dmsolutions.ca/">DM Solutions Group</a> and
124
183
<a href="http://www.gomoos.org/">GoMOOS</a>.</li>
184
<li>Full support for SpatiaLite was contributed by A.Furieri, with funding from <a href="http://www.regione.toscana.it/">Regione Toscana<a>
125
189
<li><a href="http://www.sqlite.org/">http://www.sqlite.org</a>: Main SQLite page.
126
190
<li> <a href="http://www.gaia-gis.it/spatialite/">http://www.gaia-gis.it/spatialite/</a>: SpatiaLite extension to SQLite.
127
191
<li> <A href="http://trac.osgeo.org/fdo/wiki/FDORfc16">FDO RFC 16</a>: FDO Provider for SQLite</li>
added
removed
ogr/ogrsf_frmts/sqlite/drv_sqlite.html
