核心教程¶
(本教程的灵感来自 SQLAlchemy SQL Expression Language Tutorial ,最终推荐阅读。)
本教程演示如何将SQLAlchemy表达式语言(也称为SQLAlchemy核心)与GeoAlchemy一起使用。正如SQLAlchemy文档本身所定义的,与ORM以域为中心的使用模式不同,SQL表达式语言提供了以模式为中心的使用模式。
连接到数据库¶
在本教程中,我们将使用PostGIS 2数据库。为了连接我们使用SQLAlchemy的 create_engine()
功能:
>>> from sqlalchemy import create_engine
>>> engine = create_engine('postgresql://gis:gis@localhost/gis', echo=True)
在本例中,数据库的名称、数据库用户和数据库密码是 gis
.
这个 echo
flag是设置SQLAlchemy日志的快捷方式,它是通过Python的标准日志模块实现的。启用后,我们将看到生成的所有SQL。
的返回值 create_engine
是一个 Engine
对象,它将核心接口重新呈现给数据库。
定义表¶
我们需要创建的第一个对象是 Table
. 在这里我们创建一个 lake_table
对象,它将对应于 lake
数据库中的表:
>>> from sqlalchemy import Table, Column, Integer, String, MetaData
>>> from geoalchemy2 import Geometry
>>>
>>> metadata = MetaData()
>>> lake_table = Table('lake', metadata,
... Column('id', Integer, primary_key=True),
... Column('name', String),
... Column('geom', Geometry('POLYGON'))
... )
这个表由三列组成, id
, name
和 geom
. 这个 geom
列是 geoalchemy2.types.Geometry
其列 geometry_type
是 POLYGON
.
任何 Table
对象被添加到 MetaData
对象,它是 Table
对象(和其他相关对象)。
创建表¶
和我们一起 Table
我们已经准备好(让SQLAlchemy)在数据库中创建它:
>>> lake_table.create(engine)
调用 create_all()
在 metadata
也同样有效:
>>> metadata.create_all(engine)
在这种情况下 Table
是指 metadata
将在数据库中创建。这个 metadata
对象包含一个 Table
在这里,我们现在众所周知的 lake_table
对象。
反射表¶
这个 reflection system of SQLAlchemy 可用于包含 geoalchemy2.types.Geometry
或 geoalchemy2.types.Geography
柱。在这种情况下,必须导入该类型才能注册到SQLAlchemy中,即使它不是明确使用的。
>>> from geoalchemy2 import Geometry # <= not used but must be imported
>>> from sqlalchemy import create_engine, MetaData
>>> engine = create_engine("postgresql://myuser:mypass@mydb.host.tld/mydbname")
>>> meta = MetaData()
>>> meta.reflect(bind=engine)
插入¶
我们要将记录插入 lake
桌子。为此我们需要创造一个 Insert
反对。SQLAlchemy为创建 Insert
对象,这里有一个:
>>> ins = lake_table.insert()
>>> str(ins)
INSERT INTO lake (id, name, geom) VALUES (:id, :name, ST_GeomFromEWKT(:geom))
这个 geom
列是 Geometry
纵队 :geom
绑定值包装在 ST_GeomFromEWKT
打电话。
限制 INSERT
查询 values()
方法可以使用:
>>> ins = lake_table.insert().values(name='Majeur',
... geom='POLYGON((0 0,1 0,1 1,0 1,0 0))')
...
>>> str(ins)
INSERT INTO lake (name, geom) VALUES (:name, ST_GeomFromEWKT(:geom))
小技巧
SQL表达式的字符串表示不包括 values
. 取而代之的是命名绑定参数。要查看数据,我们可以获取表达式的编译形式,并要求 params
::
>>> ins.compile.params()
{'geom': 'POLYGON((0 0,1 0,1 1,0 1,0 0))', 'name': 'Majeur'}
到目前为止,我们已经创建了 INSERT
查询,但我们尚未将此查询发送到数据库。在将它发送到数据库之前,我们需要一个数据库 Connection
. 我们可以得到一个 Connection
从 Engine
我们先前创建的对象:
>>> conn = engine.connect()
我们现在准备执行 INSERT
声明:
>>> result = conn.execute(ins)
这是日志系统应该输出的:
INSERT INTO lake (name, geom) VALUES (%(name)s, ST_GeomFromEWKT(%(geom)s)) RETURNING lake.id
{'geom': 'POLYGON((0 0,1 0,1 1,0 1,0 0))', 'name': 'Majeur'}
COMMIT
返回的值 conn.execute()
,存储在 result
是一个 sqlalchemy.engine.ResultProxy
反对。如果是 INSERT
我们可以获取从语句生成的主键值:
>>> result.inserted_primary_key
[1]
而不是使用 values()
指定我们的 INSERT
数据,我们可以将数据发送到 execute()
方法对 Connection
. 所以我们可以重写如下:
>>> conn.execute(lake_table.insert(),
... name='Majeur', geom='POLYGON((0 0,1 0,1 1,0 1,0 0))')
现在让我们使用另一个表单,允许一次插入多行:
>>> conn.execute(lake_table.insert(), [
... {'name': 'Garde', 'geom': 'POLYGON((1 0,3 0,3 2,1 2,1 0))'},
... {'name': 'Orta', 'geom': 'POLYGON((3 0,6 0,6 3,3 3,3 0))'}
... ])
...
小技巧
在上面的示例中,几何图形被指定为WKT字符串。还支持将它们指定为EWKT字符串。
选择¶
插入涉及创建 Insert
对象,因此选择包含创建 Select
反对。要生成的主构造 SELECT
语句是SQLAlchemy的 select()
功能:
>>> from sqlalchemy.sql import select
>>> s = select([lake_table])
>>> str(s)
SELECT lake.id, lake.name, ST_AsEWKB(lake.geom) AS geom FROM lake
这个 geom
列是 Geometry
它被包裹在 ST_AsEWKB
当指定为 SELECT
语句。
我们现在可以执行该语句并查看结果:
>>> result = conn.execute(s)
>>> for row in result:
... print 'name:', row['name'], '; geom:', row['geom'].desc
...
name: Majeur ; geom: 0103...
name: Garde ; geom: 0103...
name: Orta ; geom: 0103...
row['geom']
是一个 geoalchemy2.types.WKBElement
实例。在本例中,我们只使用 desc
财产。
空间查询¶
作为空间数据库的用户,执行空间查询是我们非常感兴趣的。地球炼金术来了!
空间关系¶
在SQL SELECT查询中使用空间过滤器非常常见。这些查询是通过使用空间关系函数或运算符在 WHERE
SQL查询的子句。
例如,要找到包含该点的湖泊 POINT(4 1)
,我们可以使用这个:
>>> from sqlalchemy import func
>>> s = select([lake_table],
func.ST_Contains(lake_table.c.geom, 'POINT(4 1)'))
>>> str(s)
SELECT lake.id, lake.name, ST_AsEWKB(lake.geom) AS geom FROM lake WHERE ST_Contains(lake.geom, :param_1)
>>> result = conn.execute(s)
>>> for row in result:
... print 'name:', row['name'], '; geom:', row['geom'].desc
...
name: Orta ; geom: 0103...
GeoAlchemy允许更简洁地重写:
>>> s = select([lake_table], lake_table.c.geom.ST_Contains('POINT(4 1)'))
>>> str(s)
SELECT lake.id, lake.name, ST_AsEWKB(lake.geom) AS geom FROM lake WHERE ST_Contains(lake.geom, :param_1)
这里 ST_Contains
函数应用于 lake.c.geom
. 生成的SQL lake.geom
列实际上传递给 ST_Contains
函数作为第一个参数。
下面是另一个空间查询,基于 ST_Intersects
::
>>> s = select([lake_table],
... lake_table.c.geom.ST_Intersects('LINESTRING(2 1,4 1)'))
>>> result = conn.execute(s)
>>> for row in result:
... print 'name:', row['name'], '; geom:', row['geom'].desc
...
name: Garde ; geom: 0103...
name: Orta ; geom: 0103...
This query selects lakes whose geometries intersect ``LINESTRING(2 1,4 1)``.
地球炼金术的功能都是从 ST_
. 运算符也被称为函数,但运算符函数的名称不包括 ST_
前缀。
以PostGIS为例 &&
运算符,它允许测试几何体的边界框是否相交。地球炼金术提供 intersects
功能:
>>> s = select([lake_table],
... lake_table.c.geom.intersects('LINESTRING(2 1,4 1)'))
>>> result = conn.execute(s)
>>> for row in result:
... print 'name:', row['name'], '; geom:', row['geom'].desc
...
name: Garde ; geom: 0103...
name: Orta ; geom: 0103...
加工和测量¶
这里有一个 Select
计算我们湖泊的缓冲区面积:
>>> s = select([lake_table.c.name,
func.ST_Area(
lake_table.c.geom.ST_Buffer(2)).label('bufferarea')])
>>> str(s)
SELECT lake.name, ST_Area(ST_Buffer(lake.geom, %(param_1)s)) AS bufferarea FROM lake
>>> result = conn.execute(s)
>>> for row in result:
... print '%s: %f' % (row['name'], row['bufferarea'])
Majeur: 21.485781
Garde: 32.485781
Orta: 45.485781
显然,处理和测量功能也可以用于 WHERE
条款。例如::
>>> s = select([lake_table.c.name],
lake_table.c.geom.ST_Buffer(2).ST_Area() > 33)
>>> str(s)
SELECT lake.name FROM lake WHERE ST_Area(ST_Buffer(lake.geom, :param_1)) > :ST_Area_1
>>> result = conn.execute(s)
>>> for row in result:
... print row['name']
Orta
而且,与地球炼金术支持的任何其他功能一样,处理和测量功能可以应用于 geoalchemy2.elements.WKBElement
. 例如::
>>> s = select([lake_table], lake_table.c.name == 'Majeur')
>>> result = conn.execute(s)
>>> lake = result.fetchone()
>>> bufferarea = conn.scalar(lake[lake_table.c.geom].ST_Buffer(2).ST_Area())
>>> print '%s: %f' % (lake['name'], bufferarea)
Majeur: 21.485781
使用栅格函数¶
一些功能(比如 ST_Transform() , ST_Union() , ST_SnapToGrid() ,…)可用于两个 geoalchemy2.types.Geometry
和 geoalchemy2.types.Raster
类型。在GeoAlchemy2中,这些函数只为 Geometry
因为它不能同时为多个类型定义。因此在 Raster
通过传递 type_=Raster 函数的参数:
>>> s = select([func.ST_Transform(
lake_table.c.raster,
2154,
type_=Raster)
.label('transformed_raster')])