SQLAlchemy+GeoAlchemy查询geometry数组字段报错求助

问题 #

我创建了一张存储点数组（geometry类型列）的不规则几何表，想要按measurement_point_id检索点数据，但遇到两个报错：

• 尝试转换类型时提示：ProgrammingError: (psycopg2.errors.CannotCoerce) cannot cast type geometry[] to geometry
• 移除转换后提示：ProgrammingError: (psycopg2.errors.UndefinedFunction) function st_x(geometry[]) does not exist

数据库表结构 #

column_name      | data_type | numeric_scale || udt_schema | udt_name  | 
----------------------+-----------+---------------+-------------+------------+
 id                   | integer   |             0 | | pg_catalog | int4      |
 measurement_point_id | integer   |             0 | | pg_catalog | int4      |
 axises               | ARRAY     |               | | public     | _geometry |

Irregular表类定义（SQLAlchemy） #

#%% Irregular Class
class Irregular (object):
    measurement_point_id = relationship("measurement_points", back_populates="id")

    def __init__(self,measurement_point_id,axises=None,id= None):
        self.id = id
        self.measurement_point_id = measurement_point_id
        self.axises = axises
        #self.is_xy = xy

#Irregular Object
__tablename__ = 'irregular'
irregular = Table(
    __tablename__,meta,
    Column ('id', Integer, primary_key = True), 
    Column ( 'measurement_point_id',Integer,ForeignKey('measurement_points.id')),
    Column ( 'axises', ARRAY(Geometry('POINT'))),
    #Column ( 'is_xy', Boolean),
)
mapper(Irregular, irregular)

我的查询代码 #

session.query(fns.ST_X(cast(tb.Irregular.axises, geoalchemy2.types.Geometry)),\
                             fns.ST_Y(cast(tb.Irregular.axises, geoalchemy2.types.Geometry)).filter(tb.measurement_point_id == id).all()

我认为需要将数据以元组数组形式获取，但不知如何在Python端进行类型转换及使用正确函数。

解决方法 #

1. 数据库端：展开Geometry数组（Unnest） #

ST_X/ST_Y仅支持单个geometry对象，无法直接作用于geometry[]数组。需先用PostgreSQL的unnest函数将数组展开为单个点，再调用空间函数：

from sqlalchemy import func

# 展开数组，获取每个点的X、Y坐标
query = session.query(
    Irregular.measurement_point_id,
    func.ST_X(func.unnest(Irregular.axises)).label('x'),
    func.ST_Y(func.unnest(Irregular.axises)).label('y')
).filter(Irregular.measurement_point_id == target_id)

results = query.all()

返回结果格式类似[(1, 10.0, 20.0), (1, 30.0, 40.0)]，对应每个点的关联ID和坐标。

2. Python端：直接处理Geometry数组 #

若想直接获取完整点数组，再在Python端解析坐标，可直接查询axises列，遍历数组元素提取坐标：

# 查询指定ID的点数组
result = session.query(Irregular.axises).filter(Irregular.measurement_point_id == target_id).first()

if result and result[0]:
    point_coords = []
    # 遍历数组内的每个点
    for point in result[0]:
        x = func.ST_X(point).scalar()
        y = func.ST_Y(point).scalar()
        point_coords.append((x, y))
    # 最终得到元组数组：[(x1,y1), (x2,y2), ...]

也可借助Shapely库更高效解析：

from shapely.geometry import Point

if result and result[0]:
    point_coords = []
    for wkb_point in result[0]:
        shape_point = wkb_point.to_shape()
        point_coords.append((shape_point.x, shape_point.y))

3. 核心注意事项 #

• 禁止将geometry[]强制转换为geometry，两种类型不兼容，PostgreSQL不支持此类转换。
• 处理数组类型时，要么用数据库函数展开处理，要么在Python端遍历数组元素逐个操作。

内容的提问来源于stack exchange，提问作者Sıla Öztürk