大数据操作实验一

1.Postgresql

1.1 数据库的对象创建

1.1.1 创建数据库(Database)

鼠标右键database进行创建
在这里插入图片描述

1.1.2 创建图(Schema)

鼠标右键schema，然后创建schema图纸
在这里插入图片描述

1.1.3 创建表(Table)

鼠标右键Table，创建表
在这里插入图片描述

1.2数据库实列化

1.2.1 实列化静态数据

提前在Table表中建好相关字段，我新建了个数据库，这边以static_ships表为例，这个static_ships就是上面的demo_table
在表中建立相关字段
在这里插入图片描述

使用query语句进行导入并查询
在这里插入图片描述

--静态数据的字段建立
create table ais_data.static_ships(
	id bigserial,
	sourcemmsi integer,
	imo integer,
	shipname text,
	shiptype integer,
	to_bow integer,
	to_stern integer,
	to_starboard integer,
	to_port integer,
	eta text,
	draught double presion,
	destination text,
	mothershipmmsi integer,
	ts bigint,
	constraint static_ships_pkey primary key(id)
);
--静态数据的导入，copy from句式，图.表（字段） 数据文件来源
COPY ais_data.static_ships (ID,MMSI,IMO,CALLSIGN,SHIPNAME,SHIPTYPE, TO_BOW,TO_STERN,TO_STARBOARD,TO_PORT,ETA,DRAUGHT,DESTINATION,TS) FROM 'G:/data/static_ships_from_pg.txt';
--查询导入的数据数量
SELECT COUNT(*) FROM ais_data.static_ships

1.2.2 实列化动态数据

--以下是创建动态数据表及其相关的字段（字段小写）
create table ais_data.dynamic_ships(
	id bigserial,
	mmsi integer,
	ts bigserial,
	lon double precision,
	lat double precision,
	course double precision,
	speed double precision,
	heading integer,
	rate_of_turn double precision,
	navigation_status integer,
	constraint dynamic_ships_pkey primary key(id)
);

在这里插入图片描述

更新下就能看见动态数据表
下面在query中进行导入动态数据

COPY ais_data.dynamic_ships (ID,MMSI,ts,lon,lat,course, speed,heading,rate_of_turn,navigation_status) FROM 'G:/data/dynamic_ships_from_pg.txt';

在这里插入图片描述
查询相关导入的静态数据个数

SELECT COUNT(*) FROM ais_data.dynamic_ships

在这里插入图片描述

1.3数据可视化

1.3.1新建bing地图

在这里插入图片描述

1.3.2 建立矢量数据表

alter table ais_data.dynamic_ships add column t timestamp without time zone;--增加t列时间戳

update ais_data.dynamic_ships set t=to_timestamp(ts); --ts数据转换时间戳赋值给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);

所有汇总

-- 静态数据的字段建立
-- create table ais_data.static_ships(
-- 	id bigserial,
-- 	sourcemmsi integer,
-- 	imo integer,
-- 	shipname text,
-- 	shiptype integer,
-- 	to_bow integer,
-- 	to_stern integer,
-- 	to_starboard integer,
-- 	to_port integer,
-- 	eta text,
-- 	draught double presion,
-- 	destination text,
-- 	mothershipmmsi integer,
-- 	ts bigint,
-- 	constraint static_ships_pkey primary key(id)
-- );
-- 静态数据的导入，copy from句式，图.表（字段） 数据文件来源
-- COPY ais_data.static_ships (ID,MMSI,IMO,CALLSIGN,SHIPNAME,SHIPTYPE, TO_BOW,TO_STERN,TO_STARBOARD,TO_PORT,ETA,DRAUGHT,DESTINATION,TS) FROM 'G:/data/static_ships_from_pg.txt';
-- 查询导入的数据数量
-- SELECT COUNT(*) FROM ais_data.static_ships
--以下是创建动态数据表及其相关的字段
-- create table ais_data.dynamic_ships(
-- 	id bigserial,
-- 	mmsi integer,
-- 	ts bigserial,
-- 	lon double precision,
-- 	lat double precision,
-- 	course double precision,
-- 	speed double precision,
-- 	heading integer,
-- 	rate_of_turn double precision,
-- 	navigation_status integer,
-- 	constraint dynamic_ships_pkey primary key(id)
-- );
--COPY ais_data.dynamic_ships (ID,MMSI,ts,lon,lat,course, speed,heading,rate_of_turn,navigation_status) FROM 'G:/data/dynamic_ships_from_pg.txt';
-- 查询数据的个数
--SELECT COUNT(*) FROM ais_data.dynamic_ships
--alter table ais_data.dynamic_ships add column t timestamp without time zone;--增加t列，时间戳格式但是无时间区域
--update ais_data.dynamic_ships set t=to_timestamp(ts); --更新动态数据设置时间戳
--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);
--SELECT postgis_full_version();