create schema if not exists geo collate utf8_unicode_ci;

create table geo_data_source
(
    id            int auto_increment comment '主键'
        primary key,
    layer_code    varchar(50)  null comment '图层代码',
    layer_name    varchar(50)  null comment '图层名称',
    host          varchar(20)  null comment '数据库地址',
    port          int          null comment '端口号',
    data_base     varchar(30)  null comment '数据库名',
    user_name     varchar(50)  null comment '用户名',
    user_password varchar(128) null comment '密码',
    status        char(2)      null comment '状态',
    create_user   varchar(30)  null comment '创建人',
    create_time   datetime     null comment '创建时间',
    update_time   datetime     null comment '修改时间',
    update_user   varchar(30)  null comment '修改人',
    sort          int          null comment '排序',
    db_type       varchar(20)  null comment '数据库类型',
    bbox_column   varchar(50)  null comment 'bbox字段',
    table_name    varchar(128) null comment '数据表名'
)
    comment '图层数据源管理表';

INSERT INTO geo.geo_data_source (id, layer_code, layer_name, host, port, data_base, user_name, user_password, status, create_user, create_time, update_time, update_user, sort, db_type, bbox_column, table_name) VALUES (1, 'geo_fk_ship', '融合轨迹', '172.16.67.23', 7306, 'geo', 'root', 'Ucsp123#', '1', null, null, null, null, 1, 'mysql', 'location', 'seat_fk_realtimetrajectory_data');
create table geo_test
(
    id                   int                not null
        primary key,
    name                 varchar(255)       null,
    geometry_1           geometry           null,
    point_1              point              null,
    linestring_1         linestring         null,
    polygon_1            polygon            null,
    multipoint_1         multipoint         null,
    multilinestring_1    multilinestring    null,
    multipolygon_1       multipolygon       null,
    geometrycollection_1 geometrycollection null
)
    charset = utf8;

INSERT INTO geo.geo_test (id, name, geometry_1, point_1, linestring_1, polygon_1, multipoint_1, multilinestring_1, multipolygon_1, geometrycollection_1) VALUES (1, '1', 0x00000000010100000075931804565E5E40083D9B559F3B3F40, 0x00000000010100000075931804565E5E40083D9B559F3B3F40, 0x00000000010200000003000000000000000000F03F00000000000008400000000000002840000000000000144000000000000028400000000000001C40, 0x000000000103000000010000000700000075931804565E5E4079E92631083C3F4091ED7C3F355E5E4024287E8CB93B3F40A01A2FDD245E5E4025068195433B3F4091ED7C3F355E5E40098A1F63EE3A3F4083C0CAA1455E5E4097FF907EFB3A3F405839B4C8765E5E4008AC1C5A643B3F4075931804565E5E4079E92631083C3F40, 0x0000000001040000000300000001010000000000000000000000000000000000000001010000000000000000003440000000000000344001010000000000000000004E400000000000004E40, 0x0000000001050000000200000001020000000200000000000000000024400000000000002440000000000000344000000000000034400102000000020000000000000000002E400000000000002E400000000000003E400000000000002E40, 0x0000000001060000000200000001030000000100000005000000000000000000000000000000000000000000000000002440000000000000000000000000000024400000000000002440000000000000000000000000000024400000000000000000000000000000000001030000000100000005000000000000000000144000000000000014400000000000001C4000000000000014400000000000001C400000000000001C4000000000000014400000000000001C4000000000000014400000000000001440, 0x0000000001070000000300000001010000000000000000002440000000000000244001010000000000000000003E400000000000003E400102000000020000000000000000002E400000000000002E4000000000000034400000000000003440);
create table seat_fk_realtimetrajectory_data
(
    mergeId               varchar(50)   not null comment '融合数据ID'
        primary key,
    mergeTime             varchar(50)   null comment '融合时间',
    mergeTarget           varchar(50)   null comment '船舶唯一标识',
    location              geometry      null comment '地理空间数据',
    supplyIceWaterState   int(10)       null comment '加冰加水状态',
    refueledState         int(10)       null comment '加油状态',
    motionState           int(10)       null comment '在航状态',
    targetTypeReliability int(10)       null comment '目标(航迹)可信度',
    portState             int(10)       null comment '港口状态',
    targetType            int(10)       null comment '目标分类',
    gisShipType           int(10)       null comment '船舶类型',
    gisNationality        int(10)       null comment '国籍',
    targetLength          double(10, 2) null comment '数据库类型',
    targetSpeed           double(10, 2) null comment '目标航速'
)
    comment '船舶动态融合数据';

INSERT INTO geo.seat_fk_realtimetrajectory_data (mergeId, mergeTime, mergeTarget, location, supplyIceWaterState, refueledState, motionState, targetTypeReliability, portState, targetType, gisShipType, gisNationality, targetLength, targetSpeed) VALUES ('653531606585315328', '2023-07-30 00:09:46', '7439895680000321933', 0x0000000001010000000000000000995B400000000000643440, 1, 1, 1, 60, 1, 0, 30, 412, 210, 30);
INSERT INTO geo.seat_fk_realtimetrajectory_data (mergeId, mergeTime, mergeTarget, location, supplyIceWaterState, refueledState, motionState, targetTypeReliability, portState, targetType, gisShipType, gisNationality, targetLength, targetSpeed) VALUES ('654531606585315322', '2023-07-30 09:09:46', '5439895680000321935', 0x0000000001010000000000000000865B40AE47E17A14063540, 1, 1, 1, 60, 1, 0, 30, 412, 210, 30);
INSERT INTO geo.seat_fk_realtimetrajectory_data (mergeId, mergeTime, mergeTarget, location, supplyIceWaterState, refueledState, motionState, targetTypeReliability, portState, targetType, gisShipType, gisNationality, targetLength, targetSpeed) VALUES ('654531606585315328', '2023-07-30 09:09:46', '5439895680000321933', 0x0000000001010000000000000000C65B400000000000183540, 1, 1, 1, 60, 1, 0, 30, 412, 210, 30);
INSERT INTO geo.seat_fk_realtimetrajectory_data (mergeId, mergeTime, mergeTarget, location, supplyIceWaterState, refueledState, motionState, targetTypeReliability, portState, targetType, gisShipType, gisNationality, targetLength, targetSpeed) VALUES ('654531606585315329', '2023-07-30 09:09:46', '6439895680000321933', 0x0000000001010000000000000000995B400000000000643440, 1, 1, 1, 60, 1, 0, 30, 412, 210, 30);
INSERT INTO geo.seat_fk_realtimetrajectory_data (mergeId, mergeTime, mergeTarget, location, supplyIceWaterState, refueledState, motionState, targetTypeReliability, portState, targetType, gisShipType, gisNationality, targetLength, targetSpeed) VALUES ('654531606585315421', '2023-07-30 09:09:46', '6539895680000321934', 0x0000000001010000006666666666DF5B40CDCCCCCCCC303440, 1, 1, 1, 60, 1, 0, 30, 412, 210, 30);
INSERT INTO geo.seat_fk_realtimetrajectory_data (mergeId, mergeTime, mergeTarget, location, supplyIceWaterState, refueledState, motionState, targetTypeReliability, portState, targetType, gisShipType, gisNationality, targetLength, targetSpeed) VALUES ('654531606585315429', '2023-07-30 09:09:46', '6539895680000321933', 0x00000000010100000066666666669F5B4066666666664A3440, 1, 1, 1, 60, 1, 0, 30, 412, 210, 30);