python-通过pgsql计算gps距离

语言：python3
功能：通过pgsql计算gps距离

加载空间插件
CREATE EXTENSION postgis;

计算距离
SELECT round( ST_Distance (
ST_GeomFromText (‘POINT(108.278508 22.866469)’,4326),
ST_GeomFromText (‘POINT(108.278523 22.866539)’,4326) ) * 60 * 1852)
AS distance

# -*- coding: utf-8 -*-
#通过pgsql计算gps距离
import psycopg2
class PgsqlClient():
    def __init__(self,sqlconfig):
        # sqlconfig={'host':'172.18.2.148', 'port':5432, 'user':'postgres', 'passwd':'1234zxcv', 'db':'policedb'}
        self.sqlconfig=sqlconfig
        self.connect()

    def connect(self):
        try:
            self.conn=psycopg2.connect(host=self.sqlconfig['host'], port=int(self.sqlconfig['port']), user=self.sqlconfig['user'], password=self.sqlconfig['passwd'], database=self.sqlconfig['db'])
        except:
            print("连接异常")

    def search(self,sql):
        self.cursor = self.conn.cursor()
        self.cursor.execute(sql)
        try:
            row= self.cursor.fetchall()
            # print("查询成功")
        except:
            row=[]
            print("查询无返回")
        self.conn.commit()
        self.cursor.close()
        return row

    def deal(self,sql):
        self.cursor = self.conn.cursor()
        self.cursor.execute(sql)
        row,message= "","影响行数"+str(self.cursor.rowcount())
        self.conn.commit()
        self.cursor.close()

    def conclose(self):
        self.conn.close()

sqlconfig={'host':'127.0.0.1', 'port':5432, 'user':'postgres', 'passwd':'123456', 'db':'test'}
sql='''SELECT round( ST_Distance ( ST_GeomFromText ('POINT(long1 lat1)',4326), ST_GeomFromText ('POINT(long2 lat2)',4326)) * 60 * 1852) AS distance'''

p=PgsqlClient(sqlconfig)
datalist=[((108.275759,22.866836),( 108.275744,22.866781))]
for i in datalist:
    sql0 = sql.replace("long1", i[0][1]).replace("lat1", i[0][0])
    sql1=sql0.replace("long2", i[1][1]).replace("lat2", i[1][0])
    distance = p.search(sql1)[0][0]
    print(distance ,i)
p.conclose()