>>> from env_helper import info; info()

页面更新时间： 2023-04-15 20:01:30
运行环境：
    Linux发行版本: Debian GNU/Linux 12 (bookworm)
    操作系统内核: Linux-6.1.0-7-amd64-x86_64-with-glibc2.36
    Python版本: 3.11.2

8.2. 开始运行SpatiaLite命令行¶

SpatiaLite命令行有两种运行方式。一种是使用sqlite命令，这样打开数据库之后，需要加载其空间扩展；另一种是使用spatialite命令打开数据库，这种方式会自动加载SpatiaLite扩展文件，不需要再导入任何扩展文件，这样开始运行之后就直接可以使用空间特性。

这两种方式运行的命令唯一的区别是命令行提示符的不同：前者的提示符是 spatialite> ，后者的提示符是 sqlite> 。

下面假设使用的的数据库名称为 aha.db ，这个文件现在是不存在的。

8.2.1. 使用spatialite命令打开数据库¶

我们先看一下直接使用 spatialite命令打开数据库的方法，例如：

spatialite aha.db

然后会生成下面的欢迎信息，并进行spatialite交互环境：

bk@g:/opt/gdata$ spatialite aha.db
SpatiaLite version ..: 4.1.1    Supported Extensions:
    - 'VirtualShape'    [direct Shapefile access]
    - 'VirtualDbf'      [direct DBF access]
    - 'VirtualXL'       [direct XLS access]
    - 'VirtualText'     [direct CSV/TXT access]
    - 'VirtualNetwork'  [Dijkstra shortest path]
    - 'RTree'       [Spatial Index - R*Tree]
    - 'MbrCache'        [Spatial Index - MBR cache]
    - 'VirtualSpatialIndex' [R*Tree metahandler]
    - 'VirtualXPath'    [XML Path Language - XPath]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
SQLite version ......: 3.8.7.1
Enter ".help" for instructions
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
spatialite>

当SQLite开始一个新的任务时，如果你需要的数据库不存在（即现在使用的 aha.db），它将创建一个新的。

现在尝试查看一下这个新的数据库中有哪些表，使用 .tables 命令：

spatialite> .tables
the SPATIAL_REF_SYS table already contains some row(s)
SpatialIndex                        vector_layers_auth
geom_cols_ref_sys                   vector_layers_field_infos
geometry_columns                    vector_layers_statistics
geometry_columns_auth               views_geometry_columns
geometry_columns_field_infos        views_geometry_columns_auth
geometry_columns_statistics         views_geometry_columns_field_infos
geometry_columns_time               views_geometry_columns_statistics
spatial_ref_sys                     virts_geometry_columns
spatialite_history                  virts_geometry_columns_auth
sql_statements_log                  virts_geometry_columns_field_infos
vector_layers                       virts_geometry_columns_statistics

.tables指令引起SQLite列出目前数据库中包含的所有的表。正如你看到的，这是一个全新的数据库，但里面已经初始化了一些地理空间相关的表，这些表提供了空间数据库的元信息，如地理空间参考等。

8.2.2. 使用sqlite命令打开数据库¶

spatialite完全支持SQLite。但是有时候，可能需要使用 sqlite命令来打开数据库。在这种情况下，想使用数据库的空间特性，需要加载 SQLite 的空间扩展模块。

首先删除掉刚才生成的 aha.db，然后使用sqlite3，执行以下命令：

sqlite3 aha.db

你应该收到如下信息：

bk@g:/opt/gdata$ sqlite3 aha.db
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite>

SQLite开始运行，并连接到了 aha.db 数据库。

要使用空间特性，若要进行一步可以导入SpatiaLite扩展文件：

sqlite> .load 'libspatialite.so.5'

上面是在 Debian Jessie的加载方式， Debian Wheezey文件的名称是 libspatialite.so.3，而命名在不同的系统中，文件名或版本号都可能会不一致，具体的文件名要根据实际的情况。

然后你会看到直接使用spatialite的信息。

上面是使用 SQLite 的内置命令加载文件，也可以使用 SQL 语句来加载：

sqlite> SELECT load_extension('libspatialite.so.5');

8.2.3. SQLite的内置命令¶

不管是用什么方式打开空间数据库，都可以使用SQLite的一些内置命令。

使用下面的语句设置输出的格式。

sqlite> .nullvalue NULL
sqlite> .headers on
sqlite> .mode list
sqlite>

这里是SQLite的一些选项，是些常规的操作选项。这里将 SQLite 的输出模式定义为 list ，可以修改成其他的选项试一下。想多了解更多，可以输入：

spatialite> .help

上面的命令会回显很多信息，可以找自己关心的具体来看。

SQLite 中，所有的命令是以英文句号 .开始的，因此SQLite将 .mode或 .headers 识别为“内置命令”而不是SQL 表达式。内置命令通常只是为SQLite设置选项或模式，不涉及到数据处理。

8.2.4. SpatiaLite 命令行终端中的基本SQL数据库查询用法¶

这一节我们来看一下基本的SQL语句用法。对于初次接触 SQL 的读者，可能需要先了解一下 SQL 的知识，掌握SQL的基本使用方法。

首先要准备一下数据。使用最通用的 Shape 文件作为源数据，生成进行实验使用的 SpatiaLite 数据库文件。转换的时候，使用的是GDAL/OGR 的工具 ogr2ogr。

ogr2ogr  -f SQLite -dsco SPATIALITE=YES x_china.db stats_county.shp -nlt multipolygon

开始使用 SQL¶

现在开始学习SQLite，在x_china.db 数据库上执行第一条SQL查询。

$ spatialite x_china.db
spatialite> .headers on
spatialite> select * from STATS_COUNTY limit 5;
OGC_FID|GEOMETRY|province|name|ename|popu
1||23.0|南充市|mohexian|66.0
2||23.0|塔河县|tahexian|10.0
3||15.0|额尔古纳右旗|geergunyouqi|17.0
4||23.0|呼玛县|humaxian|5.0
5||15.0|额尔古纳左旗|geergunzuoqi|8.0
5|Magliano Alfieri|1674|1|0|0|

SELECT是SQL中最常用的查询语句，上面的命令，从数据表 STATS_COUNTY 中，获取了最开始的5条（ LIMIT 5 ）记录的所有字段（* ）的信息。需要注意，所有的 SQL 语句，必须以半角分号结束，还需要注意，上面字段 GEOMETRY 输出的结果是空的，这个后面会进一步解释。

现在开始第二条SQL查询：

spatialite> SELECT name AS County, popu as Population FROM stats_county ORDER BY name LIMIT 5;
County|Population
丁青县|6.0
七台河市|49.0
万全县|21.0
万县市|0.0
万宁县|62.0

SQL的关键词是不区分大小写的；
SQL进行选择的时候可以选择要获取的列，并定义列的顺序，还可以使用AS子句来命名输出的字段；
SQL可以使用 ORDER 子句来对获取的记录进行排序。

8.2.5. 复杂一点的SQL查询¶

spatialite> select name, popu from stats_county WHERE popu > 500 order by popu DESC;
name|popu
上海市|1137.0
北京市|974.0
涪陵市|896.0
重庆市|896.0
武汉市|749.0
天津市|682.0
广州市|567.0

可以使用 WHERE 子句对查询进行条件限制，只有满足逻辑表达或条件的记录才会被选择出来。在上面的例子中，人口数目超过500万的城市被选择出来。
可以使用 DESC 子句对选择的结果进行降序排列。

8.2.6. SQL的复杂用法¶

我们看一下SQL查询的另一种模式：

spatialite> select COUNT(*) as '# 城镇',
   ...> MIN(popu) as '最少',
   ...> MAX(popu) as '最多',
   ...> SUM(popu) as '人口总数',
   ...> SUM(popu) / COUNT(*) as '城镇平均人口'
   ...> from stats_county;
# 城镇|最少|最多|人口总数|城镇平均人口
2390|0.0|1137.0|125322.0|52.4359832635983

SQL语句可以写到多行中。SQLite会把分号 ; 前面的语句当成一条指令来执行；
可以在SQL查询中用到的函数，如 COUNT()、SUM()、MIN()、MAX()，这些意义都是显而易见的，不再赘述；
更进一步地，在查询中进行计算。在上面的语句中，通过对 SUM()和 COUNT() 作除法，得到了城镇的平均人口。

注意，有效的SQL查询由简单的表达式和函数组成。

请看下面的例子：

spatialite> select (10 - 11) * 2 as number, abs((10 - 11) * 2) as absolutevalue;
number|absolutevalue
-2|2

上面 (10 - 11) * 2 是表达式；
ABS() 是绝对值函数；
在这个例子中，并没有使用数据库字段或数据表。

现在，我们稍做一下调整，然后再次执行上述查询：

spatialite> select name, popu, HEX(GEOMETRY) from stats_county WHERE popu > 500 order by popu DESC;
name|popu|HEX(GEOMETRY)
上海市|1137.0|0001CAF80400DA2CBC76210B37419E72327E805B4E4112912 ... ...
北京市|974.0|0001CAF8040089536BC915292A418505E287E150524119F204 ... ...
涪陵市|896.0|0001CAF80400BC9DD1586C080741D466174E90C04B41AB76BF ... ...
重庆市|896.0|0001CAF8040088D147F6BF47FF4032E5513FA3BE4B4192D70E ... ...
武汉市|749.0|0001CAF804002ACF64BED4322941789BF197B9AF4C41E2C05E ... ...
天津市|682.0|0001CAF80400B27E94A949552E4172BCB19009025241C45EF6 ... ...
广州市|567.0|0001CAF804003ABD370486232A41566628A0539D4641A1DACA ... ...

上面的 HEX(GEOMETRY)的结果并没有完全被打印出来。

HEX() 函数返回 GEOMETRY 字段的二进制大对象的十六进制表达；
GEOMETRY 字段看起来像是空的，现在通过使用 HEX() 函数，我们知道里面包含有二进制数据；
GEOMETRY 保存了几何要素的坐标信息，使用二进制大对象（BLOB）的方式存储以提高效率，并由 SpatiaLite 进行内部编码；
SQLite 本身无法识别GEOMETRY，且无法进行操作。只有通过SpatiaLite扩展才能识别GEOMETRY。

你已经熟悉了基本的SQL操作，现在可以学习SpatiaLite了。想退出现有的SQLite交互模式，请输入：

spatialite> .quit

8.2.7. 导出GIS数据¶

在上一节中，我们将 Shapefile 转换成 SpatiaLite 数据库 x_china.db ，然后进行了一些说明。现在了解一下如何将 SpatiaLite 中的数据导出。一个 SpatiaLite 数据库中可能有多个地理空间数据表，在导出的时候，需要指明要导出的表名及数据类型。

首先是启动SpatiaLite的交互环境：

spatialite x_china.db

一个 SpatiaLite 数据库中可能存在多个地理空间数据表，在导出的时候，需要指明导出的表名及数据几何类型。

spatialite> .dumpshp stats_county Geometry xx_atowns utf-8 POLYGON
========
Dumping SQLite table 'stats_county' into shapefile at 'xx_atowns'

Exported 2390 rows into SHAPEFILE
========

SpatiaLite 的 .dumpshp 宏命令将整个地理空间数据表（具有几何字段）导出的 Shapefile。

SpatiaLite 的宏命令具有位置参数，下面将对各个参数进行解释：

参数 #1 表示要导出的地理空间数据表的名称；
参数 #2 表示要导出的数据的几何字段的名称，这个不一定是前面用的 GEOMETRY；
参数 #3 表示要生成的 Shapefile 的路径，相对路径或绝对路径。这个名称不能带有 .shp、.shx或 .dbf的后缀；
参数 #4 这个是导出到Shapefile时的字符串型属性的时候需要指明的字符名称（charset_name）。我导出的时候使用了 utf-8，在 Windows 10 中可以正常识别；简体中文Windows的编码页（Code Page）一般是 cp936；
参数 #5 是可选项，指明要输出的几何类型。如果声明的话，可以是 POINT、LINESTRING、 POLYGON或 MULTIPOINT。

位置参数（position parameter），函数或命令接收的参数，在解析的时候由参数给定的顺序来确定，被称为位置参数。

在输出的路径中执行 ls 命令，例如：

shape_highways.dbf  shape_regions.dbf  shape_towns.dbf
shape_highways.shp  shape_regions.shp  shape_towns.shp
shape_highways.shx  shape_regions.shx  shape_towns.shx

现在可以使用桌面GIS软件，如 QGIS 查看一下地理空间数据。现在可以看到的是图形，而不是数据库输出的 WKB 或 WKT 文本内容。

8.2.8. 创建一个新的SpatiaLite数据库，并进行发布¶

前面我们使用 OGR 工具将 Shapefile 文件转换成 SpatiaLite 数据库，然后又用 SpatiaLite 宏命令将数据库导出为 Shapefile。这一节我们要从头开始创建一个全新的、空的数据库，然后将 Shapefile 中的数据导入到里面去。

创建一个新的SQLite数据库非常简单，确定当前路径中没有 x_new_db.sqlite 文件，然后启动一个新的SQLite 会话，并输入下面的命令。

spatialite x_new_db.sqlite
spatialite> .nullvalue NULL
spatialite> .headers on
spatialite> .mode list

上面命令会生成一个新的 SpatiaLite 数据库，并初始化一些地理空间相关的表。

导入数据¶

现在你可以试着导入之前的shape文件来填充该数据库，例如：

spatialite> .loadshp xx_atowns  new_town utf-8
========
Loading shapefile at 'xx_atowns' into SQLite table 'new_town'

BEGIN;
CREATE TABLE "new_town" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT, "OGC_FID" INTEGER,
"province" DOUBLE, "name" TEXT, "ename" TEXT, "popu" DOUBLE);
SELECT AddGeometryColumn('new_town', 'Geometry', -1, 'MULTIPOLYGON', 'XY');
COMMIT;

Inserted 2390 rows into 'new_town' from SHAPEFILE
========

上面命令主要有两个步骤。第一步是根据Shapefile 的字段创建新的表；第二步是将数据文件导入到表里面。

SpatiaLite的.loadshp宏命令导入整体 shapefile 来创建一个数据库表，这个宏命令使用下面的位置参数。

参数 #1 是要导入的 Shapefile 的路径。如同导出的时候一样，这个 Shapefile 不能带有 .shp、verb|.shx|或 .dbf的后缀；
参数 #2 指明了要创建的表的名称。在导入之前的数据库中不应该有这个表；
参数 #3 声明 Shapefile 中字符串型字段的字符编码；
参数 #4 是可选的，指明数据的 SRID 的值。如果不指明的话，这个值默认是 -1 ；
参数 #5 是可选的，表示几何字段的名称，缺省的时候使用 GEOMETRY。

在这个例子中我们没有指定它，所以（默认情况下），上面第9行，几何要素字段使用了缺省名称 Geometry 。

这个宏命令调用了一系列的 SQL 语句，下面是其大概的意义。

CREATE TABLE SQL 命令用来创建一个新的表，并定义其字段；
向数据表中插入新的数据行的时候使用INSERT INTO 命令；
几何要素字段需要使用 SpatiaLite 的 AddGeometryColumn() 函数来单独定义；
BEGIN 与 COMMIT SQL 命令定义了一个事务。

一个单个（事务）的目的是定义一个（不可分割）的操作。整体的（事务）将成功或失败，如果由于任何原因出现错误，我们的数据库将保持不变。

查看导入的结果¶

运行 .tables 命令，数据库中已经有了 new_town 数据表。

PRAGMA table_info(table_name)指令会列出指定的表的所有的字段的信息。

返回下面的结果：

spatialite> pragma table_info(new_town);
cid|name|type|notnull|dflt_value|pk
PK_UID|INTEGER|0|NULL|1
OGC_FID|INTEGER|0|NULL|0
province|DOUBLE|0|NULL|0
name|TEXT|0|NULL|0
ename|TEXT|0|NULL|0
popu|DOUBLE|0|NULL|0
Geometry|MULTIPOLYGON|0|NULL|0

请注意原始列名可能截断。

spatialite> SELECT count(*), GeometryType(Geometry) FROM new_town GROUP BY GeometryType(Geometry);
count(*)|GeometryType(Geometry)
2390|MULTIPOLYGON

上面列出了数据表 new_town 中的几何类型与数目。

管理空间数据¶

现在我们必须导入最新的shape文件shape_towns。

这次我们将做一些改变来检查不同的方式导入shape文件。

spatialite> .loadshp xx_atowns new_town2 utf-8 32632 geom
========
Loading shapefile at 'xx_atowns' into SQLite table 'new_town2'

BEGIN;
CREATE TABLE "new_town2" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"OGC_FID" INTEGER,
"province" DOUBLE,
"name" TEXT,
"ename" TEXT,
"popu" DOUBLE);
SELECT AddGeometryColumn('new_town2', 'geom', 32632, 'MULTIPOLYGON', 'XY');
COMMIT;

Inserted 2390 rows into 'new_town2' from SHAPEFILE
========

.read宏命令执行一个SQL脚本。

你必须指定 charset_name 来编码SQL脚本文本；这次我们用ASCII字符串。

在执行 init_spatialite-2.3.sql 脚本后，你可以马上看到2个表已经被建立例如： spatial_ref_sys 和 geometry_columns 。

一个 geom_cols_ref_sys 类似表的对象已经被建立；这实际上不是一个真正的表；这一次我们使用5个形式为（ .loadshp ）的参数来指定一个SRID和几何列表名称。因此， .loadshp 宏函数使用一个单独的 AddGeometryColumn() 语句以创建几何列，这一点符合空间元数据的处理。

spatialite> SELECT * FROM geometry_columns;
f_table_name|f_geometry_column|geometry_type|coord_dimension|srid|spatial_index_enabled
new_town|geometry|6|2|-1|0
new_town2|geom|6|2|32632|0
spatialite> SELECT * FROM spatial_ref_sys WHERE Srid = 32632;
srid|auth_name|auth_srid|ref_sys_name|proj4text|srtext
32632|epsg|32632|WGS 84 / UTM zone 32N|+proj=utm +zone=32 +datum=WGS84 +units=m +no_defs|PROJCS["WGS 84 / UTM zone 32N",GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",9],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","32632"]]

现在空间元数据正确报道在表（NewTowns）中有一个空间列并且这个列必须包含 32632 SRID标识的几何点。你也可以检查识别YTM区32N的32632 SRID和有许多的大地参数来完全限定它。

spatialite> SELECT * FROM geom_cols_ref_sys;
f_table_name|f_geometry_column|geometry_type|coord_dimension|srid|auth_name|auth_srid|ref_sys_name|proj4text|srtext
new_town|geometry|6|2|-1|NONE|-1|Undefined - Cartesian||Undefined
new_town2|geom|6|2|32632|epsg|32632|WGS 84 / UTM zone 32N|+proj=utm +zone=32 +datum=WGS84 +units=m +no_defs|PROJCS["WGS 84 / UTM zone 32N",GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",9],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","32632"]]
spatialite> SELECT * FROM sqlite_master WHERE name = 'geom_cols_ref_sys';
type|name|tbl_name|rootpage|sql
view|geom_cols_ref_sys|geom_cols_ref_sys|0|CREATE VIEW geom_cols_ref_sys AS
SELECT f_table_name, f_geometry_column, geometry_type,
coord_dimension, spatial_ref_sys.srid AS srid,
auth_name, auth_srid, ref_sys_name, proj4text, srtext
FROM geometry_columns, spatial_ref_sys
WHERE geometry_columns.srid = spatial_ref_sys.srid

geom_cols_ref_sys 看上去是表，但实际上是一个视图。一个视图实际上是一个虚拟的表并且在内部定义为一个SQL 查询；如果一个视图实际上是一个真正的表，你可以直接在SQLite实施中查询它，你可以在一个视图中运用选择，但是插入，更新或者删除是被禁止的。（定义）视图是一个用来简化数据库查询的非常有用的方式，例如：

spatialite> SELECT * FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'NewTowns';

SQLite支持触发器，所以SpatiaLite在通过AddGeometryColumn（）创建一个新的几何列时，定义了oder中的4个触发器，以确保：

该列中包含的几何具有所有相同的几何类型
并且它们都是指相同的SRID
用于INSERT和UPDATE
所以你现在可以自信地依赖于你正在执行一个受到强烈约束和检查好的空间表的假设。

我们已经下载了2个表NewRegions和NewHighWays而没有与空间数据关联。

它永远不晚，我们可以现在恢复。

spatialite> UPDATE NewRegions SET Geometry = SetSrid(Geometry, 32632);
spatialite> SELECT RecoverGeometryColumn('NewRegions', 'Geometry',32632, 'MULTIPOLYGON', 2);
    RecoverGeometryColumn('NewRegions', 'Geometry',32632, 'MULTIPOLYGON', 2)
    1

首先，我们必须为NewRegions表中的几何要素，设定一个明确的SRID值。否则，任何试图关联空间数据的尝试都将失败，接着为了关联需要的列，我们可以试着使用RecoverGeometryColumn()功能。这个功能将会扫描整个表来检查是否在几何列的任何值都满足需要的类型和SRID。如果这个条件都正确，这个几何列将与空间列关联，并且触发也被创建。

spatialite> SELECT * FROM geometry_columns;
spatialite> SELECT name, tbl_name FROM sqlite_master
    WHERE type = 'trigger';

你可以看到，任何已经恢复在NewRegions表中的几何列与空间数据正确关联，它可能用途有限，但是你可以反转此操作。你必须简单地使用 DiscardGeometryColumn('NewRegions','Geometry') 。

8.1. 开源地理空间数据库

8.3. 在Python中使用SpatiaLite

Python与开源GIS