Home Explore Help
Sign In
siwei
/
sanya-data-management-back
1
0
Fork 0
Files Issues 0 Pull Requests 0 Wiki
Branch: dev-1208
Branches Tags
sanya-data-mana...
/
sql
/
jc_urban_expansion.sql

jc_urban_expansion.sql 4.0 KB
Permalink History Raw

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859 
  1. -- 1. 创建表 (id 使用 BIGSERIAL 实现自增)
    2. 

  2. CREATE TABLE "vector"."jc_urban_expansion" (
    3. 

  3.     id              BIGSERIAL PRIMARY KEY,             -- 自增主键
    4. 

  4.     year            INTEGER NOT NULL,                  -- 年份
    5. 

  5.     layer_type      VARCHAR(20) NOT NULL,              -- 图层类型: 'boundary' 或 'expand'
    6. 

  6.     dim_type        VARCHAR(10) NOT NULL DEFAULT '2d', -- 维度类型: '2d' 或 '3d'
    7. 

  7.     layer_name      VARCHAR(100) NOT NULL,             -- 图层名称(对应DataSource)
    8. 

  8.     prev_year       INTEGER,                           -- 上一年份
    9. 

  9.     data            NUMERIC(18,2),                     -- 数据(面积/数量)
    10. 

  10.     service_id      VARCHAR(50),                       -- 服务ID(如 map-city)
    11. 

  11.     sort_order      INTEGER DEFAULT 0,                 -- 排序
    12. 

  12.     status          SMALLINT DEFAULT 1,                -- 状态: 1-启用, 0-禁用
    13. 

  13.     create_time     TIMESTAMP DEFAULT NOW(),           -- 创建时间
    14. 

  14.     update_time     TIMESTAMP DEFAULT NOW(),           -- 更新时间
    15. 

  15.     remark          VARCHAR(500)                       -- 备注
    16. 

  16. );
    17. 

  17. 

  18. -- 2. 添加注释
    19. 

  19. COMMENT ON TABLE "vector"."jc_urban_expansion" IS '城市扩张图层配置表';
    20. 

  20. COMMENT ON COLUMN "vector"."jc_urban_expansion".id IS '自增主键ID';
    21. 

  21. COMMENT ON COLUMN "vector"."jc_urban_expansion".year IS '年份';
    22. 

  22. COMMENT ON COLUMN "vector"."jc_urban_expansion".layer_type IS '图层类型: boundary-边界, expand-扩张';
    23. 

  23. COMMENT ON COLUMN "vector"."jc_urban_expansion".dim_type IS '维度类型: 2d-二维, 3d-三维';
    24. 

  24. COMMENT ON COLUMN "vector"."jc_urban_expansion".layer_name IS '图层名称';
    25. 

  25. COMMENT ON COLUMN "vector"."jc_urban_expansion".prev_year IS '上一年份';
    26. 

  26. COMMENT ON COLUMN "vector"."jc_urban_expansion".data IS '统计数据';
    27. 

  27. COMMENT ON COLUMN "vector"."jc_urban_expansion".service_id IS '服务ID';
    28. 

  28. COMMENT ON COLUMN "vector"."jc_urban_expansion".sort_order IS '排序';
    29. 

  29. COMMENT ON COLUMN "vector"."jc_urban_expansion".status IS '状态';
    30. 

  30. 

  31. -- 3. 创建索引
    32. 

  32. CREATE INDEX idx_urban_exp_year ON "vector"."jc_urban_expansion"(year);
    33. 

  33. CREATE INDEX idx_urban_exp_type ON "vector"."jc_urban_expansion"(layer_type, dim_type);
    34. 

  34. 

  35. -- 4. 插入示例数据 (需将 service_id 设为 map-city)
    36. 

  36. INSERT INTO "vector"."jc_urban_expansion" (year, layer_type, dim_type, layer_name, prev_year, data, service_id, sort_order) VALUES
    37. 

  37. -- 边界图层
    38. 

  38. (2009, 'boundary', '2d', 'boundary_2009@DataSource', NULL, 125.36, 'map-city', 1),
    39. 

  39. (2010, 'boundary', '2d', 'boundary_2010@DataSource', NULL, 132.58, 'map-city', 2),
    40. 

  40. (2011, 'boundary', '2d', 'boundary_2011@DataSource', NULL, 145.20, 'map-city', 3),
    41. 

  41. (2012, 'boundary', '2d', 'boundary_2012@DataSource', NULL, 156.80, 'map-city', 4),
    42. 

  42. (2013, 'boundary', '2d', 'boundary_2013@DataSource', NULL, 168.45, 'map-city', 5),
    43. 

  43. (2014, 'boundary', '2d', 'boundary_2014@DataSource', NULL, 182.30, 'map-city', 6),
    44. 

  44. (2015, 'boundary', '2d', 'boundary_2015@DataSource', NULL, 195.60, 'map-city', 7),
    45. 

  45. (2016, 'boundary', '2d', 'boundary_2016@DataSource', NULL, 210.85, 'map-city', 8),
    46. 

  46. (2017, 'boundary', '2d', 'boundary_2017@DataSource', NULL, 228.40, 'map-city', 9),
    47. 

  47. (2018, 'boundary', '2d', 'boundary_2018@DataSource', NULL, 245.75, 'map-city', 10),
    48. 

  48. (2019, 'boundary', '2d', 'boundary_2019@DataSource', NULL, 262.30, 'map-city', 11),
    49. 

  49. -- 扩张图层
    50. 

  50. (2010, 'expand', '2d', 'expand_2009_2010@DataSource', 2009, 7.22, 'map-city', 1),
    51. 

  51. (2011, 'expand', '2d', 'expand_2010_2011@DataSource', 2010, 12.62, 'map-city', 2),
    52. 

  52. (2012, 'expand', '2d', 'expand_2011_2012@DataSource', 2011, 11.60, 'map-city', 3),
    53. 

  53. (2013, 'expand', '2d', 'expand_2012_2013@DataSource', 2012, 11.65, 'map-city', 4),
    54. 

  54. (2014, 'expand', '2d', 'expand_2013_2014@DataSource', 2013, 13.85, 'map-city', 5),
    55. 

  55. (2015, 'expand', '2d', 'expand_2014_2015@DataSource', 2014, 13.30, 'map-city', 6),
    56. 

  56. (2016, 'expand', '2d', 'expand_2015_2016@DataSource', 2015, 15.25, 'map-city', 7),
    57. 

  57. (2017, 'expand', '2d', 'expand_2016_2017@DataSource', 2016, 17.55, 'map-city', 8),
    58. 

  58. (2018, 'expand', '2d', 'expand_2017_2018@DataSource', 2017, 17.35, 'map-city', 9),
    59. 

  59. (2019, 'expand', '2d', 'expand_2018_2019@DataSource', 2018, 16.55, 'map-city', 10);
    60.