はじめに
何気なしにネットの海を漂っていると、「"電子国土基本図(地名情報)「住居表示住所」"」の情報がありました。
別記事で郵便番号の情報をSQLServerに取り込んでいたのですが、それと突合せすることでよりデータの確度があがると思い、こちらの展開をするようにしました。
電子国土基本図(地名情報)「住居表示住所」とは
電子国土基本図(地名情報)「住居表示住所」とは、「住居表示に関する法律」による住居表示が行われている地区の住居番号(○○市○○○丁目○番○号という住所のうち、「○号」に該当する番号)を決める際に用いる「基礎番号」を国土地理院がデータ化した基本測量成果です。
提供されているデータ
国土基本図(地名情報)「住居表示住所」のデータは次のサイトからダウンロードできます。
https://saigai.gsi.go.jp/jusho/download/index.html
上のページでは県単位でリンクがあり、例えば東京都のページは次のようになります。
https://saigai.gsi.go.jp/jusho/download/pref/13.html
このページでは市区町村ごとにzipファイルへのリンクが並んでいます。
例えば大田区のzipファイルは次のようになります。
https://saigai.gsi.go.jp/jusho/download/data/13111.zip
ZIPファイルの内容はつぎのようになっています。
東京都大田区の場合(13111.zip)
\13111\j13111k_20231222.csv (座標などのCSVファイル)
\13111\shp\j13111k_20231222.cpg GISで用いるシェープファイル(属性情報文字コード)
\13111\shp\j13111k_20231222.dbf GISで用いるシェープファイル(図形属性情報)
\13111\shp\j13111k_20231222.prj GISで用いるシェープファイル(座標系定義情報)
\13111\shp\j13111k_20231222.shp GISで用いるシェープファイル(図形情報)
\13111\shp\j13111k_20231222.shx GISで用いるシェープファイル(図形インデクス情報)
shpフォルダ内のファイルはGISで用いるシェープファイルなので、今の段階では考慮しません。
csvファイルの内容は以下のとおりです。
13111,池上一丁目,3,1,http://gi.gsi.go.jp/jusho/13111/IKG-1/3/1,,139.704324317,35.581663436,2500
13111,池上一丁目,3,2,http://gi.gsi.go.jp/jusho/13111/IKG-1/3/2,,139.704209983,35.581779989,2500
13111,池上一丁目,3,3,http://gi.gsi.go.jp/jusho/13111/IKG-1/3/3,,139.704120697,35.581859353,2500
... (省略) ...
13111,池上一丁目,3,15,http://gi.gsi.go.jp/jusho/13111/IKG-1/3/15,,139.705001742,35.581677133,2500
13111,池上一丁目,3,16,http://gi.gsi.go.jp/jusho/13111/IKG-1/3/16,,139.704797739,35.581657867,2500
13111,池上一丁目,4,1,http://gi.gsi.go.jp/jusho/13111/IKG-1/4/1,,139.704768103,35.581616367,2500
13111,池上一丁目,4,2,http://gi.gsi.go.jp/jusho/13111/IKG-1/4/2,,139.704919050,35.581631672,2500
... (省略) ...
csvのフォーマットは次のようになっています。
- 市区町村コード: 文字列
- 町又は字の名称: 文字列
- 街区符号: 文字列
- 基礎番号: 文字列
- 住所コード(可読): 文字列
- 住所コード(数値): 空)
- 経度: 実数
- 緯度: 実数
- 地図情報レベル: 整数
データの取り込み方法
データ取り込みの流れ
データの取り込みは次の2つのテーブルに収納することを考えます。
- 市区町村毎の情報(HPのページ単位)
- ZIPファイル内のCSVファイルの内容の情報(ZIPファイル単位)
そのため取り込みのステップは次のようになります。
- https://saigai.gsi.go.jp/jusho/download/index.htmlからHTMLをSQLServerに読み込む
- SQLServerでHTMLをパースして都道府県単位のリンクの情報を登録
- 都道府県のページ(東京都など)からHTMLをSQLServerに読み込む
- SQLServerでHTMLをパースして市区町村単位のZIPファイルへのリンクの情報を登録
- 市区町村単位のZIPファイルをダウンロードする
- ZIPファイルからCSVファイルを抽出する
- CSVファイルをSQLServerに読み込む
- 読み込んだファイルをパースして行ごとの情報を登録
取り込みに使う道具
上記の取り込みのステップには、次の課題があります。
- URLを元にHTMLを文字列として取得する
- 文字列をパースしてテーブルに登録する
- URLを元にZIPファイルを取得する
- ZIPファイルから特定のファイルを抽出(展開)する
- CSVファイルを文字列として取り込む
それぞれの内容を解決するために複数のCLRを作成しました。
(CLRの要所のみ記載します)
URLを元に文字列を取得、ファイルを取得
''' <summary>
''' ネットに接続して結果をGETする
''' </summary>
''' <param name="vURLAddress">相手先URL</param>
''' <returns>結果のテーブル</returns>
<SqlFunction(
DataAccess:=DataAccessKind.None,
IsDeterministic:=False,
IsPrecise:=False,
SystemDataAccess:=SystemDataAccessKind.None,
FillRowMethodName:="GetResultFromURL_FillRow",
TableDefinition:="url nvarchar(max), isSuccessed bit, result nvarchar(max), errorMessage nvarchar(max)",
Name:="GetResultFromURL"
)>
Public Shared Function GetResultFromURL(ByVal vURLAddress As SqlString) As IEnumerable
Dim nodeList As New List(Of StcNodeItem)
If vURLAddress.IsNull = False Then
Dim isSuccess As Boolean
Dim result As String = ""
Dim errorMessage As String = ""
isSuccess = In_GetResultFromURL(vURLAddress.Value, result, errorMessage)
nodeList.Add(New StcNodeItem() With {
.url = vURLAddress,
.isSuccessed = New SqlBoolean(isSuccess),
.result = New SqlString(result),
.errorMessage = New SqlString(errorMessage)
})
End If
Return nodeList
End Function
''' <summary>
''' Netからデータを取得する関数
''' </summary>
''' <param name="vUrlAddress">相手先のURLアドレス</param>
''' <param name="vResult">取得した結果</param>
''' <returns>取得に成功した場合True</returns>
Private Shared Function In_GetResultFromURL(ByVal vUrlAddress As String, ByRef vResult As String, ByRef vErrorMessage As String) As Boolean
Dim ans As Boolean = False
vResult = ""
vErrorMessage = ""
Try
ans = NetAccess.GetDataFromHttp(vUrlAddress, vResult)
Catch ex As Exception
vErrorMessage = ex.Message
End Try
Return ans
End Function
''' <summary>
''' ネットに接続して結果をGETする
''' </summary>
''' <param name="vURLAddress">相手先URL</param>
''' <returns>結果のテーブル</returns>
<SqlFunction(
DataAccess:=DataAccessKind.None,
IsDeterministic:=False,
IsPrecise:=False,
SystemDataAccess:=SystemDataAccessKind.None,
FillRowMethodName:="GetFileFromURL_FillRow",
TableDefinition:="url nvarchar(max), isSuccessed bit, resultFilePath nvarchar(max), errorMessage nvarchar(max)",
Name:="GetFileFromURL"
)>
Public Shared Function GetFileFromURL(ByVal vURLAddress As SqlString, ByVal vFilePath As SqlString) As IEnumerable
Dim nodeList As New List(Of StcNodeItemBin)
If vURLAddress.IsNull = False Then
Dim isSuccess As Boolean
Dim errorMessage As String = ""
Dim savePath As String
If vFilePath.IsNull Then
savePath = IO.Path.GetTempFileName
Else
savePath = vFilePath.Value
End If
isSuccess = In_GetFileFromURL(vURLAddress.Value, savePath, errorMessage)
nodeList.Add(New StcNodeItemBin() With {
.url = vURLAddress,
.isSuccessed = New SqlBoolean(isSuccess),
.resultFilePath = New SqlString(savePath),
.errorMessage = New SqlString(errorMessage)
})
End If
Return nodeList
End Function
''' <summary>
''' Netからデータを取得する関数
''' </summary>
''' <param name="vUrlAddress">相手先のURLアドレス</param>
''' <param name="vResultFilePath">取得した結果</param>
''' <returns>取得に成功した場合True</returns>
Private Shared Function In_GetFileFromURL(ByVal vUrlAddress As String, ByRef vResultFilePath As String, ByRef vErrorMessage As String) As Boolean
Dim ans As Boolean = False
vErrorMessage = ""
Try
ans = NetAccess.GetFileFromHttp(vUrlAddress, vResultFilePath)
Catch ex As Exception
vErrorMessage = ex.Message
End Try
Return ans
End Function
''' <summary>
''' URIのデータ取得サンプル
''' http://dobon.net/vb/dotnet/internet/webclientopenread.html から引用
''' TLSの問題は
''' https://elleneast.com/?p=3162 から引用
''' </summary>
''' <param name="vUrl">対象とするURL</param>
''' <param name="vFilePath">結果の文字列</param>
''' <returns>取得が成功すればTrue</returns>
Friend Shared Function GetFileFromHttp(ByVal vUrl As String, ByRef vFilePath As String) As Boolean
Dim ans As Boolean = False
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls Or SecurityProtocolType.Tls11 Or SecurityProtocolType.Tls12 Or SecurityProtocolType.Tls13
Try
'WebClientを作成
Using wc As New WebClient()
'データを文字列としてダウンロードする
wc.DownloadFile(vUrl, vFilePath)
'後始末
wc.Dispose()
ans = True
End Using
Catch ex As Exception
vFilePath = ""
End Try
Return ans
End Function
WebClientは非推奨なので、これからコーディングする方はHttpClientを使うようにしてください。
使い方はこのようになります。
-- URLからファイルをダウンロードし保存する
declare @url nvarchar(max)='https://saigai.gsi.go.jp/jusho/download/data/09215.zip';
declare @name nvarchar(max)=dbo.CLR_FileIO_GetTemporaryFilename();
select
*
from dbo.CLR_WebAccess_GetFileFromURL(@url,@name)
文字列をパースしてテーブルに登録する
別記事として、正規表現関数を公開しています。
ZIPファイルから特定のファイルを抽出(展開)する
VB.NETでSystem.IO.Compressionを使ってCLRを作成します。
Imports System.Data.SqlTypes
Imports System.Xml '不要そうに見えるけれど必要
Imports Microsoft.SqlServer.Server
Imports System.IO.Compression
''' <summary>
''' 対象のZIPファイルの中にあるファイルの一覧を取得する関数
''' </summary>
''' <param name="vZipFilename">対象の文字列</param>
''' <returns>抽出した文字列</returns>
<SqlFunction(
DataAccess:=DataAccessKind.None,
IsDeterministic:=True,
IsPrecise:=False,
SystemDataAccess:=SystemDataAccessKind.None,
Name:="ListFilesInZip",
FillRowMethodName:="ListFilesInZip_FillRow",
TableDefinition:="FileIndex int,TargetName nvarchar(max),FileSize int,LastWriteTimeUTC datetime"
)>
Public Shared Function ListFilesInZip(ByVal vZipFilename As SqlString) As IEnumerable
''https://dobon.net/vb/dotnet/file/zipfile.html
Dim ans As New ArrayList()
Using archive As ZipArchive = ZipFile.OpenRead(vZipFilename.Value)
Dim i As Integer = 1
For Each entry As ZipArchiveEntry In archive.Entries
ans.Add(New DirElementOfFiles(
FileIndex:=i,
TargetName:=entry.FullName,
FileSize:=entry.Length,
LastWriteTimeUTC:=entry.LastWriteTime.UtcDateTime
))
i += 1
Next
End Using
Return ans
End Function
''' <summary>
''' 対象のZIPファイルの中にある一つのファイルを取得する関数
''' </summary>
''' <param name="vZipFilename">対象の文字列</param>
''' <param name="vSourceFilename">Zipファイル内のファイルパス</param>
''' <param name="vDestinationFilename">保存先のファイルパス</param>
''' <param name="vOverWriteFlag">上書きするか否かのフラグ</param>
''' <returns>抽出したファイルのサイズ</returns>
<SqlFunction(
DataAccess:=DataAccessKind.None,
IsDeterministic:=True,
IsPrecise:=False,
SystemDataAccess:=SystemDataAccessKind.None,
Name:="ExtractFileInZip"
)>
Public Shared Function ExtractFileInZip(ByVal vZipFilename As SqlString, ByVal vSourceFilename As SqlString, ByVal vDestinationFilename As SqlString, ByVal vOverWriteFlag As SqlBoolean) As SqlInt64
''https://dobon.net/vb/dotnet/file/zipfile.html
Dim ans As SqlInt64 = SqlInt64.Null
Using archive As ZipArchive = ZipFile.OpenRead(vZipFilename.Value)
Dim entry As ZipArchiveEntry = archive.GetEntry(vSourceFilename.Value)
If entry IsNot Nothing Then
entry.ExtractToFile(vDestinationFilename.Value, vOverWriteFlag.Value)
ans = entry.Length
End If
End Using
Return ans
End Function
使い方はこの通りです。
-- ZIPファイルから一つのファイルを取り出す
declare @target nvarchar(max);
select
@target=A.TargetName
from dbo.CLR_FileCompression_ListFilesInZip(@name) A
where A.TargetName like '%.csv';
declare @csv nvarchar(max)=dbo.CLR_FileIO_GetTemporaryFilename();
select
dbo.CLR_FileCompression_ExtractFileInZip(
@name
,@target
,@csv
,1
)
;
CSVファイルを文字列として取り込む
CLRのコード(の一部)は以下のとおりです。
Imports System.Data.SqlTypes
Imports System.Xml '不要そうに見えるけれど必要
Imports Microsoft.SqlServer.Server
Imports System.IO
''' <summary>
''' テキストファイルを読み込む関数
''' </summary>
''' <param name="ServerPathname">サーバのファイルパス</param>
''' <param name="Encoding">ファイルを指定したエンコードで読み込む</param>
''' <returns>存在した場合はファイルの内容、存在しない場合はNull</returns>
<SqlFunction(
DataAccess:=DataAccessKind.None,
IsDeterministic:=False,
IsPrecise:=False,
SystemDataAccess:=SystemDataAccessKind.None,
Name:="ReadTextFile"
)>
Public Shared Function ReadTextFile(ByVal ServerPathname As SqlString, ByVal Encoding As SqlString) As SqlString
Dim ans As SqlString
Try
ans = SqlString.Null
If ServerPathname.IsNull Then
Exit Try
End If
Dim pathname As String = ServerPathname.Value
If File.Exists(pathname) = False Then
Exit Try
End If
Dim buf As String
Dim rd As IO.StreamReader
If Encoding.IsNull Then
rd = New StreamReader(pathname)
Else
rd = New StreamReader(pathname, Text.Encoding.GetEncoding(Encoding.Value))
End If
buf = rd.ReadToEnd()
rd.Close()
ans = buf
Catch
End Try
Return ans
End Function
CLRを実行した例です。
declare @buf nvarchar(max);
select @buf=dbo.CLR_FileIO_ReadTextFile(@csv,'utf-8');
### データ取り込み用スクリプト
前述のCLRを拡張した状態で電子国土基本図(地名情報)「住居表示住所」を取り込むスクリプトは以下のとおりです。
if OBJECT_ID('dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP') is not null
begin
drop table dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP;
create table dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP
(
uid int identity(1,1) not null
,ベースurl nvarchar(120)
,カレントurl nvarchar(120)
,参考テキスト nvarchar(120)
,階層 int
,摘要 nvarchar(400)
);
end;
if OBJECT_ID('dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP') is not null
begin
drop table dbo.WkTbl_共通_電子国土基本図_地名情報_CSV;
create table dbo.WkTbl_共通_電子国土基本図_地名情報_CSV
(
uid int identity(1,1) not null
,市区町村コード nvarchar(5)
,町または字の名称 nvarchar(100)
,街区符号 nvarchar(10)
,基礎番号 nvarchar(10)
,住所コード nvarchar(200)
,経度 decimal(13,10)
,緯度 decimal(13,10)
,地図情報レベル nvarchar(20)
,摘要 nvarchar(100)
);
end;
truncate table dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP;
dbcc checkident('dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP', RESEED, 1);
truncate table dbo.WkTbl_共通_電子国土基本図_地名情報_CSV;
dbcc checkident('dbo.WkTbl_共通_電子国土基本図_地名情報_CSV', RESEED, 1);
declare @base nvarchar(40)='https://saigai.gsi.go.jp/jusho/download/';
insert into dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP
(
ベースurl
,カレントurl
,参考テキスト
,階層
,摘要
)
select
A.url as ベースurl
,@base+FN.TargetWord1 as カレントurl
,FN.TargetWord2 参考テキスト
,1 as 階層
,'都道府県' as 摘要
from dbo.CLR_WebAccess_GetResultFromURL(@base+'/index.html') A
cross apply dbo.CLR_Regex_FindMatchesAsTable2(A.result,'<a href="(pref.+)">(.+)</a>') FN
;
insert into dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP
(
ベースurl
,カレントurl
,参考テキスト
,階層
,摘要
)
select
FN1.url as ベースurl
,@base+FN2.TargetWord1 as カレントurl
,FN2.TargetWord2 参考テキスト
,2 as 階層
,'市区町村' as 摘要
from dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP A
cross apply dbo.CLR_WebAccess_GetResultFromURL(A.カレントurl) FN1
cross apply dbo.CLR_Regex_FindMatchesAsTable2(FN1.result,'<a href="\.\./(data/.+)">(.+)</a>') FN2
where A.階層=1
;
select * from dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP;
declare @min int;
declare @max int;
select
@min=MIN(A.uid)
,@max=MAX(A.uid)
from dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP A
where A.階層=2
;
declare @i int=@min;
while @i<=@max
begin
-- ダウンロード元のURL
declare @url nvarchar(max)=(select A.カレントurl from dbo.WkTbl_共通_電子国土基本図_地名情報_ZIP A where A.uid=@i);
-- ダウンロード先の一時ファイル名
declare @name nvarchar(max)=dbo.CLR_FileIO_GetTemporaryFilename();
-- ファイルをダウンロード
select
*
from dbo.CLR_WebAccess_GetFileFromURL(@url,@name);
-- ZIPファイルから一つのファイルを取り出す
declare @target nvarchar(max);
select
@target=A.TargetName
from dbo.CLR_FileCompression_ListFilesInZip(@name) A
where A.TargetName like '%.csv';
declare @csv nvarchar(max)=dbo.CLR_FileIO_GetTemporaryFilename();
select
dbo.CLR_FileCompression_ExtractFileInZip(
@name
,@target
,@csv
,1
)
;
declare @buf nvarchar(max);
select @buf=dbo.CLR_FileIO_ReadTextFile(@csv,'utf-8');
insert into dbo.WkTbl_共通_電子国土基本図_地名情報_CSV
(
市区町村コード
,町または字の名称
,街区符号
,基礎番号
,住所コード
,経度
,緯度
,地図情報レベル
,摘要
)
select
A.TargetWord1 as 市区町村コード
,A.TargetWord2 as 町または字の名称
,A.TargetWord3 as 街区符号
,A.TargetWord4 as 基礎番号
,A.TargetWord5 as 住所コード
-- ,A.TargetWord6 as 住所コード数値
,cast(A.TargetWord7 as decimal(13,10)) as 経度
,cast(A.TargetWord8 as decimal(13,10)) as 緯度
,cast(A.TargetWord9 as int) as 地図情報レベル
,@url as 摘要
from dbo.CLR_Regex_FindMatchesAsTable2(@buf,'(\d*?),(.*?),(.*?),(.*?),(.*?),(.*?),(\d*?\.\d*?),(\d*?\.\d*?),(\d*?)\r\n') A;
-- 対象ファイルの削除
select
dbo.CLR_FileIO_DeleteFile(@name);
select
dbo.CLR_FileIO_DeleteFile(@csv);
set @i=@i+1;
end;
このスクリプトを実行すると、私のPCでは1時間以上動いています。
結果は次のようになります。
uid 市区町村コード 町または字の名称 街区符号 基礎番号 住所コード 経度 緯度 地図情報レベル 摘要
1 01101 旭ケ丘一丁目 1 1 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/1 141.3198560690 43.0440017920 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
2 01101 旭ケ丘一丁目 1 2 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/2 141.3199639160 43.0439709440 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
3 01101 旭ケ丘一丁目 1 3 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/3 141.3200685530 43.0439410140 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
4 01101 旭ケ丘一丁目 1 4 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/4 141.3201736520 43.0439109510 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
5 01101 旭ケ丘一丁目 1 5 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/5 141.3202766850 43.0438814800 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
6 01101 旭ケ丘一丁目 1 6 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/6 141.3202677810 43.0438491160 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
7 01101 旭ケ丘一丁目 1 7 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/7 141.3201833110 43.0438071470 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
8 01101 旭ケ丘一丁目 1 8 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/8 141.3200890970 43.0437603380 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
9 01101 旭ケ丘一丁目 1 9 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/9 141.3200073390 43.0437197180 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
10 01101 旭ケ丘一丁目 1 10 http://gi.gsi.go.jp/jusho/01101/AGO-1/1/10 141.3199052240 43.0436689820 2500 https://saigai.gsi.go.jp/jusho/download/data/01101.zip
... (省略) ...
終わりに
電子国土基本図(地名情報)「住居表示住所」を取り込んでみました。
このデータを使うことで、住所から緯度経度を求めることで地図表示ができそうです。
反対に、緯度経度の情報から住所の情報が取得できそうです。
他記事で郵便番号データをSQLServerで使えるようにしているのですが、街区などの情報を照合することで、より使いやすい形にできそうです。