>>> 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
8.3. 关于 SQLite 的类型¶
8.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的显式“设计功能”。
8.3.2. 处理的方法¶
如果你需要对一些列执行一个有效的类型值检查， SQLite可以帮你做这样的事情：
CHECK子句允许您定义基于列的约束，从而实现类型的一致性。
在插入或更新列值之前，SQLite强制执行CHECKing。
适当时SQLite也可以执行隐式类型转换。
8.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()
8.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')
8.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)