python - 使用 Flask Python 将点几何插入 PostGIS

Question

已成功定义一个表并将其迁移到具有以下定义的 PostGIS 数据库中:

from db import db
from geoalchemy2.types import Geometry


class HouseModel(db.Model):
    __tablename__ = 'house'
    __table_args__ = {'schema': 'housing'}
    house_id = db.Column(db.Integer, primary_key=True)
    address1 = db.Column(db.String)
    address2 = db.Column(db.String)
    suburb = db.Column(db.String)
    country = db.Column(db.String)
    state = db.Column(db.String)
    postcode = db.Column(db.String)
    location = db.Column(Geometry(geometry_type='POINT', srid=4326))

    def save_to_db(self):
        db.session.add(self)
        db.session.commit()

    def delete_from_db(self):
        db.session.delete(self)
        db.session.commit()

但是当我尝试插入该表时，我正在努力弄清楚如何插入。将数据类型设置为 shapely Point 类型甚至 f'POINT({lat},{long})' 字符串时，出现以下错误:

sqlalchemy.exc.InternalError: (psycopg2.errors.InternalError_) parse error - invalid geometry
HINT:  "SRID=4269,P" <-- parse error at position 11 within geometry

[SQL: INSERT INTO housing.house (address1, address2, suburb, country, state, postcode, location) VALUES (%(address1)s, %(address2)s, %(suburb)s, %(country)s, %(state)s, %(postcode)s, ST_GeomFromEWKT(%(location)s
)) RETURNING housing.house.house_id]
[parameters: {'address1': None, 'address2': None, 'suburb': None, 'country': None, 'state': None, 'postcode': None, 'location': 'SRID=4269,POINT(-33.90345045347954, 152.7345762127824)'}]
(Background on this error at: http://sqlalche.me/e/14/2j85)

知道当我运行 house.save_to_db() 时此函数需要什么数据类型或什么格式吗？

Answer 1

原来它期望文本格式为:

'SRID=4326;POINT(-33.9034 152.73457)'

在 SRID 和 POINT 之间需要 ;，在纬度和经度之间不需要 ,。