postgres 基本postgis扩展将点生成线

前言

之前有个需求，通过时间轴加载船的位置（点）和船行驶的轨迹（线），分析船只活动的时间段和范围。

给了一批数据比较烂导入到了postgres数据库，经过清洗有old，编号，方向，坐标，日期等。

1）由于是时间轴加载需要转换成unix时间戳

2）对加了时间戳的数据再加工（去重，避免加载多次相同的）

3）将坐标生成线（轨迹）

4）给轨迹加上年月周的标签

通过时间轴生成的时间转换成时间戳加载坐标，轨迹（小时/天/周/月年）

文件导入

//csv导入
copy iot_menu from '/home/postgres/a.bak' with csv;

copy aa_log (id, callid, calledid) from 'e:\t01.csv' with (format csv, header true, quote '"', DELIMITER ',', encoding 'UTF8');

时间转换

导入数据格式为日期：xxxx-xx-xx

update rm set time=date(to_timestamp(tstamp));                                //时间戳转日期(日) tstamp时间戳
update rm set tstamp=extract(epoch from data_TRUNC("minute",to_timestamp(time,"YYYY-MM-DD HH24:MI:SS")))  //日期转时间戳
select to_char(to_timestamp(tstamp),'YYYYMMDDWW') from rm limit 10; //周

数据去重：ctid （postgres有内置ctid字段去重）

create table new as select * from tablename where ctid in ( select min(ctid) from tablename group by tstamp || baich_no) //去重 时间戳和id分组

geom地理要素：

postgres常用语法：传送门

alter table rm add column geom geometry;    //数据库增加geom字段
update tablename set geom=st_point(x,y);    //x,y转成地理要素
# insert into test values(st_transform(st_geomfromtext('POINT(10070507.650288 4282901.6281314)',900913),4326));            

 生成轨迹线地理要素：

create table rmt as (select oid,month,week,st_makeline(st_point(x,y)) from rm group by oid,month,week); #将x,y生成轨迹线

发不成geoserver服务图层，可通过url直接访问数据。

时间轴加载:https://blog.csdn.net/weixin_40902527/article/details/85790628