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

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

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

1，启动pgAdmin，方式图解如下：

启动界面如下：

2，连接Postgresql数据库服务器，图解如下：

3，创建新的数据库和Schema，图解如下：

4，创建新的数据表，图解如下：

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

在Postgresql中导入数据操作介绍

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

1，打开Query Tool窗口，图解如下：

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';

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

按如下图解，执行该导入数据命令：

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

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

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

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

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

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

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

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，导入结束后，可以利用如下命令，查询导入了多少条数据到数据表中：

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

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

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

还原后 shiptype

to_bow

to_stern

to_starboard

to_port 都是整数，应该是id。每个id对应的什么？

1，关于shiptype

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

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

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

3，关于id

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

附件：AIS相关的技术规范（现在不一定是最新的版本了，但是仍然具有参考价值）

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

1，QGIS的下载安装

QGIS的下载地址如下：

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

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

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

2，添加底图图层（以最简单常见的添加瓦片（tile）式图层为例）

（1）安装好QGIS后，打开该软件。

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

到此，在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);

由于提供的动态数据量多大700多万条数据，为了便于后续的计算处理，防止在硬件配置较低的计算机上处理时出现卡顿现象，建议新建一张新的数据表用于存储少量的数据用作后续的实验。方法如下：

在PostgreSQL数据库参照dynamic_ships的表结构新建一张表，存储少量数据。

create table ais_data.dynamic_ships_mini ( like ais_data.dynamic_ships including indexes including defaults);

然后从dynamic_ships表中向新建的表中插入少量的数据，比如运行如下命令，插入3个小时的数据：

insert into ais_data.dynamic_ships_mini select * from
ais_data.dynamic_ships where t>='2019-09-01 0900' and t<='2019-09-01 1200';

随后的实验就可以在这份少量数据表的基础上继续完成。

首先执行如下命令，让PostgreSQL支持空间数据类型：

CREATE EXTENSION postgis ;

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

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

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

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

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

4，在QGIS中显示矢量数据

4.1：在QGIS中连接PostGIS

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

在弹出的窗口中，注意红框处的填写：

Name：自定义连接名称；

Service：不要填，保持空白即可；

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

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

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

测试连接是否成功：

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

4.3 修改矢量图层显示

右键点击“dynamic_ships_mini”图层，在右键菜单上选择“Properties”菜单：

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

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

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

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

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

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

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

在QGIS中显示航迹线

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

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

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

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

CREATE INDEX idx_dynamic_ships_geom3035 ON ais_data.dynamic_ships_mini 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_mini) as q1
where mmsi = mmsi2;

2，在QGIS中显示轨迹线段

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

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

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

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

在上面操作的基础上，利用所提供的船舶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_mini
WHERE mmsi= stops.mmsi AND
t >= stops.t_begin AND t <= stops.t_end
);

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

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

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

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

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

（2）搜索安装插件“geohey toolbox”

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

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

（4）对停船轨迹点数据进行纠偏

纠偏后的效果如下：

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

四、利用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_mini
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_mini
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中可以可是话上述表中的空间数据字段，如下图所示：

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

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

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

在URL栏输入：

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

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

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

建议做如下尝试：

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

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

走到这一步出错了 求解谢谢

这个报错信息提示数据库不认识“Geometry"这个数据类型。

出现这个错误的原因应该是在安装Postgresql的时候没有同步安装Postgis。

请参考如下链接再安装一下Postgis：

https://blog.csdn.net/LY1201A/article/details/107080664

也可能是没有在PostgreSQL中执行如下命令：

create extension postgis;

hello