sqlalchemy与postGIS的结合

sqlalchemy一般使用

模型创建

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


class Base(db.Model):
    """基础数据库模型：提供id、创建时间、更新时间"""
    __abstract__ = True
    __table_args__ = {'mysql_collate': 'utf8_general_ci'}  # 支持中文
    id = Column(Integer, primary_key=True, comment='主键')
    create_time = Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')


class User(Base):
    """用户表"""
    name = db.Column(db.String(32), unique=True, nullable=False, comment='用户名')
    email = db.Column(db.String(32), unique=True, nullable=True, comment='邮箱')
    is_admin = db.Column(db.Boolean, unique=False, nullable=False, default=False, comment='是否是超级管理员')
    is_active = db.Column(db.Boolean, unique=False, nullable=False, default=True, comment='是否激活')

计算个数

from sqlalchemy import func

total = db.session.query(func.count(id)).filter(*condition).scalar()

分页

from sqlalchemy import and_

db.session.query(User).filter(and_(*condition)).offset(offset).limit(limit).all()

排序

# asc 升序
# desc 降序

db.session.query(User).filter(and_(*condition)).order_by(User.time.desc()).offset(offset).limit(limit).all()

字段重命名（select … as …）

db.session.query(User.name.label("u_name"))).all()

geoalchemy2扩展

模型创建

from geoalchemy2 import Geometry

class Point(Base):
    """点图层"""
    name = db.Column(db.String(32), comment='名称')
    the_geom = db.Column(Geometry(geometry_type='POINT', srid=4326), comment='经纬度')

添加几何要素

point=Point()
point.name='这是一个点'
point.the_geom="SRID=4326;POINT(111 11)"
db.session.add(point)

周边国家查询

# 创建点
point = func.ST_GeomFromEWKT(f"SRID=4326;POINT(111 11)")
# 创建1000KM缓冲区（先将点重投影为3857）
_buffer = func.ST_Buffer(func.ST_Transform(point, 3857), 1000 * 1000)
# 判断是否相交
db.session.query(Country).filter(func.ST_Intersects(Country.the_geom, _buffer)).all()

获取点的经纬度

points = db.session.query(func.ST_x(Point.the_geom).label('lng'), func.ST_y(Point.the_geom).label('lat')).all()

for point in points:
	lng, lat = point.lng, point.lat

最近距离计算

原生SQL语句

SELECT *
FROM table_name
ORDER BY the_geom <-> ST_GeomFromEWKT('SRID=4326;POINT(116 40)')
LIMIT 1;

sqlalchemy示例：

point = func.ST_GeomFromEWKT('SRID=4326;POINT(116 40)')
table = db.session.query(Table).order_by(Table.the_geom.op('<->')(point)).limit(1).first()

参考资料： https://geoalchemy-2.readthedocs.io/