階層化


hierarchyid による階層化をおこなった場合
コード記述者は階層体系をデータ構築側に任せて単一テーブル/単一カラムのみを参照すればよい。

ユーザファイル格納用テーブル等々階層化の数量(テーブル分割数)や階層化が途中で変わる可能性のある住所 (郡部で一括り等)
社員等の組織体系等で使えるか。

下部データ整備続きを終わらせると下記のようなクエリで手繰れる。
起点の県一覧が何か遅いがRoot部分は別フラグとしても速度的には大きくは問題なさそう。
速度優先でテーブル小分けにして参照整合と合わせたのツリー構築するにしても、まあ使えるか。

--CREATE INDEX inx_hid ON CITY_ADDR(HID);

SELECT
 *
FROM
 CITY_ADDR
WHERE
 HID.GetAncestor(1) = HIERARCHYID::GetRoot()
;

DECLARE @hid_1 HIERARCHYID;

SELECT
 @hid_1 = HID
FROM
 CITY_ADDR
WHERE
 ID = 1351813

SELECT
 *
FROM
 CITY_ADDR
WHERE
 HID.GetAncestor(1) = @hid_1
;


DECLARE @hid_2 HIERARCHYID;

SELECT
 @hid_2 = HID
FROM
 CITY_ADDR
WHERE
 ID = 1726048

SELECT
 *
FROM
 CITY_ADDR
WHERE
 HID.GetAncestor(1) = @hid_2
;


DECLARE @hid_3 HIERARCHYID;

SELECT
 @hid_3 = HID
FROM
 CITY_ADDR
WHERE
 ID = 1737672

SELECT
 *
FROM
 CITY_ADDR
WHERE
 HID.GetAncestor(1) = @hid_3

HID

データ整備の続き

親階層ID付与


USE gisdb;
GO

--県
ALTER TABLE
 CITY_ADDR
ADD 
 KEN_ID BIGINT
GO
--市区町村
ALTER TABLE
 CITY_ADDR
ADD 
 SHI_ID BIGINT
GO
--大字町丁目
ALTER TABLE
 CITY_ADDR
ADD 
 OOAZA_ID BIGINT
GO

SELECT * INTO
 #TMP_TAB_KEN
FROM
(
SELECT
 KEN,
 ID AS KEN_ID
FROM
 CITY_ADDR
WHERE
 ID IN 
 (
   SELECT
    MIN(ID)
   FROM
    CITY_ADDR
  WHERE
    SHIKUTYOSON IS NULL
   GROUP BY
    KEN
 )
) TAB
;

CREATE INDEX INX_K_ID ON #TMP_TAB_KEN(KEN);

SELECT * INTO
 #TMP_TAB_SHI
FROM
(
SELECT
 SHIKUTYOSON,
 ID AS SHI_ID
 --, *
FROM
 CITY_ADDR
WHERE
 ID IN 
 (
   SELECT
    MIN(ID)
   FROM
    CITY_ADDR
  WHERE
    OOAZATYOU IS NULL
   GROUP BY
    SHIKUTYOSON
 )
AND
 NOT SHIKUTYOSON IS NULL
) TAB
;

CREATE INDEX INX_S_ID ON #TMP_TAB_SHI(SHIKUTYOSON);


SELECT * INTO
 #TMP_TAB_OOAZA
FROM
(
SELECT
 SHIKUTYOSON,
 OOAZATYOU,
 ID AS OOAZA_ID

 --, *
FROM
 CITY_ADDR
WHERE
 ID IN 
 (
   SELECT
    MIN(ID)
   FROM
    CITY_ADDR
  WHERE
    GAIKU IS NULL
   GROUP BY
    SHIKUTYOSON,OOAZATYOU
 )
AND
 NOT OOAZATYOU IS NULL
) TAB
;

CREATE INDEX INX_O_ID ON #TMP_TAB_OOAZA(OOAZATYOU);



UPDATE
 CITY_ADDR 
SET
 CITY_ADDR.KEN_ID = TMP.KEN_ID
FROM
 #TMP_TAB_KEN TMP
WHERE
 CITY_ADDR.KEN = TMP.KEN;

UPDATE
 CITY_ADDR 
SET
 CITY_ADDR.SHI_ID = TMP.SHI_ID
FROM
 #TMP_TAB_SHI TMP
WHERE
 CITY_ADDR.SHIKUTYOSON = TMP.SHIKUTYOSON;

UPDATE
 CITY_ADDR 
SET
 CITY_ADDR.OOAZA_ID = TMP.OOAZA_ID
FROM
 #TMP_TAB_OOAZA TMP
WHERE
 CITY_ADDR.SHIKUTYOSON = TMP.SHIKUTYOSON
  AND
 CITY_ADDR.OOAZATYOU = TMP.OOAZATYOU;

階層化


UPDATE 
 CITY_ADDR
SET
 HID = CASE WHEN SHI_ID IS NULL THEN
         CONVERT(HIERARCHYID,'/' + CONVERT(VARCHAR, KEN_ID) + '/')
       WHEN OOAZA_ID IS NULL AND GAIKU IS NULL THEN
         CONVERT(HIERARCHYID,'/' + CONVERT(VARCHAR, KEN_ID)+ '/' + CONVERT(VARCHAR, SHI_ID)+ '/' )
       WHEN OOAZA_ID IS NULL AND NOT GAIKU IS NULL THEN
         CONVERT(HIERARCHYID,'/' + CONVERT(VARCHAR, KEN_ID)+ '/' + CONVERT(VARCHAR, SHI_ID) + '/' + CONVERT(VARCHAR, ID)+ '/' )
       WHEN NOT OOAZA_ID IS NULL AND GAIKU IS NULL THEN
         CONVERT(HIERARCHYID,'/' + CONVERT(VARCHAR, KEN_ID)+ '/' + CONVERT(VARCHAR, SHI_ID) + '/' + CONVERT(VARCHAR, OOAZA_ID)+ '/' )
       ELSE
         CONVERT(HIERARCHYID,'/' + CONVERT(VARCHAR, KEN_ID)+ '/' + CONVERT(VARCHAR, SHI_ID) + '/' + CONVERT(VARCHAR, OOAZA_ID)+ '/'  + CONVERT(VARCHAR, ID)+ '/')
       END
カテゴリー: 開発 タグ: パーマリンク