階層構造の集約


階層化は深さが予測できないとか不揃いの時に効果を発揮する。
結局カラム指定でも同じことが出来るが、面で使用する場合、上から一階層、二階層は確実に存在するが
支庁を階層に入れるか入れないかとか、階層が不揃いで条件に応じて集約対象が変化する場合など
便利な場面は多そう。

階層化を単純に試したい場合、市区町村界2000件ほどでも試せる

SELECT * INTO
 JPN_HI
FROM
(
SELECT
 CONVERT(
  hierarchyid,
  '/' + CONVERT(varchar, L1_TAB.L1_ID) +
  '/' + CONVERT(varchar, L2_TAB.L2_ID) +
  '/' + CONVERT(varchar, OBJECTID) + '/'
 ) AS HID
 ,L1_TAB.L1_ID
 ,L2_TAB.L2_ID
 ,L1_TAB.TXT AS L1_TXT
 ,L2_TAB.TXT AS L2_TXT
 ,BASE_TAB.*
FROM
 JPN71 BASE_TAB
LEFT OUTER JOIN
(
SELECT
 ROW_NUMBER() OVER (ORDER BY MIN(OBJECTID)) L1_ID
 ,KEN AS TXT
FROM
 JPN71
GROUP BY
 KEN
) L1_TAB
ON
 BASE_TAB.KEN = L1_TAB.TXT
LEFT OUTER JOIN
(
SELECT
  ROW_NUMBER() OVER (ORDER BY MIN(OBJECTID)) L2_ID
  , KEN ,
 --ltrim(SICHO) + 
 ltrim(GUN) + ltrim(SEIREI) AS TXT
FROM
 JPN71
GROUP BY
 KEN,GUN,SEIREI
) L2_TAB
ON
 BASE_TAB.KEN = L2_TAB.KEN
  AND
 ltrim(BASE_TAB.GUN) + ltrim(BASE_TAB.SEIREI) = L2_TAB.TXT
) AS INS_TAB


CREATE INDEX inx_hid ON JPN_HI (HID);

ALTER TABLE JPN_HI ALTER COLUMN OBJECTID INT NOT NULL;
ALTER TABLE JPN_HI ADD PRIMARY KEY (OBJECTID);

CREATE SPATIAL INDEX
 inx_geom
ON
 JPN_HI (Shape)
USING  GEOMETRY_GRID 
WITH (BOUNDING_BOX =
(
 120, 20, 155, 55),
 GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
 CELLS_PER_OBJECT = 16
);

面的集約をする場合

SELECT
 SUM(P_NUM) PNUM,
 GEOMETRY::UnionAggregate(Shape) Shaep
 ,HID.GetAncestor(HID.GetLevel() - 1).ToString() 階層
 ,MIN(KEN) KEN
FROM
 JPN_HI 
GROUP BY
 HID.GetAncestor(HID.GetLevel() - 1)

hid_agg

カテゴリー: 開発 タグ: パーマリンク