FastApi地理坐标数据存取实践

目录

说明：

要点：

设计：

建表sql

Pydantic Model

SqlAlchemy Model

 FastApi 入口

Examples:

Python操作

 Sql 查询

Api

 数据库记录

参考：

---

说明：

• 应用Pydantic Model 验证/出入 数据， SqlAlchemy Model数据实体，Fastapi提供API机制支持。
• 数据表的坐标字段采用Mysql的GEOMETRY类型
• 目前还没成功使用Pydantic的Coordinate类型，待后续改良

要点：

• 输出的结果是DeviceLocationSimpleOut模型，里面的验证/转换方法需通过，否则不能录入成功
• 如果是Postgresql数据库，需加入PostGIS扩展并在指定数据库激活

  create extension postgis;

设计：

建表sql

/*==============================================================*/
/* Table: ia_iot_device_location                                */
/*==============================================================*/
create table ia_iot_device_location
(
   id                   bigint not null auto_increment  comment '',
   iot_device_id        bigint default 0  comment '',
   label                varchar(255)  comment '',
   coordinates          GEOMETRY not null  comment '地理坐标',
   create_datetime      datetime not null default CURRENT_TIMESTAMP  comment '创建时间',
   update_datetime      datetime not null default CURRENT_TIMESTAMP  comment '更新时间',
   delete_datetime      datetime  comment '',
   is_delete            tinyint not null default 0  comment '',
   primary key (id)
);

/*==============================================================*/
/* Index: Index_1                                               */
/*==============================================================*/
create index Index_1 on ia_iot_device_location
(
   iot_device_id
);

/*==============================================================*/
/* Index: Index_2                                               */
/*==============================================================*/
create SPATIAL index Index_2 on ia_iot_device_location
(
   coordinates
);

Pydantic Model

(apps\vadmin\iot\schemas\DeviceLocation.py)

#!/usr/bin/python
# -*- coding: utf-8 -*-
# @version        : 1.0
# @Create Time    : 2024/05/22 20:45
# @File           : Device.py
# @IDE            : PyCharm
# @desc           : pydantic 模型，用于数据库序列化操作

from pydantic import (
    BaseModel,
    Field,
    ConfigDict,
    ValidationError,
    validator,
    field_validator,
    constr,
)
from core.data_types import DatetimeStr
from datetime import datetime, timezone, timedelta
from apps.vadmin.iot.models.data_types import *
from apps.vadmin.iot.utils import utils
from application import settings
from pydantic_extra_types.coordinate import Coordinate
from geoalchemy2.shape import to_shape
from geoalchemy2 import Geometry, WKBElement
from typing import Any
from shapely.geometry.base import dump_coords

class DeviceLocation(BaseModel):
    label: str | None = Field(None, title="标签")
    iot_device_id: int | None = Field(..., title="None")
    # coordinates: Coordinate | None = Field(..., title="地理坐标")
    coordinates: str | None = Field(..., title="地理坐标")


class DeviceLocationSimpleIn(DeviceLocation):
    '''
    输入模型
    '''
    model_config = ConfigDict(from_attributes=True)

    id: int = Field(..., title="编号")
    create_datetime: DatetimeStr = Field(..., title="创建时间")
    update_datetime: DatetimeStr = Field(..., title="更新时间")

    @validator("create_datetime", "update_datetime", pre=True)
    def convert_utc_to_local(cls, value):
        return utils.convert_utc_to_local(value)

    @field_validator("coordinates", mode="before")
    def turn_coordinates_into_wkt(cls, value):
        return to_shape(value).wkt


class DeviceLocationSimpleOut(DeviceLocation):
    '''
    输出模型
    '''
    model_config = ConfigDict(from_attributes=True)

    id: int = Field(..., title="编号")
    create_datetime: DatetimeStr = Field(..., title="创建时间")
    update_datetime: DatetimeStr = Field(..., title="更新时间")
    coordinates: Any

    @validator("create_datetime", "update_datetime", pre=True)
    def convert_utc_to_local(cls, value):
        return utils.convert_utc_to_local(value)

    @field_validator("coordinates", mode="before")
    def parse_coordinates(cls, value: WKBElement):
        return dump_coords(to_shape(value))[0] if value else None

SqlAlchemy Model

(apps\vadmin\iot\models\models.py)

from typing import List, Optional
from datetime import datetime
from sqlalchemy import BigInteger, Column, DateTime, ForeignKey, ForeignKeyConstraint, Index, Integer, String, Table, Text, text
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.orm import Mapped, declarative_base, mapped_column, relationship
from sqlalchemy.orm.base import Mapped
from geoalchemy2 import Geometry, WKBElement
from sqlalchemy.orm import relationship, Mapped, mapped_column
from db.db_base import BaseModel
from .data_types import DeviceType
import uuid
import secrets

metadata = BaseModel.metadata

class DeviceLocation(BaseModel):
    __tablename__ = 'ia_iot_device_location'
    __table_args__ = (
        Index('Index_1', 'iot_device_id'),
        Index('Index_2', 'coordinates')
    )

    id = mapped_column(BigInteger, primary_key=True)
    coordinates: Mapped[WKBElement] = mapped_column(Geometry(geometry_type='POINT', spatial_index=True), nullable=False, comment='地理坐标')
    iot_device_id = mapped_column(BigInteger, server_default=text("'0'"))
    label = mapped_column(String(255, 'utf8mb4_general_ci'))

 FastApi 入口

(apps\vadmin\iot\views.py)

###########################################################
#    设备地理位置
###########################################################
@app.get("/device-location", summary="获取设备地理位置列表", tags=["设备地理位置"])
async def get_deviceLocation_list(p: params.DeviceLocation = Depends(), auth: Auth = Depends(AllUserAuth())):
    datas, count = await crud.DeviceLocationDal(auth.db).get_datas(**p.dict(), v_return_count=True)
    return SuccessResponse(datas, count=count)


@app.post("/device-location", summary="创建设备地理位置", tags=["设备地理位置"])
async def create_deviceLocation(data: schemas.DeviceLocation, auth: Auth = Depends(AllUserAuth())):
    return SuccessResponse(await crud.DeviceLocationDal(auth.db).create_data(data=data))


@app.delete("/device-location", summary="删除设备地理位置", description="硬删除", tags=["设备地理位置"])
async def delete_deviceLocation_list(ids: IdList = Depends(), auth: Auth = Depends(AllUserAuth())):
    await crud.DeviceLocationDal(auth.db).delete_datas(ids=ids.ids, v_soft=False)
    return SuccessResponse("删除成功")


@app.put("/device-location/{data_id}", summary="更新设备地理位置", tags=["设备地理位置"])
async def put_deviceLocation(data_id: int, data: schemas.DeviceLocation, auth: Auth = Depends(AllUserAuth())):
    return SuccessResponse(await crud.DeviceLocationDal(auth.db).put_data(data_id, data))


@app.get("/device-location/{data_id}", summary="获取设备地理位置信息", tags=["设备地理位置"])
async def get_deviceLocation(data_id: int, db: AsyncSession = Depends(db_getter)):
    schema = schemas.deviceLocationSimpleOut
    return SuccessResponse(await crud.DeviceLocationDal(db).get_data(data_id, v_schema=schema))

Examples:

Python操作

@classmethod
    async def import_iot_data(self, env: Environment = Environment.pro):
        async_session = db_getter()
        db = await async_session.__anext__()
        
        # 清空表
        await db.execute(text('SET FOREIGN_KEY_CHECKS = 0;'))
        await db.execute(delete(DeviceLocation))
        await db.execute(text('SET FOREIGN_KEY_CHECKS = 1;'))
        
        file = os.path.join(self.SCRIPT_DIR, "data", "kafka_messages.log")
        with open(file) as f:
            for line in f:
                log = json.loads(line)
                if "id" in log:
                    sql = select(Device).where(Device.code == log['id'])
                    queryset = await db.scalars(sql)
                    device: Device = queryset.one()
                    if device:
                        print(f"{device.id}: {device.code}: {device.label}")
                        newModel = {
                            "iot_device_id":device.id,
                            "coordinates":f"POINT({log['lat']} {log['lng']})",
                            "create_datetime":log['time'],
                        }
                        await db.execute(insert(DeviceLocation), newModel)
                        await db.flush()
                # print(log)
        await db.commit()

 Sql 查询

​
INSERT INTO ia_iot_device_location (label, coordinates) VALUES ('New York City', POINT(-74.0060, 40.7128));
INSERT INTO ia_iot_device_location (label, coordinates) VALUES ('帝国大厦', POINT(-73.98577,40.748418));


# 使用ST_DISTANCE函数计算两个地理点之间的距离，并过滤出距离纽约市不到10公里的位置信息。
SELECT label
FROM ia_iot_device_location
WHERE ST_DISTANCE(coordinates, POINT(-74.0060, 40.7128)) < 10;

​

Api

添加：

 数据库记录

查询:

 

参考：

- Working with Spatial Data using FastAPI and GeoAlchemy

- sql server中对geometry类型的常用操作、SRID、GeoTools工具简单使用，geometry和geojson格式之间的转换_sqlserver sde geometry 转text-CSDN博客