目录

上一个主题

6.2. 开始运行SpatiaLite命令行

下一个主题

6.4. 开始使用 SpatiaLite

关注公众号


常见问题

  1. Windows下的安装说明
  2. Jupyter免费在线实验环境
  3. 勘误与补充


>>> from helper import info; info()
页面更新时间: 2020-03-28 11:14:01
操作系统/OS: Linux-4.19.0-8-amd64-x86_64-with-debian-10.3 ;Python: 3.7.3

6.3. 关于 SQLite 的类型

6.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 0x7fe94c0e4e30>

从现在开始,以前使用的任何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 0x7fe94c0e4e30>

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

>>> cursor.execute("SELECT * FROM some_table")
>>> [print(rec) for rec in cursor]
('aaaa', 'bbbb', 'cccc', '1234')
('A', 'B', 'C', '1234.6789')
[None, None]
  1. SQLite不执行列值的类型检查。

  2. 您可以在每列中放置任何您想要的数据限制;不进行检查以确保符合在CREATE TABLE中声明的列类型。

  3. 你可能会爱或恨这个;它不是一个大问题,它是一个SQLite的显式“设计功能”。

6.3.2. 处理的方法

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

  1. CHECK子句允许您定义基于列的约束,从而实现类型的一致性。

  2. 在插入或更新列值之前,SQLite强制执行CHECKing。

  3. 适当时SQLite也可以执行隐式类型转换。

6.3.3. 导入Shapefile

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

例如:

>>> 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))"
>>>
>>> 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()

6.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))"
>>>
>>> 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()
>>>
>>> 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 TABLE gshhs USING INTEGER PRIMARY KEY (rowid=?)')
(6, 0, 0, 'LIST SUBQUERY 1')
(8, 6, 0, 'SCAN TABLE 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')

6.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 0x7fe928e2cf10>

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

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

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

AddGeometryColumn() error: unexpected metadata layout

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

>>> cursor = db.cursor()
>>> cursor.execute('SELECT InitSpatialMetaData();')
<sqlite3.Cursor at 0x7fe928db6030>

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

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

创建数据表及数据

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

>>> cursor.execute("DROP TABLE IF EXISTS cities")
<sqlite3.Cursor at 0x7fe928db6030>
>>> cursor.execute("CREATE TABLE cities (" +
>>>     "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
>>>     "name CHAR(255))")
<sqlite3.Cursor at 0x7fe928db6030>

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

>>> cursor.execute("SELECT AddGeometryColumn('cities', 'geom', 4326, 'POLYGON', 2)")
<sqlite3.Cursor at 0x7fe928db6030>

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

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

>>> cursor.execute("SELECT CreateSpatialIndex('cities', 'geom')")
<sqlite3.Cursor at 0x7fe928db6030>

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

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

读取数据

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

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