Psycopg ppygis选择查询 [英] Psycopg ppygis select query

问题描述

我正在尝试使用python ppygis软件包设置基本的工作postgis设置.

I'm trying to setup a basic working postgis setup with python ppygis package.

>>> import psycopg2
>>> import ppygis
>>> connection = psycopg2.connect(database='spre', user='postgres')
>>> cursor = connection.cursor()
>>> cursor.execute('CREATE TABLE test (geometry GEOMETRY)')
>>> cursor.execute('INSERT INTO test VALUES(%s)', (ppygis.Point(1.0, 2.0),))
>>> cursor.execute('SELECT * from test')
>>> point = cursor.fetchone()[0]
>>> print point
0101000000000000000000F03F0000000000000040
>>>

我应该有一个带有单独的X和Y坐标的python对象.

I should have got a python object with separate X and Y coordinate. Something like

>>> Point(X: 1.0, Y: 2.0)

我在做什么错了?

推荐答案

您没有做错任何事情.遵循与PPyGIS基本示例相同的步骤，如问题(010100 ...)所示，与十六进制编码的EWKB 相同.通常是预期的.也许这适用于旧版本的PPyGIS/Psycopg?今天不是.

You are doing nothing wrong. Following the same steps as the PPyGIS basic example, I get the same hex-encoded EWKB as shown in the question (010100...), which is normally expected. Perhaps this worked with an older version of PPyGIS / Psycopg? It doesn't today.

该软件包似乎无法正确地将自己注册为PostGIS类型的适配器，因此我的建议是不要使用该软件包.此外，您不需要其他程序包即可使用Psycopg2中的PostGIS.

The package does not appear to properly register itself as an adapter for PostGIS types, so my advice is to not use the package. Besides, you don't need additional packages to use PostGIS from Psycopg2.

这是读取/写入点的常规方法，没有任何额外的软件包:

Here is the normal approach to read/write points, without any extra packages:

# Assuming PostGIS 2.x, use a typmod
cursor.execute('CREATE TEMP TABLE test (geom geometry(PointZ,4326));')
# Longyearbyen, 78.22°N 15.65°E, altitude 10 m
cursor.execute('''\
    INSERT INTO test (geom)
    VALUES(ST_SetSRID(ST_MakePoint(%s, %s, %s), 4326));
''', (15.65, 78.22, 10.0))
cursor.execute('''\
    SELECT ST_Y(geom) AS latitude, ST_X(geom) AS longitude, ST_Z(geom) AS altitude
    FROM test;
''')
print(cursor.fetchone())  # (78.22, 15.65, 10.0)
cursor.execute('SELECT ST_AsText(geom) FROM test;')
print(cursor.fetchone()[0])  # POINT Z (15.65 78.22 10)
cursor.execute('SELECT ST_AsLatLonText(geom) FROM test;')
print(cursor.fetchone()[0])  # 78°13'12.000"N 15°39'0.000"E

如果希望客户端上的几何对象对实际几何做更多工作，请考虑使用Shapely，可以使用WKB数据进行接口连接

If you want a geometry object on the client-side to do more work with the actual geometry, consider using Shapely, which can be interfaced using WKB data:

from shapely.wkb import loads
cursor.execute('SELECT geom FROM test;')
pt = loads(cursor.fetchone()[0], hex=True)
print(pt)  # POINT Z (15.65 78.22 10)

这篇关于Psycopg ppygis选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！