sync_land_forest_to_road.sql
2.67 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- ============================================================
-- 将 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;