はじめに

何気なしにネットの海を漂っていると、「"電子国土基本図（地名情報）「住居表示住所」"」の情報がありました。
別記事で郵便番号の情報を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ファイルの内容は以下のとおりです。

　j13111k_20231222.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のフォーマットは次のようになっています。

市区町村コード：　文字列
町又は字の名称：　文字列
街区符号：　文字列
基礎番号：　文字列
住所コード（可読）：　文字列
住所コード（数値）：　空）
経度：　実数
緯度：　実数
地図情報レベル：　整数

データの取り込み方法

データ取り込みの流れ

データの取り込みは次の２つのテーブルに収納することを考えます。

市区町村毎の情報（ＨＰのページ単位）
ZIPファイル内のCSVファイルの内容の情報（ＺＩＰファイル単位）

そのため取り込みのステップは次のようになります。

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を使うようにしてください。

使い方はこのようになります。

.sql


-- 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

使い方はこの通りです。

.sql




-- 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を実行した例です。

.sql


declare @buf nvarchar(max);
select @buf=dbo.CLR_FileIO_ReadTextFile(@csv,'utf-8');

###　データ取り込み用スクリプト

前述のCLRを拡張した状態で電子国土基本図（地名情報）「住居表示住所」を取り込むスクリプトは以下のとおりです。

.sql


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で使えるようにしているのですが、街区などの情報を照合することで、より使いやすい形にできそうです。