返回APP

分享到

航运大数据技术课程实验指南,持续更新....
 
Notifications
Clear all

航运大数据技术课程实验指南,持续更新...


杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

一、Postgresql下载地址:

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

根据本机的操作系统类型,选择下载安装包文件。如下图。

image

 

以下以Windows操作系统为例,介绍在Windows下的安装、初始化和导入数据的步骤和注意事项:

 

二、为防止最后初始化数据库失败而提前采取的措施:

具体情况介绍和采取的措施参考:

https://blog.51cto.com/u_15677788/5437054

比如,如果要打算把postgresql数据库服务器安装在C:\postgresql 文件夹下,可以先在C盘新建该文件夹,然后按照上方链接的介绍,修改这个文件夹的访问权限。

 

三、执行数据库的安装

1,以管理员身份运行下载的数据库服务器安装文件,如下图:

image

注意:要检查下载文件大小,以防在下载过程中出错,下载的文件不完整,导致不能正常安装。

右键点击安装文件,从右键菜单中,选择“以管理员身份运行”,如下图:

image

注意:windows操作系统可能会弹出安全警示对话框,类似如下:

image

请点击“运行”。

 

2,后续步骤图如下:

image

 

image

 

image

 

image

 

image

 

image

 

image

 

image

 

image

 

image

 

image

3,安装客户端工具和PostGIS组件

安装结束后,会自动跳转到如下安装界面:

image

后续的安装步骤图如下:

image

1,展开Add-ons, tools and utilities,勾选pgAgent for PostgreSQL,这个组件是postgresql数据库服务器的客户端软件。

2,展开Spatial Extensions,勾选下方的PostGIS 3.4 Bundle for PostgreSQL,这个是postgresql的空间扩展组件。

如下图:

image

选择组件安装文件下载目录,建议:保持默认,如下图:

image

下载组件,这一步会等待较长时间,请耐性等待,保持网络连接正常。如下图:

image

下载组件完毕后,会自动跳转到安装组件界面,如下图:

image

后续的安装步骤图如下:

image

 

image

 

image

 

image

 

image

 

image

 

image

 

image

 

image

至此,安装完成。

 

This topic was modified 3月 前 by HIFLEET

引用
杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

在Postgresql中建数据库、Schema和数据表操作介绍

以下以在windows操作系统中为例:

一、启动pgAdmin,连接Postgresql数据库服务器

1,启动pgAdmin,方式图解如下:

image

启动界面如下:

image

2,连接Postgresql数据库服务器,图解如下:

image

 

image

 

image

3,创建新的数据库和Schema,图解如下:

image

 

image

 

image

 

image

 

image

 

image

4,创建新的数据表,图解如下:

image

 

image

 

image

 

 

image

 

image

 

image

至此,新建数据库,Schema,数据表操作结束。


回复引用
杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

在Postgresql中导入数据操作介绍

以下以在windows操作系统中,向已经建立好的ais_data 下的static_ships 表中导入本帖附件中的数据为例。

1,打开Query Tool窗口,图解如下:

image

 

image

 

2,在Query区域,输入类似如下命令:

COPY AIS_DATA.STATIC_SHIPS (ID,MMSI,IMO,CALLSIGN,SHIPNAME,SHIPTYPE, TO_BOW,TO_STERN,TO_STARBOARD,TO_PORT,ETA,DRAUGHT,DESTINATION,TS) FROM 'd:/static_ships_from_pg/static_ships_from_pg.txt';

注意:红字部分表示要导入的数据文件在哪个路径下。请根据要导入的数据文件实际保存位置替换上述红字部分的内容。

按如下图解,执行该导入数据命令:

image

3,查看导入的数据数量,

输入如下命令,可以查询向static_ships数据表中导入了多少条数据,图解如下:

image

至此,向ais_data.static_ships 表中导入数据完成。

要导入的静态数据文件在这里:


回复引用
杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

在Postgresql中导入船舶ais动态数据操作介绍:

以下以在Windows操作系统中导入船舶ais动态数据为示例:

1,按照上述方法新建一个数据表,数据表名称:dynamic_ships,数据表的字段和主键约束等图示如下:

image

 

image

 

image

 

2,在Query Tool 里输入类似如下命令:

COPY AIS_DATA.dynamic_ships (ID,MMSI,ts,lon,lat,course, speed,heading,rate_of_turn,navigation_status) FROM 'd:/maritime_informatics/dynamic_ships_from_pg.txt';

注意:红字部分要根据下载的待导入数据的存放路径进行修改。

 

3,选中上述命令,开始执行导入。

 

4,导入结束后,可以利用如下命令,查询导入了多少条数据到数据表中:

image

至此,向ais_data.dynamic_ships表中导入船舶ais动态数据操作结束。

 

要导入的动态数据文件在此:

https://hifleet-static-files.oss-cn-beijing.aliyuncs.com/maritime_informatics/dynamic_ships_from_pg.rar


回复引用
liu
 liu
访客
已加入: 3月 前
帖子: 1
 

还原后 shiptype

to_bow

to_stern

to_starboard

to_port 都是整数,应该是id。每个id对应的什么?


回复引用
杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

1,关于shiptype

按照AIS技术规范(文件附后)第113页表52中的介绍,参考第114页表53的内容,可以获得该字段值对应的船舶类型。

2,关于to_bow, to_stern, to_starboard, to_port:

按照上述规范,参考第113页表52中的介绍,以及参考115页图41的示意。

 

3,关于id

这个是数据表中的主键字段,用于标识数据表中每条数据的唯一标识符。

 

附件:AIS相关的技术规范(现在不一定是最新的版本了,但是仍然具有参考价值)


回复引用
杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

在QGIS中可视化船舶AIS动态数据。

1,QGIS的下载安装

QGIS的下载地址如下:

https://qgis.org/en/site/

安装不复杂,与一般应用软件类似,一路next。此处不再详细介绍。

后续篇幅,以windows操作系统下,QGIS3.0版本为介绍对象。

image

 

2,添加底图图层(以最简单常见的添加瓦片(tile)式图层为例)

(1)安装好QGIS后,打开该软件。

image

 

image

 

Name可以自定义,

以显示必应地图为例,URL地址处填写:

https://t1.dynamic.tiles.ditu.live.com/comp/ch/{q}?mkt=zh-CN&ur=CN&it=G,RL&n=z&og=804&cstl=vb

image

 

image

 

image

 

到此,在QGIS中将必应地图作为底图图层展示操作完毕。

 

3,建立矢量数据表

在PostgreSQL数据库中给dynamic_ships表添加时间和空间字段数据

打开pgAdimin(操作介绍在上面帖子),在Query Tool窗口内,分别依次执行如下命令:

alter table ais_data.dynamic_ships add column t timestamp without time zone;

update ais_data.dynamic_ships set t=to_timestamp(ts); 

这两条命令,给dynamic_ships数据表添加了时间字段;

执行下面的命令,给新添加的时间字段(t),添加索引:

CREATE INDEX idx_dynamic_ships_t ON ais_data.dynamic_ships using btree(t);

再依次执行下面两条命令:

alter table ais_data.dynamic_ships add column geom geometry(Point,4326);

update ais_data.dynamic_ships set geom=st_setSRID(ST_MakePoint(lon,lat),4326);

image

上面两条命令,给dynamic_ships添加空间字段并更新该字段值。

至此,在PostgreSQL数据库以船舶动态AIS数据为基础,建立空间数据表。

 

4,在QGIS中显示矢量数据

4.1:在QGIS中连接PostGIS

右键点击PostGIS,在右键菜单中选择New Connection... 如下:

image

在弹出的窗口中,注意红框处的填写:

image

Name:自定义连接名称;

Service:不要填,保持空白即可;

Host:根据PostgreSQL数据库安装的情况填写,比如,如果PostgreSQL数据库安装在本机,则填写:localhost

Port:根据PostgreSQL数据库安装时的端口填写,如果在安装PostgreSQL数据库时没有改动默认端口,则默认端口是5432

Database: 根据上面帖子中的教程,此处填写:ship_ais

image

测试连接是否成功:

image

 

4.2 展开PostgreSQL数据库中的库表,添加矢量数据图层

image

 

image

4.3 修改矢量图层显示

右键点击“dynamic_ships”图层,在右键菜单上选择“Properties”菜单:

image

在弹出的窗口上,选择“Symbology”选项卡,注意:红框处,可以调节矢量数据显示的颜色,透明度,形状,大小等参数:

image

 

4.4 从矢量数据中筛选显示数据

以在矢量数据中筛选我校育明轮的轨迹数据点为例。

在“dynamic_ships”图层,在右键菜单上选择“Filter...”菜单。在弹出的对话框中输入如下:

image

 

image

4.5 给矢量数据显示标签信息

右键点击“dynamic_ships”图层,在右键菜单上选择“Properties”菜单。在弹出的对话框里选择Labels 选项卡:

image

 

image

 

image

这样,就给轨迹点数据显示了他的时间标签。

 

 

 

 

 

 

 

 

 

 

 

 

 

 


回复引用
杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

在QGIS中显示航迹线

1,依次在pgAdmin的Query Tool中执行如下命令,建立轨迹线段数据。

ALTER TABLE ais_data.dynamic_ships ADD COLUMN geom3035 geometry(Point,3035);

UPDATE ais_data.dynamic_ships SET geom3035=ST_Transform(geom, 3035);

为了提高后续执行速度,使用下面的命令,在geom3035字段上加上索引:

CREATE INDEX idx_dynamic_ships_geom3035 ON ais_data.dynamic_ships USING GiST(geom3035);

使用下面的命令,建立轨迹段数据:

create table ais_data.segments as
select mmsi,t1,t2,speed1,speed2,p1,p2,
st_makeline(p1,p2) as segment,
st_distance(p1,p2) as distance,
extract(epoch from (t2-t1)) as duration_s,
(st_distance(p1,p2)/(1+extract(epoch from (t2-t1)))) as speed_m_s
from (
select mmsi,lead(mmsi) over (order by mmsi,t) as mmsi2,
t as t1,
lead(t) over (order by mmsi, t) as t2,
speed as speed1,
lead(speed) over (order by mmsi,t) as speed2,
geom3035 as p1,
lead(geom3035) over (order by mmsi,t) as p2
from ais_data.dynamic_ships) as q1
where mmsi = mmsi2;

 

2,在QGIS中显示轨迹线段

在QGIS中,鼠标右键点击先前建立的PostGIS数据库,选择“Refresh”,刷新数据表,如下图:

image

刷新后,可看到前面建立的轨迹段矢量数据表:

image

双击该数据,QGIS会自动将他添加到图层中:

image

 

image

可见,数据中存在不少问题数据点。可思考、设计相应的算法处理掉。

 


回复引用
杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

在上面操作的基础上,利用所提供的船舶AIS动态数据识别停船点,停船区域,并可视化(含“火星坐标”纠偏操作)。

一、识别开始停船、结束停船

在pgAdmin 的 Query Tool 窗口中依次执行如下6条SQL语句:

drop table if exists ais_data.stop_begin;
CREATE TABLE if not exists ais_data.stop_begin AS 
	SELECT mmsi, t2 as t_begin
	FROM ais_data.segments
WHERE speed1 >0.1 AND speed2 <=0.1; 	
CREATE INDEX idx_stop_begin_mmsi_t ON ais_data.stop_begin USING btree(mmsi,t_begin);

drop table if exists ais_data.stop_end;
CREATE TABLE if not exists ais_data.stop_end AS 
	SELECT mmsi , t1 as t_end
	FROM ais_data.segments
WHERE speed1 <=0.1 AND speed2 >0.1; 
CREATE INDEX idx_stop_end_mmsi_t ON ais_data.stop_end USING btree(mmsi,t_end);

 

二、创建停船点数据表

在pgAdmin 的Query Tool窗口依次执行如下SQL语句,在Postgresql中创建stops数据表,并且给该表增加center和nb_pos字段,且center字段的数据类型是Geometry。利用update 语句,更新center字段和nb_pos字段的值。

drop table if exists ais_data.stops;
CREATE TABLE if not exists ais_data.stops AS
	SELECT
		mmsi , 
		t_begin , 
		t_end , 
		extract (epoch FROM (t_end-t_begin)) as duration_s
	FROM ais_data.stop_begin INNER JOIN LATERAL (
		SELECT t_end
		FROM ais_data.stop_end
		WHERE stop_begin.mmsi= stop_end.mmsi AND
		t_begin <= t_end 
		ORDER BY t_end LIMIT 1 
	) AS q2 ON (true);
ALTER TABLE ais_data.stops ADD COLUMN center geometry (Point, 4326);
ALTER TABLE ais_data.stops ADD COLUMN nb_pos integer ;

UPDATE ais_data.stops SET (center, nb_pos ) = (
SELECT st_centroid(st_collect(geom)) , 
count(id) as nb
FROM ais_data.dynamic_ships
WHERE mmsi= stops.mmsi AND
t >= stops.t_begin AND t <= stops.t_end
);

 

三、在QGIS中可视化停船点数据并纠偏

按照上述介绍的步骤,打开QGIS软件,并显示地图,展开PostGIS下的数据表。如下图:

image

 

image

由于众所周知的“火星坐标”问题(请自行百度了解有关情况),轨迹点在图上显示时(准确的说,在我国范围内的地图上显示时),会发生偏移现象,如上图,因此需要“纠偏”处理。即,把正确的轨迹点位置“纠偏”到图上的“正确”位置。

可以利用QGIS的相关插件进行。如下:

(1)在QGIS的菜单栏选择“Plugins”  -- Manage and Install Plugins...

image

(2)搜索安装插件“geohey toolbox”

image

(3)安装完毕后,在QGIS的菜单栏中依次选择:“Processing” -- Toolbox 可以在右侧打开工具箱。

在工具箱中寻找并展开Geohey,可以看到该插件下提供了多种转换工具。进行“火星坐标”转换时,选用的工具是"WGS 2 GJC02",如下图。

image

(4)对停船轨迹点数据进行纠偏

image

 

image

纠偏后的效果如下:

image

到这里,对“火星坐标”进行“纠偏”的操作介绍完毕。后续需要纠偏的操作,以此类同不再重复介绍。

 

四、利用Postgresql自带的DBSCAN算法识别计算停船点区域

在pgAdmin的Query Tool中依次执行如下SQL命令:

ALTER TABLE ais_data.stops ADD COLUMN center geometry (Point, 4326);
ALTER TABLE ais_data.stops ADD COLUMN nb_pos integer ;

UPDATE ais_data.stops SET (center, nb_pos ) = (
SELECT st_centroid(st_collect(geom)) , 
count(id) as nb
FROM ais_data.dynamic_ships
WHERE mmsi= stops.mmsi AND
t >= stops.t_begin AND t <= stops.t_end
);

ALTER TABLE ais_data.stops ADD COLUMN avg_dist_centroid numeric ;
ALTER TABLE ais_data.stops ADD COLUMN max_dist_centroid numeric ;

UPDATE ais_data.stops
SET (avg_dist_centroid,max_dist_centroid) =(
SELECT avg(d),max(d)
FROM (
SELECT st_distance (center,geom) as d 
FROM ais_data.dynamic_ships
WHERE mmsi= stops.mmsi AND
t >= stops.t_begin AND t <=stops.t_end -- timestamp range
) as q1);

CREATE INDEX idx_stops_center ON ais_data.stops USING gist(center);

-- 注意:
--(1)0.00045,单位:度,大约是50米。计算方式是 0.00045 = 50/(1852*60)
--(2)5,单位:个,即在这个50米的范围内,轨迹点的数量不小于5个
-- (3)60,单位:秒。即船舶停泊的持续时间不小于1分钟
CREATE TABLE if not exists ais_data.cluster_stops AS
SELECT *,ST_ClusterDBSCAN(center,eps:=0.00045, minpoints:=5) OVER() AS cid
FROM ais_data.stops
WHERE duration_s>=60; 

CREATE TABLE if not exists ais_data.cluster_stops_hulls AS
SELECT cid,
ST_ConvexHull(st_collect(center)) as convex_hull,
ST_MinimumBoundingCircle(st_collect(center)) as bounding_circle,
ST_Centroid(st_collect(center)) as centroid,
count(*) as nb_stops,
sum(nb_pos) as nb_pos, 
count(DISTINCT mmsi ) as nb_ships, 
min(duration_s) as min_dur ,
avg(duration_s) as avg_dur ,
max( duration_s) as max_dur
FROM ais_data.cluster_stops
WHERE cid IS NOT NULL 
GROUP BY cid; 

上述SQL命令,会在Postgresql中创建:

cluster_stops, cluster_stops_hulls 表。

在QGIS中可以可是话上述表中的空间数据字段,如下图所示:

image

需要纠偏的,请参照上述纠偏介绍进行处理。

 

 

 

 

This post was modified 2月 前 by HIFLEET

回复引用
杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

在QGIS上展示类google地图的卫星遥感图层

以下介绍在QGIS上展示类似Google Earth的卫星遥感图层。

image

 

image

在URL栏输入:

https://api.mapbox.com/styles/v1/yangchun/cjpy1gzxd10fi2smh0475aexr/wmts?access_token=pk.eyJ1IjoieWFuZ2NodW4iLCJhIjoiY2lyN2hjd2xmMDAwdWlmbm5yd3dtN2xkNCJ9.ymq4Tyc-aS2M4fRuqFPTKQ

 

image

这个图层没有“火星坐标”问题,不需要用Geohey Toolbox进行“纠偏”转换,可以直接将wgs坐标系下的数据显示在图层上。如下图:

image

回复引用
上海海事大学310宿舍
 上海海事大学310宿舍
访客
已加入: 2月 前
帖子: 1
 

Postgresql安装包打开后提示如下内容
并且采用管理员权限打开此安装包也没用

 

回复引用
杨春
Active Member
已加入: 3年 前
帖子: 13
Topic starter  

建议做如下尝试:

1,将安装文件放在其他的文件夹下,比如D盘某个路径下,比如D:/postgres_install_file,安装结束后,可以将该安装文件删除掉;

2,安装文件所在的路径以及数据库安装到的目录,其路径都不要包含中文,比如,在C盘新建目录postgresql,然后安装postgresql时,将Postgresql安装到c:/postgresql 


回复引用

留下回复

Author Name

Author Email

标题 *

文件不超过 100MB

Preview 0 Revisions Saved

 

滚动到顶部