geom_img/db_util.py

# 连接数据库
import psycopg2
from psycopg2.extras import DictCursor
import logging

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


conn = psycopg2.connect(
    dbname="test",
    user="postgres",
    password="postgis",
    host="192.168.100.30",
    port="5432"
)


def getDiffEwkt(code, ewkt):
    with conn.cursor(cursor_factory=DictCursor) as cur:
        # sql = """ SELECT
        #                 st_asewkt ( st_difference ( st_geomfromewkt ( %s ), tb.geom ) )
        #             FROM
        #                 tb
        #             WHERE
        #                 tb."编号" = %s;"""
        sql="""SELECT
	st_asewkt ( st_difference ( st_geomfromewkt ( %s ), st_union ( tb.geom ) ) )
FROM
	tb
WHERE
	st_contains ( st_geomfromewkt ( %s ), tb.geom );"""
        # complete_sql = cur.mogrify(sql, (ewkt,ewkt,)).decode('utf-8')
        # logger.info(f"Executing SQL: {complete_sql}")
        cur.execute(sql, (ewkt,ewkt,))
        res = cur.fetchone()
    return res[0]
# ewkt='SRID=4490;POLYGON ((106.67844989540063 38.207187208754306, 106.67844989540063 38.212680372816806, 106.67295673133813 38.212680372816806, 106.67295673133813 38.207187208754306, 106.67844989540063 38.207187208754306))'
# getEwkt=getDiffEwkt('6401812024070108270001',ewkt)
# print(getEwkt)