有没有将shapely与PostgreSQL结合使用的“最佳实践”?在
与register_adapter和朋友玩我有以下代码。有更好的办法吗?在
from psycopg2.extensions import (
adapt, register_adapter, AsIs, new_type, register_type
)
from shapely.geometry import Point, Polygon
import numpy as np
import psycopg2
conn = psycopg2.connect(host='localhost', user='postgres')
cur = conn.cursor()
cur.execute('CREATE TABLE pts (pt POINT)')
conn.commit()
def quote(v):
return adapt(v).getquoted().decode()
def adapt_point(pt):
x, y = quote(pt.x), quote(pt.y)
return AsIs("'(%s, %s)'" % (x, y))
register_adapter(Point, adapt_point)
points = [
(Point(x, y), )
for x, y in
[(0, 0), (1, 0), (1, 1), (2, 3), (0, 1)]
]
cur.executemany('INSERT INTO pts (pt) VALUES (%s)', points)
conn.commit()
def adapt_polygon(poly):
pts = np.stack(poly.exterior.xy).T
inner = ', '.join('(%s, %s)' % (quote(x), quote(y)) for x, y in pts)
return AsIs("'(%s)'" % inner)
register_adapter(Polygon, adapt_polygon)
def cast_point(value, cur):
if value is None:
return None
# '(2.7,3.6)'
try:
x, y = value[1:-1].split(',')
except ValueError:
raise psycopg2.InterfaceError('bad point representation: %r' % value)
return Point(float(x), float(y))
cur.execute('SELECT NULL::point')
point_oid = cur.description[0].type_code
POINT = new_type((point_oid,), 'POINT', cast_point)
register_type(POINT)
poly = Polygon([(0, 0), (0, 1), (1, 1), (1, 0)])
cur.execute('''
SELECT pt
FROM pts
WHERE
pt <@ polygon %s
''', (poly,))
for pt, in cur:
print(pt, type(pt))
目前没有回答
相关问题 更多 >
编程相关推荐