航海大数据作业：第三章练习题

[TOC]

一：实验前准备

1.1本书重要索引网站（非常重要！！！！）

1.SQL-答案文件：第三章习题答案
2.需要处理的数据：数据集地址

注意：打开以上两个网站需要魔法

1.2知识准备

1.2.1所要处理的数据类型如下

•导航数据，即围绕主要航线航行的船舶的AIS历史位置;
•面向船舶的数据(例如船舶注册，即具有其特征的船舶列表);
•地理数据(即船舶航行的制图、地形和监管背景);
•环境数据(即气候和海况相关信息)

1.2.2领先的海洋信息研究课题

•船舶轨迹分析和预测，最近主要集中在AIS系统和补充源来预测和分析船舶轨迹[
•交通预测，如港口吞吐量处理和货物吞吐量预测
•碰撞预防，通过分析船舶的航行行为来分析船舶碰撞的风险
•船舶检测、分类和识别，分析划定区域内摄像机的视频，例如检测小型船只，或在较大区域内的遥感图像
•异常检测，主要采用数据驱动的方法对正常流量进行建模，任何异常行为都与潜在威胁相关
•对海上人类活动的分析，如捕鱼、非法交通(人类、毒品、货物)、海盗
•多源信息融合，减少可用数据的不确定性，提高分析的准确性
•海上运输网络的动态，其中通过设计用于分析行为模式的网络抽象，在聚合级别考虑轨迹数据

1.2.2重要概念

imo(国际海事组织)、
mmsi(海上移动业务标识)
SQL(Structured Query Language):结构化查询语言
B-Tree/R-Tree/GiST(广义搜索树)

1.3软件下载安装（非常重要！！！！）

####1.3.1PostgreSQL
为了组织、存储、访问和处理海事数据的数据库管理系统（DBMS）：PostgreSQL
a.PostGIS是强大的附件，为DBMS增加了地理空间功能
b.PGAdmin可以创建和查询新的数据库和表

注意：只需安装PostgresSQL,PostGIS与PGAdmin为扩展应用

1.3.2QGIS

可视化分析工具： QGIS

1.3.3软件版本

==本章中介绍的所有查询都是使用本地安装的PostgreSQL 11.5、PostGIS 2.5.3和pgAdmin4v4.18 (Windows x86-64)编写的==

二：开始实验

2.1建立数据库并进行查询

2.1.1建立数据库maritime_informatics

打开pgAdmin

建立数据库

命名数据库

建立模式ais_data

2.1.2创建表ais_data . static_ships

postgres=# CREATE TABLE ais_data . static_ships ( id bigserial,sourcemmsi integer,imo integer ,callsign text ,shipname text ,shiptype integer ,to_bow integer ,to_stern integer,to_starboard integer ,to_port integer ,eta text ,draught double precision ,destination text ,mothershipmmsi integer ,ts bigint ,CONSTRAINT static_ships_pkey PRIMARY KEY(id) );

2.1.3加载数据到数据库

postgres=# COPY ais_data . static_ships(sourcemmsi , imo , callsign , shipname , shiptype, to_bow ,to_stern ,to_starboard ,to_port , eta , draught , destination , mothershipmmsi , ts ) FROM 'E:\YANYI\[P1] AIS Data\nari_static.csv 'DELIMITER ',' CSV HEADER;

注意：绝对路径要用英文

2.1.4查询计算了表static_ships中列出了多少个不同的船名

SELECT count(DISTINCT shipname) as nb FROM ais_data.static_ships;

2.1.5对船舶的重复信息，使用SQL视图将该表中的重复行分组在一起

postgres=# CREATE OR REPLACE VIEW ais_data.ships AS SELECT sourcemmsi , imo , callsign , shipname FROM ais_data . static_ships GROUP BY sourcemmsi , imo , callsign, shipname ;