オーストラリアでの住所からのジオコーディングは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でのコード作成が容易であったことがその理由である．
日本においては東大が同様のサービスを展開しているが，精度の点では今ひとつかもしれない．国内で唯一住所レベルのジオデータを有しているのはゼンリンであろう．個人情報保護の観点からは難しいのかもしれないが，防災の観点からは公開が望ましい．政府と企業の協業が必要である．