-- ============================================================
-- 将 garden_gis_land_forest_data 数据同步到 garden_gis_road
-- 按 greenname + greentype 分组聚合
--   - xbmj 求和 → gis_plot_area
--   - geometry 所有 MULTIPOLYGON 环拼接 → gis_polygon_coords (WKT)
--   - 其他 gis_ 字段取分组内 MAX 值
--
-- 注意：MySQL 不支持 ST_Union 聚合函数，geometry 并集通过
--   GROUP_CONCAT 拼接各 MULTIPOLYGON 的内部环来实现，不做空间融合。
--   如需真正融合重叠区域，建议在 Java 层用 JTS 处理。
-- ============================================================

-- 避免 GROUP_CONCAT 默认 1024 字节限制导致 WKT 截断
SET SESSION group_concat_max_len = 10485760;  -- 10MB

INSERT INTO garden_gis_road (
    gis_plot_name,
    gis_green_type_name,
    gis_plot_area,
    gis_polygon_coords,
    gis_owner_unit,
    gis_property_unit,
    gis_manage_unit,
    gis_street,
    gis_plot_code,
    gis_green_type_code,
    street_id,
    company_id,
    dept_id
)
SELECT
    greenname                                                          AS gis_plot_name,
    greentype                                                          AS gis_green_type_name,
    ROUND(SUM(xbmj), 2)                                                AS gis_plot_area,
    CONCAT(
        'MULTIPOLYGON(',
        GROUP_CONCAT(
            SUBSTR(
                ST_AsText(geometry),
                14,                                                      -- 跳过 'MULTIPOLYGON('（13字符）
                LENGTH(ST_AsText(geometry)) - 14                         -- 去掉首尾各1个括号
            )
            SEPARATOR ','
        ),
        ')'
    )                                                                   AS gis_polygon_coords,
    MAX(cqdw)                                                           AS gis_owner_unit,
    MAX(cqdw)                                                           AS gis_property_unit,
    MAX(gl_dw)                                                          AS gis_manage_unit,
    MAX(CONCAT_WS('-', sheng, shi, xian, xiang))                       AS gis_street,
    ''                                                                  AS gis_plot_code,
    ''                                                                  AS gis_green_type_code,
    ''                                                                  AS street_id,
    0                                                                   AS company_id,
    0                                                                   AS dept_id
FROM garden_gis_land_forest_data
WHERE greenname IS NOT NULL
  AND greentype IS NOT NULL
GROUP BY greenname, greentype;