SQLite/spacealite关系数据库管理系统

司机简称

SQLite

生成依赖项

libsqlite3或libspacealite

OGR可选地支持存储在SQLite 3.x数据库文件中的空间表和非空间表。SQLite是一个“轻量级”的基于单文件的RDBMS引擎，具有相当完整的SQL语义和令人满意的性能。

The driver can handle "regular" SQLite databases, as well as Spatialite databases (spatial enabled SQLite databases). The type of an existing database can be checked from the SQLITE debug info value "OGR style SQLite DB found/ SpatiaLite DB found/SpatiaLite v4 DB found" obtained by running ogrinfo db.sqlite --debug on

从GDAL 2.2开始，SQLite驱动程序还可以使用 RasterLite2 raster coverages .

SQLite数据库本质上是无类型的，但SQLite驱动程序将尝试根据表中第一条记录的内容将属性字段分类为文本、整数或浮点。Datetime字段类型也会被处理。

从GDAL 2.2开始，“JSonStringList”、“JSonIntegerList”、“JSonInteger64List”和“JSonRealList”SQLite声明类型用于映射相应的OGR StringList、IntegerList、Integer64List和RealList类型。然后将字段值编码为JSon数组，并使用正确的CSV转义。

由于对锁定的支持不足，SQLite数据库通常无法在NFS或其他一些网络文件系统协议上正常工作。只在本地系统的物理磁盘上的SQLite文件上操作是最安全的。

SQLite是一个可选的编译内驱动程序。默认情况下不在中编译。

默认情况下，SQL语句直接传递给SQLite数据库引擎。也可以请求驱动程序使用 OGR SQL 发动机，通过 “OGRSQL” ExecuteSQL（）方法的字符串，作为SQL方言的名称。

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 related documentation.

Any SQLite pragma can be specified with the OGR_SQLITE_PRAGMA configuration option. The syntax is OGR_SQLITE_PRAGMA = "pragma_name=pragma_value[,pragma_name2=pragma_value2]*".

驱动程序功能

Supports Create()

This driver supports the GDALDriver::Create() operation

Supports Georeferencing

This driver supports georeferencing

Supports VirtualIO

This driver supports virtual I/O operations (/vsimem/, etc.)

“常规”SQLite数据库

驱动程序寻找一个geometry_columns表，该表按照OGC简单特性标准（特别是在 FDO RFC 16 . 如果找到，则用于将表映射到层。

如果找不到geometry_列，则将每个表视为一个图层。带有WKT U几何字段的图层将被视为空间表，WKT U几何列将被视为已知的文本几何。

如果找到geometry_列，它将用于在spatial_ref_sys表中查找空间参照系统。

虽然SQLite驱动程序支持从记录中读取空间数据，但不支持空间索引，因此空间查询往往很慢（为此使用Spatialite）。属性查询可能很快，特别是在使用“CREATE INDEX ON（）”SQL命令为适当的属性列构建索引时。

驱动程序还支持读取和写入以下非线性几何类型：循环字符串、复合曲线、曲线多边形、多曲线和多曲面。注意：对于Spatialite数据库，这是不正确的，因为当前的Spatialite版本不支持这些几何类型。

具有多个几何列的表

根据中描述的新API，可以创建、修改或读取具有多个几何列的图层 RFC 41:支持OGR中的多个几何字段

REGEXP运算符

默认情况下，REGEXP运算符在SQLite中没有实现。使用针对PCRE库的OGRbuilt，REGEXP运算符在OGR运行的SQL语句中可用。

使用SpatiaLite库（SQLite的空间扩展）

SQLite驱动程序可以读写SpatiaLite数据库。创建或更新spatialite数据库需要与spatialite库进行显式链接（版本>=2.3.1）。与空间库的显式链接还提供了对该库提供的空间索引、空间函数等功能的访问。。。

举几个例子：

# 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

以“VirtualShape:”打开

（需要空间支持）

有可能在飞行中打开一个形状文件作为一个虚拟的空间形状。用于数据源的语法是“VirtualShape:/path/to/shapefile.shp”（shapefile必须是“真实”文件）。

这样就可以使用Spatialite的空间操作（请注意，虚拟表上的空间索引不可用）。

SQLite SQL方言

如果使用 SQL SQLite方言 .

VirtualOGR SQLite扩展

GDAL/OGR库可以作为 SQLite extension . 扩展名使用load_extension（gdal_library_name）SQL函数加载，其中gdal_library_name通常是libgdal.so（在Unix/Linux上）、gdal110.dll（在Windows上）等。。

加载扩展后，可以使用以下SQL语句之一创建与OGR层对应的虚拟表：

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);

哪里：

  • datasource_name is the connection string to any OGR datasource.

  • update_mode =0表示只读模式（默认值），1表示更新模式。

  • layer_name =打开的数据源的层的名称。

  • expose_ogr_style =0以防止显示OGR_STYLE special（默认值）或1以公开它。

注： layer_name 如果数据源只有一个单层，则不需要指定。

在sqlite3控制台中，一个典型的用例是：

sqlite> SELECT load_extension('libgdal.so');

sqlite> SELECT load_extension('mod_spatialite.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

或者，您可以使用 [ogr_datasource_load_layers(datasource_name[, update_mode[, prefix]])] 函数自动加载数据源的所有层。

sqlite> SELECT load_extension('libgdal.so');

sqlite> SELECT load_extension('mod_spatialite.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')

参考 SQL SQLite方言 有关VirtualOGR表功能的概述。

创建问题

SQLite驱动程序支持创建新的SQLite数据库文件，或向现有文件添加表。

交易支持

驱动程序在数据库级别实现事务，根据 RFC 54：数据集事务

数据集打开选项

  • LIST_ALL_TABLES =是/否：这可以是“是”，以强制列出所有表，包括非空间表。

  • LIST_VIRTUAL_OGR =YES/NO：这可能是“YES”，以强制列出VirtualOGR虚拟表。仅应在受信任的数据源上启用此选项，以避免潜在的安全问题。

  • PRELUDE_STATEMENTS =字符串（GDAL>=3.2）。在SQLite3连接上发送的SQL语句先于任何其他语句。如果有多个语句，则必须用半列（；）符号分隔。此选项可能有助于 attach another database 并发出跨数据库请求。

    备注

    另一个数据库必须是此驱动程序可识别的类型，以便正确识别其几何体blob（因此通常不是GeoPackage blob）

Database creation options

  • METADATA=YES/NO ：这可用于避免在新数据库中创建几何列和空间参考系统表。默认情况下，这些元数据表是在创建新数据库时创建的。

  • SPATIALITE=YES/NO ：创建元数据表的SpatiaLite风格，它与此OGR驱动程序使用的元数据和OGC规范稍有不同。暗示 METADATA=YES .
    请注意：OGR必须与 锂空间分子 为了支持在SpatiaLite上插入/写入；如果不支持， read-only 强制模式。
    尝试在SpatiaLite上执行任何插入/写入操作时跳过相应的库支持只会生成损坏的数据库文件。
    重要提示：当 锂空间分子 is v.2.3.1（或任何先前版本）任何几何图形将被铸造为二维 [XY] ，因为此库的早期版本只支持二维 [XY] 尺寸。为了支持2.5D，需要版本2.4.0（或任何后续版本） [XYZ] .
  • INIT_WITH_EPSG=YES/NO ：将EPSG CSV文件的内容插入spatialu refu sys表。对于常规SQLite数据库，默认为NO。
    请注意：如果 SPATIALITE=YES 以及潜在的 锂空间分子 是v2.4或v3.X， INIT_WITH_EPSG 忽略；这些库版本将在创建新数据库时无条件地将EPSG数据集加载到spatial_ref_sys表中( self-initialization ). 从libspacealite4.0开始，INIT_with_EPSG默认为YES，但可以设置为NO。

图层创建选项

  • FORMAT=WKB/WKT/SPATIALITE ：控制用于几何列的格式。默认情况下使用WKB（众所周知的二进制文件）。这通常比WKT（众所周知的文本）更节省空间和处理效率，但在简单的应用程序中更难检查或使用。SpatiaLite扩展使用自己的二进制格式存储几何图形，您也可以选择它。当使用 SPATIALITE=YES 选项。空间值可用。

  • GEOMETRY_NAME ：默认情况下，OGR使用名为geometry的geometry列创建新表（如果FORMAT=WKT，则使用WKTu geometry）。如果要使用其他名称，可以随“几何图形名称”图层创建选项一起提供。

  • LAUNDER=YES/NO ：控制是否清除图层名和字段名以便于在SQLite中使用。清洗后的名称将转换为小写，一些特殊字符（'-#）将更改为下划线。默认为“是”。

  • SPATIAL_INDEX=YES/NO ：如果数据库是SpatiaLite风格的，并且OGR是针对libspatialite链接的，则可以使用此选项控制是否必须创建空间索引。默认为“是”。

  • COMPRESS_GEOM=YES/NO: If the format of the geometry BLOB is of the SpatiaLite flavor, 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.

  • SRID=srid ：用于强制与层关联的sr的SRID号。如果未指定此选项并且某个SRS与该层相关联，则会在空间u ref u sys中进行搜索，以查找SRS的匹配项，如果没有匹配项，则会在空间u ref u sys表中为SRS插入一个新条目。如果指定了SRID选项，则不会执行此搜索（以及最终插入新条目）：将使用指定的SRID。

  • [COMPRESS_COLUMNS=column_name1[,column_name2, ...]] ：必须用ZLib DEFLATE算法压缩的（字符串）列的列表。这对于具有大字符串blob的数据库可能是有益的。但是，请小心使用，因为这类列的值将与其他SQLite实用程序（或以前的OGR版本）一起被视为压缩的二进制内容。使用OGR，在插入、修改或查询压缩列时，压缩/解压缩是透明的。但是，使用属性过滤器或WHERE子句无法（轻松）查询这些列。注意：在表定义中，这类列具有“VARCHARu deflate”声明类型。

  • FID=fid_name ：要创建的FID列的名称。默认为OGCu FID。

  • STRICT=YES/NO: (SQLite >= 3.37 and GDAL >= 3.35). Defaults to NO. Whether the table should be created as a strict table, that is strong column type checking. This normally has little influence when operating only through OGR, since it has typed columns, but can help to strengthen database integrity when the database might be edited by external tools. Note that databases that contain STRICT tables can only be read by SQLite >= 3.37. The set of column data types supported in STRICT mode is: Integer, Integer64, Real, String, DateTime, Date and Time. The COMPRESS_COLUMNS option is ignored in strict mode.

配置选项

The following configuration options are available:

  • SQLITE_LIST_ALL_TABLES =YES/NO: Set to "YES" to list all tables (not just the tables listed in the geometry_columns table). This can also be done using the LIST_ALL_TABLES open option. Default is NO.

  • OGR_SQLITE_LIST_VIRTUAL_OGR =YES/NO* Set to "YES" to list VirtualOGR layers. Defaults to "NO" as there might be some security implications if a user is provided with a file and doesn't know that there are virtual OGR tables in it.

  • OGR_SQLITE_JOURNAL can be used to set the journal mode of the SQLite file, see also https://www.sqlite.org/pragma.html#pragma_journal_mode.

  • OGR_SQLITE_CACHE: see Performance hints.

  • OGR_SQLITE_SYNCHRONOUS: see Performance hints.

  • OGR_SQLITE_LOAD_EXTENSIONS =extension1,...,extensionN,ENABLE_SQL_LOAD_EXTENSION: (GDAL >= 3.5.0). Comma separated list of names of shared libraries containing extensions to load at database opening. If a file cannot be loaded directly, attempts are made to load with various operating-system specific extensions added. So for example, if "samplelib" cannot be loaded, then names like "samplelib.so" or "samplelib.dylib" or "samplelib.dll" might be tried also. The special value ENABLE_SQL_LOAD_EXTENSION can be used to enable the use of the SQL load_extension() function, which is normally disabled in standard builds of sqlite3. Loading extensions as a potential security impact if they are untrusted.

  • OGR_SQLITE_PRAGMA: with this option any SQLite pragma can be specified. The syntax is OGR_SQLITE_PRAGMA = "pragma_name=pragma_value[,pragma_name2=pragma_value2]*".

  • SQLITE_USE_OGR_VFS =YES enables extra buffering/caching by the GDAL/OGR I/O layer and can speed up I/O. More information here. Be aware that no file locking will occur if this option is activated, so concurrent edits may lead to database corruption.

性能提示

SQLite是一个事务性DBMS；虽然许多INSERT语句是按紧密顺序执行的，但必须适当地调用BEGIN TRANSACTION和COMMIT TRANSACTION语句（使用OGRu Lu StartTransaction（）/OGRu Lu CommitTransaction（））才能获得最佳性能。默认情况下，如果没有显式启动事务，SQLite将在每条语句上自动提交，这将很慢。如果使用ogr2ogr，它的默认行为是每插入20000行提交一个事务。这个 -gt 参数允许显式设置每个事务的行数。增加到 -gt 65536 在填充包含数十万行或一百万行的表时，可以确保最佳性能。

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. You can explicitly set the internal Page Cache size using the configuration option OGR_SQLITE_CACHE value [value 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.

Setting the OGR_SQLITE_SYNCHRONOUS configuration option to OFF might also increase performance when creating SQLite databases (although at the expense of integrity in case of interruption/crash ).

如果将许多源文件收集到同一个Spatialite表中，则可以更快地使用-lco spatial_index=NO初始化不带空间索引的表，并在追加所有数据后使用单独的命令创建空间索引。可以使用ogrinfo命令创建空间索引

ogr2ogr -f SQLite -dsco SPATIALITE=YES db.sqlite first.shp -nln the_table -lco SPATIAL_INDEX=NO
ogr2ogr -append db.sqlite second.shp -nln the_table
...
ogr2ogr -append db.sqlite last.shp -nln the_table
ogrinfo db.sqlite -sql "SELECT CreateSpatialIndex('the_table','GEOMETRY')"

如果数据库已完成编辑操作，则运行 VACUUM 查询以压缩和优化它。

ogrinfo db.sqlite -sql "VACUUM"

例子

  • 将非空间SQLite表转换为GeoPackage：

ogr2ogr \
  -f "GPKG" output.gpkg \
  input.sqlite \
  -sql \
  "SELECT
     *,
     MakePoint(longitude, latitude, 4326) AS geometry
   FROM
     my_table" \
  -nln "location" \
  -s_srs "EPSG:4326"

  • 在两个SQLite/Spatialite数据库之间执行联接：

ogrinfo my_spatial.db \
    -sql "SELECT poly.id, other.foo FROM poly JOIN other_schema.other USING (id)" \
    -oo PRELUDE_STATEMENTS="ATTACH DATABASE 'other.db' AS other_schema"

信用