>>> from env_helper import info; info()

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

7.3. 关于 SQLite 的类型¶

7.3.1. SQLite类型¶

在离开本节之前，需要强调一些关于SQLite非常独特的管理列类型的基础概念。如下面示例：

>>> import sqlite3 as sqlite
>>> db = sqlite.connect(':memory:')
>>> db.enable_load_extension(True)
>>> # db.execute('SELECT load_extension("libspatialite.so.5")')  # In Debian 8
>>> db.execute('SELECT load_extension("mod_spatialite.so.7")')  # In Debian 9
>>> cursor = db.cursor()
>>> cursor.execute('SELECT InitSpatialMetaData();')
>>>
>>> cursor.execute("CREATE TABLE cities (" +
>>>     "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
>>>     "name CHAR(255))")
>>>
>>>
>>> cursor.execute('CREATE TABLE some_table ( N1 SMALLINT, N2 INTEGER NOT NULL, N3 DOUBLE, STR VARCHAR(4) NOT NULL);')

<sqlite3.Cursor at 0x7f46e9ab8940>

从现在开始，以前使用的任何DBMS都将以这种方式完成这些操作。

>>> cursor.execute("INSERT INTO some_table VALUES ('aaaa', 'bbbb', 'cccc', 1234)")
>>> cursor.execute("INSERT INTO some_table VALUES ('A', 'B', 'C', 1234.6789)")

<sqlite3.Cursor at 0x7f46e9ab8940>

刚刚插入没有任何数字列中的投诉字符串值。

>>> cursor.execute("SELECT * FROM some_table")
>>> [print(rec) for rec in cursor]

('aaaa', 'bbbb', 'cccc', '1234')
('A', 'B', 'C', '1234.6789')

[None, None]

SQLite不执行列值的类型检查。
您可以在每列中放置任何您想要的数据限制；不进行检查以确保符合在CREATE TABLE中声明的列类型。
你可能会爱或恨这个；它不是一个大问题，它是一个SQLite的显式“设计功能”。

7.3.2. 处理的方法¶

如果你需要对一些列执行一个有效的类型值检查， SQLite可以帮你做这样的事情：

CHECK子句允许您定义基于列的约束，从而实现类型的一致性。
在插入或更新列值之前，SQLite强制执行CHECKing。
适当时SQLite也可以执行隐式类型转换。

7.3.3. 导入Shapefile¶

导入Shapefile到数据库与我们程序中的其他版本差不多。

例如：

>>> import sqlite3 as sqlite
>>> db = sqlite.connect(':memory:')
>>> db.enable_load_extension(True)
>>> db.execute('SELECT load_extension("mod_spatialite.so.7")')  # In Debian 9
>>> cursor = db.cursor()
>>> cursor.execute('SELECT InitSpatialMetaData();')
>>>
>>>
>>> cursor.execute("DROP TABLE IF EXISTS gshhs")
>>> cursor.execute("CREATE TABLE gshhs (" +
>>> "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
>>> "level INTEGER)")
>>> cursor.execute("CREATE INDEX gshhs_level on gshhs(level)")
>>> cursor.execute("SELECT AddGeometryColumn('gshhs', 'geom', " +
>>> "4326, 'POLYGON', 2)")
>>> cursor.execute("SELECT CreateSpatialIndex('gshhs', 'geom')")
>>> db.commit()
>>>
>>>
>>> sql_tpl = "INSERT INTO gshhs (level, geom) VALUES (2, GeomFromText('{0}', 4326))"
>>>
>>> from osgeo import ogr
>>> fName = '/gdata/GSHHS_c.shp'
>>> shapefile = ogr.Open(fName)
>>> layer = shapefile.GetLayer(0)
>>> for i in range(layer.GetFeatureCount()):
>>>     feature = layer.GetFeature(i)
>>>     geometry = feature.GetGeometryRef()
>>>     wkt = geometry.ExportToWkt()
>>>     cursor.execute( sql_tpl.format(wkt))
>>>
>>> db.commit()

7.3.4. 在表中进行空间查询查找¶

我们已经生成了数据库。现在我们想从数据库中查找需要的多边形。下面是利用SpatiaLite来实现这一点的：

>>> import sqlite3 as sqlite
>>> db = sqlite.connect(':memory:')
>>> db.enable_load_extension(True)
>>> # db.execute('SELECT load_extension("libspatialite.so.5")')  # In Debian 8
>>> db.execute('SELECT load_extension("mod_spatialite.so.7")')  # In Debian 9
>>> cursor = db.cursor()
>>> cursor.execute('SELECT InitSpatialMetaData();')
>>>
>>>
>>> cursor.execute("DROP TABLE IF EXISTS gshhs")
>>> cursor.execute("CREATE TABLE gshhs (" +
>>> "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
>>> "level INTEGER)")
>>> cursor.execute("CREATE INDEX gshhs_level on gshhs(level)")
>>> cursor.execute("SELECT AddGeometryColumn('gshhs', 'geom', " +
>>> "4326, 'POLYGON', 2)")
>>> cursor.execute("SELECT CreateSpatialIndex('gshhs', 'geom')")
>>> db.commit()
>>>
>>>
>>> sql_tpl = "INSERT INTO gshhs (level, geom) VALUES (2, GeomFromText('{0}', 4326))"
>>>
>>> ogrfName = '/gdata/GSHHS_c.shp'
>>> shapefile = ogr.Open(fName)
>>> layer = shapefile.GetLayer(0)
>>> for i in range(layer.GetFeatureCount()):
>>>     feature = layer.GetFeature(i)
>>>     geometry = feature.GetGeometryRef()
>>>     wkt = geometry.ExportToWkt()
>>>     cursor.execute( sql_tpl.format(wkt))
>>>
>>> db.commit()

>>>
>>> import shapely.wkt
>>> LONDON = 'POINT(-0.1263 51.4980)'
>>> pt = shapely.wkt.loads(LONDON)
>>> cursor.execute("SELECT id,level,AsText(geom) " +
>>>      "FROM gshhs WHERE id IN " +
>>>      "(SELECT pkid FROM idx_gshhs_geom" +
>>>      " WHERE xmin <= ? AND ? <= xmax" +
>>>      " AND ymin <= ? and ? <= ymax) " +
>>>      "AND Contains(geom, GeomFromText(?, 4326))",
>>>      (pt.x, pt.x, pt.y, pt.y, LONDON))
>>> shoreline = None
>>> for id,level,wkt in cursor:
>>>     #if level == 1:
>>>     shoreline = wkt

在缺省的情形下， SpatiaLite进行查询的时候没有使用空间索引，因此我们在查找中不得不明确指出idx_gshhs_geom索引来优化查询过程。然而要注意空间查询的机制，SpatiaLite不是利用Shapely来提取多边形来确保该点涵盖其中；而是利用SpatiaLite的Contains()函数直接进行查找范围内整个多边形的检测。

保存结果¶

通过前面的诸多步骤，我们检索出了我们希望获取的结果。我们将结果保存起来以便更进一步的使用。保存的时候直接以纯文本的格式保存成 WKT 数据。

>>> f = open("uk-shoreline.wkt", "w")
>>> f.write(shoreline)
>>> f.close()

优化¶

空间查询的具体实现是复杂的，然而理论上会产生一个快速而准确的答案。

已安装的SpatiaLite版本，可能无法使用SQLite命令行。因此，让我们利用Python的EXPLAIN QUERY PLAN命令：

>>> cursor.execute("EXPLAIN QUERY PLAN " +
>>>     "SELECT id,level,AsText(geom) " +
>>>     "FROM gshhs WHERE id IN " +
>>>     "(SELECT pkid FROM idx_gshhs_geom" +
>>>     " WHERE xmin <= ? AND ? <= xmax" +
>>>     " AND ymin <= ? and ? <= ymax) " +
>>>     "AND Contains(geom, GeomFromText(?, 4326))",
>>>     (pt.x, pt.x, pt.y, pt.y, LONDON))
>>> for row in cursor:
>>>     print (row)

(2, 0, 0, 'SEARCH gshhs USING INTEGER PRIMARY KEY (rowid=?)')
(6, 0, 0, 'LIST SUBQUERY 1')
(8, 6, 0, 'SCAN idx_gshhs_geom VIRTUAL TABLE INDEX 2:B0D1B2D3')

运行上面的程序表明， SpatiaLite查询优化器将利用空间指数和数据表，并迅速识别边界盒的特征：

>>> for row in cursor:
>>>     print(row)
>>> (0, 0, 0, u'SEARCH TABLE gshhs USING INTEGER PRIMARY KEY (rowid=?)')
>>> (0, 0, 0, u'EXECUTE LIST SUBQUERY 1')
>>> (1, 0, 0, u'SCAN TABLE idx_gshhs_geom VIRTUAL TABLE INDEX 2:B0D1B2D3')

(1, 0, 0, 'SCAN TABLE idx_gshhs_geom VIRTUAL TABLE INDEX 2:B0D1B2D3')

7.3.5. 在Python中使用SpatiaLite¶

这一部分说明了如何在Python使用和操作SpatiaLite。

我们用SpatiaLite来重写一下这个程序。我们先创建一个数据库文件，然后 将spatialite-2.3.sql 初始化文件的内容导入这个数据库文件中。这样就能够创建和初始化空间数据库，并利用大量普遍应用的空间参数来进行普及 spatial_ref_sys 。

使用SpatiaLite¶

在许多方面，SpatiaLite都效仿PostGIS。在旧版本中，在用SpatiaLite之前，需要在数据库里加载“初始化文件”，并利用 AddGeometryColumn() 功能定义空间列。

在最新版本中，这个“初始化文件”不需要单独来做，只需加载扩展模块，则会自动完成初始化任务。

下面来创建SpatiaLite数据库和数据库表格。第一步连接数据库，并加载SpatiaLite扩展，如下：

>>> import sqlite3 as sqlite
>>> db = sqlite.connect(':memory:')
>>> db.enable_load_extension(True)
>>> # db.execute('SELECT load_extension("libspatialite.so.5")')  # In Debian 8
>>> db.execute('SELECT load_extension("mod_spatialite.so.7")')  # In Debian 9

<sqlite3.Cursor at 0x7f469a0d8f40>

注意，上面执行 SQL 语句时，返回了一些信息，但这些信息对于学习是没有用的。在下面的章节中，对于此类信息，不在文中出现，使用的时候要注意。

注，因为SQLite是无服务器数据库， myDatabase.db 数据库就像硬盘上的文件。如果在Mac操作系统上运行，可以略过 enable_load_extension ， load_extension 。

这个需要进行初始化。否则，会出现下面的错误。

AddGeometryColumn() error: unexpected metadata layout

另外， Python 2中有 pyspatialite 模块，可以简化一些操作。但是，这个模块最后一次更新时间是 2013 年，并且不支持 Python 3，所以在本书中就不做介绍了。使用 Python 2 的话可以了解一下。

>>> cursor = db.cursor()
>>> cursor.execute('SELECT InitSpatialMetaData();')

<sqlite3.Cursor at 0x7f469a0d8940>

Mac用户可以跳过db.enable_load_extension(...) 和 db.execute('SELECT load_extension(...)')函数。

运行初始化脚本将生成SpatiaLite需要的内部数据库表格，也会载入一系列空间参照，所以可以用 SRID值来定义空间参照。运行初始化脚本后，可以创建一个新的数据库表格来保存空间数据。就像PostGIS需要二步，一是利用标准的SQL语句来创建表格中的非空间数据。

创建数据表及数据¶

在数据库初始化之后，已经包含有一系列的表。现在我们来创建一个新的数据表，并看一下如何向表中插入几何数据。

>>> cursor.execute("DROP TABLE IF EXISTS cities")

<sqlite3.Cursor at 0x7f469a0d8940>

>>> cursor.execute("CREATE TABLE cities (" +
>>>     "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
>>>     "name CHAR(255))")

<sqlite3.Cursor at 0x7f469a0d8940>

然后，用SpatiaLite的AddGeometryColumn()函数来定义表格中的空间列。

>>> cursor.execute("SELECT AddGeometryColumn('cities', 'geom', 4326, 'POLYGON', 2)")

<sqlite3.Cursor at 0x7f469a0d8940>

4326是用来识别列属性的空间参照ID(SRID)，是用经纬度和WGS84来定义的空间参照。

可以用 CreateSpatialIndex() 函数来创建几何对象中的空间索引。如下：

>>> cursor.execute("SELECT CreateSpatialIndex('cities', 'geom')")

<sqlite3.Cursor at 0x7f469a0d8940>

已经创建了数据库表格，用 GeomFromText() 功能插入几何对象的属性。

>>> cursor.execute("INSERT INTO cities (name, geom)" + \
>>>        " VALUES ({0}, GeomFromText({1}, 4326))".format('"city"', '"wkt"'))

<sqlite3.Cursor at 0x7f469a0d8940>

读取数据¶

读取数据相对就简单一些，可以使用 SQL 的 SELECT 语句。需要注意几何要素的读取。这里有一个AsText() 函数可以将BLOB对象转换成可以理解的文本对象。

>>> for name,wkt in cursor:
>>>     print(name,wkt)

目录

上一个主题

下一个主题

常见问题