关于 SQLite 的类型

SQLite类型

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

In [2]:
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);')
Out[2]:
<sqlite3.Cursor at 0x7f106441d2d0>

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

In [3]:
cursor.execute("INSERT INTO some_table VALUES ('aaaa', 'bbbb', 'cccc', 1234)")
cursor.execute("INSERT INTO some_table VALUES ('A', 'B', 'C', 1234.6789)")
Out[3]:
<sqlite3.Cursor at 0x7f106441d2d0>

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

In [4]:
cursor.execute("SELECT * FROM some_table")
[print(rec) for rec in cursor]
('aaaa', 'bbbb', 'cccc', '1234')
('A', 'B', 'C', '1234.6789')
Out[4]:
[None, None]
  1. SQLite不执行列值的类型检查。
  2. 您可以在每列中放置任何您想要的数据限制;不进行检查以确保符合在CREATE TABLE中声明的列类型。
  3. 你可能会爱或恨这个;它不是一个大问题,它是一个SQLite的显式“设计功能”。

处理的方法

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

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

导入Shapefile

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

例如:

In [6]:
# from pysqlite2 import dbapi2 as sqlite
# db = sqlite.connect("myDatabase2.sqlite")
# db.enable_load_extension(True)
# db.execute('SELECT load_extension("libspatialite.so.5")')
# cursor = db.cursor()
# cursor.execute('SELECT InitSpatialMetaData();')

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_l/GSHHS_l_L2.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()
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-6-47f48f92c65b> in <module>()
     31 fName = '/gdata/GSHHS_l/GSHHS_l_L2.shp'
     32 shapefile = ogr.Open(fName)
---> 33 layer = shapefile.GetLayer(0)
     34 for i in range(layer.GetFeatureCount()):
     35     feature = layer.GetFeature(i)

AttributeError: 'NoneType' object has no attribute 'GetLayer'
In [ ]:
 

在表中进行空间查询查找

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

In [1]:
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_l/GSHHS_l_L2.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()
In [2]:
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 数据。

In [4]:
f = open("uk-shoreline.wkt", "w")
f.write(shoreline)
f.close()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-4-4534a1efd36c> in <module>()
      1 f = open("uk-shoreline.wkt", "w")
----> 2 f.write(shoreline)
      3 f.close()

TypeError: write() argument must be str, not None

优化

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

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

In [5]:
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
  File "<ipython-input-5-120790ba16f3>", line 10
    print row
            ^
SyntaxError: Missing parentheses in call to 'print'

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

In [8]:
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')
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-8-2466b85ad23a> in <module>()
----> 1 for row in cursor:
      2     print(row)
      3 (0, 0, 0, u'SEARCH TABLE gshhs USING INTEGER PRIMARY KEY (rowid=?)')
      4 (0, 0, 0, u'EXECUTE LIST SUBQUERY 1')
      5 (1, 0, 0, u'SCAN TABLE idx_gshhs_geom VIRTUAL TABLE INDEX 2:B0D1B2D3')

NameError: name 'cursor' is not defined

在Python中使用SpatiaLite

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

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

使用SpatiaLite

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

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

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

In [1]:
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
Out[1]:
<sqlite3.Cursor at 0x7fe61c411b90>

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

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

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

AddGeometryColumn() error: unexpected metadata layout

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

In [2]:
cursor = db.cursor()
cursor.execute('SELECT InitSpatialMetaData();')
Out[2]:
<sqlite3.Cursor at 0x7f4f1467b110>

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

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

创建数据表及数据

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

In [3]:
cursor.execute("DROP TABLE IF EXISTS cities")
Out[3]:
<sqlite3.Cursor at 0x7f4f1467b110>
In [4]:
cursor.execute("CREATE TABLE cities (" +
    "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
    "name CHAR(255))")
Out[4]:
<sqlite3.Cursor at 0x7f4f1467b110>

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

In [5]:
cursor.execute("SELECT AddGeometryColumn('cities', 'geom', 4326, 'POLYGON', 2)")   
Out[5]:
<sqlite3.Cursor at 0x7f4f1467b110>

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

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

In [6]:
cursor.execute("SELECT CreateSpatialIndex('cities', 'geom')")
Out[6]:
<sqlite3.Cursor at 0x7f4f1467b110>

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

In [7]:
cursor.execute("INSERT INTO cities (name, geom)" + \
       " VALUES ({0}, GeomFromText({1}, 4326))".format('"city"', '"wkt"'))
Out[7]:
<sqlite3.Cursor at 0x7f4f1467b110>

读取数据

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

In [8]:
for name,wkt in cursor:
    print(name,wkt)