需求:
在a表中有1000个点,b表中有5000条线路,现在需要一次查询a表中1000个点的最近线路。
我知道在postgis中可以通过ST_Distance实现快速查询,但每次只能查询a表中的1个点,自己太菜了不能够把这1000点一次性全部查询出来。弄了好久都没成功。最终还是通过python和postgis结合的方式总算实现了目的。
1、postgis ST_Distance()的查询方法如下,能查询出a表中id=1的点它最近的线路。
SELECT a.id,a.点名称,b.id,b.线路名称,a.geom::geography<->b.geom::geography "Distance"
FROM "a_point" a, "b_road" b
WHERE a.id=1
ORDER BY ST_Distance(a.geom, b.geom) asc
limit 1
上面为SQL语句,每次只能查询1个点,自己对SQL实在是太生疏了,没找到高级的方法来解决它。
2、通过python和postgis数据库相结合的方式也可以来解决查询需求。
import psycopg2
import datetime
import pandas as pd
if __name__ == "__main__":
st_time = datetime.datetime.now()
connect_ = psycopg2.connect(
database="postgis_33_sample",
user="*****",
password="*****",
host="127.0.0.1",
port="5432")
cursor_ = connect_.cursor() # 创建游标对象
sql1 = '''select * from tqdian''' # sql1查询该数据库一共有多少条数据
cursor_.execute(sql1) # 执行sql1命令
numb = cursor_.rowcount
df2 = [] # 定义df2,用于存放查询结果
for n in range(1, numb + 1):
sql2 = '''
SELECT a.id,a.点名称,b.id,b.线路名称,a.geom::geography<->b.geom::geography "Distance"
FROM "a_point" a, "b_road" b
WHERE a.id = %s
ORDER BY ST_Distance(a.geom, b.geom) asc
limit 1
'''
parameter_ = (n,) # 这里使用变量参数%s查询的方法,利用id查询
cursor_.execute(sql2, parameter_) # 执行sql2
count_ = cursor_.fetchone() # 抓取结果
connect_.commit() # 事物提交
df2.append(count_)
print("查询进度:", "{:.2%}".format(n / (numb + 1)))
print("恭喜,查询完成。")
cursor_.close()
connect_.close() # 关闭数据库连接
ed_time = datetime.datetime.now()
print("查询时长: ", ed_time - st_time)
df2 = pd.DataFrame(df2)
df2.to_excel(r'd:\查询结果_ST_Distance.xlsx', encoding='gb18030', index=None)
通过此方法可以在几十秒内完成查询,虽然有点慢,但总算还是实现了。
后面发现有更好的方法再更新吧。