熟悉Geometry

在开始之前,先运行:

In [1]:
import sqlite3 as sqlite
conn = sqlite.connect("/gdata/test-2.3.sqlite")
conn.enable_load_extension(True)
conn.execute('SELECT load_extension("mod_spatialite.so.7")')
Out[1]:
<sqlite3.Cursor at 0x7fbb5d64a340>
In [2]:
cursor = conn.cursor()

现在执行第一个查询。

In [3]:
sql = 'SELECT name , peoples , AsText(Geometry) from Towns where peoples > 350000 order by peoples DESC'
cursor.execute(sql)
Out[3]:
<sqlite3.Cursor at 0x7fbb5d64a2d0>
In [4]:
for x in cursor:
    print(x)
('Roma', 2546804, 'POINT(788703.57 4645636.3)')
('Milano', 1256211, 'POINT(514820.49 5034534.56)')
('Napoli', 1004500, 'POINT(942636.1 4535272.55)')
('Torino', 865263, 'POINT(395553.63 4991768.9)')
('Palermo', 686722, 'POINT(880179.17 4227024.08)')
('Genova', 610307, 'POINT(492370.69 4918665.57)')
('Bologna', 371217, 'POINT(686263.23 4929405.15)')
('Firenze', 356118, 'POINT(681514.97 4848768.02)')

AsText() 是SpatiaLite 函数, 它返回 Geometry 字段的 WKT 值。 在前面, 我们使用 HEX() 函数返回无法查阅的二进制数据。 现在, AsText() 函数返回有用,且是易于理解的字符串。

点是最简单的 Geometry 类,且它只由一对 [X, Y] 坐标构成。

下面我们使用不同的方式来执行前面的查询。

In [5]:
sql = 'SELECT name , X(Geometry), Y(Geometry) FROM Towns WHERE peoples > 350000 ORDER BY peoples DESC;'
cursor.execute(sql)
Out[5]:
<sqlite3.Cursor at 0x7fbb5d64a2d0>
In [6]:
for x in cursor:
    print(x)
('Roma', 788703.57, 4645636.3)
('Milano', 514820.49, 5034534.56)
('Napoli', 942636.1, 4535272.55)
('Torino', 395553.63, 4991768.9)
('Palermo', 880179.17, 4227024.08)
('Genova', 492370.69, 4918665.57)
('Bologna', 686263.23, 4929405.15)
('Firenze', 681514.97, 4848768.02)

SpatiaLite的 X() 函数,返回点的 X 坐标。 Y() 函数返回点的 Y 坐标。

使用下面的 Geometry格式转换函数:

In [7]:
sql = "SELECT HEX(GeomFromText('POINT(10 20) '));"
cursor.execute(sql)
Out[7]:
<sqlite3.Cursor at 0x7fbb5d64a2d0>
In [8]:
for rec in cursor:
    print(rec)
('00010000000000000000000024400000000000003440000000000000244000000000000034407C0100000000000000000024400000000000003440FE',)
In [9]:
sql = "SELECT HEX(AsBinary(GeomFromText('POINT(10 20) ')));"
cursor.execute(sql)
Out[9]:
<sqlite3.Cursor at 0x7fbb5d64a2d0>
In [10]:
for rec in cursor:
    print(rec)
('010100000000000000000024400000000000003440',)
In [11]:
sql = "SELECT AsText(GeomFromWKB(X'010100000000000000000024400000000000003440'));"
cursor.execute(sql)
Out[11]:
<sqlite3.Cursor at 0x7fbb5d64a2d0>
In [12]:
for rec in cursor:
    print(rec)
('POINT(10 20)',)
  • SpatiaLite的函数 GeomFromText() 返回使用内部 BLOB 方式表示的几何图形;
  • 函数 AsBinary() 返回WKB(Well Known Binary)表示的几何图形;
  • 函数 GeomFromWKB() 将WKB的值,转换成对应的内部BLOB的值。

WKB是实现了OpenGIS规范的一种表示方法

GEOMETRY 类

开始之前,先运行:

In [13]:
import sqlite3 as sqlite
conn = sqlite.connect("/gdata/test-2.3.sqlite")
conn.enable_load_extension(True)
conn.execute('SELECT load_extension("mod_spatialite.so.7")')
Out[13]:
<sqlite3.Cursor at 0x7fbb5d64a650>
In [14]:
cursor = conn.cursor()

这一部分主要是探讨 OpenGIS 中的定义,SpatiaLite支持的不同 Geometry 类。 简单来讲,任何的 Geometry 类都是一种几何类型。

LINESTRING类型

了解了POINT 类,下面我们来熟悉一下 LINESTRING 类型的 简单的查询。

In [15]:
sql = "SELECT PK_UID, AsText(Geometry) FROM HighWays WHERE PK_UID = 2"
cursor.execute(sql)
Out[15]:
<sqlite3.Cursor at 0x7fbb5d64a570>
In [16]:
[print(rec) for rec in cursor]
(2, 'LINESTRING(671365.867442 4854173.770802, 671404.13073 4854162.864623)')
Out[16]:
[None]

LINESTRING 是另一个 GEOMETRY 类,并由许多点组成。 在上面你得到一个非常简单的 LINESTRING ,使用四个顶点来表示线。 在实际的GIS数据中,为数以千计的顶点组成的线计数并不简单。

为了更进一步了解,我们来看下面的例子。

In [17]:
sql = '''SELECT PK_UID, NumPoints(Geometry), GLength(Geometry) ,Dimension(Geometry),
  GeometryType(Geometry) FROM HighWays ORDER BY NumPoints(Geometry) DESC LIMIT 5'''
cursor.execute(sql)
Out[17]:
<sqlite3.Cursor at 0x7fbb5d64a570>
In [18]:
[print(x) for x in cursor]
(774, 6758, 94997.87213441564, 1, 'LINESTRING')
(775, 5120, 75453.12477267074, 1, 'LINESTRING')
(153, 4325, 69052.10246774448, 1, 'LINESTRING')
(205, 3109, 46831.323559640885, 1, 'LINESTRING')
(773, 2755, 60165.97761745972, 1, 'LINESTRING')
Out[18]:
[None, None, None, None, None]

SpatiaLite 的 NumPoint() 函数返回线几何要素的顶点的数目。 GLength() 函数返回以地图单位计算的线几何类型的几何长度。 Dimension() 函数返回任何一种几何类的维度(对线来讲是1)。 GeometryType() 函数返回任何 Geometry 类型的值。

下面我们来看更多的函数。

In [19]:
sql = '''SELECT PK_UID, NumPoints(Geometry),
        AsText(StartPoint(Geometry)), Y(PointN(Geometry, 2))
        FROM HighWays ORDER BY NumPoints(Geometry) DESC LIMIT 5'''
cursor = cursor.execute(sql)
[print(x) for x in cursor]
(774, 6758, 'POINT(632090.156998 4835616.546126)', 4835625.748753577)
(775, 5120, 'POINT(663292.190654 4795627.307765)', 4795626.489419861)
(153, 4325, 'POINT(668247.593086 4862272.349444)', 4862273.561966714)
(205, 3109, 'POINT(671613.424233 4854121.472532)', 4854129.554368173)
(773, 2755, 'POINT(619601.675367 4855174.599496)', 4855174.743988363)
Out[19]:
[None, None, None, None, None]

SpatiaLite 的 StartPoint() 函数返回线状几何要素的第一个点。 EndPoint() 函数返回线状几何要素的最后一个点。 PointN() 函数返回选中的顶点和返回点状要素,每个点通过相对索引来标志; 第一个点通过索引值1来标识, 第二个点通过索引值2来标识,以下类推。

你可以通过GEOMETRY类将内部函数的返回值作为参数传递给外部函数,进而查看大量的 SpatiaLite 函数。

多边形类型

In [20]:
sql = 'SELECT name, AsText(Geometry) FROM Regions WHERE PK_UID = 52'
cursor.execute(sql)
Out[20]:
<sqlite3.Cursor at 0x7fbb5d64a570>
In [21]:
[print(x) for x in cursor]
('EMILIA-ROMAGNA', 'MULTIPOLYGON(((761808.155309 4966649.458816, 762432.549628 4966393.94736, 764168.27812 4966137.407299, 763486.212544 4966081.379442, 762462.485356 4966252.975485, 761808.155309 4966649.458816)))')
Out[21]:
[None]

POLYGON 是另外一个GEOMETRY类。 POLYGON 是一个非常简单的多边形,且只有此部的环(没有内部的洞)。 要注意,POLYGON可以包含何意数据的洞, 通过内部环分隔开来。

外部环是一个简单的 LINESTRING (内部洞也是 LINESTRING )。 注意 POLYGON 是一个闭合的几何类型, POLYGON的第一个点与最后一个点的位置是完全相同的。

In [22]:
sql = '''SELECT PK_UID, Area(Geometry), AsText(Centroid(Geometry)),
             Dimension(Geometry), GeometryType(Geometry) FROM Regions
             ORDER BY Area(Geometry) DESC LIMIT 5'''
cursor.execute(sql)
Out[22]:
<sqlite3.Cursor at 0x7fbb5d64a570>
In [23]:
[print(x) for x in cursor]
(101, 25779695636.913013, 'POINT(955762.441021 4173082.511675)', 2, 'MULTIPOLYGON')
(105, 25391974998.07893, 'POINT(414613.865386 4990738.194216)', 2, 'MULTIPOLYGON')
(106, 24069782849.521782, 'POINT(560092.709693 5052167.502959)', 2, 'MULTIPOLYGON')
(74, 23962322618.286892, 'POINT(502694.575482 4437256.791656)', 2, 'MULTIPOLYGON')
(53, 22657959720.099735, 'POINT(672979.667565 4814145.593235)', 2, 'MULTIPOLYGON')
Out[23]:
[None, None, None, None, None]

在前面我们使用了 SpatiaLite 的 Dimension() 函数与 GeometryType() 函数。 对于 POLYGON 类型,这两个函数的意义与其他都是一样的。 SpatiaLite的 Area() 函数返回多边形的几何面积, Centroid() 函数返回多边形几何要素的质心。

In [24]:
sql = '''SELECT PK_UID, NumInteriorRings(Geometry),
        NumPoints(ExteriorRing(Geometry)),
        NumPoints(InteriorRingN(Geometry, 1))
        FROM regions ORDER BY NumInteriorRings(Geometry) DESC LIMIT 5'''
cursor.execute(sql)
Out[24]:
<sqlite3.Cursor at 0x7fbb5d64a570>
In [25]:
[print(x) for x in cursor]
(55, 1, 602, 9)
(1, 0, 6, None)
(2, 0, 12, None)
(3, 0, 20, None)
(4, 0, 7, None)
Out[25]:
[None, None, None, None, None]

SpatiaLite 的 ExteriorRing() 函数返回给定几何要素的外部线环。 任何有效的多边形要素必须要有一个外部线环,并且这个线环是闭合的。 SpatiaLite 的 NumInteriorRings() 函数返回多边形中内部洞的数目可以是一个有效的多边形可以有一些洞,也可以没有。

SpatiaLite 的 InteriorRingN() 函数以 LINESTRING 的格式返回第 N 个内部洞。 每个洞都以相对索引来标识:第一个的索引值是1, 第二个的索引值是2,其余依次类推。

ring 一个 LINESTRING, 所以我们可以使用 NumPoints()函数来获取相关的顶点的数目。 在无效的要素上,则返回结果为NULL

查看多边形的坐标

In [26]:
sql = '''SELECT AsText(InteriorRingN(Geometry, 1)),
    AsText(PointN(InteriorRingN(Geometry, 1), 4)),
    X(PointN(InteriorRingN(Geometry, 1), 5))
    FROM Regions WHERE PK_UID = 55'''
cursor.execute(sql)
Out[26]:
<sqlite3.Cursor at 0x7fbb5d64a570>
In [27]:
[print(x) for x in cursor]
('LINESTRING(756881.706704 4850692.62625, 760361.595005 4852743.267975, 759582.880944 4855493.610807, 757549.382306 4855414.551183, 755734.189332 4856112.118807, 755020.910885 4855996.887913, 754824.031873 4854723.577451, 756021.000385 4850937.420842, 756881.706704 4850692.62625)', 'POINT(757549.382306 4855414.551183)', 755734.1893322569)
Out[27]:
[None]

我们已经在前面遇到过用法了。

对于POLYGON来说,它变得更加乏味,但仍然容易理解。 例如,为了获得最后一列,我们使用了InteriorRingN()来获取第一个内部环, 然后通过PointN()获得第五个顶点。

最后我们可以调用Y()来获取坐标值。

更多的类型

点、线、面是 GEOMETRY 中的基本类。 但是 GEOMETRY 也支持复杂类。

  1. MULTIPOINT 是属于同一个实体的两个或更多点的集合。
  2. MULTILINESTRING 是两个或更多线状要素。
  3. MULTIPOLYGON 是两个或更多多边形要素。
  4. GEOMETRYCOLLECTION 是包含多种要素类型的集合。

对于上面这些类型就不进行更多说明了。总体来讲, 这个类型比基本的类型会多一些性质。

  1. SpatiaLite 的 NumGeometries() 函数返回集合中元素的数目。
  2. GeometryN() 函数返回集合中的第 N 个元素。
  3. GLength() 函数由 MULTILINESTRING 集合中所有线状要素组成的各单独长度的和。
  4. Area() 函数返回 MULTIPOLYGON 集中中所有多边形要素的单独面积的和。
  5. Centroid() 函数返回 MULTIPOLYGON 的平均质心。

GEOMETRY 外包矩形

在开始之前,先运行:

In [28]:
import sqlite3 as sqlite
conn = sqlite.connect("/gdata/test-2.3.sqlite")
conn.enable_load_extension(True)
conn.execute('SELECT load_extension("mod_spatialite.so.7")')
Out[28]:
<sqlite3.Cursor at 0x7fbb5d64a880>
In [29]:
cursor = conn.cursor()

下面是所有 GEOMETRY 类的基本属性:

In [30]:
sql = '''SELECT Name, AsText(Envelope(Geometry)) FROM Regions LIMIT 5'''
cursor.execute(sql)
for x in cursor:
    print(x)
('VENETO', 'POLYGON((752912.250297 5027429.54477, 753828.826422 5027429.54477, 753828.826422 5028928.677375, 752912.250297 5028928.677375, 752912.250297 5027429.54477))')
('VENETO', 'POLYGON((751455.937063 5026778.301798, 752928.785333 5026778.301798, 752928.785333 5029157.835014, 751455.937063 5029157.835014, 751455.937063 5026778.301798))')
('VENETO', 'POLYGON((759461.944608 5026112.935302, 766247.780711 5026112.935302, 766247.780711 5036802.775999, 759461.944608 5036802.775999, 759461.944608 5026112.935302))')
('VENETO', 'POLYGON((750183.233817 5025314.495578, 750842.445895 5025314.495578, 750842.445895 5026793.815968, 750183.233817 5026793.815968, 750183.233817 5025314.495578))')
('LIGURIA', 'POLYGON((378359.802362 4848919.510676, 584633.642747 4848919.510676, 584633.642747 4947990.084988, 378359.802362 4947990.084988, 378359.802362 4848919.510676))')

SpatiaLite 的 Envelope() 函数总是返回给定多边形的最小边界矩形。 (Minimum Bounding Rectangle - MBR)。 最小边界矩形由5个点组成(第一个点与最后一个点是相同的)。 需要注意, 在不同的投影方式,同一多边形的最小边界矩形是不一样的(包括形状、大小与方向)。

MBR 也被称为矩形边界框(bounding boxes, BBOX)

有效的点如下所示。

  • POINT #1: minX,minY
  • POINT #2: maxX,minY
  • POINT #3: maxX,maxY
  • POINT #4: minX,maxY
  • POINT #5: minX,minY

MBR 在使用中是比较特别的。 通过 MBR, 可以对多边形的空间关系进行简单与大致的分析。 由于 MBR 计算起来非常快, 所以在提高数据处理速度中得到了广泛的应用。

到目前,通过前面的实例,对于空间数据处理的基本核心有了基本的了解。

针对数据表的操作

开始运行

In [31]:
import os,shutil,stat
import sqlite3 as sqlite
tmp_db = '/tmp/xx_new_db.sqlite'
if os.path.exists(tmp_db):
    os.remove(tmp_db)
shutil.copy("/gdata/test-2.3.sqlite", tmp_db)
os.chmod(tmp_db, stat.S_IRUSR + stat.S_IWUSR)
conn = sqlite.connect(tmp_db)
conn.enable_load_extension(True)
conn.execute('SELECT load_extension("mod_spatialite.so.7")')
cursor = conn.cursor()

创建表

创建一个新表,并在其中插入一些记录:

In [32]:
cursor.execute('CREATE TABLE MyTable (name TEXT NOT NULL, geom BLOB NOT NULL)')
cursor.execute("INSERT INTO MyTable (name, geom) VALUES ('one', GeomFromText('POINT(1 1)'))")
cursor.execute("INSERT INTO MyTable (name, geom) VALUES ('two', GeomFromText('POINT(2 2)'))")
cursor.execute("INSERT INTO MyTable (name, geom) VALUES ('three', GeomFromText('POINT(3 3)'))")
cursor.execute("SELECT name, AsText(geom) FROM MyTable;")
[print(rec) for rec in cursor]
('one', 'POINT(1 1)')
('two', 'POINT(2 2)')
('three', 'POINT(3 3)')
Out[32]:
[None, None, None]

CREATE TABLE 语句是用来创建一个新表的,创建的时候要指明它要包含的属性列。 属性列的定义可以在后期添加、删除,或修改类型都可以。

还应该了解,一个地理空间属性列的类型是 BLOBINSERT INTO 语句可以在数据表中存储新行。 使用 GeoMFromText() ,你可以创建新的几何对象数据。 最后,使用 SELECT 语句,执行了一个最终的检查。

更新表

更新行与插入或删除它们是一样简单的:

In [33]:
cursor.execute("SELECT pk_uid, name, peoples, AsText(geometry) FROM Towns WHERE pk_uid = 8006")
Out[33]:
<sqlite3.Cursor at 0x7fbb5d64a8f0>
In [34]:
[print(rec) for rec in cursor]
(8006, 'Monza', 120204, 'POINT(521332.99 5047818.45)')
Out[34]:
[None]
In [35]:
cursor.execute('''UPDATE Towns SET peoples = 150000, name = 'MONZA',
    geometry = GeomFromText('POINT(10 10)', 32632)  WHERE pk_uid = 8006''')
Out[35]:
<sqlite3.Cursor at 0x7fbb5d64a8f0>
In [36]:
cursor.execute("SELECT pk_uid, name, peoples, AsText(geometry) FROM Towns WHERE pk_uid = 8006")
Out[36]:
<sqlite3.Cursor at 0x7fbb5d64a8f0>
In [37]:
[print(rec) for rec in cursor]
(8006, 'MONZA', 150000, 'POINT(10 10)')
Out[37]:
[None]
  • UPDATE SQL语句允许您修改任何列值。
  • 你只需要SET列名称和新的值即可替换当前的。你可以像往常一样使用GeomFromText()函数获取几何值。 PK_UID列是一个特殊的函数,作为这个表的主要键。
  • 在每个表中,PRIMARY KEY列都保证您唯一 一行可能包含一个选定的值,从而确保一致性。

如果你的一张表格,由于任何原因,没有主要的钥匙,别担心; SQLite自动管理表中每一行都有用,每个行的名称都是ROWID; 您可以直接引用SQL表达式,就像普通的列表一样。

还要注意,在这个例子中,你显然没有使用任何交易。(您既不使用BEGIN也不使用COMMIT)

  • 一个新的事务隐式地为每个SQL语句被开始
  • 并且在处理后自动执行隐式COMMIT SQL语句。
  • 这可能很容易导致或多或少严重的性能下降; 到目前为止,SQLite更倾向于您明确的BEGIN和COMMIT 交易,特别是当你执行许多和许多 连续的INSERT和/或UPDATE。

选择数据创建新表格

以下示例需要原始的my_new_db.sqlite数据库。 因此,避免我们执行的DELETE和UPDATE导致任何错位,现在重建它更好。

SQLite的SQL语法为您提供了一种直观的方式来创建一个新的表,并同时使用从另一个表格中选出的数据进行填充:

In [38]:
cursor.execute('BEGIN')
Out[38]:
<sqlite3.Cursor at 0x7fbb5d64a8f0>
In [39]:
cursor.execute('CREATE TABLE Villages AS SELECT * FROM Towns WHERE peoples < 500')
Out[39]:
<sqlite3.Cursor at 0x7fbb5d64a8f0>
In [40]:
conn.commit()
cursor.execute('SELECT count(*) FROM Villages')
Out[40]:
<sqlite3.Cursor at 0x7fbb5d64a8f0>
In [41]:
[print(rec) for rec in cursor]
(845,)
Out[41]:
[None]

SQLite还支持另一种不同的SQL结构,允许您创建一个新表,然后使用现有表中的数据进行填充:

In [42]:
cursor.execute('BEGIN')
cursor.execute('CREATE TABLE Metropolis ( Name TEXT NOT NULL, Population INTEGER NOT NULL, Geometry BLOB NOT NULL);')
cursor.execute('''INSERT INTO Metropolis (Name, Population, Geometry)
        SELECT name, peoples, geometry FROM Towns
        WHERE peoples > 1000000;''')
conn.commit()
cursor.execute('SELECT name, population, AsText(geometry) FROM Metropolis')
[print(rec) for rec in cursor]
('Roma', 2546804, 'POINT(788703.57 4645636.3)')
('Milano', 1256211, 'POINT(514820.49 5034534.56)')
('Napoli', 1004500, 'POINT(942636.1 4535272.55)')
Out[42]:
[None, None, None]

首先您创建新表,自由选择列,名称等,然后调用INSERT INTO SELECT执行自动数据传输选择或完成之间的数据。

这两种SQL构造在面向GIS的环境中非常有用; 很多时候,您可能需要提取一小部分数据,例如,只有与单个县或镇\相关的,以便捷的方式编辑它们,执行一些非常专门的空间分析,产生一些专门的输出,转储选定的shapefile等。

在这种情况下,创建一组派生表可以在很大程度上有所帮助。

一旦你不需要更多的保留这些表,你可以删除它们:

In [43]:
cursor.execute('DROP TABLE Villages')
cursor.execute('DROP TABLE Metropolis')
cursor.execute('VACUUM')
Out[43]:
<sqlite3.Cursor at 0x7fbb5d64a8f0>

一个DROP语句将完全消除一个表及其包含的所有数据。 不要忘了在删除表之后执行VACUUM,以便真正释放未使用的空间,压缩数据库等等。

插入、更新与删除记录

问题

成功地创建了一个表格,并在其中插入一些空间数据。现在可以退出 SQLite 任务了。

如果再开始一个新的SQLite 任务,想从中新数据取回,使用下面的语句:

In [44]:
import sqlite3 as sqlite
# db = sqlite.connect(':memory:')

conn = sqlite.connect('/gdata/my_new_db.sqlite')
conn.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();')

cur = conn.cursor()
recs = cur.execute("SELECT name, AsText(geom) FROM MyTable;")
for rec in recs:
    print(rec)
    
# from pyspatialite import dbapi2 as db
# conn = db.connect('my_new_db.sqlite')
# cur = conn.cursor()
# recs = cur.execute("SELECT name, AsText(geom) FROM MyTable;")
# for rec in recs:
#     print(rec)
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-44-65cffc230041> in <module>()
     10 
     11 cur = conn.cursor()
---> 12 recs = cur.execute("SELECT name, AsText(geom) FROM MyTable;")
     13 for rec in recs:
     14     print(rec)

OperationalError: no such table: MyTable

结果为空。

事实上的确是没有数据了,再看一下上面插入数据的工作:

您通过调用BEGIN开始一个交易,然后创建了您的 表,插入了一些行。

别忘了调用COMMIT。

Python + SQLite的事务操作

将test-2.3.sqlite 复制为 my_new_db.sqlite, 原来文件作为备份,下面的操作都在新的文件中操作。

In [ ]:
from pyspatialite import dbapi2 as db
conn = db.connect('/tmp/xx_new_db.sqlite')
cur = conn.cursor()

SQL的BEGIN状态就是告知SQLite你准备要开始一个命令,在此命令中你所做的任何操作都可能随时被取消,同时数据库会返回至起始未经改动的状态。SQL的ROLLBACK命令,就会准确无误地告知SQLite操作后的结果。

Python调用SQLite,默认是使用事务操作的。 开始常规的 SQL 命令:

In [ ]:
del_sql = 'DELETE FROM Towns WHERE peoples < 100000'
cur.execute(del_sql)
sql = 'SELECT count(*) FROM Towns'
res = cur.execute(sql)
res.next()

结果显示为42个。 上面删除的数据是无法恢复。 所以上面的操作一定要小心。 稳妥的方法是使用数据的事务功能, 一旦出现问题,可以及时回撤。

In [ ]:
del_sql = 'DELETE FROM Towns WHERE peoples < 100000'
cur.execute(del_sql)
sql = 'SELECT count(*) FROM Towns'
res = cur.execute(sql)
res.next()
conn.rollback()
res = cur.execute(sql)
res.next()

del_sql = 'DELETE FROM Towns WHERE peoples < 100000'
cur.execute(del_sql)
sql = 'SELECT count(*) FROM Towns'
res = cur.execute(sql)
res.next()
conn.commit()
res = cur.execute(sql)
res.next()

commit 是一种决定性的函数,它是用来指定所有的操作为待定的未知状态。

如果你想继续以事务方式工作,你应该开始一个 新的事务再次调用BEGIN。 代码“rollback”和“commit”。

如果您只是省略使用所有BEGIN,COMMIT或ROLLBACK语句,SQLite将假设您希望以所谓的方式运行,即隐式假设每个单个语句都包含在自动事务中。

管理坐标参考与坐标转换

每个坐标值都是完全限定的域名,需要一个有明确标识。

此值标识了几何相关描述、几何对象定义的坐标空间。

作为一种普遍的规则,只有当它们的坐标是在同样的空间参考系统,不同的几何对象才能进行有意义的互操作。
[1]

在GIS中,一种常用的操作,是地理坐标重投影(coordinate reprojection), 是将不同的GIS数据转换成唯一的的空间参考系统,然后进行一些互操作和集成。

当您尝试使用属于不同坐标参考系统的两个GIS数据集时,实体下降得非常远,因为坐标的数值显然在两个不同的空间中。

但是,如果您为一个数据集应用一些时机,则将其放在另一个数据集的相同坐标参考系统中,实体将按预期正确重叠。

欧洲石油调查组[EPSG]维护和分配了一个大的数据集,它描述任何坐标系统和坐标变换都应用于世界各地的GIS数据。

SpatiaLite为任何类型的几何类实现SRID,并支持EPSG数据集来识别坐标参考系。

In [ ]:
import sqlite3 as sqlite
db = sqlite.connect(':memory:')
db.enable_load_extension(True)
db.execute('SELECT load_extension("mod_spatialite.so.7")')
cursor = db.cursor()
cursor.execute('BEGIN')
cursor.execute('SELECT InitSpatialMetaData();')

脚本为了初始化Spatial MetaData

开始新的spatialite.exe会话:

In [ ]:
cursor = cursor.execute('SELECT * FROM spatial_ref_sys LIMIT 5;')
[print(rec) for rec in cursor]

spatial_ref_sys表是一个空间元数据, 通过执行init \ _spatialite-2.3.sql脚本进行初始化 坐标参考系。auth\ _name和auth\ _srid分别标识权限 生成这些数据和原始Srid。如果你想了解更多,你可以 阅读以下网站内容:http://en.wikipedia.org/wiki/Geodeticftp://ftp.remotesensing.org/proj/OF90-284.pdf

In [ ]:
cursor.close()
db.close()

查看数据库信息

首先我们使用新的数据库,来查看信息:

In [ ]:
import os,shutil,stat
import sqlite3 as sqlite
tmp_db = '/tmp/xx_new_db.sqlite'
if os.path.exists(tmp_db):
    os.remove(tmp_db)
shutil.copy("/gdata/test-2.3.sqlite", tmp_db)
os.chmod(tmp_db, stat.S_IRUSR + stat.S_IWUSR)
conn = sqlite.connect(tmp_db)
conn.enable_load_extension(True)
conn.execute('SELECT load_extension("mod_spatialite.so.7")')
cursor = conn.cursor()
cursor.execute("select name from sqlite_master where type='table' order by name;")
[print(rec) for rec in cursor]

查看数据库中的空间参考:

In [ ]:
cursor = cursor.execute('SELECT * FROM spatial_ref_sys LIMIT 5;')
[print(rec) for rec in cursor]

进一步查看数据库的信息:

In [ ]:
cursor = cursor.execute('SELECT DISTINCT Srid(geometry) FROM Towns;')
[print(rec) for rec in cursor]
In [ ]:
cursor = cursor.execute('''SELECT DISTINCT SRID(Towns.geometry), spatial_ref_sys.ref_sys_name FROM Towns,
        spatial_ref_sys WHERE SRID(Towns.geometry) = spatial_ref_sys.srid;''')
[print(rec) for rec in cursor]
  1. SpatiaLite SRID()函数允许您识别标识任何类型的几何srid值。
  2. 你可以简单的运行,然后你会发现srid 32632真正的意思。

创建

您可以通过SQL查询,来完成这个非常复杂的任务。

In [ ]:
cursor.execute('BEGIN')
cursor.execute("SELECT AddGeometryColumn('Towns', 'wgs84', 4326, 'POINT', 2)")
cursor.execute("UPDATE Towns SET wgs84 = Transform(geometry, 4326);")
conn.commit()
cursor.execute('SELECT AsText(geometry), Srid(geometry),AsText(wgs84), Srid(wgs84) FROM Towns LIMIT 5;')
[print(rec) for rec in cursor]
  1. WGS 84的srid是4326。
  2. 根据需要应用SpatiaLite Transform()函数,从原始函数获取一个新的几何体。
  3. NewTowns表中有两个替代几何体:
    • 原始的geom列包含32623中的几何。
    • UTM区32N坐标参考系。
    • 新的wgs84列包含4326中的几何。
    • WGS84坐标参考系。
    • 您现在可以根据需要和适当的方式随意使用一个或另一个。