オーストラリアのジオコーディング

　オーストラリアでの住所からのジオコーディングはGeoscape社が担当しており，そのプロジェクト名をG-NAFという．オーストラリア政府から補助金を受けており，2029年まで無料公開されることが決まっている．

　データ数は1500万件以上，空間参照系はGDA94(EPSG: 4283)またはGDA2020(EPSG: 7844)である．

　今回はSQL Serverでデータベースからテーブル作成，データのインポート，テーブルへの主キーと外部キーの作成までを行う．

データのダウンロード

　データのダウンロードはここから行う．2024年7月7日現在，MAY 24 – Geoscape G-NAF – GDA2020(ZIP)という名称のファイルが最新である．サイズは約5GBある．zipファイルを解凍すると7GBに増える．

ファイルの概観

　ディレクトリは大まかにExtrasフォルダとG-NAF MAY 2024フォルダに分けられる．Extrasフォルダにはテーブル作成のためのsqlファイルと主キー・外部キー作成のためのsqlファイル，さらにビューを作成するためのsqlファイルが置かれている．G-NAF MAY 2024フォルダにはテーブルに格納されるデータが置かれている．G-NAF MAY 2024ファルダはさらにAuthority CodeフォルダとStandardフォルダに分かれており，それぞれマスタデータ，トランザクションデータがファイルとして置かれている．

Authority Codeフォルダ

　Authority Codeフォルダ内のファイルを記載しておく．psvファイルとはpipe separated valueファイルのことで，デリミタがパイプ(|)であるファイルのことである．

Authority_Code_ADDRESS_ALIAS_TYPE_AUT_psv.psv

Authority_Code_ADDRESS_CHANGE_TYPE_AUT_psv.psv

Authority_Code_ADDRESS_TYPE_AUT_psv.psv

Authority_Code_FLAT_TYPE_AUT_psv.psv

Authority_Code_GEOCODED_LEVEL_TYPE_AUT_psv.psv

Authority_Code_GEOCODE_RELIABILITY_AUT_psv.psv

Authority_Code_GEOCODE_TYPE_AUT_psv.psv

Authority_Code_LEVEL_TYPE_AUT_psv.psv

Authority_Code_LOCALITY_ALIAS_TYPE_AUT_psv.psv

Authority_Code_LOCALITY_CLASS_AUT_psv.psv

Authority_Code_MB_MATCH_CODE_AUT_psv.psv

Authority_Code_PS_JOIN_TYPE_AUT_psv.psv

Authority_Code_STREET_CLASS_AUT_psv.psv

Authority_Code_STREET_LOCALITY_ALIAS_TYPE_AUT_psv.psv

Authority_Code_STREET_SUFFIX_AUT_psv.psv

Authority_Code_STREET_TYPE_AUT_psv.psv

Standardフォルダ

　Standardフォルダ内のファイルを記載しておく．171ファイルがあるが，州ごとに19ファイルずつ細分されている．

ACT_ADDRESS_ALIAS_psv.psv

ACT_ADDRESS_DEFAULT_GEOCODE_psv.psv

ACT_ADDRESS_DETAIL_psv.psv

ACT_ADDRESS_FEATURE_psv.psv

ACT_ADDRESS_MESH_BLOCK_2016_psv.psv

ACT_ADDRESS_MESH_BLOCK_2021_psv.psv

ACT_ADDRESS_SITE_GEOCODE_psv.psv

ACT_ADDRESS_SITE_psv.psv

ACT_LOCALITY_ALIAS_psv.psv

ACT_LOCALITY_NEIGHBOUR_psv.psv

ACT_LOCALITY_POINT_psv.psv

ACT_LOCALITY_psv.psv

ACT_MB_2016_psv.psv

ACT_MB_2021_psv.psv

ACT_PRIMARY_SECONDARY_psv.psv

ACT_STATE_psv.psv

ACT_STREET_LOCALITY_ALIAS_psv.psv

ACT_STREET_LOCALITY_POINT_psv.psv

ACT_STREET_LOCALITY_psv.psv

NSW_ADDRESS_ALIAS_psv.psv

NSW_ADDRESS_DEFAULT_GEOCODE_psv.psv

NSW_ADDRESS_DETAIL_psv.psv

NSW_ADDRESS_FEATURE_psv.psv

NSW_ADDRESS_MESH_BLOCK_2016_psv.psv

NSW_ADDRESS_MESH_BLOCK_2021_psv.psv

NSW_ADDRESS_SITE_GEOCODE_psv.psv

NSW_ADDRESS_SITE_psv.psv

NSW_LOCALITY_ALIAS_psv.psv

NSW_LOCALITY_NEIGHBOUR_psv.psv

NSW_LOCALITY_POINT_psv.psv

NSW_LOCALITY_psv.psv

NSW_MB_2016_psv.psv

NSW_MB_2021_psv.psv

NSW_PRIMARY_SECONDARY_psv.psv

NSW_STATE_psv.psv

NSW_STREET_LOCALITY_ALIAS_psv.psv

NSW_STREET_LOCALITY_POINT_psv.psv

NSW_STREET_LOCALITY_psv.psv

NT_ADDRESS_ALIAS_psv.psv

NT_ADDRESS_DEFAULT_GEOCODE_psv.psv

NT_ADDRESS_DETAIL_psv.psv

NT_ADDRESS_FEATURE_psv.psv

NT_ADDRESS_MESH_BLOCK_2016_psv.psv

NT_ADDRESS_MESH_BLOCK_2021_psv.psv

NT_ADDRESS_SITE_GEOCODE_psv.psv

NT_ADDRESS_SITE_psv.psv

NT_LOCALITY_ALIAS_psv.psv

NT_LOCALITY_NEIGHBOUR_psv.psv

NT_LOCALITY_POINT_psv.psv

NT_LOCALITY_psv.psv

NT_MB_2016_psv.psv

NT_MB_2021_psv.psv

NT_PRIMARY_SECONDARY_psv.psv

NT_STATE_psv.psv

NT_STREET_LOCALITY_ALIAS_psv.psv

NT_STREET_LOCALITY_POINT_psv.psv

NT_STREET_LOCALITY_psv.psv

OT_ADDRESS_ALIAS_psv.psv

OT_ADDRESS_DEFAULT_GEOCODE_psv.psv

OT_ADDRESS_DETAIL_psv.psv

OT_ADDRESS_FEATURE_psv.psv

OT_ADDRESS_MESH_BLOCK_2016_psv.psv

OT_ADDRESS_MESH_BLOCK_2021_psv.psv

OT_ADDRESS_SITE_GEOCODE_psv.psv

OT_ADDRESS_SITE_psv.psv

OT_LOCALITY_ALIAS_psv.psv

OT_LOCALITY_NEIGHBOUR_psv.psv

OT_LOCALITY_POINT_psv.psv

OT_LOCALITY_psv.psv

OT_MB_2016_psv.psv

OT_MB_2021_psv.psv

OT_PRIMARY_SECONDARY_psv.psv

OT_STATE_psv.psv

OT_STREET_LOCALITY_ALIAS_psv.psv

OT_STREET_LOCALITY_POINT_psv.psv

OT_STREET_LOCALITY_psv.psv

QLD_ADDRESS_ALIAS_psv.psv

QLD_ADDRESS_DEFAULT_GEOCODE_psv.psv

QLD_ADDRESS_DETAIL_psv.psv

QLD_ADDRESS_FEATURE_psv.psv

QLD_ADDRESS_MESH_BLOCK_2016_psv.psv

QLD_ADDRESS_MESH_BLOCK_2021_psv.psv

QLD_ADDRESS_SITE_GEOCODE_psv.psv

QLD_ADDRESS_SITE_psv.psv

QLD_LOCALITY_ALIAS_psv.psv

QLD_LOCALITY_NEIGHBOUR_psv.psv

QLD_LOCALITY_POINT_psv.psv

QLD_LOCALITY_psv.psv

QLD_MB_2016_psv.psv

QLD_MB_2021_psv.psv

QLD_PRIMARY_SECONDARY_psv.psv

QLD_STATE_psv.psv

QLD_STREET_LOCALITY_ALIAS_psv.psv

QLD_STREET_LOCALITY_POINT_psv.psv

QLD_STREET_LOCALITY_psv.psv

SA_ADDRESS_ALIAS_psv.psv

SA_ADDRESS_DEFAULT_GEOCODE_psv.psv

SA_ADDRESS_DETAIL_psv.psv

SA_ADDRESS_FEATURE_psv.psv

SA_ADDRESS_MESH_BLOCK_2016_psv.psv

SA_ADDRESS_MESH_BLOCK_2021_psv.psv

SA_ADDRESS_SITE_GEOCODE_psv.psv

SA_ADDRESS_SITE_psv.psv

SA_LOCALITY_ALIAS_psv.psv

SA_LOCALITY_NEIGHBOUR_psv.psv

SA_LOCALITY_POINT_psv.psv

SA_LOCALITY_psv.psv

SA_MB_2016_psv.psv

SA_MB_2021_psv.psv

SA_PRIMARY_SECONDARY_psv.psv

SA_STATE_psv.psv

SA_STREET_LOCALITY_ALIAS_psv.psv

SA_STREET_LOCALITY_POINT_psv.psv

SA_STREET_LOCALITY_psv.psv

TAS_ADDRESS_ALIAS_psv.psv

TAS_ADDRESS_DEFAULT_GEOCODE_psv.psv

TAS_ADDRESS_DETAIL_psv.psv

TAS_ADDRESS_FEATURE_psv.psv

TAS_ADDRESS_MESH_BLOCK_2016_psv.psv

TAS_ADDRESS_MESH_BLOCK_2021_psv.psv

TAS_ADDRESS_SITE_GEOCODE_psv.psv

TAS_ADDRESS_SITE_psv.psv

TAS_LOCALITY_ALIAS_psv.psv

TAS_LOCALITY_NEIGHBOUR_psv.psv

TAS_LOCALITY_POINT_psv.psv

TAS_LOCALITY_psv.psv

TAS_MB_2016_psv.psv

TAS_MB_2021_psv.psv

TAS_PRIMARY_SECONDARY_psv.psv

TAS_STATE_psv.psv

TAS_STREET_LOCALITY_ALIAS_psv.psv

TAS_STREET_LOCALITY_POINT_psv.psv

TAS_STREET_LOCALITY_psv.psv

VIC_ADDRESS_ALIAS_psv.psv

VIC_ADDRESS_DEFAULT_GEOCODE_psv.psv

VIC_ADDRESS_DETAIL_psv.psv

VIC_ADDRESS_FEATURE_psv.psv

VIC_ADDRESS_MESH_BLOCK_2016_psv.psv

VIC_ADDRESS_MESH_BLOCK_2021_psv.psv

VIC_ADDRESS_SITE_GEOCODE_psv.psv

VIC_ADDRESS_SITE_psv.psv

VIC_LOCALITY_ALIAS_psv.psv

VIC_LOCALITY_NEIGHBOUR_psv.psv

VIC_LOCALITY_POINT_psv.psv

VIC_LOCALITY_psv.psv

VIC_MB_2016_psv.psv

VIC_MB_2021_psv.psv

VIC_PRIMARY_SECONDARY_psv.psv

VIC_STATE_psv.psv

VIC_STREET_LOCALITY_ALIAS_psv.psv

VIC_STREET_LOCALITY_POINT_psv.psv

VIC_STREET_LOCALITY_psv.psv

WA_ADDRESS_ALIAS_psv.psv

WA_ADDRESS_DEFAULT_GEOCODE_psv.psv

WA_ADDRESS_DETAIL_psv.psv

WA_ADDRESS_FEATURE_psv.psv

WA_ADDRESS_MESH_BLOCK_2016_psv.psv

WA_ADDRESS_MESH_BLOCK_2021_psv.psv

WA_ADDRESS_SITE_GEOCODE_psv.psv

WA_ADDRESS_SITE_psv.psv

WA_LOCALITY_ALIAS_psv.psv

WA_LOCALITY_NEIGHBOUR_psv.psv

WA_LOCALITY_POINT_psv.psv

WA_LOCALITY_psv.psv

WA_MB_2016_psv.psv

WA_MB_2021_psv.psv

WA_PRIMARY_SECONDARY_psv.psv

WA_STATE_psv.psv

WA_STREET_LOCALITY_ALIAS_psv.psv

WA_STREET_LOCALITY_POINT_psv.psv

WA_STREET_LOCALITY_psv.psv

データベースの作成

　SQL Server Management Studioのオブジェクトエクスプローラでデータベースを右クリックし，「新しいデータベース…」を選択する．

　任意のデータベース名を入力する．ここではGNAFDBとしている．

テーブルの作成

　テーブルの作成はスクリプト化されており，GNAF_TableCreation_Scriptsフォルダ内のcreate_tables_sqlserver.sqlファイルをSQL Server Management Studioから開いて実行するだけでテーブルが作成される．create_tables_sqlserverファイル内のスクリプトを記載しておく．データ長が50を超える列に注意が必要である．後でデータをインポートする際に最初にデータ長を広げておかないと，エラーで弾かれることになる．

--  
-- SQL script to Create the published ANSI compliant G-NAF database tables
--   
-- Automatically generated by Lynx on 2018.07.27 AD at 11:26:58 EST   
-- Manually updated 2021.08.23 - added CREATE TABLE scripts for ADDRESS_MESH_BLOCK_2021 and MB_2021.  
--   
--


-- ********************************************************************************************
-- ********************************************************************************************
-- Create ANSI tables 
-- ********************************************************************************************
-- ********************************************************************************************

CREATE TABLE ADDRESS_ALIAS (
 address_alias_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 principal_pid varchar(15) NOT NULL,
 alias_pid varchar(15) NOT NULL,
 alias_type_code varchar(10) NOT NULL,
 alias_comment varchar(200)
);

CREATE TABLE ADDRESS_ALIAS_TYPE_AUT (
 code varchar(10) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE ADDRESS_CHANGE_TYPE_AUT (
 code varchar(50) NOT NULL,
 name varchar(100) NOT NULL,
 description varchar(500)
);

CREATE TABLE ADDRESS_DEFAULT_GEOCODE (
 address_default_geocode_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_detail_pid varchar(15) NOT NULL,
 geocode_type_code varchar(4) NOT NULL,
 longitude numeric(11,8),
 latitude numeric(10,8)
);

CREATE TABLE ADDRESS_DETAIL (
 address_detail_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_last_modified date,
 date_retired date,
 building_name varchar(200),
 lot_number_prefix varchar(2),
 lot_number varchar(5),
 lot_number_suffix varchar(2),
 flat_type_code varchar(7),
 flat_number_prefix varchar(2),
 flat_number numeric(5),
 flat_number_suffix varchar(2),
 level_type_code varchar(4),
 level_number_prefix varchar(2),
 level_number numeric(3),
 level_number_suffix varchar(2),
 number_first_prefix varchar(3),
 number_first numeric(6),
 number_first_suffix varchar(2),
 number_last_prefix varchar(3),
 number_last numeric(6),
 number_last_suffix varchar(2),
 street_locality_pid varchar(15),
 location_description varchar(45),
 locality_pid varchar(15) NOT NULL,
 alias_principal char(1),
 postcode varchar(4),
 private_street varchar(75),
 legal_parcel_id varchar(20),
 confidence numeric(1),
 address_site_pid varchar(15) NOT NULL,
 level_geocoded_code numeric(2) NOT NULL,
 property_pid varchar(15),
 gnaf_property_pid varchar(15),
 primary_secondary varchar(1)
);

CREATE TABLE ADDRESS_FEATURE (
 address_feature_id varchar(16) NOT NULL,
 address_feature_pid varchar(16) NOT NULL,
 address_detail_pid varchar(15) NOT NULL,
 date_address_detail_created date NOT NULL,
 date_address_detail_retired date,
 address_change_type_code varchar(50)
);

CREATE TABLE ADDRESS_MESH_BLOCK_2016 (
 address_mesh_block_2016_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_detail_pid varchar(15) NOT NULL,
 mb_match_code varchar(15) NOT NULL,
 mb_2016_pid varchar(15) NOT NULL
);

CREATE TABLE ADDRESS_MESH_BLOCK_2021 (
 address_mesh_block_2021_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_detail_pid varchar(15) NOT NULL,
 mb_match_code varchar(15) NOT NULL,
 mb_2021_pid varchar(15) NOT NULL
);

CREATE TABLE ADDRESS_SITE (
 address_site_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_type varchar(8),
 address_site_name varchar(200)
);

CREATE TABLE ADDRESS_SITE_GEOCODE (
 address_site_geocode_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_site_pid varchar(15),
 geocode_site_name varchar(200),
 geocode_site_description varchar(45),
 geocode_type_code varchar(4),
 reliability_code numeric(1) NOT NULL,
 boundary_extent numeric(7),
 planimetric_accuracy numeric(12),
 elevation numeric(7),
 longitude numeric(11,8),
 latitude numeric(10,8)
);

CREATE TABLE ADDRESS_TYPE_AUT (
 code varchar(8) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE FLAT_TYPE_AUT (
 code varchar(7) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE GEOCODED_LEVEL_TYPE_AUT (
 code numeric(2) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(70)
);

CREATE TABLE GEOCODE_RELIABILITY_AUT (
 code numeric(1) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(100)
);

CREATE TABLE GEOCODE_TYPE_AUT (
 code varchar(4) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(250)
);

CREATE TABLE LEVEL_TYPE_AUT (
 code varchar(4) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE LOCALITY (
 locality_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 locality_name varchar(100) NOT NULL,
 primary_postcode varchar(4),
 locality_class_code char(1) NOT NULL,
 state_pid varchar(15) NOT NULL,
 gnaf_locality_pid varchar(15),
 gnaf_reliability_code numeric(1) NOT NULL
);

CREATE TABLE LOCALITY_ALIAS (
 locality_alias_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 locality_pid varchar(15) NOT NULL,
 name varchar(100) NOT NULL,
 postcode varchar(4),
 alias_type_code varchar(10) NOT NULL,
 state_pid varchar(15) NOT NULL
);

CREATE TABLE LOCALITY_ALIAS_TYPE_AUT (
 code varchar(10) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(100)
);

CREATE TABLE LOCALITY_CLASS_AUT (
 code char(1) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(200)
);

CREATE TABLE LOCALITY_NEIGHBOUR (
 locality_neighbour_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 locality_pid varchar(15) NOT NULL,
 neighbour_locality_pid varchar(15) NOT NULL
);

CREATE TABLE LOCALITY_POINT (
 locality_point_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 locality_pid varchar(15) NOT NULL,
 planimetric_accuracy numeric(12),
 longitude numeric(11,8),
 latitude numeric(10,8)
);

CREATE TABLE MB_2016 (
 mb_2016_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 mb_2016_code varchar(15) NOT NULL
);

CREATE TABLE MB_2021 (
 mb_2021_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 mb_2021_code varchar(15) NOT NULL
);


CREATE TABLE MB_MATCH_CODE_AUT (
 code varchar(15) NOT NULL,
 name varchar(100) NOT NULL,
 description varchar(250)
);

CREATE TABLE PRIMARY_SECONDARY (
 primary_secondary_pid varchar(15) NOT NULL,
 primary_pid varchar(15) NOT NULL,
 secondary_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 ps_join_type_code numeric(2) NOT NULL,
 ps_join_comment varchar(500)
);

CREATE TABLE PS_JOIN_TYPE_AUT (
 code numeric(2) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(500)
);

CREATE TABLE STATE (
 state_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 state_name varchar(50) NOT NULL,
 state_abbreviation varchar(3) NOT NULL
);

CREATE TABLE STREET_CLASS_AUT (
 code char(1) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(200)
);

CREATE TABLE STREET_LOCALITY (
 street_locality_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 street_class_code char(1) NOT NULL,
 street_name varchar(100) NOT NULL,
 street_type_code varchar(15),
 street_suffix_code varchar(15),
 locality_pid varchar(15) NOT NULL,
 gnaf_street_pid varchar(15),
 gnaf_street_confidence numeric(1),
 gnaf_reliability_code numeric(1) NOT NULL
);

CREATE TABLE STREET_LOCALITY_ALIAS (
 street_locality_alias_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 street_locality_pid varchar(15) NOT NULL,
 street_name varchar(100) NOT NULL,
 street_type_code varchar(15),
 street_suffix_code varchar(15),
 alias_type_code varchar(10) NOT NULL
);

CREATE TABLE STREET_LOCALITY_ALIAS_TYPE_AUT (
 code varchar(10) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(15)
);

CREATE TABLE STREET_LOCALITY_POINT (
 street_locality_point_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 street_locality_pid varchar(15) NOT NULL,
 boundary_extent numeric(7),
 planimetric_accuracy numeric(12),
 longitude numeric(11,8),
 latitude numeric(10,8)
);

CREATE TABLE STREET_SUFFIX_AUT (
 code varchar(15) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE STREET_TYPE_AUT (
 code varchar(15) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(15)
);

データのインポート

ウィザード編

　データのインポートにはウィザードを使う．Authority Codeフォルダ内の16ファイル，Standardフォルダ内の171ファイルに対してインポートを行う．骨の折れる作業であるが，変換先のテーブルを手動で選択してやらないとならず，ウィザードではここが自動化できない．eSTATの小地域（町丁・字等別）毎の年齢（５歳階級、４区分）別、男女別人口をSQL ServerにBULK INSERTするを参考にBULK INSERTしたほうが良い．ファイル名にテーブル名が含まれているのがヒントである．

　また，データ長が50を超える列が混じっており，「データソースの選択」の際に「詳細設定」で該当列のOutputColumnWidthの値を500などの大きい値に変更しておかないとインポートの最後にエラーで弾かれることになる．

　ともかく，インポートの手順を示す．

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

　「データソースの選択」で「Flat File Source」を選択し，「参照」からファイルを選択する．

　「データソースの選択」の「列」に進んだところ．「列区切り記号」がパイプになっていることに注目．

　「データソースの選択」の「詳細設定」でデータ長が50を超える列のOutputColumnWidthを500に広げている．ここはウィザードのインポートでつまづきやすい箇所である．

　「変換先の選択」で「SQL Server Native Client 11.0」を選択する．

　「コピー元のテーブルおよびビューを選択」で変換元のファイルと変換先のテーブルを一致させる．ファイル名とテーブル名を照合して選択する．

　「列マッピング」で列名とデータ長を確認する．

　Finishをクリックして成功すると画面のようになる．

BULK INSERT編

　コードを記載する．

BULK INSERT 	ADDRESS_ALIAS_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_ADDRESS_ALIAS_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	ADDRESS_CHANGE_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_ADDRESS_CHANGE_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	ADDRESS_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_ADDRESS_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	FLAT_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_FLAT_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	GEOCODED_LEVEL_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_GEOCODED_LEVEL_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	GEOCODE_RELIABILITY_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_GEOCODE_RELIABILITY_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	GEOCODE_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_GEOCODE_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	LEVEL_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_LEVEL_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	LOCALITY_ALIAS_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_LOCALITY_ALIAS_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	LOCALITY_CLASS_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_LOCALITY_CLASS_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	MB_MATCH_CODE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_MB_MATCH_CODE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	PS_JOIN_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_PS_JOIN_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	STREET_CLASS_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_STREET_CLASS_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	STREET_LOCALITY_ALIAS_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_STREET_LOCALITY_ALIAS_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	STREET_SUFFIX_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_STREET_SUFFIX_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)
BULK INSERT 	STREET_TYPE_AUT	 FROM 	'C:\path\g-naf_may24_allstates_gda2020_psv_1015\G-NAF\G-NAF MAY 2024\Authority Code\\Authority_Code_STREET_TYPE_AUT_psv.psv'	 WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW = 2)

(8 行処理されました)
(511 行処理されました)
(3 行処理されました)
(54 行処理されました)
(8 行処理されました)
(6 行処理されました)
(29 行処理されました)
(16 行処理されました)
(2 行処理されました)
(9 行処理されました)
(5 行処理されました)
(2 行処理されました)
(2 行処理されました)
(2 行処理されました)
(19 行処理されました)
(276 行処理されました)

　このコードの作成にはEXCELのPower Queryを用いている．フォルダからデータを抽出し，ファイル名とファイルパスを取得して加工し，クエリのテーブル名（BULK INSERT句）とファイル名（FROM句）にしている．

Power Queryでのクエリ文字列生成

　具体例を見ていこう．

　EXCELのデータタブの「データの取得」「ファイルから」「フォルダーから」へと進む．

　ユーザーインターフェースでフォルダを指定する．

　「データの変換」をクリックする．

　NameとFolder Path以外の列を削除する．

　カスタム列を挿入し図の文字列を記述する．これはFROM句で指定するファイルのフルパスになる．

　Name列を分割する．「区切り文字による分割」を選択する．

　「区切り記号」をカスタムとしアンダーバーを入力し，「一番右の区切り記号」をチェックする．

　再度「区切り文字による分割」から区切り記号をカスタムとしアンダーバーを入力し，「分割」を「一番左の区切り記号」とする．

　ファイル名からテーブル名を切り出した状態．選択した列を「削除」する．

　カスタム列を挿入し「”BULK INSERT”」と入力する．

　再度カスタム列を挿入し「”FROM”」と入力する．

　三度カスタム列を挿入し「”WITH (FIELDTERMINATOR = ‘|’, ROWTERMINATOR = ‘\n’, FIRSTROW = 2)”」と入力する．

　カスタム列を移動してクエリの順に並べる．

　「閉じて読み込む」をクリックすると171行のデータが読み込まれる．2行目から172行目までをコピーしてメモ帳に貼り付け，任意のファイル名に.sql拡張子を付加して保存すれば完成である．ただし，1500万件以上あるデータを読み込む作業なのでそれなりに時間がかかる．

(5145 行処理されました)
(270167 行処理されました)
(270167 行処理されました)
(194 行処理されました)
(270167 行処理されました)
(270167 行処理されました)
(480682 行処理されました)
(270167 行処理されました)
(185 行処理されました)
(844 行処理されました)
(146 行処理されました)
(146 行処理されました)
(6393 行処理されました)
(6662 行処理されました)
(99196 行処理されました)
(1 行処理されました)
(211 行処理されました)
(6839 行処理されました)
(6924 行処理されました)
(291187 行処理されました)
(5072576 行処理されました)
(5072576 行処理されました)
(58607 行処理されました)
(5072576 行処理されました)
(5072576 行処理されました)
(4880574 行処理されました)
(5072576 行処理されました)
(7716 行処理されました)
(26322 行処理されました)
(4745 行処理されました)
(4745 行処理されました)
(109880 行処理されました)
(112738 行処理されました)
(1818100 行処理されました)
(1 行処理されました)
(9275 行処理されました)
(180069 行処理されました)
(187533 行処理されました)
(3816 行処理されました)
(117658 行処理されました)
(117658 行処理されました)
(478 行処理されました)
(117658 行処理されました)
(117658 行処理されました)
(111569 行処理されました)
(117658 行処理されました)
(2198 行処理されました)
(1514 行処理されました)
(1098 行処理されました)
(1098 行処理されました)
(3299 行処理されました)
(3361 行処理されました)
(40059 行処理されました)
(1 行処理されました)
(177 行処理されました)
(8240 行処理されました)
(9511 行処理されました)
(40 行処理されました)
(5169 行処理されました)
(5169 行処理されました)
(58 行処理されました)
(5169 行処理されました)
(5169 行処理されました)
(3523 行処理されました)
(5169 行処理されました)
(15 行処理されました)
(2 行処理されました)
(15 行処理されました)
(15 行処理されました)
(137 行処理されました)
(136 行処理されました)
(471 行処理されました)
(1 行処理されました)
(18 行処理されました)
(165 行処理されました)
(250 行処理されました)
(230780 行処理されました)
(3466610 行処理されました)
(3466610 行処理されました)
(55011 行処理されました)
(3466610 行処理されました)
(3466610 行処理されました)
(3331086 行処理されました)
(3466610 行処理されました)
(8030 行処理されました)
(19160 行処理されました)
(3543 行処理されました)
(3543 行処理されました)
(69764 行処理されました)
(71862 行処理されました)
(975266 行処理されました)
(1 行処理されました)
(10411 行処理されました)
(154257 行処理されました)
(170083 行処理されました)
(38483 行処理されました)
(1242329 行処理されました)
(1242329 行処理されました)
(43706 行処理されました)
(1242329 行処理されました)
(1242329 行処理されました)
(1272995 行処理されました)
(1242329 行処理されました)
(3599 行処理されました)
(10760 行処理されました)
(2184 行処理されました)
(2184 行処理されました)
(28205 行処理されました)
(28431 行処理されました)
(208704 行処理されました)
(1 行処理されました)
(5712 行処理されました)
(64777 行処理されました)
(67804 行処理されました)
(8707 行処理されました)
(368350 行処理されました)
(368350 行処理されました)
(1615 行処理されました)
(368350 行処理されました)
(368350 行処理されました)
(350574 行処理されました)
(368350 行処理されました)
(768 行処理されました)
(3876 行処理されました)
(782 行処理されました)
(782 行処理されました)
(12981 行処理されました)
(13034 行処理されました)
(65382 行処理されました)
(1 行処理されました)
(812 行処理されました)
(19057 行処理されました)
(19912 行処理されました)
(218748 行処理されました)
(4262962 行処理されました)
(4262962 行処理されました)
(36410 行処理されました)
(4262962 行処理されました)
(4262962 行処理されました)
(8120440 行処理されました)
(4262962 行処理されました)
(3693 行処理されました)
(17236 行処理されました)
(2996 行処理されました)
(2996 行処理されました)
(85014 行処理されました)
(88739 行処理されました)
(1233160 行処理されました)
(1 行処理されました)
(11698 行処理されました)
(195429 行処理されました)
(203116 行処理されました)
(63250 行処理されました)
(1624635 行処理されました)
(1624635 行処理されました)
(10225 行処理されました)
(1624635 行処理されました)
(1624635 行処理されました)
(1572934 行処理されました)
(1624635 行処理されました)
(3318 行処理されました)
(9658 行処理されました)
(2052 行処理されました)
(2052 行処理されました)
(42449 行処理されました)
(43322 行処理されました)
(358753 行処理されました)
(1 行処理されました)
(1599 行処理されました)
(83459 行処理されました)
(87734 行処理されました)

主キーと外部キーの作成

　データのインポートが終わったらテーブルの主キーと外部キーの作成である．この作業もスクリプト化されており，GNAF_TableCreation_Scriptsフォルダ内のadd_fk_constraints.sqlファイルをSQL Server Management Studioから開いて実行するだけである．この作業にはやや時間がかかる．

　スクリプトを記載しておく．

--  
-- SQL script to Add primary and foreign key constraints
--   
-- Automatically generated by Lynx on 2018.07.27 AD at 11:27:05 EST   
-- Manually updated 2021.08.23 - added ADDRESS_MESH_BLOCK_2021_PK and MB_2021_PK constraint.  
-- Manually updated 2021.08.23 - added ADDRESS_MESH_BLOCK_2021_FK1, ADDRESS_MESH_BLOCK_2021_FK2 and ADDRESS_MESH_BLOCK_2021_FK3.
--
--  

-- ********************************************************************************************
-- ********************************************************************************************
-- Create primary key table constraints 
-- ********************************************************************************************
-- ********************************************************************************************
USE GNAFDB;
GO
ALTER TABLE ADDRESS_ALIAS ADD
 CONSTRAINT ADDRESS_ALIAS_PK PRIMARY KEY (address_alias_pid);

ALTER TABLE ADDRESS_ALIAS_TYPE_AUT ADD
 CONSTRAINT ADDRESS_ALIAS_TYPE_AUT_PK PRIMARY KEY (code);

ALTER TABLE ADDRESS_CHANGE_TYPE_AUT ADD
 CONSTRAINT ADDRESS_CHANGE_TYPE_AUT_PK PRIMARY KEY (code);

ALTER TABLE ADDRESS_DEFAULT_GEOCODE ADD
 CONSTRAINT ADDRESS_DEFAULT_GEOCODE_PK PRIMARY KEY (address_default_geocode_pid);

ALTER TABLE ADDRESS_DETAIL ADD
 CONSTRAINT ADDRESS_DETAIL_PK PRIMARY KEY (address_detail_pid);

ALTER TABLE ADDRESS_FEATURE ADD
 CONSTRAINT ADDRESS_FEATURE_PK PRIMARY KEY (address_feature_id);

ALTER TABLE ADDRESS_MESH_BLOCK_2016 ADD
 CONSTRAINT ADDRESS_MESH_BLOCK_2016_PK PRIMARY KEY (address_mesh_block_2016_pid);

ALTER TABLE ADDRESS_MESH_BLOCK_2021 ADD
 CONSTRAINT ADDRESS_MESH_BLOCK_2021_PK PRIMARY KEY (address_mesh_block_2021_pid); 

ALTER TABLE ADDRESS_SITE ADD
 CONSTRAINT ADDRESS_SITE_PK PRIMARY KEY (address_site_pid);

ALTER TABLE ADDRESS_SITE_GEOCODE ADD
 CONSTRAINT ADDRESS_SITE_GEOCODE_PK PRIMARY KEY (address_site_geocode_pid);

ALTER TABLE ADDRESS_TYPE_AUT ADD
 CONSTRAINT ADDRESS_TYPE_AUT_PK PRIMARY KEY (code);

ALTER TABLE FLAT_TYPE_AUT ADD
 CONSTRAINT FLAT_TYPE_AUT_PK PRIMARY KEY (code);

ALTER TABLE GEOCODED_LEVEL_TYPE_AUT ADD
 CONSTRAINT GEOCODED_LEVEL_TYPE_AUT_PK PRIMARY KEY (code);

ALTER TABLE GEOCODE_RELIABILITY_AUT ADD
 CONSTRAINT GEOCODE_RELIABILITY_AUT_PK PRIMARY KEY (code);

ALTER TABLE GEOCODE_TYPE_AUT ADD
 CONSTRAINT GEOCODE_TYPE_AUT_PK PRIMARY KEY (code);

ALTER TABLE LEVEL_TYPE_AUT ADD
 CONSTRAINT LEVEL_TYPE_AUT_PK PRIMARY KEY (code);

ALTER TABLE LOCALITY ADD
 CONSTRAINT LOCALITY_PK PRIMARY KEY (locality_pid);

ALTER TABLE LOCALITY_ALIAS ADD
 CONSTRAINT LOCALITY_ALIAS_PK PRIMARY KEY (locality_alias_pid);

ALTER TABLE LOCALITY_ALIAS_TYPE_AUT ADD
 CONSTRAINT LOCALITY_ALIAS_TYPE_AUT_PK PRIMARY KEY (code);

ALTER TABLE LOCALITY_CLASS_AUT ADD
 CONSTRAINT LOCALITY_CLASS_AUT_PK PRIMARY KEY (code);

ALTER TABLE LOCALITY_NEIGHBOUR ADD
 CONSTRAINT LOCALITY_NEIGHBOUR_PK PRIMARY KEY (locality_neighbour_pid);

ALTER TABLE LOCALITY_POINT ADD
 CONSTRAINT LOCALITY_POINT_PK PRIMARY KEY (locality_point_pid);

ALTER TABLE MB_2016 ADD
 CONSTRAINT MB_2016_PK PRIMARY KEY (mb_2016_pid);

ALTER TABLE MB_2021 ADD
 CONSTRAINT MB_2021_PK PRIMARY KEY (mb_2021_pid);

ALTER TABLE MB_MATCH_CODE_AUT ADD
 CONSTRAINT MB_MATCH_CODE_AUT_PK PRIMARY KEY (code);

ALTER TABLE PRIMARY_SECONDARY ADD
 CONSTRAINT PRIMARY_SECONDARY_PK PRIMARY KEY (primary_secondary_pid);

ALTER TABLE PS_JOIN_TYPE_AUT ADD
 CONSTRAINT PS_JOIN_TYPE_AUT_PK PRIMARY KEY (code);

ALTER TABLE STATE ADD
 CONSTRAINT STATE_PK PRIMARY KEY (state_pid);

ALTER TABLE STREET_CLASS_AUT ADD
 CONSTRAINT STREET_CLASS_AUT_PK PRIMARY KEY (code);

ALTER TABLE STREET_LOCALITY ADD
 CONSTRAINT STREET_LOCALITY_PK PRIMARY KEY (street_locality_pid);

ALTER TABLE STREET_LOCALITY_ALIAS ADD
 CONSTRAINT STREET_LOCALITY_ALIAS_PK PRIMARY KEY (street_locality_alias_pid);

ALTER TABLE STREET_LOCALITY_ALIAS_TYPE_AUT ADD
 CONSTRAINT STREET_LOCALITY_ALIAS_TYPE__PK PRIMARY KEY (code);

ALTER TABLE STREET_LOCALITY_POINT ADD
 CONSTRAINT STREET_LOCALITY_POINT_PK PRIMARY KEY (street_locality_point_pid);

ALTER TABLE STREET_SUFFIX_AUT ADD
 CONSTRAINT STREET_SUFFIX_AUT_PK PRIMARY KEY (code);

ALTER TABLE STREET_TYPE_AUT ADD
 CONSTRAINT STREET_TYPE_AUT_PK PRIMARY KEY (code);

-- ********************************************************************************************
-- ********************************************************************************************
-- Create foreign key table constraints 
-- ********************************************************************************************
-- ********************************************************************************************

ALTER TABLE ADDRESS_ALIAS ADD
 CONSTRAINT ADDRESS_ALIAS_FK1 FOREIGN KEY (alias_pid)
  REFERENCES ADDRESS_DETAIL (address_detail_pid);

ALTER TABLE ADDRESS_ALIAS ADD
 CONSTRAINT ADDRESS_ALIAS_FK2 FOREIGN KEY (alias_type_code)
  REFERENCES ADDRESS_ALIAS_TYPE_AUT (code);

ALTER TABLE ADDRESS_ALIAS ADD
 CONSTRAINT ADDRESS_ALIAS_FK3 FOREIGN KEY (principal_pid)
  REFERENCES ADDRESS_DETAIL (address_detail_pid);

ALTER TABLE ADDRESS_DEFAULT_GEOCODE ADD
 CONSTRAINT ADDRESS_DEFAULT_GEOCODE_FK1 FOREIGN KEY (address_detail_pid)
  REFERENCES ADDRESS_DETAIL (address_detail_pid);

ALTER TABLE ADDRESS_DEFAULT_GEOCODE ADD
 CONSTRAINT ADDRESS_DEFAULT_GEOCODE_FK2 FOREIGN KEY (geocode_type_code)
  REFERENCES GEOCODE_TYPE_AUT (code);

ALTER TABLE ADDRESS_DETAIL ADD
 CONSTRAINT ADDRESS_DETAIL_FK1 FOREIGN KEY (address_site_pid)
  REFERENCES ADDRESS_SITE (address_site_pid);

ALTER TABLE ADDRESS_DETAIL ADD
 CONSTRAINT ADDRESS_DETAIL_FK2 FOREIGN KEY (flat_type_code)
  REFERENCES FLAT_TYPE_AUT (code);

ALTER TABLE ADDRESS_DETAIL ADD
 CONSTRAINT ADDRESS_DETAIL_FK3 FOREIGN KEY (level_geocoded_code)
  REFERENCES GEOCODED_LEVEL_TYPE_AUT (code);

ALTER TABLE ADDRESS_DETAIL ADD
 CONSTRAINT ADDRESS_DETAIL_FK4 FOREIGN KEY (level_type_code)
  REFERENCES LEVEL_TYPE_AUT (code);

ALTER TABLE ADDRESS_DETAIL ADD
 CONSTRAINT ADDRESS_DETAIL_FK5 FOREIGN KEY (locality_pid)
  REFERENCES LOCALITY (locality_pid);

ALTER TABLE ADDRESS_DETAIL ADD
 CONSTRAINT ADDRESS_DETAIL_FK6 FOREIGN KEY (street_locality_pid)
  REFERENCES STREET_LOCALITY (street_locality_pid);

ALTER TABLE ADDRESS_FEATURE ADD
 CONSTRAINT ADDRESS_FEATURE_FK1 FOREIGN KEY (address_change_type_code)
  REFERENCES ADDRESS_CHANGE_TYPE_AUT (code);

ALTER TABLE ADDRESS_FEATURE ADD
 CONSTRAINT ADDRESS_FEATURE_FK2 FOREIGN KEY (address_detail_pid)
  REFERENCES ADDRESS_DETAIL (address_detail_pid);

ALTER TABLE ADDRESS_MESH_BLOCK_2016 ADD
 CONSTRAINT ADDRESS_MESH_BLOCK_2016_FK1 FOREIGN KEY (address_detail_pid)
  REFERENCES ADDRESS_DETAIL (address_detail_pid);

ALTER TABLE ADDRESS_MESH_BLOCK_2016 ADD
 CONSTRAINT ADDRESS_MESH_BLOCK_2016_FK2 FOREIGN KEY (mb_2016_pid)
  REFERENCES MB_2016 (mb_2016_pid);

ALTER TABLE ADDRESS_MESH_BLOCK_2016 ADD
 CONSTRAINT ADDRESS_MESH_BLOCK_2016_FK3 FOREIGN KEY (mb_match_code)
  REFERENCES MB_MATCH_CODE_AUT (code);

ALTER TABLE ADDRESS_MESH_BLOCK_2021 ADD
 CONSTRAINT ADDRESS_MESH_BLOCK_2021_FK1 FOREIGN KEY (address_detail_pid)
  REFERENCES ADDRESS_DETAIL (address_detail_pid);

ALTER TABLE ADDRESS_MESH_BLOCK_2021 ADD
 CONSTRAINT ADDRESS_MESH_BLOCK_2021_FK2 FOREIGN KEY (mb_2021_pid)
  REFERENCES MB_2021 (mb_2021_pid);

ALTER TABLE ADDRESS_MESH_BLOCK_2021 ADD
 CONSTRAINT ADDRESS_MESH_BLOCK_2021_FK3 FOREIGN KEY (mb_match_code)
  REFERENCES MB_MATCH_CODE_AUT (code);

ALTER TABLE ADDRESS_SITE ADD
 CONSTRAINT ADDRESS_SITE_FK1 FOREIGN KEY (address_type)
  REFERENCES ADDRESS_TYPE_AUT (code);

ALTER TABLE ADDRESS_SITE_GEOCODE ADD
 CONSTRAINT ADDRESS_SITE_GEOCODE_FK1 FOREIGN KEY (address_site_pid)
  REFERENCES ADDRESS_SITE (address_site_pid);

ALTER TABLE ADDRESS_SITE_GEOCODE ADD
 CONSTRAINT ADDRESS_SITE_GEOCODE_FK2 FOREIGN KEY (geocode_type_code)
  REFERENCES GEOCODE_TYPE_AUT (code);

ALTER TABLE ADDRESS_SITE_GEOCODE ADD
 CONSTRAINT ADDRESS_SITE_GEOCODE_FK3 FOREIGN KEY (reliability_code)
  REFERENCES GEOCODE_RELIABILITY_AUT (code);

ALTER TABLE LOCALITY ADD
 CONSTRAINT LOCALITY_FK1 FOREIGN KEY (gnaf_reliability_code)
  REFERENCES GEOCODE_RELIABILITY_AUT (code);

ALTER TABLE LOCALITY ADD
 CONSTRAINT LOCALITY_FK2 FOREIGN KEY (locality_class_code)
  REFERENCES LOCALITY_CLASS_AUT (code);

ALTER TABLE LOCALITY ADD
 CONSTRAINT LOCALITY_FK3 FOREIGN KEY (state_pid)
  REFERENCES STATE (state_pid);

ALTER TABLE LOCALITY_ALIAS ADD
 CONSTRAINT LOCALITY_ALIAS_FK1 FOREIGN KEY (alias_type_code)
  REFERENCES LOCALITY_ALIAS_TYPE_AUT (code);

ALTER TABLE LOCALITY_ALIAS ADD
 CONSTRAINT LOCALITY_ALIAS_FK2 FOREIGN KEY (locality_pid)
  REFERENCES LOCALITY (locality_pid);

ALTER TABLE LOCALITY_NEIGHBOUR ADD
 CONSTRAINT LOCALITY_NEIGHBOUR_FK1 FOREIGN KEY (locality_pid)
  REFERENCES LOCALITY (locality_pid);

ALTER TABLE LOCALITY_NEIGHBOUR ADD
 CONSTRAINT LOCALITY_NEIGHBOUR_FK2 FOREIGN KEY (neighbour_locality_pid)
  REFERENCES LOCALITY (locality_pid);

ALTER TABLE LOCALITY_POINT ADD
 CONSTRAINT LOCALITY_POINT_FK1 FOREIGN KEY (locality_pid)
  REFERENCES LOCALITY (locality_pid);

ALTER TABLE PRIMARY_SECONDARY ADD
 CONSTRAINT PRIMARY_SECONDARY_FK1 FOREIGN KEY (primary_pid)
  REFERENCES ADDRESS_DETAIL (address_detail_pid);

ALTER TABLE PRIMARY_SECONDARY ADD
 CONSTRAINT PRIMARY_SECONDARY_FK2 FOREIGN KEY (ps_join_type_code)
  REFERENCES PS_JOIN_TYPE_AUT (code);

ALTER TABLE PRIMARY_SECONDARY ADD
 CONSTRAINT PRIMARY_SECONDARY_FK3 FOREIGN KEY (secondary_pid)
  REFERENCES ADDRESS_DETAIL (address_detail_pid);

ALTER TABLE STREET_LOCALITY ADD
 CONSTRAINT STREET_LOCALITY_FK1 FOREIGN KEY (gnaf_reliability_code)
  REFERENCES GEOCODE_RELIABILITY_AUT (code);

ALTER TABLE STREET_LOCALITY ADD
 CONSTRAINT STREET_LOCALITY_FK2 FOREIGN KEY (locality_pid)
  REFERENCES LOCALITY (locality_pid);

ALTER TABLE STREET_LOCALITY ADD
 CONSTRAINT STREET_LOCALITY_FK3 FOREIGN KEY (street_class_code)
  REFERENCES STREET_CLASS_AUT (code);

ALTER TABLE STREET_LOCALITY ADD
 CONSTRAINT STREET_LOCALITY_FK4 FOREIGN KEY (street_suffix_code)
  REFERENCES STREET_SUFFIX_AUT (code);

ALTER TABLE STREET_LOCALITY ADD
 CONSTRAINT STREET_LOCALITY_FK5 FOREIGN KEY (street_type_code)
  REFERENCES STREET_TYPE_AUT (code);

ALTER TABLE STREET_LOCALITY_ALIAS ADD
 CONSTRAINT STREET_LOCALITY_ALIAS_FK1 FOREIGN KEY (alias_type_code)
  REFERENCES STREET_LOCALITY_ALIAS_TYPE_AUT (code);

ALTER TABLE STREET_LOCALITY_ALIAS ADD
 CONSTRAINT STREET_LOCALITY_ALIAS_FK2 FOREIGN KEY (street_locality_pid)
  REFERENCES STREET_LOCALITY (street_locality_pid);

ALTER TABLE STREET_LOCALITY_ALIAS ADD
 CONSTRAINT STREET_LOCALITY_ALIAS_FK3 FOREIGN KEY (street_suffix_code)
  REFERENCES STREET_SUFFIX_AUT (code);

ALTER TABLE STREET_LOCALITY_ALIAS ADD
 CONSTRAINT STREET_LOCALITY_ALIAS_FK4 FOREIGN KEY (street_type_code)
  REFERENCES STREET_TYPE_AUT (code);

ALTER TABLE STREET_LOCALITY_POINT ADD
 CONSTRAINT STREET_LOCALITY_POINT_FK1 FOREIGN KEY (street_locality_pid)
  REFERENCES STREET_LOCALITY (street_locality_pid);

ビューの作成

　GNAF_View_Scriptsフォルダ内のaddress_view.sqlファイルをSQL Server Management Studioで開いて実行するとビューが作成される．

USE GNAFDB;
GO

CREATE VIEW ADDRESS_VIEW

AS

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,

AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,

FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,

LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,

AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,

SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,

L.LOCALITY_NAME as LOCALITY_NAME,

ST.STATE_ABBREVIATION as STATE_ABBREVIATION,

AD.POSTCODE as POSTCODE,

ADG.LATITUDE as LATITUDE,
ADG.LONGITUDE as LONGITUDE,
GTA.NAME as GEOCODE_TYPE,

AD.CONFIDENCE as CONFIDENCE,

AD.ALIAS_PRINCIPAL as ALIAS_PRINCIPAL,
AD.PRIMARY_SECONDARY as PRIMARY_SECONDARY,

AD.LEGAL_PARCEL_ID as LEGAL_PARCEL_ID,

AD.DATE_CREATED as DATE_CREATED

FROM

ADDRESS_DETAIL AD 
LEFT JOIN FLAT_TYPE_AUT FTA ON AD.FLAT_TYPE_CODE=FTA.CODE
LEFT JOIN LEVEL_TYPE_AUT LTA ON AD.LEVEL_TYPE_CODE=LTA.CODE
JOIN STREET_LOCALITY SL ON AD.STREET_LOCALITY_PID=SL.STREET_LOCALITY_PID
LEFT JOIN STREET_SUFFIX_AUT SSA ON SL.STREET_SUFFIX_CODE=SSA.CODE
LEFT JOIN STREET_CLASS_AUT SCA ON SL.STREET_CLASS_CODE=SCA.CODE 
LEFT JOIN STREET_TYPE_AUT STA ON SL.STREET_TYPE_CODE=STA.CODE
JOIN LOCALITY L ON AD.LOCALITY_PID = L.LOCALITY_PID
JOIN ADDRESS_DEFAULT_GEOCODE ADG ON AD.ADDRESS_DETAIL_PID=ADG.ADDRESS_DETAIL_PID
LEFT JOIN GEOCODE_TYPE_AUT GTA ON ADG.GEOCODE_TYPE_CODE=GTA.CODE
LEFT JOIN GEOCODED_LEVEL_TYPE_AUT GLTA ON AD.LEVEL_GEOCODED_CODE=GLTA.CODE
JOIN STATE ST ON L.STATE_PID=ST.STATE_PID

WHERE 
AD.CONFIDENCE > -1

クエリの実行

　ヒルトンケアンズの住所（34 Esplanade, Cairns City QLD 4870）を入力してみる．

SELECT [ADDRESS_DETAIL_PID]
      ,[STREET_LOCALITY_PID]
      ,[LOCALITY_PID]
      ,[BUILDING_NAME]
      ,[LOT_NUMBER_PREFIX]
      ,[LOT_NUMBER]
      ,[LOT_NUMBER_SUFFIX]
      ,[FLAT_TYPE]
      ,[FLAT_NUMBER_PREFIX]
      ,[FLAT_NUMBER]
      ,[FLAT_NUMBER_SUFFIX]
      ,[LEVEL_TYPE]
      ,[LEVEL_NUMBER_PREFIX]
      ,[LEVEL_NUMBER]
      ,[LEVEL_NUMBER_SUFFIX]
      ,[NUMBER_FIRST_PREFIX]
      ,[NUMBER_FIRST]
      ,[NUMBER_FIRST_SUFFIX]
      ,[NUMBER_LAST_PREFIX]
      ,[NUMBER_LAST]
      ,[NUMBER_LAST_SUFFIX]
      ,[STREET_NAME]
      ,[STREET_CLASS_CODE]
      ,[STREET_CLASS_TYPE]
      ,[STREET_TYPE_CODE]
      ,[STREET_SUFFIX_CODE]
      ,[STREET_SUFFIX_TYPE]
      ,[LOCALITY_NAME]
      ,[STATE_ABBREVIATION]
      ,[POSTCODE]
      ,[LATITUDE]
      ,[LONGITUDE]
      ,[GEOCODE_TYPE]
      ,[CONFIDENCE]
      ,[ALIAS_PRINCIPAL]
      ,[PRIMARY_SECONDARY]
      ,[LEGAL_PARCEL_ID]
      ,[DATE_CREATED]
  FROM [GNAFDB].[dbo].[ADDRESS_VIEW]
  WHERE POSTCODE = '4870'
  AND	STATE_ABBREVIATION = 'QLD'
  AND	LOCALITY_NAME = 'Cairns City'
  AND	STREET_NAME = 'Esplanade'
  AND	NUMBER_FIRST = '34'

結果

　結果は複数行だが，経度，緯度の座標はすべて一致している．

ADDRESS_DETAIL_PID	STREET_LOCALITY_PID	LOCALITY_PID	BUILDING_NAME	LOT_NUMBER_PREFIX	LOT_NUMBER	LOT_NUMBER_SUFFIX	FLAT_TYPE	FLAT_NUMBER_PREFIX	FLAT_NUMBER	FLAT_NUMBER_SUFFIX	LEVEL_TYPE	LEVEL_NUMBER_PREFIX	LEVEL_NUMBER	LEVEL_NUMBER_SUFFIX	NUMBER_FIRST_PREFIX	NUMBER_FIRST	NUMBER_FIRST_SUFFIX	NUMBER_LAST_PREFIX	NUMBER_LAST	NUMBER_LAST_SUFFIX	STREET_NAME	STREET_CLASS_CODE	STREET_CLASS_TYPE	STREET_TYPE_CODE	STREET_SUFFIX_CODE	STREET_SUFFIX_TYPE	LOCALITY_NAME	STATE_ABBREVIATION	POSTCODE	LATITUDE	LONGITUDE	GEOCODE_TYPE	CONFIDENCE	ALIAS_PRINCIPAL	PRIMARY_SECONDARY	LEGAL_PARCEL_ID	DATE_CREATED
GAQLD720480696	QLD172446	loc48dc42f9f7dd	NULL	NULL	NULL	NULL	SHOP	NULL	3	NULL	NULL	NULL	NULL	NULL	NULL	34	NULL	NULL	38	NULL	ESPLANADE	C	CONFIRMED	NULL	NULL	NULL	CAIRNS CITY	QLD	4870	-16.92483379	145.78013420	PROPERTY CENTROID	0	P	S	10/SP214821	2020-07-20
GAQLD720483038	QLD172446	loc48dc42f9f7dd	NULL	NULL	NULL	NULL	SHOP	NULL	6	NULL	NULL	NULL	NULL	NULL	NULL	34	NULL	NULL	38	NULL	ESPLANADE	C	CONFIRMED	NULL	NULL	NULL	CAIRNS CITY	QLD	4870	-16.92483379	145.78013420	PROPERTY CENTROID	0	P	S	10/SP214821	2020-07-20
GAQLD720482479	QLD172446	loc48dc42f9f7dd	NULL	NULL	NULL	NULL	SHOP	NULL	5	NULL	NULL	NULL	NULL	NULL	NULL	34	NULL	NULL	38	NULL	ESPLANADE	C	CONFIRMED	NULL	NULL	NULL	CAIRNS CITY	QLD	4870	-16.92483379	145.78013420	PROPERTY CENTROID	0	P	S	10/SP214821	2020-07-20
GAQLD720481480	QLD172446	loc48dc42f9f7dd	NULL	NULL	NULL	NULL	SHOP	NULL	7	NULL	NULL	NULL	NULL	NULL	NULL	34	NULL	NULL	38	NULL	ESPLANADE	C	CONFIRMED	NULL	NULL	NULL	CAIRNS CITY	QLD	4870	-16.92483379	145.78013420	PROPERTY CENTROID	0	P	S	10/SP214821	2020-07-20
GAQLD720481625	QLD172446	loc48dc42f9f7dd	NULL	NULL	NULL	NULL	SHOP	NULL	2	NULL	NULL	NULL	NULL	NULL	NULL	34	NULL	NULL	38	NULL	ESPLANADE	C	CONFIRMED	NULL	NULL	NULL	CAIRNS CITY	QLD	4870	-16.92483379	145.78013420	PROPERTY CENTROID	0	P	S	10/SP214821	2020-07-20
GAQLD155118382	QLD172446	loc48dc42f9f7dd	HILTON HOTEL	NULL	10	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	34	NULL	NULL	38	NULL	ESPLANADE	C	CONFIRMED	NULL	NULL	NULL	CAIRNS CITY	QLD	4870	-16.92483379	145.78013420	PROPERTY CENTROID	2	P	P	10/SP214821	2021-07-25
GAQLD720481476	QLD172446	loc48dc42f9f7dd	NULL	NULL	NULL	NULL	SHOP	NULL	4	NULL	NULL	NULL	NULL	NULL	NULL	34	NULL	NULL	38	NULL	ESPLANADE	C	CONFIRMED	NULL	NULL	NULL	CAIRNS CITY	QLD	4870	-16.92483379	145.78013420	PROPERTY CENTROID	0	P	S	10/SP214821	2020-07-20
GAQLD720480712	QLD172446	loc48dc42f9f7dd	NULL	NULL	NULL	NULL	SHOP	NULL	1	NULL	NULL	NULL	NULL	NULL	NULL	34	NULL	NULL	38	NULL	ESPLANADE	C	CONFIRMED	NULL	NULL	NULL	CAIRNS CITY	QLD	4870	-16.92483379	145.78013420	PROPERTY CENTROID	0	P	S	10/SP214821	2020-07-20

Cairns Cityのポイントを抽出するクエリ

　下記クエリを実行する．

SELECT [ADDRESS_DETAIL_PID]
      ,[STREET_LOCALITY_PID]
      ,[LOCALITY_PID]
      ,[BUILDING_NAME]
      ,[LOT_NUMBER_PREFIX]
      ,[LOT_NUMBER]
      ,[LOT_NUMBER_SUFFIX]
      ,[FLAT_TYPE]
      ,[FLAT_NUMBER_PREFIX]
      ,[FLAT_NUMBER]
      ,[FLAT_NUMBER_SUFFIX]
      ,[LEVEL_TYPE]
      ,[LEVEL_NUMBER_PREFIX]
      ,[LEVEL_NUMBER]
      ,[LEVEL_NUMBER_SUFFIX]
      ,[NUMBER_FIRST_PREFIX]
      ,[NUMBER_FIRST]
      ,[NUMBER_FIRST_SUFFIX]
      ,[NUMBER_LAST_PREFIX]
      ,[NUMBER_LAST]
      ,[NUMBER_LAST_SUFFIX]
      ,[STREET_NAME]
      ,[STREET_CLASS_CODE]
      ,[STREET_CLASS_TYPE]
      ,[STREET_TYPE_CODE]
      ,[STREET_SUFFIX_CODE]
      ,[STREET_SUFFIX_TYPE]
      ,[LOCALITY_NAME]
      ,[STATE_ABBREVIATION]
      ,[POSTCODE]
      ,[LATITUDE]
      ,[LONGITUDE]
      ,[GEOCODE_TYPE]
      ,[CONFIDENCE]
      ,[ALIAS_PRINCIPAL]
      ,[PRIMARY_SECONDARY]
      ,[LEGAL_PARCEL_ID]
      ,[DATE_CREATED]
  FROM [GNAFDB].[dbo].[ADDRESS_VIEW]
  WHERE	LOCALITY_NAME = 'CAIRNS CITY'

(6357 行処理されました)

結果

　結果をQGISで表示する．

まとめ

　オーストラリアのGeoscape社による住所からのジオコーディングを行った．ファイルからデータベースへのBULK INSERTにより10分とかからずデータベースを構築できた．テーブル名をファイル名に含めてあり，Power Queryでのコード作成が容易であったことがその理由である．

　日本においては東大が同様のサービスを展開しているが，精度の点では今ひとつかもしれない．国内で唯一住所レベルのジオデータを有しているのはゼンリンであろう．個人情報保護の観点からは難しいのかもしれないが，防災の観点からは公開が望ましい．政府と企業の協業が必要である．