geo_geo_data_source.sql 7.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869
  1. create schema if not exists geo collate utf8_unicode_ci;
  2. create table geo_data_source
  3. (
  4. id int auto_increment comment '主键'
  5. primary key,
  6. layer_code varchar(50) null comment '图层代码',
  7. layer_name varchar(50) null comment '图层名称',
  8. host varchar(20) null comment '数据库地址',
  9. port int null comment '端口号',
  10. data_base varchar(30) null comment '数据库名',
  11. user_name varchar(50) null comment '用户名',
  12. user_password varchar(128) null comment '密码',
  13. status char(2) null comment '状态',
  14. create_user varchar(30) null comment '创建人',
  15. create_time datetime null comment '创建时间',
  16. update_time datetime null comment '修改时间',
  17. update_user varchar(30) null comment '修改人',
  18. sort int null comment '排序',
  19. db_type varchar(20) null comment '数据库类型',
  20. bbox_column varchar(50) null comment 'bbox字段',
  21. table_name varchar(128) null comment '数据表名'
  22. )
  23. comment '图层数据源管理表';
  24. 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');
  25. create table geo_test
  26. (
  27. id int not null
  28. primary key,
  29. name varchar(255) null,
  30. geometry_1 geometry null,
  31. point_1 point null,
  32. linestring_1 linestring null,
  33. polygon_1 polygon null,
  34. multipoint_1 multipoint null,
  35. multilinestring_1 multilinestring null,
  36. multipolygon_1 multipolygon null,
  37. geometrycollection_1 geometrycollection null
  38. )
  39. charset = utf8;
  40. 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);
  41. create table seat_fk_realtimetrajectory_data
  42. (
  43. mergeId varchar(50) not null comment '融合数据ID'
  44. primary key,
  45. mergeTime varchar(50) null comment '融合时间',
  46. mergeTarget varchar(50) null comment '船舶唯一标识',
  47. location geometry null comment '地理空间数据',
  48. supplyIceWaterState int(10) null comment '加冰加水状态',
  49. refueledState int(10) null comment '加油状态',
  50. motionState int(10) null comment '在航状态',
  51. targetTypeReliability int(10) null comment '目标(航迹)可信度',
  52. portState int(10) null comment '港口状态',
  53. targetType int(10) null comment '目标分类',
  54. gisShipType int(10) null comment '船舶类型',
  55. gisNationality int(10) null comment '国籍',
  56. targetLength double(10, 2) null comment '数据库类型',
  57. targetSpeed double(10, 2) null comment '目标航速'
  58. )
  59. comment '船舶动态融合数据';
  60. 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);
  61. 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);
  62. 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);
  63. 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);
  64. 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);
  65. 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);