住所データ階層化の下準備


[更新 07/30 夜]

元データを用意する方法

大字・町丁目レベル


区レベル位置参照のSQL Server取り込み

やりたいことは住所の階層化
こんなイメージ
____________
─香川県
├─さぬき市
│ ├─うどん
│ │ ├─101
│ │ ├─102
│ │ └─・・・
│ └─・・・
├─高松市
└─・・・
____________

香川県を選ぶと一覧が出て、その次、とドロップダウンやリスト表示で深い階層を手繰りたい。
がコードやクエリで階層を意識したくない。

そのための下準備

とりあえず大字・町丁目と街区レベルを1テーブルに集約するとともに
市のみ県のみのデータを作成 (本当は市役所や県庁の緯度経度を統合したいがあとからでも出来るだろうということで。)

USE gisdb;
GO

DROP TABLE CITY_ADDR;
GO

SELECT * INTO CITY_ADDR FROM
(
SELECT
 *
 ,ROW_NUMBER() OVER(ORDER BY lat,lon) ID
FROM
(
SELECT 
   KEN
  ,NULL AS SHIKUTYOSON
  ,NULL AS OOAZATYOU
  ,NULL AS GAIKU
  ,AVG( lat ) lat
  ,AVG( lon ) lon
  ,GEOGRAPHY::Point( AVG(lat),AVG(lon),4612) AS SHAPE -- 4612=JGD2000
FROM
 CITY_PT
GROUP BY
 KEN

UNION ALL

SELECT 
   KEN
  ,SHIKUTYOSON
  ,NULL AS OOAZATYOU
  ,NULL AS GAIKU
  ,AVG( lat ) lat
  ,AVG( lon ) lon
  ,GEOGRAPHY::Point( AVG(lat),AVG(lon),4612) AS SHAPE -- 4612=JGD2000
FROM
 CITY_PT
GROUP BY
 KEN,SHIKUTYOSON

UNION ALL


SELECT
 CT.KEN
 ,CT.SHIKUTYOSON
 ,NULL AS OOAZATYOU
 ,NULL AS GAIKU
 ,AVG( CT.lat ) lat
 ,AVG( CT.lon ) lon
 ,GEOGRAPHY::Point( AVG(CT.lat),AVG(CT.lon),4612) AS SHAPE -- 4612=JGD2000
FROM
 CB_GEO CT
LEFT JOIN
(SELECT 
   KEN
  ,SHIKUTYOSON
FROM
 CITY_PT
GROUP BY
 KEN,SHIKUTYOSON) TAB
ON
 CT.KEN = TAB.KEN 
  AND
 CT.SHIKUTYOSON = TAB.SHIKUTYOSON
WHERE
 TAB.KEN IS NULL
GROUP BY
 CT.KEN, CT.SHIKUTYOSON

UNION ALL


SELECT
 CT.KEN
 ,CT.SHIKUTYOSON
 ,CT.OOAZATYOU
 ,NULL AS GAIKU
 ,AVG( CT.lat ) lat
 ,AVG( CT.lon ) lon
 ,GEOGRAPHY::Point( AVG(CT.lat),AVG(CT.lon),4612) AS SHAPE -- 4612=JGD2000
FROM
 CB_GEO CT
LEFT JOIN
(SELECT 
   KEN
  ,SHIKUTYOSON
  ,OOAZATYOU
FROM
 CITY_PT
GROUP BY
 KEN,SHIKUTYOSON,OOAZATYOU) TAB
ON
 CT.KEN = TAB.KEN 
  AND
 CT.SHIKUTYOSON = TAB.SHIKUTYOSON
  AND
 CT.OOAZATYOU = TAB.OOAZATYOU
WHERE
 TAB.KEN IS NULL
GROUP BY
 CT.KEN, CT.SHIKUTYOSON, CT.OOAZATYOU

UNION ALL

SELECT 
  KEN
  ,SHIKUTYOSON
  ,OOAZATYOU
  ,NULL AS GAIKU
  ,lat
  ,lon
  ,SHAPE
FROM
 CITY_PT

UNION ALL



SELECT
  --ROW_NUMBER() OVER(ORDER BY ID ASC) ID,
  KEN
 ,SHIKUTYOSON
 ,OOAZATYOU
 ,GAIKU
 ,lat
 ,lon
 ,SHAPE
FROM
 CB_GEO

) ITAB ) TAB

インデックスやらプライマリキーを設定

USE gisdb;
GO

ALTER TABLE
 CITY_ADDR
ALTER COLUMN
 ID INT NOT NULL;
GO

ALTER TABLE
 CITY_ADDR
ADD PRIMARY KEY
 (ID);
GO


CREATE SPATIAL INDEX
 inx_shape
ON
 CITY_ADDR(SHAPE)
USING
 GEOGRAPHY_GRID
WITH (
  GRIDS = (HIGH, HIGH, HIGH, HIGH ),
  CELLS_PER_OBJECT = 64,
  PAD_INDEX  = ON );

GO

ALTER TABLE
 CITY_ADDR
ADD
 HID HIERARCHYID;
GO

CREATE INDEX INX_OO ON CITY_ADDR (OOAZATYOU);
GO

CREATE INDEX INX_SHI ON CITY_ADDR (SHIKUTYOSON);
GO

CREATE INDEX INX_KEN ON CITY_ADDR (KEN);
GO

階層化するため県のみのデータや市区町村のみのデータの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;
カテゴリー: 開発 タグ: パーマリンク