postgres 基本postgis扩展将点生成线

谁借莪１个温暖的怀抱￠ 2022-03-16 16:20 521阅读 0赞

### 前言 ###

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

给了一批数据比较烂导入到了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常用语法：[传送门][Link 1]

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][https_blog.csdn.net_weixin_40902527_article_details_85790628]

[Link 1]: http://www.cnblogs.com/cf532088799/p/7592888.html
[https_blog.csdn.net_weixin_40902527_article_details_85790628]: https://blog.csdn.net/weixin_40902527/article/details/85790628