投稿日: 投稿者: Admin

QGIS から SQL Server へデータをアップロードする際の勘所

　QGIS から SQL Server 2008 R2 に空間データをアップロードする際には Shape2SQL というツールを使った．便利なツールではあるが，国土数値情報の河川データの属性テーブルの日本語が文字化けするという問題を抱えており，何とかならないかと試行錯誤した結果を備忘録として投稿する．

国土数値情報の河川データは文字化けする

　最初，属性テーブルの文字コードを Shift_JIS か UTF8 かいずれかでエクスポートすれば解決するのではないかと思って試したのだが，解決しなかった．そのため，地物とそれ以外の属性データを分けてアップロードする方針に変更した．

属性テーブルには ID が必要

　残念ながら，属性テーブルには主キーとなる属性は存在しないため，フィールドを追加して「自動インクリメント属性の追加」が必要になる．

自動インクリメント属性の追加

　「プロセシングツールボックス」から「ベクタテーブル」「自動インクリメント属性の追加」へと進む．

「ベクタテーブル」「自動インクリメント属性の追加」
「ベクタテーブル」「自動インクリメント属性の追加」

　「属性名」と「開始値」を指定する．ここでは属性名を ID とし，開始値を 0 から 1 に変更している．「グループ化のための属性」はよく分からない．「出力レイヤ」は「一時レイヤを作成」のままで良いだろう．

　「実行」をクリックする．

「属性名」と「開始値」を指定
「属性名」と「開始値」を指定

　「自動インクリメント属性の追加」のログが出力される．「閉じる」でダイアログを抜ける．

「自動インクリメント属性の追加」ログ
「自動インクリメント属性の追加」ログ

　この後，追加された ID フィールドは「属性のリファクタリング」で先頭に移動しておく．

Shape2SQL で空間データを SQL Server にアップロード

　Database Configuration の Server name を入力する．

Database ConfigurationのServer name
Database ConfigurationのServer name

　Database Configuration の Server name を入力したところである．Server name は SQL Server Management Studio において，オブジェクトエクスプローラのサーバーのプロパティで確認できる．

Database ConfigurationのServer name
Database ConfigurationのServer name

　Database Configuration の Select or enter a database name から目的のデータベースを選択する．

Database Configuration の Select or enter a database name
Database Configuration の Select or enter a database name

　Database properties でサーバーとデータベースを指定したところである．

Database properties でサーバーとデータベースを指定したところ
Database properties でサーバーとデータベースを指定したところ

　ユーザーインターフェースからシェープファイルを指定する．

ユーザーインターフェースからシェープファイルを指定
ユーザーインターフェースからシェープファイルを指定

　Shapefile にフルパス付きのシェープファイル名が指定されたところである．ID Column はデフォルトでは ID となっているが，属性テーブルですでに使用している名前であり競合する．そのため AnotherID と別名を付けている．

　Upload to Database をクリックする．

Shape2SQL
Shape2SQL

　時折現れるエラーは OK で無視する．

エラーはOKで無視
エラーはOKで無視

属性テーブルは csv ファイルにエクスポート

　色々試行錯誤した結果，日本語部分は csv ファイルにエクスポートするのがベターだろうという結論に至った．

レイヤの「エクスポート」「地物の保存…」

　レイヤを右クリックして「エクスポート」「地物の保存…」へと進む．

レイヤの「エクスポート」「地物の保存...」
レイヤの「エクスポート」「地物の保存…」

「ベクタレイヤを名前を付けて保存…」ダイアログの注意点

　まず，「形式」は「カンマで区切られた値(CSV)」を選ぶ．「ファイル名」はフルパス付きでないとエラーとなるため，右側のボタンをクリックして遷移したダイアログでファイル名を指定する．

　さて，下図のように赤で印したオプションが注意すべき点である．

　「文字コード」を Shift_JIS にする．

　「ジオメトリタイプ」を「ジオメトリなし」にする．

　「レイヤオプション」の STRING_QUOTING を IF_NEEDED に変更する．ここを変更しないと SQL Server 側のウィザードでインポートした値にダブルクオーテーションが付加されてしまう．

「ベクタレイヤを名前を付けて保存...」ダイアログの注意点
「ベクタレイヤを名前を付けて保存…」ダイアログの注意点

SQL Server のウィザード

　データベースを右クリックして「タスク」「データのインポート…」と進む．

「タスク」「データのインポート...」
「タスク」「データのインポート…」

データソースの選択

　「データソースの選択」は「Flat File Source」を選ぶ．

「データソースの選択」は「Flat File Source」
「データソースの選択」は「Flat File Source」

　ユーザーインターフェースでcsvファイルを選択する．

ユーザーインターフェースでcsvファイルを選択
ユーザーインターフェースでcsvファイルを選択

　桁あふれを防ぐため，「詳細設定」で河川名 (W05_004) および path の OutputColumnWidthを広げておく．

「詳細設定」でOutputColumnWidthを広げておく
「詳細設定」でOutputColumnWidthを広げておく

変換先の選択

　「変換先の選択」はSQL Server Native Client 11.0 を選ぶ．

「変換先の選択」はSQL Server Native Client 11.0
「変換先の選択」はSQL Server Native Client 11.0

コピー元のテーブルおよびビューを選択

　「コピー元のテーブルおよびビューを選択」ではデータベース内のテーブル名を変更できる．下図では AllStreamShift_JIS となっているが，試行錯誤の途中キャプチャなので実際には StreamProperties としている．

　「マッピングの編集…」へと進む．

「コピー元のテーブルおよびビューを選択」
「コピー元のテーブルおよびビューを選択」

マッピングの編集

　「列マッピング」でデータ型とデータ長を指定する．下図のようになるのが理想的だが，往々にして桁あふれでエラーとなることが頻発するため， nvarchar (255) と妥協することが多い．

「列マッピング」でデータ型とデータ長を指定
「列マッピング」でデータ型とデータ長を指定

　幾度かの試行錯誤を経て，「操作は正常に実行されました」と操作が完了する．

「操作は正常に実行されました」
「操作は正常に実行されました」

河川コードと水域系コードをインポートする

　河川コードと水域系コードはそれぞれ国土数値情報に定義されている．Power Query で読み込もうとしたのだが，なぜかうまく行かないため，やむなくコピペでワークシートに貼り付け，csv ファイルに保存する．そのファイルを先程と同様，SQL Server のウィザードを使ってインポートする．

　インポートしてできたテーブル名をそれぞれ RiverName, WaterSystem とする．

結果

　下記クエリを発行する．

SELECT [AnotherID]
      ,[ID]
      ,[W05_001]
      ,[W05_002]
      ,[W05_003]
      ,[W05_004]
      ,[W05_005]
      ,[W05_006]
      ,[W05_007]
      ,[W05_008]
      ,[W05_009]
      ,[W05_010]
      ,[layer]
      ,[path]
      ,[geom]
  FROM [RiverGIS].[dbo].[AllStream]

  SELECT [ID]
      ,[W05_001]
      ,[W05_002]
      ,[W05_003]
      ,[W05_004]
      ,[W05_005]
      ,[W05_006]
      ,[W05_007]
      ,[W05_008]
      ,[W05_009]
      ,[W05_010]
      ,[layer]
      ,[path]
  FROM [RiverGIS].[dbo].[StreamProperties]

  SELECT [RiverCode]
      ,[River]
  FROM [RiverGIS].[dbo].[RiverName]

  SELECT [WaterSystemCode]
      ,[WaterSystem]
  FROM [RiverGIS].[dbo].[WaterSystem]

　結果は下記のとおりである．国土数値情報の河川データの地物の座標は 26 件切り捨てられたことが分かる．

(286421 行処理されました)
(286437 行処理されました)
(35449 行処理されました)
(5476 行処理されました)

　WaterSystem および RiverName はマスターとして扱うべきであるが，照合してみるとマスターに存在しない河川コードや水域系コードが存在するようだ．

未定義の水域系コード

　未定義の水域系コードを抽出した．結果は 47 件あるが，影響の及ぶ河川は多い．これを可視化したらどうなるか，見てみたい気もする．

WITH CTE(Undefiend)
AS
(
SELECT W05_001 
FROM dbo.StreamProperties
EXCEPT 
SELECT WaterSystemCode 
FROM dbo.WaterSystem 
)
SELECT	CTE.Undefiend
	,	COUNT(P.W05_002)	AS CNT
FROM	CTE
INNER	JOIN dbo.StreamProperties	AS P
ON	CTE.Undefiend = P.W05_001
GROUP	BY CTE.Undefiend
ORDER	BY CTE.Undefiend
(47 行処理されました)
Undefiend	CNT
010000	354
010206	3
020000	1826
030000	1497
040000	380
050000	526
060000	209
070000	1198
080000	11
100000	1
120000	227
130000	67
140000	50
150000	1975
160000	909
170000	1118
180000	520
190000	2
220000	266
230000	490
240000	226
250000	3
260000	110
270000	40
280000	208
290000	8
300000	47
310000	60
320000	243
330000	180
340000	347
350000	207
360000	39
370000	129
380000	122
390000	142
400000	941
400069	6
410000	356
410103	1
420000	1473
430000	1124
440000	823
441030	2
450000	1340
460000	1809
470000	948

未定義の河川コード

　水域系コードと違って数が桁違いに多いため，結果は省略する．コードはあるが名前の存在しない河川が存在する．

WITH CTE(Undefiend)
AS
(
SELECT	W05_002	AS 'Undefined'
FROM	dbo.StreamProperties
EXCEPT
SELECT	RiverCode
FROM	dbo.RiverName
)
SELECT	CTE.Undefiend
	,	COUNT(P.W05_002)	AS CNT
FROM	CTE
INNER	JOIN dbo.StreamProperties	AS P
ON	CTE.Undefiend = P.W05_002
GROUP	BY CTE.Undefiend
ORDER	BY CTE.Undefiend
(1667 行処理されました)

まとめ

　国土数値情報の河川データを QGIS から SQL Server へアップロードした．

　属性テーブルについては日本語が文字化けするため別途 csv ファイルとしてウィザードによりインポートした．

　マスターとしての河川コードおよび水域系コードには不備がある．また，河川データそのものにも不備がある．

　河川端点である W05_007 から W05_010 までの4つのフィールドをどう扱うべきか，分からない．リレーショナルデータベースでは再帰の嵐になる可能性がある．新しいアプローチ法が必要かもしれない．

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。