在PostgreSQL中使用shapely

Question

有没有将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))