1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
|
<html>
<head>
<title>SQLite / Spatialite RDBMS</title>
</head>
<body bgcolor="#ffffff">
<h1>SQLite / Spatialite RDBMS</h1>
<p>OGR optionally supports spatial and non-spatial tables stored in SQLite 3.x
database files. SQLite is a "light weight" single file based RDBMS engine
with fairly complete SQL semantics and respectible performance.</p>
<p>The driver can handle "regular" SQLite databases, as well as Spatialite
databases (spatial enabled SQLite databases).</p>
<p>The SQLite database is essentially typeless, but the SQLite driver will
attempt to classify attributes field as text, integer or floating point
based on the contents of the first record in a table. None of the list
attribute field types existing in SQLite. Starting with OGR 1.10, datetime
field types are also handled.</p>
<p>SQLite databases often due not work well over NFS, or some other networked
file system protocols due to the poor support for locking. It is safest
to operate only on SQLite files on a physical disk of the local system.</p>
<p>SQLite is an optionally compiled in driver. It is not compiled in by default.</p>
<p>By default, SQL statements are passed directly to the SQLite database engine.
It's also possible to request the driver to handle SQL commands
with <a href="/ogr/ogr_sql.html">OGR SQL</a> engine,
by passing <strong>"OGRSQL"</strong> string to the ExecuteSQL()
method, as name of the SQL dialect.</p>
<p>Starting with OGR 1.8.0, the OGR_SQLITE_SYNCHRONOUS configuration option has been added.
When set to OFF, this issues a 'PRAGMA synchronous = OFF' command to the SQLite database.
This has the advantage of speeding-up some write operations (e.g. on EXT4 filesystems), but
at the expense of data safety w.r.t system/OS crashes. So use it carefully in
production environments and read the SQLite
<a href="http://www.sqlite.org/pragma.html#pragma_synchronous">related documentation</a>.</p>
<h2>"Regular" SQLite databases</h2>
<p>The driver looks for a geometry_columns table layed out as defined
loosely according to OGC Simple Features standards, particularly as defined
in <A href="http://trac.osgeo.org/fdo/wiki/FDORfc16">FDO RFC 16</a>. If
found it is used to map tables to layers.</P>
<p>If geometry_columns is not found, each table is treated as a layer. Layers
with a WKT_GEOMETRY field will be treated as spatial tables, and the
WKT_GEOMETRY column will be read as Well Known Text geometry.</p>
<p>If geometry_columns is found, it will be used to lookup spatial reference
systems in the spatial_ref_sys table.</P>
<p>While the SQLite driver supports reading spatial data from records, there is
no support for spatial indexing, so spatial queries will tend to be slow (use Spatialite for that).
Attributes queries may be fast, especially if indexes are built for
appropriate attribute columns using the "CREATE INDEX <indexname>
ON <tablename> ( <columnname> )" SQL command.</p>
<h2>Tables with multiple geometries</h2>
Starting with OGR 1.10, tables that have multiple geometry columns registered in geometry_columns can be used by OGR.
For such tables, there are as many OGR layers exposed as there are geometry columns. They are named
"table_name(geometry_column_name)".<p>
Note: this support is limited to read-only operations.<p>
<h2>REGEXP operator</h2>
By default, the REGEXP operator has no implementation in SQLite. With OGR >= 1.10 built against
the PCRE library, the REGEXP operator is available in SQL statements run by OGR.
<h2>VSI Virtual File System API support</h2>
(Require OGR >= 1.9.0 and SQLite >= 3.6.0)<p>
The driver supports reading and writing to files managed by VSI Virtual File System API, which include
"regular" files, as well as files in the /vsimem/ (read-write), /vsizip/ (read-only), /vsigzip/ (read-only), /vsicurl/ (read-only) domains.<p>
Note: for regular files, the standard I/O operations provided by SQLite are used, in order to benefit
from its integrity guarantees.<p>
<h2>Using the SpatiaLite library (Spatial extension for SQLite)</h2>
(Starting with GDAL 1.7.0)<p>
The SQLite driver can read and write SpatiaLite databases. Creating or updating a spatialite database requires
explicit linking against SpatiaLite library (version >= 2.3.1). Explicit linking against SpatiaLite library also
provides access to functions provided by this library, such as spatial indexes, spatial functions, etc...<p>
A few examples :
<pre>
# Duplicate the sample database provided with SpatiaLite
ogr2ogr -f SQLite testspatialite.sqlite test-2.3.sqlite -dsco SPATIALITE=YES
# Make a request with a spatial filter. Will work faster if spatial index has
# been created and explicit linking against SpatiaLite library.
ogrinfo testspatialite.sqlite Towns -spat 754000 4692000 770000 4924000
</pre>
<h2>Opening with 'VirtualShape:'</h2>
(Require OGR >= 1.9.0 and Spatialite support)<p>
It is possible to open on-the-fly a shapefile as a VirtualShape with Spatialite. The syntax to use for the
datasource is "VirtualShape:/path/to/shapefile.shp" (the shapefile must be a "real" file).<p>
This gives the capability to use the spatial operations of Spatialite (note that spatial indexes on virtual
tables are not available).<p>
<h2>The SQLite SQL dialect</h2>
Starting with OGR 1.10, the SQLite SQL engine can be used to run SQL queries
on any OGR datasource if using the <a href="ogr_sql_sqlite.html">SQLite SQL</a> dialect.
<h2>The VirtualOGR SQLite extension</h2>
Starting with OGR 1.10, the GDAL/OGR library can be loaded as a <a href="http://www.sqlite.org/lang_corefunc.html#load_extension">SQLite extension</a>.
The extension is loaded with the load_extension(gdal_library_name) SQL function, where gdal_library_name is
typically libgdal.so on Unix/Linux, gdal110.dll on Windows, etc..<p>
After the extension is loaded, a virtual table, corresponding to a OGR layer, can be created with one of
the following SQL statement :
<pre>
CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name);
CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name, update_mode);
CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name, update_mode, layer_name);
CREATE VIRTUAL TABLE table_name USING VirtualOGR(datasource_name, update_mode, layer_name, expose_ogr_style);
</pre>
where :
<ul>
<li><i>datasource_name</i> is the connexion string to any OGR datasource.</li>
<li><i>update_mode</i> = 0 for read-only mode (default value) or 1 for update mode.</li>
<li><i>layer_name</i> = the name of a layer of the opened datasource.</li>
<li><i>expose_ogr_style</i> = 0 to prevent the OGR_STYLE special from being displayed (default value) or 1 to expose it.</li>
</ul>
Note: <i>layer_name</i> does not need to be specified if the datasource has only one single layer.
<p>
From the sqlite3 console, a typical use case is :
<pre>
sqlite> SELECT load_extension('libgdal.so');
sqlite> SELECT load_extension('libspatialite.so');
sqlite> CREATE VIRTUAL TABLE poly USING VirtualOGR('poly.shp');
sqlite> SELECT *, ST_Area(GEOMETRY) FROM POLY;
215229.266|168.0|35043411||215229.265625
247328.172|179.0|35043423||247328.171875
261752.781|171.0|35043414||261752.78125
547597.188|173.0|35043416||547597.2109375
15775.758|172.0|35043415||15775.7578125
101429.977|169.0|35043412||101429.9765625
268597.625|166.0|35043409||268597.625
1634833.375|158.0|35043369||1634833.390625
596610.313|165.0|35043408||596610.3359375
5268.813|170.0|35043413||5268.8125
</pre>
<p>
Alternatively, you can use the <i>ogr_datasource_load_layers(datasource_name[, update_mode[, prefix]])</i>
function to automatically load all the layers of a datasource.
<pre>
sqlite> SELECT load_extension('libgdal.so');
sqlite> SELECT load_extension('libspatialite.so');
sqlite> SELECT ogr_datasource_load_layers('poly.shp');
1
sqlite> SELECT * FROM sqlite_master;
table|poly|poly|0|CREATE VIRTUAL TABLE "poly" USING VirtualOGR('poly.shp', 0, 'poly')
</pre>
Refer to the <a href="ogr_sql_sqlite.html">SQLite SQL</a> dialect for an overview of the capabilities of VirtualOGR tables.<p>
<h2>Creation Issues</h2>
<p>The SQLite driver supports creating new SQLite database files, or adding
tables to existing ones. Note that a new database file cannot be
created over an existing file.</p>
<h3>Database Creation Options</h3>
<ul>
<li><p><b>METADATA=yes/no</b>: This can be used to avoid creating the
geometry_columns and spatial_ref_sys tables in a new database. By default
these metadata tables are created when a new database is created.</p></li>
<li><p><b>SPATIALITE=yes/no</b>: (Starting with GDAL 1.7.0) Create the SpatiaLite flavour of the metadata
tables, which are a bit differ from the metadata used by this OGR driver and
from OGC specifications. Implies <b>METADATA=yes</b>.<br>
Please note: (Starting with GDAL 1.9.0) OGR must be linked against <i>libspatialite</i> in order to support
insert/write on SpatiaLite; if not, <i>read-only</i> mode is enforced.<br>
Attempting to perform any insert/write on SpatiaLite skipping the appropriate library support simply produces broken (corrupted) DB-files.<br>
Important notice: when the underlaying <i>libspatialite</i> is v.2.3.1 (or any previous
version) any Geometry will be casted to 2D [XY], because earlier versions of this library
are simply able to support 2D [XY] dimensions. Version 2.4.0 (or any subsequent) is required in order to support 2.5D [XYZ].</p></li>
<li><p><b>INIT_WITH_EPSG=yes/no</b>: (Starting with GDAL 1.8.0) Insert the content of the EPSG CSV files
into the spatial_ref_sys table. Defaults to NO for regular SQLite databases</b>.<br>
Please note: if <b>SPATIALITE=yes</b> and the underlaying <i>libspatialite</i> is v2.4 or v3.X,
<b>INIT_WITH_EPSG</b> is ignored; those library versions will unconditionally load the EPSG
dataset into the spatial_ref_sys table when creating a new DB (<i>self-initialization</i>). Starting
with libspatialite 4.0, INIT_WITH_EPSG defaults to YES, but can be set to NO.</p></li>
</ul>
<h3>Layer Creation Options</h3>
<ul>
<li><p><b>FORMAT=WKB/WKT/SPATIALITE</b>: Controls the format used for the
geometry column. By default WKB (Well Known Binary) is used. This is
generally more space and processing efficient, but harder to inspect or use in
simple applications than WKT (Well Known Text). SpatiaLite extension uses its
own binary format to store geometries and you can choose it as well. It will
be selected automatically when SpatiaLite database is opened or created with
<b>SPATIALITE=yes</b> option. SPATIALITE value is available starting with GDAL 1.7.0.</p></li>
<li><p><b>LAUNDER=yes/no</b>: Controls whether layer and field names will be
laundered for easier use in SQLite. Laundered names will be convered to lower
case and some special characters(' - #) will be changed to underscores. Default
to yes.</p></li>
<li><p><b>SPATIAL_INDEX=yes/no</b>: (Starting with GDAL 1.7.0) If the database is
of the SpatiaLite flavour, and if OGR is linked against libspatialite, this option
can be used to control if a spatial index must be created. Default to yes.</p></li>
<li><p><b>COMPRESS_GEOM=yes/no</b>: (Starting with GDAL 1.9.0) If the format of the
geometry BLOB is of the SpatiaLite flavour, this option can be used to control
if the compressed format for geometries (LINESTRINGs, POLYGONs) must be used. This
format is understood by Spatialite v2.4 (or any subsequent version). Default to no.
Note: when updating an existing Spatialite DB, the COMPRESS_GEOM configuration option
can be set to produce similar results for appended/overwritten features.</p></li>
<li><p><b>SRID=srid</b>: (Starting with GDAL 1.10) Used to force the SRID number of the
SRS associated with the layer. When this option isn't specified and that a SRS is
associated with the layer, a search is made in the spatial_ref_sys to find a match for the SRS,
and, if there is no match, a new entry is inserted for the SRS in the spatial_ref_sys table.
When the SRID option is specified, this search (and the eventual insertion of a new entry) will
not be done : the specified SRID is used as such.</p></li>
<li><p><b>COMPRESS_COLUMNS=column_name1[,column_name2, ...]</b>: (Starting with GDAL 1.10.0)
A list of (String) columns that must be compressed with ZLib DEFLATE algorithm. This might be beneficial
for databases that have big string blobs. However, use with care, since the value of such columns
will be seen as compressed binary content with other SQLite utilities (or previous OGR versions).
With OGR, when inserting, modifying or queryings compressed columns, compression/decompression is done transparently.
However, such columns cannot be (easily) queried with an attribute filter or WHERE clause.
Note: in table definition, such columns have the "VARCHAR_deflate" declaration type.</p></li>
</ul>
<h2>Performance hints</h2>
SQLite is a Transactional DBMS; while many INSERT statements are executed in close
sequence, BEGIN TRANSACTION and COMMIT TRANSACTION statements have to be invoked
appropriately in order to get optimal performance.
The default OGR behavior is to COMMIT a transaction every 200 inserted rows. This
value is surely too low for SQLite; and closing too much frequently the current
transaction causes severe performance degradation.
The <b>-gt</b> argument allows to explicitly set the number of rows for each transaction.
Explicitly defining <b>-gt 1024</b> usually ensures a noticeable performance boost;
defining an even bigger <b>-gt 65536</b> ensures optimal performance while
populating some table containing many hundredth thousand or million rows.<p>
SQLite usually has a very minimal memory foot-print; just about 20MB of RAM are
reserved to store the internal Page Cache [merely 2000 pages].
This value too may well be inappropriate under many circumstances, most notably when
accessing some really huge DB-file containing many tables related to a corresponding
Spatial Index.
Explicitly setting a much more generously dimensioned internal Page Cache may often
help to get a noticeably better performance.
Starting since GDAL 1.9.0 you can explicitly set the internal Page Cache size using the
configuration option <b>OGR_SQLITE_CACHE</b> <i>value</i> [<i>value</i> being measured in MB];
if your HW has enough available RAM, defining a Cache size as big as 512MB (or even 1024MB)
may sometimes help a lot in order to get better performance.<p>
Setting the <b>OGR_SQLITE_SYNCHRONOUS</b> configuration option to <i>OFF</i> might also
increase performance when creating SQLite databases (altough at the expense of integrity in case of
interruption/crash ).<p>
<h2>Credits</h2>
<ul>
<li>Development of the OGR SQLite driver was supported by
<a href="http://www.dmsolutions.ca/">DM Solutions Group</a> and
<a href="http://www.gomoos.org/">GoMOOS</a>.</li>
<li>Full support for SpatiaLite was contributed by A.Furieri, with funding from <a href="http://www.regione.toscana.it/">Regione Toscana<a>
</ul>
<h2>Links</h2>
<ul>
<li><a href="http://www.sqlite.org/">http://www.sqlite.org</a>: Main SQLite page.
<li> <a href="http://www.gaia-gis.it/spatialite/">http://www.gaia-gis.it/spatialite/</a>: SpatiaLite extension to SQLite.
<li> <A href="http://trac.osgeo.org/fdo/wiki/FDORfc16">FDO RFC 16</a>: FDO Provider for SQLite</li>
</ul>
</body>
</html>
|
