1、postgres控件postgis安装
CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology;
2、计算路段长度(单位M)
SELECT guid,ST_Length(gemo,false) distance from gemotable
gemotable :表名
guid:键值
gemo:表中gemo数据的字段名称
distance:查询结果中长度字段名称
3、计算路段定长分割点
SELECT st_astext(ST_LineInterpolatePoint(ST_LineMerge(gemo),{i/lenth})) FROM gemotable WHERE guid = ‘';
gemotable :表名
guid = '’:指定路段的查询条件
gemo:表中gemo数据的字段名称
i、lenth:第几个分割点,分割点总数(如定长为1cm,则分割点总数=长度*100)
4、分割点计算python
open(file, 'w', encoding='utf-8').close()
gps,m,l={},0,[]
for i in range(lenth):
sql1=f"SELECT st_astext(ST_LineInterpolatePoint(ST_LineMerge(geom),{i/lenth})) FROM geomtable WHERE guid = '{gid}';"
cur.execute(sql1)
distance = cur.fetchall()
if list(distance):
#获得经纬度
long,lat=distance[0][0].replace("(","").split(" ")[2:4]
#根据前一个点经纬度,计算进入该点的角度
degree=self.getDegree(gps_point_map[luxian][i - 1][:2], (long, lat)) if i > 0 else config["gps"]["firstdegree"]
gps[i]=(long,lat,degree)
m+=1
if m%1000==0 or i+1==lenth:
#进度及数据保存
print(time.time(),(i+1)/lenth)
l0=sorted(list(set(gps)-set(l)))
with open(file, 'a+', encoding='utf-8') as f:
[f.write(",".join([str(j) for j in gps[k]])+"\n") for k in l0]
l=list(gps)