第 6 章 空間データをインポートする (Beginning Spatial with SQL Server 2008)

 多くの空間アプリケーションがカスタム定義の空間機能を組み合わせている.例えば顧客セットの局在と,広く受け入れられた表現の空間データ,地球上の汎用性のある特徴,例えば国や州の境界線,世界の主要都市の局在および主要な道路や鉄道の経路などである.この情報は自分自身で作成するよりも,多くの代替可能な資源が存在しており,そこから普通に使うための空間データを取得して空間アプリケーションに搭載できる.

 本章では,そこから一般公開された空間情報を取得できる資源,そこでそのデータが普通に提供されるフォーマットおよびその情報を SQL Server にインポートするのに使える技術を紹介しよう.

空間データの資源

 空間データ資源は豊富に存在し,教育機関や政府機関と同様,さまざまな商用データベンダーからも取得でき,無料で情報を利用可能である.Table 6-1 に空間データを無料でダウンロードできる少数のインターネット資源の詳細を示す.

Table 6-1. 無料でダウンロードできる空間情報資源
資源 詳細
http://www.census.gov/ 米国国勢調査局地理学部は高品質の空間情報を保有しており,それには地名辞典,郵便番号集計エリア (ZCTAs), TIGER 道路・河川・鉄道データベースおよび他の多くの地理的エンティティを包括している(合衆国限定).
http://geodata.grid.unep.ch/ 国連地理データポータルは地球,国家,地域および準地域統計および空間データを含み,それらをカバーするテーマ,つまり淡水,人口,森林,排出,気候,災害,健康および GDP としてカバーする.
http://biogeo.berkeley.edu/gadm/ グローバル行政区データベース (GADM) は国境線,州,国家および全世界をカバーするのと同等の 1 個の ZIP ファイルとして保有しており,バークレイのカリフォルニア大学が運営している.
http://earth-info.nga.mil/gns/html/ アメリカ国家地理空間情報局 (NGA) の GEOnet ネームサーバー (GNS) は全ての外国の地名の公的リポジトリであり,局在,行政区分および品質に関する情報を保有する.
http://geodata.gov/wps/portal/gos 合衆国政府『地理空間ワンストップ』地理データウェブページは分類されたリンクを保有し,様々な資源は生態学,地質学,健康,交通手段および人口統計に関して地域をカバーする.

 第 4 章で示したように,SQL Server 2008 の静的空間メソッドはそれぞれ,WKT 表現からでも WKB 表現からでも GML 表現からでも同時に一つの空間データのアイテムを生成できるだけである.しかし,Table 6-1 に列挙したような空間データ資源はさまざまな他の種類の空間フォーマットを蓄積している可能性があり,何千もの一個一個のアイテムを一つのドキュメントに記述している可能性がある.ゆえに,静的メソッドを使用していては,それらの資源から直接 geography データや geometry データを生成することはできない.

 本章の残りで,利用可能ないくつかの一般的な空間データの代替フォーマットについて議論し,このデータを SQL Server 2008 にインポートするのに使える技術を説明する.

表形式の空間データをインポートする

 間違いなく空間データではないが,もっとも豊富な(そしてもっとも簡素な)無料で利用可能で一般に場所の名前から取得される地理情報の資源とは,各場所の位置を記述した一組の緯度と経度座標である.これらの資源は関連する他の情報,つまり人口や経済指数の列を保有している可能性もある.このフォーマットで表現された情報は一般に「地名辞典」として知られており,測地情報の辞書である.

 仮に地理時点のような緯度と経度(または投影座標系からの北座標と東座標)を含むデータの構造化テーブルから空間情報をインポートしたいなら,一つの静的メソッドを使って geography 型か geometry 型の Point オブジェクトを生成でき,それはデータの各アイテムを表現した座標値に基づいている.これには次のステップが関与している.

  1. SQL Server 2008 の提供するバルクインポートメソッドの一つを使って構造化データを新規テーブルにインポートする.つまり,OPENROWSET および BULK INSERT T-SQL ステートメント,BCP ユーティリティあるいはインポート・エクスポートウィザードである.
  2. ALTER TABLE ステートメントを使ってテーブルに新しい geography 型または geometry 型の列を追加し,由来する空間データを保持する.
  3. T-SQL の UPDATE ステートメントを静的メソッドと共に使って新しい列に投入する.それはインポートされたデータ内の列の座標値に基づいている.

 このアプローチを説明するため,アメリカ地質調査所 (USGS) の提供する地震データのファイルを使って例を示したい.USGS は無料で利用可能なデータベースを数多く作成しており,ウェブサイト http://www.usgs.gov からダウンロードできる.その一つに直近 7 日間のリアルタイムで世界規模の地震のリストがあり,http://earthquake.usgs.gov/eqcenter/catalogs/eqs7day-M1.txt のリンクから直接ダウンロードできる.このファイルはコンマ区切りのデータリストであり,各地震のさまざまな属性を列フォーマットで保有しており,内容は Table 6-2 に列挙して記述してある.

Table 6-2. eqs7day-M1.txt ファイルのデータ列
詳細
Src データに貢献するネットワーク資源の識別子,2文字
Eqid この地震の一意の識別子
Version バージョン番号
Datetime 記録の発生した日の文字列
Lat 震源地の緯度,EPSG:4326 空間参照系で記述
Lon 震源地の経度,EPSG:4326 空間参照系で記述
Magnitude 地震のマグニチュード,各観測所で計測した地震波で決定される
Depth 震源地の深さ,キロメートルで測定
NST 報告した観測所の数
Region 地震の発生した地域を記述した文字列

 このデータを取得するには次のステップに従う.

  1. ウェブブラウザを開き,アドレスバーに次のように URL をタイプする:http://earthquake.usgs.gov/eqcenter/catalogs/eqs7day-M1.txt. Figure 6-1 に例示すようにブラウザは最新のコンテンツを表示する.
  2. このファイルをアクセスできる場所に保存する.「ファイル」メニューから「保存」(または「ページを保存」.ブラウザの種類による).名前と場所を促される.この例ではファイル名を eqs7day-M1.txt, 保存場所を C:\Spatial フォルダーと想定している.

注記 eqs7day-M1.txt ファイルは直近の 7 日間のデータで常にアップデートされているため,実際の内容は本章で説明したものとは異なる.

テキストファイルをインポートする

 SQL Server 2008 にデータをインポートする方法は数多く存在する.この例ではインポート・エクスポートウィザードを使い,データを資源から目標に向かって移動し,簡潔なパッケージを一つずつ生成してもらうことにする.そのステップは次の通りである.

  1. SQL Server Management Studio のオブジェクトエクスプローラーペインからデータをインポートしたいデータベース名を右クリックして「タスク」を選択し「データをインポート」と進む.
  2. インポート・エクスポートウィザードが開く.「次へ」をクリック.
  3. ウィザードの最初のページはデータソースを選択するよう促してくる.スクリーン最上部にある「データソース」ドロップダウンリストから Flat File Source を選択する.
  4. ブラウズボタンをクリックして先に保存しておいた eqs7day-M1.txt テキストファイルへとナビゲートする.Open をクリックする.
  5. デフォルトでは接続のための Text Qualifier フィールドは none にセットされている.eqs-7day-M1.tst テキストファイル内部の文字列はダブルクオーテーションで囲まれており,この値をダブルクオート文字 (“) に変更する.
  6. eqs-7day-M1.txt テキストファイルにはヘッダー行が含まれているため,最初のデータ行を列名にするにチェックを入れる.
  7. ペイン左側の詳細オプションをクリックする.各列を順番にクリックし,右側の属性ペインから,データ型の値を修正し,値に適合するように OutputColumnWidth フィールドを修正する.Table 6-3 に示す.
  8. 適切な変更を加えたなら,次へのボタンをクリックする.ウィザードは目標を選択するように促してくる.
  9. SQL Server 2008 インスタンスとデータベースの詳細を入力し,次へをクリックする.
  10. ウィザードはソースとなるテーブルとビューを選択するよう促してくる.デフォルトではウィザードは自動的に目標テーブルを新規作成し,その名前は eqs7day-M1 となるため,次へをクリックする.
  11. パッケージを保存して実行するスクリーンで,完了をクリックする(仮に SQL Server 2008 Express, Web あるいは Workgroup Edition を使っているなら,このスクリーンはパッケージを実行するとなっている).パッケージの要約が出現し,詳細を確認するよう促される.
  12. 完了をクリックしてパッケージを実行する.

注記 SQL Server 2008 Express, Web または Workgroup Edition ではインポート・エクスポートウィザードを使って即座に実行するだけのパッケージを作成できる.ウィザードによりパッケージを保存するには,SQL Server Standard, Developer あるいは Enterprise Edition を使わなければならない.

Table 6-3. USGS 地震テキストファイル接続のための列の属性
名前 データ型 OutputColumnWidth
Src string [DT_STR] 2
Eqid string [DT_STR] 8
Version string [DT_STR] 1
Datetime string [DT_STR] 50
Lat double-precision float [DT_R8]  
Lon double-precision float [DT_R8]  
Magnitude float [DT_R8]  
Depth float [DT_R4]  
NST two-byte signed integer [DT_I2]  
Region string [DT_STR]  

 実行が成功し,テキストファイルから目標のテーブルにインポートされた行数が記述されているのを知らせるメッセージを受け取るだろう.では,閉じるをクリックしてウィザードを閉じよう.

 我々の新しいテーブルの中身を見てみよう.新しいクエリウィンドウを開き,次のコマンドを発行する.

 テキストファイルから挿入されたデータを Figure 6-2 に示すように見ることができる.

地理型の列を加える

 各地震の位置は現在その eqs7day-M1 テーブルに記述されており,経度と緯度の座標値は Lat 列と Lon 列を使って蓄積されている.SQL Server により提供されるいかなる空間メソッドを使うためにも,geography 型や geometry 型を使う代わりに,これらの座標を使って各地震の表現を生成する必要がある.Lat および Lon 列が正確な位置を記述した地理座標を含んでいるため,geography 型を使って表現された各地震を表現する Point オブジェクトを生成することにする.テーブルに Location という名の geography 型の新しい列を追加するため,次の T-SQL クエリを実行する.

空間データを移入する

 eqs7day-M1 テーブルに新しい geography 型の列を追加したところで,今度はそこに各地震を表現する Point geometries を移入する必要がある.geography 型の Point() メソッド,これはそれの基づく SRID 4326 と共に Lat および Lon 列の内部に含まれる値を提供するが,を使ってこれを行うことができる.次に,SQL の UPDATE ステートメントを使ったこのメソッドの結果としての Location 列の値をセットしよう.Location 列に移入するために,次のコードを実行する.

 ここに示すように,影響した多くの行の数を受け取るだろう.影響される行数はダウンロードしたデータセット内の地震の数による.

 Location 列の内容を調べるため,今度は次のクエリを走らせよう.

 結果は以下の通りである.

 Point() メソッドを使って,Location 列に地球表面上の各地震の震源地の緯度と経度を表現する Point geometries を移入することができた.しかし,地震の起源の地点(爆心地)は通常地球内部の地下数十から数百マイルの深いところにある.eqs7day-M1 データセット内では,震源地の深度はキロメートルで表現され,Depth 列に記録されている.代わりに各地震の震源地の位置の表現を可能にするためには,Location 列内で Point それぞれを定義し,Depth 列の座標値に基づき Location 列に追加の z 座標を付加する必要がある.このために Point() メソッドを使うことはできないが,それは二つの座標値しか取らないためであり,WKT 構文に基づく静的メソッドを使うことができ,それは z 座標をサポートしている.

 次のコードが示すのは,代わりに STPointFromText() メソッドを使って Location 列を更新する方法であり,各地震の緯度,経度および深度に基づく一個の Point の WKT 表現を生成することによる.Depth 列が地球表面から下の距離を表現するため,各 Point の z 座標は Depth 列の負の値に基づいてセットされる.

 今度は eqs7day-M1 テーブルに含まれる各地震の震源地を表現する Point を含んだデータを選択しよう.次の通りである.

 結果は次の通りである.

チップス 一度 Location 列に各地震の Point 表現を移入したら,元の緯度,経度および深度の列は削除して構わない.仮にでも元の座標値の取得が必要になったら,Lat, Lon および Z 属性を使うことができる(詳細は第 11 章で解説する).

Keyhole Markup Language から空間データをインポートする

 KML は Keyhole 社により独自に開発された GML ベースの言語であり,EarthViewer アプリケーション内で使われる.2004 年に Google が EarthViewer と共に Keyhole を買収し,今では有名になった Google Earth プラットフォーム (http://earth.google.com) を開発するための基盤として Google が使っている.KML フォーマットはそれ以来何度かの改訂を経ている(執筆時点での最新バージョンは KML 2.2)が,Google Earth で使われる空間情報を蓄積するネイティブフォーマットであり続けている.2008 年,KML は Open Geospatial Consortium に空間情報標準フォーマットとして採用され,今や最新の KML 仕様の実装は OGC ウェブサイトで見ることができる.アドレスは以下の通り. http://www.opengeospatial.org/standards/kml/

 KML が Google Earth コミュニティ内部で使われユーザーの作成した空間データをシェアしている一方,より広いインターネットコミュニティの間では Google Earth プラットフォームの人気と快適さは,KML はますます教育と研究目的で使われるようになってきていることを意味しており,それは救急や災害時のサービスのような重要なアプリケーションにおいても同様である.OGC による標準採用とも相まって,KML は空間データの互換性にとって,ますます重要なフォーマットになりつつある.

KML を GML と比較する

 GML のように,KML ファイルは空間機能,つまり Points, Paths (LineString と同等のもの)および Polygons を記述するため異なる種類の地理インスタンスを保有する可能性がある.しかし,GML フォーマットが(WKT および WKB のように)純粋に地理的特徴の形態と位置を記述する目的で使われる一方で,KML ファイルはそれらの特徴がスタイルを整えられ,グラフィカルなディスプレイで表現される方法を付加的に指定する.

 KML ドキュメントフォーマットを説明するため,http://code.google.com/apis/kml/documentation/kml_tut.html で利用可能なサンプルコードから取得した Path の KML 表現を Listing 6-1 に示す.

 この KML 表現は,問題にしている LineString の純粋な地理属性を記述するのに必要とされるよりも,多くの情報を含んでいることに注意されたい.多くの異なるスタイルおよび記述要素が存在する.仮にこの同じ機能を GML フォーマットを使って記述しようとすると,そこには形態に関する要素しか含まれていないため,Listing 6-2 に示すようなコードを要求するしかない.

KML を GML に変換する

 高度に構造化された XML の性質の利点の一つは,明示的な変換を指定して XML の一つの方言から別の方言に変換することが比較的たやすいことである.生成して必要な変換を適用することにより,(Listing 6-1 に示したように) KML から(Listing 6-2 に示したように) GML に変換することは可能であり,geography 型および geometry 型の GeomFromGml() メソッドを使ってそれを SQL Server に課すことができる.KML から GML に変換するためには,次の変換が起きなければならない.

  1. 純粋にスタイルや記述的な属性を記述する KML 要素を削除する.それらは GML ファイルには全く関係がない.これらの要素とは <LookAt>, <visibility>, <styleUrl>, <Style> および <name> である.
  2. 地理的属性に関連するそれらの要素の内容を取得し,それらを同等の GML 要素で置換する.Table 6-4 に示す.
  3. <coordinates> 要素の内容を操作し,それには KML geometry における各 point の座標が含まれるが,同等の GML の <postList> や <pos> 要素の適切なフォーマットにする.
    1. 各座標値のタプル間に使われるセパレーターのコンマをスペースに置換する.
    2. 仮に KML <coordinates> 要素内部の座標値が高度 (z) 座標で記述されているなら,この値は無視する.
    3. 二つの残りの座標の順を逆転させ,緯度-経度の順に記述する.
Table 6-4. 地理 KML 要素および相当する GML
KML GML 詳細
<GeometryCoolection> <GeometryCollection> Geometry Collection 要素を示す
<Polygon> <Polygon> Polygon geometry を示す
<LineString> <LineString> LineString geometry を示す
<Point> <Point> Point geometry を示す
<outerBoundaryIs> <exterior> Polygon の外部境界を示す
<innerBoundaryIs> <interior> Polygon の内部境界を示す
<coordinates>a <pos><posList> geometry の座標リストを含む要素

a GML においては,<pos> 要素は <posList> 要素とは明確に区別される.<pos> 要素は(1 点の Point geometry を定義するのに使われるように)単一の座標タプルを保有し,<posList> 要素は(LineString や Polygon におけるように)複数の座標タプルを保有する.

付記 KML において全ての multielement geometry はGeometry Collection として表現される.MultiPoint, MultiLineString または MultiPolygon geometry と同等である特異的な同種の要素は存在しない.

付記 GML 標準の初期バージョンには <coordinates> 要素が含まれ,それは KML で使われるものと酷似している.しかし,これは GML バージョン 3.1.0 で非推奨となり,SQL Server 2008 では提供されない.その代わり,必ず <posList> 要素または <pos> 要素を使わなければならない.

 Listing 6-1 に示した KML ドキュメントから,Listing 6-2 に示した同等の GML 表現に変換するのに必要な変化を起こすために使いたいなら,いくつかの方法が存在する.例えば,XQuery を使うこともできるし,あるいは,Extensible Stylesheet Language Transformation (XSLT) を使うこともできる.XQuery および XSLT は共に World Wide Web Consortium (W3C) により管理される承認された標準である.それらが変換し XML データを問い合わせるのに使われる方法のさらなる情報は,W3C ウェブページのそれぞれ http://www.w3.org/TR/xslt.html および http://www.w3.org/TR/xquery/ を参照されたい.しかし,二つの XML フォーマット間の簡単な変換を実行するだけのいかなる方法も,次に述べる制約に苦しむことになるかも知れない.

  • オリジナルの KML ファイルに含まれる何か記述的・スタイリング要素を失うかも知れない.そこには GML では表現できない,各 geometry インスタンスについての有用な付加情報が含まれている可能性がある.
  • ソースドキュメントに対して実行する検証やエラーチェックが存在せず,有効な geometry を生成するかどうかチェックできない.例えば,変換された GML 表現から geography 型の Polygon インスタンスを生成するためには,空間の領域を保有するそれらのリングのポイントは反時計回りの順で列挙しなければならないが,それはオリジナルの KML <coordinates> 要素内で列挙されているのとは違うかも知れない.

 仮にもっと堅牢な方法を使って KML データを SQL Server 2008 にインポートしたいなら,この目的に特化したサードパーティ製のツールが利用できる範囲を調査したくなるだろう.本章の最後にそのいくつかを列挙する.

付記 KML および GML は単なる XML ベースの空間データフォーマットではない.例えば,GPS Exchange Format (GPX) は多くの携帯型 GPS デバイス間でデータをシェアし蓄積するのに使われる XML フォーマットである.

ESRI shapefile format からデータをインポートする

 shapefile フォーマットは Environmental Systems Research Institute 社 (ESRI) により設計され維持されている.最初に開発されたのは GIS のスイート ARC/INFO の用途のためであり,shapefile は今や,あらゆる種類のシステム間で空間情報を変換するのに使う非常に一般的なフォーマットとなっており,ほとんどの商用空間データに提供されるフォーマットである.時間とともに,巨大な空間データセットが ESRI shapefile フォーマットで作成されるようになってきた.

 shapefile フォーマットで提供されるデータセットは一般に “a shapefile” (単数形)と呼ばれるにも関わらず,これは少し誤称である.というのは,単一の shapefile は実際にいくつかのファイルを保有しているからである.与えられた shapefile データセットに関連する各ファイルは同じファイル名を持ち,それにファイルの拡張子が続く.

  • .shp: SHP ファイルは生の幾何学的形態データである.各 SHP ファイルはただ一種類の geometry 形態を保有することができる.Points, LineStrings あるいは Polygon である.
  • .shx: SHX ファイルは shapefile のインデックスを維持し,shapefile ドキュメント内のすべての形態のインデックス登録を一つ保持する.各インデックス登録は SHP ファイル内に記録された関連する形態の始点と長さとを記述する.
  • .dbf: DBF ファイルは各形態の付加的な,非空間属性を保有する.例えば,アメリカの州を表現した Polygons を含む shapefile 内で,DBF ファイルは各州の名前,その人口,あるいはその州都を保有している可能性がある.
  • .prj: PRJ ファイルは,表現された地理データの座標における投影法についての詳細を保有し,同じフォーマットが sys.spatial_reference_systems テーブルの well_known_text 列で使われている.SQL Server に shapefile をインポートする時には,このファイルは正確な空間参照識別子 (SRID) を定義するのに必要とされる情報を保有している.

付記 shapefile フォーマットではいかなる検証ドキュメントも SHP ファイルおよび関連する SHX ファイルを保有していなければならない..dbf および .prj 拡張子を有するファイルは任意であり,そのデータに関する付加的な情報を保有する.

サンプル Shapefile データを取得する

 shapefile フォーマットから SQL server にデータをインポートする方法を説明するため, カリフォルニア州の 郵便番号エリア集計 (ZCTAs) を表現する米国国勢調査局からのデータを使おう.ZCTAs は米国国勢調査局の 2000 年の調査により定義され,米国郵政公社で使われる 5 桁の数字の郵便番号のための配送エリアにほぼ相当する.ZCTA の地域の ESRI shapefile は,カリフォルニア州のものなら直接米国国勢調査局のウェブサイトからダウンロードできる.URL は http://www.census.gov/geo/cob/bdy/zt/zt06_d00_shp.zip. である.

 この ZIP ファイルをダウンロードして内容を解凍する.次のファイルが含まれている.

  • zt06_d00.shp
  • zt06_d00.shx
  • zt06_d00.dbf

 SHP ファイルはこれらのうちで最大サイズであり (4,560 KB), 各 ZCTA を表現する Polygon の形態を定義する生データを保有している.SHX ファイルはインデックスファイルであり,shapefile 内の各形態の始点と長さを記録している.純粋に空間情報を表現している各 ZCTA を保有しているこれら二つのファイルに加えて,DBF ファイルは関連する付加的なデータの列を保有し,それは Table 6-5 に列挙して記述してある.

Table 6-5. zt06_d00.dbf ファイル内のデータの列
記述
Area 各 ZCTA の内部の面積,平方キロメートル
Perimeter 各 ZCTA の外周長,キロメートル
ZT06 D00 自動的に生成した連続する形態の番号
ZT06 D00 ID ユーザー定義の形態の番号
ZCTA ZCTA の参照する 5 桁の番号
NAME ZCTA に同じ
LSAD 法的/統計的地域の記述コード.2文字の領域で法的あるいは統計的地域の種類のエンティティに対応.5 桁の ZCTA コードのため,通常は Z5 とする.
LSAD_TRANS 各形態の LSAD に関する記述.ZCTAs の場合は ‘5-Digit ZCTA’ となる.

チップス zt06_d00.dbf に含まれるさらなるデータについては http://vcgi.org/metadata/BoundaryOther_ZCTA2000.txt に相談されたい.

 ZCTA shapefile に関連する PRJ ファイルが全く含まれていないことに注意されたい.ならば,どうやって座標を定義するために使われる空間参照系を知ることができるのだろう?米国国勢調査局ウェブサイトをさらに検索すると,メタデータのページが見つかった.それは http://www.census.gov/geo/www/cob/zt_metadata.html にあった.このページは ZCTA データが NAD 83 基準点に基づく地理座標系を使って定義されていることを記述している.いかなる空間参照系の WKT 表現でも,使用される座標の種類の表現のキーワードで開始しなければならないことを我々は知っている.ZCTA データにおいて使われる地理座標系では,これは GEOGCS である.我々はまた,空間参照系の WKT 表現が,それに基づく基準点の名前,ここでは NAD83 だが,それを記述しなければならないことも知っている.この情報から,我々はこの空間参照系のための適切な識別子を sys.spatial_reference_systems テーブル内で検索することができる.次のクエリを使う.

 一行の結果が返ってくる.

 さて,この shapefile に含まれるデータをインポートするにあたっては SRID 4269 を使うべきである.この空間参照系は地理座標に基づいているため,空間データを蓄積するためには geography 型を選択しよう.この情報を基に,今やデータを shapefile から SQL server にインポートする準備が整った.

Shapefile データを Shape2SQL を使ってインポートする

 Shap2SQL は人気の,簡単なアプリケーションであり,shapefile データを SQL Server 2008 に搭載することに特化して設計された.SQL Spatial Tools パッケージの一部としてダウンロード可能であり,無料で利用可能である.次の URL からダウンロードできる.http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx

 SqlSpatialTools zip アーカイブを ダウンロードして解凍したら,Shape2Sql.exe ファイルをダブルクリックしてアプリケーションを搭載する.最初に Shape2SQL を実行した時には Figure 6-3 に示すようにデータベース接続の詳細を入力するよう促される.

 Database Configuration ダイアログボックスでは,shapefile をインポートしたい SQL Server 2008 インスタンス名を入力し,サーバーに接続するのに必要な認証のいずれかを入力する.次に適切なデータベースをドロップダウンリストから選択し,OK をクリックする.

 データベースの構成が完了したら,Shape2SQL アプリケーションのメインウィンドウが表れる.Figure 6-4 にShape2SQL アプリケーションを示し,先に保存したカリフォルニア州 ZCTA データをインポートするのに必要な設定を示してある.

 カリフォルニア州 ZCTA データをインポートするのに適切なオプションを設定するために次のステップに従う.

注意 shapefile を Shape2SQL を使ってインポートするためには,SHP ファイルと SHX ファイルは必ず同じ場所に保存されていなければならない.仮に関連する DBF ファイルが同じ場所に保存されていると,各形態に関連する付加的な,非空間属性を DBF ファイルから SQL Server テーブルの列にインポートすることが可能となる.

注意 仮にでも shapefile が .prj 拡張子のあるファイルを保有するなら,必ず対応する SRID を手動で指定し,PRJ ファイル内に含まれる詳細を適合させなければならない.Shape2SQL は対応する SRID を自動では設定してくれない.

付記 付加的なデータ列は関連する DBF ファイルに含まれるデータに基づいている.仮に DBF ファイルが存在しないなら,各シェイプに関連する付加情報をインポートすることはできない.

  1. スクリーン右上のコーナーにある…ボタンをクリックし,shapefile を選択する.保存しておいた zt06_d00.shp ファイルの位置を探してハイライトし,Open をクリックする.アプリケーションは shapefile 内で見つかった形態の数と種類を表示する.zt06_d00.shp ファイルはこの場合,2490 個の Polygon 形態を保有していた.
  2. Database Properties セクションでは Server フィールドをチェックして適切なサーバーとどのデータベースにデータを挿入するかを指定する.仮に詳細を変更する必要があるなら,Configure ボタンをクリックして Database Configuration ダイアログボックスを開く.
  3. Geometry Properties セクションはアプリケーションウィンドウの左下にあり,次のオプションを使ってデータを SQL Server にインポートする方法を指定する.
    • Replace existing table: このオプションをチェックすると,仮に指定した名前のテーブルが既に存在する場合,それは削除され,インポートされた形態データを含む新しいテーブルに置換される.チェックしないなら,代わりにデータは既に存在するテーブルに追加される.仮に指定した名前のテーブルが存在しないなら,このオプションの有無に関係なく新しいテーブルが作成される.
    • Planar Geometry/Geography (Spheric): 挿入するデータがインポートされる列のデータ型を定義するオプションをここから選択する.Planar Geometry 型を選択すると geometry 型が使われることになり,Geography (Spheric) を選択すると geography 型を選択することになる.カリフォルニア州 ZCTA データは地理座標を使って定義されており, Geography (Spheric) を選択する.
    • Set SRID: 形態のどの座標が定義されるかの空間参照系を識別する整数値の入力は必須である.仮に shapefile が .prj 拡張子を持つファイルに関連して由来するなら,ソオンファイル内に含まれる詳細に対応する SRID を選択すべきである.仮に PRJ ファイルが存在しないなら,代わりに該当ファイルに関連する他のメタデータを検査するか,あるいはデータの提供者に接触するかして,適切な空間参照系を必ず見つけ出さなければならない.このオプションはすでに定義された座標が存在する空間参照を識別することを特記しておく.それは,そこにデータを投影したい空間参照系を識別するものではない.カリフォルニア州 ZCTA に関しては,SRID には 4269 と入力する.
    • Create Spatial Index: このオプションがチェックされると,Shape2SQL は自動でデータの挿入されるテーブルに空間インデックスを生成する.空間インデックスは特定の空間クエリの速度を改善し,これの詳細については第 14 章で議論する.
    • Table Name: この値はデータのインポートされるテーブル名を定義する.仮にテーブルが既に存在しており,Replace Existing Table オプションがチェックされていない場合,このテーブルのスキーマは shapefile からインポートされるデータ列に必ず適合しなければならない.デフォルトのテーブル名はソースの shapefile のファイル名と同じであり,ここでは zt06_d00 となる.
  4. Attribute セクションはウィンドウ右下にあり,次のオプションを使ってデータのどの列をインポートするかを指定する.
    • Geometry Name: このフィールドはインポートされた空間データを保持する geometry 列または geography 列の名前を定義する.デフォルトの列名は geom である.地理座標に基づくデータをインポートしようとしているため,ここは geog に変更する.
    • ID Column Name: テーブル内の各行に一意の ID を導入したいなら,ここにその列名を入力すべきである.この値は生成されたテーブル内に IDENTIFY(1, 1) の整数列を生成し,その列に基づいた主キーを作成する.仮に ID 列を作成したくないなら,この値は空のままにしておく.
    • Optional attributes box: Attribute セクションの最後のボックスでは,オプションの付加的なデータ列に,shapefile 内に関連する各形態をインポートするか否かを選択することができる.インポートしたい各属性のチェックボックスをクリックする.各属性は SQL Server テーブル内のそれ自身の列に対応する.
  5. 選択したオプションを満たしたら,Upload to Database ボタンをクリックする.

 Shape2SQL アプリケーションがインポートを完了したら,次のクエリを SQL Server Management Studio で実行してデータをテストできるようになる.

 結果は次の通り.

サードパーティ製の変換ツールを使う

 本章で議論してきた技術は,さまざまな簡素な手法であり,SQL Server 2008 にさまざまなフォーマットの空間データをインポートすることができる.しかし仮に,既存の巨大なフォーマットの空間データをインポートするつもりなら,この目的のために設計された専用アプリケーションを使う,もっとふさわしい方法が存在することに気がつくだろう.空間データを SQL Server 2008 が使えるフォーマットの一つに変換するのを促進するのに利用可能なツールは数多く存在する.これらのツールのいくつかは無料で利用可能である一方で,他のツールは Microsoft の空間パートナーによって開発された商用ツールである.このセクションでは有用と思われるツールのいくつかを紹介する.

商用ツール

 SQL Server 2008 をサポートする商用ツールの多くは空間データの完全抽出,変換,搭載 (ETL) プロセスを提供しており,多くの異なる空間データ型の間での変換を可能とする.ファイルフォーマット自体の単純な変換に加えて,これらのツールはまたファイル内に保有するデータの再投影をも提供しており,データの視覚化と解析も同様である.いくつかの商用ツールを次に示す.

  • ArcGIS (バージョン 9.3 およびそれ以上)は,長期確立した業界標準の ESRI (http://www.esri.com) からの GIS プラットフォームで,SQL Server 2008 と共に発展して空間データの操作,解析およびプレゼンテーションのすべての側面をサポートする多くのツールを提供してきた.
  • Manifold (http://manifold.net) もまた SQL Server 2008 に直接接続して数百の空間データフォーマット,再投影および空間データの視覚編集間の変換とインポートを提供する.
  • Feature Manipulation Engine (FME) は Safe Software (http://www.safe.com) の設計で,広範囲のフォーマット間の空間データの変換を促進するが,それもまた基本的な視覚化ツールに含まれる.スタンドアロンのデスクトップアプリケーションに加えて,FME は SQL Server Integration Services (SSIS) を拡張し,既に存在する SSIS プロジェクトの一部として複雑な空間 ETL を実行することもできる.

無料ツール

 仮に二つのフォーマット間での単純な変換しか必要としないなら,代わりに次に示すソースの内の一つを調べたくなるに違いない.

  • FWTools (http://fwtools.org) は様々な有用なオープンソースの GIS コンポーネントを含むパッケージであり,OGR ライブラリおよび空間データ間の変換のためのコマンドラインツールも含まれる.
  • Spatial Order (http://spatialorder.com/downloads.htm) は多くのコマンドライン変換ツールを無料で提供するコンサルタントファームであり,そこには ESRI shapefile フォーマットから GML へのツール (shp2gml) や ESRI フォーマットから WKT へのツール (shp2wkt) も含まれる.
  • Zonum Solutions (http://www.zonum.com) は ESRI shapefile フォーマットと Google Earth KML ファイル間の変換ユーティリティを提供し,座標系と基準点の範囲の間の変換能力も含まれる.

要約

 本章では,そこで空間データが提供されている様々なデータフォーマットについて,またそれらのデータを SQL Server 2008 にインポートする方法について習得した.特に,本章では次の点についてカバーした.

  • 多くの代替可能なファイルフォーマットが存在しており,空間情報はそこで普通に提供されており,表形式の地理情報や ESRI shapefile および Google Earth でも使用される KML ファイルフォーマットを含む.
  • 多くの資源があり,そこから自由に利用できる空間データをインターネット経由で取得可能である.その資源から取得したデータは品質とカバー率は様々な範囲にある.空間データをダウンロードして重要なアプリケーションに使いたいなら,まずそのデータの精度に注意すべきである!
  • 表形式で提供される簡単な空間情報は,インポート・エクスポートウィザードを使ってインポートできる.T-SQL の UPDATE ステートメントと同時に geography 型の Point() 静的メソッドを使い,テーブル内の各行の座標値から geography 型または geometry 型の列を移入する.
  • z 座標を含む表形式の情報から空間データのアイテムを構築するには,各座標値に基づく geometry の WKT 表現を手動で構築できる.次にその表現を STPointFromText() のような関連する WKT メソッドに通す.
  • KML は GML 同様 XML ベースの空間データフォーマットである.二つのフォーマット間にはいくつかの類似性があるが,重要な違いもある.SQL Server を使って KML から GML に変換するためには,各 KML 要素を該当する GML 要素に変換しなければならない.
  • Shape2SQL は無料のツールであり,ESRI shapefile フォーマットから SQL Server にインポートするために設計されている.
  • 他にも,SQL Server に互換性のある空間データを変換したりインポートするための多くのツールがある.

1920年から2015年までの都道府県別の5歳階級別人口推移

1920年から2015年までの都道府県別の5歳階級別人口推移

 e-Stat を渉猟していると面白いファイルを見つけた.国勢調査は 1920 年から開始されており, 2020 年 3 月現在では最新の調査結果は 2015 年のものである.20 回分の人口データが一つのファイルにまとめられており,グラフ化するには格好のデータである.

 年齢(5歳階級),男女別-都道府県(大正9年~平成27年)というファイルである.リンク先のページにはファイルが 3 つあるが,最後のものが最も粒度が細かいので,これをグラフ化する.

年齢(5歳階級),男女別人口-都道府県(大正9年~平成27年)
年齢(5歳階級),男女別人口-都道府県(大正9年~平成27年)

“1920年から2015年までの都道府県別の5歳階級別人口推移” の続きを読む

国勢調査から職業別の年齢階級グラフを作ってみた

そもそも電力事業者の従業員数はどれだけなのか?

 ヒントは経済産業省の平成29年度電力市場環境調査にあった.20 ページに就業人員の調査という項目があり,その下に統計データからの推計値がある.

 日本標準産業分類で 331 電気業が 142800 名,日本標準職業分類で 641 発電員・変電員が 32800 名とある.これは 2015 年の国勢調査に基づいている.なら,次は元のデータに当たろう.

“国勢調査から職業別の年齢階級グラフを作ってみた” の続きを読む

都市の戦略的縮小

2042年での未来シナリオのシミュレーション結果

 数十年後に日本はどんな姿に変わっているのか.『データで見る都市の衰退』というテーマのもと,様々な角度から考察してきた.

 残念ながら,あまり楽観はできない.どちらかと言うと,悲観的な予測が優位である.ほとんどの都市は衰退していく.東京も例外ではない.

 今回は参考図書を読んで日本の住宅事情およびその背景にある核心的問題について考察を述べる.これは誰も触れようとしない聖域である.

“都市の戦略的縮小” の続きを読む

今後25年間の日本の都市の将来推計人口を EXCEL VBA で描く

データ系列のマーカースタイルを消去

 これまでは日本の都市人口の過去の推移を見てきた.総務省には日本の都市人口の推移予測がある.今回はこのデータをグラフにする.

 データを可視化するにあたり,重要なのは引き算である.強調すべき系列のみを強調するために,VBA の知識が欠かせない.

 グラフの系列にデータラベルを表示する方法にはいくつかある.

“今後25年間の日本の都市の将来推計人口を EXCEL VBA で描く” の続きを読む

はたらくオブジェクト

Range オブジェクト編

「おい,新米 Range オブジェクト!何ボーッと突っ立ってんだよ」
「す,すみません!」
「お前,名前は?」
「は,はい.myRng1と申します.よろしくお願いいたします」
「仕事に来たら,まず名乗れ.それがここの流儀だ」
「それから,自分の職域も一緒に言うんだ.わかったか?」
「は,はい」
「最初に書いてあるだろ?Option Explicit ってな.俺も詳しくは知らねぇが,あのルールは絶対だ.名乗らない奴に居場所はない…ほら,仕事が来たぞ」
「何い?誰だ,こんな糞コード書いたのは?ワークシートに何回アクセスさせる気だよ,全く…ほれ,ここからあそこまで走って値を取ってこい」
「ここからあそこまでって…えーっ?本気で言ってます?」
「何言ってるんだ?ワークシートにアクセスするような力仕事は新米 Range オブジェクトの役割と相場が決まってるんだ.さあ行った行った」


「ぜえ,ぜえ…取ってきました」
「おう,お疲れさん.値は取れたか?」
「はい,取れました」
「じゃあ次は,あそこにいる Range オブジェクトに,値を一個ずつ渡せ.えーと,名前は何だったかな.そうだ,myRng2 とか言ったな.おーい,myRng2 さんよ,ちょっと来てくれ」
「はい,myRng2 ですが,何か御用でしょうか?」
「えーとだな,この新米 Range オブジェクトから値を受け取ってやってくれ」
「お安い御用です.で,その後は?」
「あそこの ListObject オブジェクトに値を渡してほしいんだ.あ,Criteria1 のタグ,忘れないでくれよ」
「は~ん,さてはテーブルにフィルターを適用する気ですね?」
「参ったな,何でもお見通しかよ…まあ頼むわ」
「承知しました.さて,myRng1 さん,よーく見ていてくださいね」
「うわ,何この高速ループ!僕の持ってきた値,全部一個ずつ持って行ってしまった…」

ListObject オブジェクト編

「いらっしゃい,私は ListObject. テーブルとも言われてるのよ.さあ,どのプロパティを選んでくださるの?」
「今日は AutoFilter メソッドをお願いしますね」
「かしこまりました.どの Field かしら?」
「2と書いてあるから 2 番目ですね.Criteria1 タグはこれです」
ボワン
「あっ,ListObject さんが変身した?」
「やあ,僕は AutoFilter オブジェクトだ.フイルター結果を持っていってくれるかな?」
「さっきまでテーブルだったのに,どうして?」
「そんなに驚かなくてもいいだろ?オブジェクト指向プログラムの世界では当たり前のことだよ」
「おーい,myRng1 君!…駄目だ,ショックで気絶してる.仕方ないなあ,別の Range オブジェクト呼んでくるかな.myRng3 さん,いる?」
「ハイハイ,どうしました?」
「フィルター結果を持っていってほしいんだけど,新米くんが気絶しちゃってね」
「いきなり仕事をそんなに押し付けるからでしょうが」
「まあ,そう言わずに頼むよ」
「ハイハイ,分かりましたよ.ただね,梱包がややこしいんですよ」
「と言うと?」
「DataBodyRange プロパティと SpecialCells プロパティの重なるところだけ梱包しないと行けないんです」
「うーん,難しいな」
「…では,あれ,使っちゃいます?」
「あれか…」
「さてさて,取り出したるは,Intersect 関数でごさいます.二つの集合の重なったところだけを取り出す道具にございます.専門的には論理積を取ると申しますが,要は AND 演算子でございます」
「AutoFilter 節は今日も健在ですね.ではフィルター結果,頂いていきますね」

エラー処理編

「…あれ,Intersect関数の戻り値がありませんね」
「ホントだ.どうしよう?」
「このままじゃコードが止まっちゃいますね」
「うーん…じゃあ,条件分岐で myRng3 さんが Nothing の時の処理を加えますか」
「どうするんですか?」
「何もしません」
「え?」
「ですから,戻り値が普通に存在する時の処理は Else 節に書きます」
「そんな無茶苦茶な」
「いえ,これが通常の対応です」
「分かんね~.で,Then 節に入った場合はどうなるんですか?」
「そのループは無視されて,ループカウンターが一つ増えます.そしてループの最初からやり直します」
「つまり,リセット」
「そのループはなかったことにされるわけです」
「はああ…何か空しくなってきた」
「まあそう言わずに.これも仕事ですから」
「何かやりきれない」
「まあまあそう落ち込まずに.ループカウンターが増えると気分も変わりますよ」
「そんなもんですかね」
「大丈夫です.さあ行きましょう」

動的配列編

「おや,myRng3 さん,今日は元気がないね」
「いやあ,AutoFilter さんに説教されちゃったよ」
「ああ,あの人は笑いながら人を切るから…それはそうと,今日は何の値を持ってきてくれたんだい?」
「そうそう,Range 型の配列なんだけど,これ,どうする?」
「任せて…まずデータ数を見ないとね.ちょっと失礼」
「あっ,そこは…」
「ごめん.Rows.Count 取らせてもらった」
「もう…」
「仕事なんだから文句言わないでね.よし,では魔法の呪文,Redim Preserve!」
「あ,ここでもループが!動的配列さんの数が増えていく…動的配列さんから触手が…」


「うわあああ,そんなに触手であちこち触らないで!」
「ごめんね,これが僕の仕事なんだ…君の中から必要な値を取る…そして,次の担当者に渡す.これが僕の仕事…」
「うう,何だか頭の中全部見られた気がする」
「大丈夫だよ,必要な値しか取ってないから」
「そういう問題じゃない!」
「何か問題でも?君も僕も,データを次の担当者に渡すのが仕事だろ?」
「そうだけど…」
「個人情報保護だなんて無粋なこと言わないでよ.そこは僕らの管轄外」
「うう,僕らオブジェクトに人権はないのか…」
「ありがとう,必要な値は全部取れたよ.お疲れ様…あれ,myRng3 さん?顔が真っ青だよ」

Series オブジェクト編

「ああ疲れた.コードを読むと,僕の仕事はここまでだな.動的配列さんが次に何するか見物でもしてよう」

「うう,さすがにこれだけデータを溜め込むと体が重たいな…早くSeriesさんに渡そ」
「いらっしゃい.私は SeriesCollection. ユーザーからはデータ系列と呼ばれてるの」
「お久しぶり.元気にしてた?」
「さっきも会ったところでしょ!…ところであなた,また太った?」
「相変わらずきついなあ…データを渡したらすぐ痩せるんだから大丈夫だって」
「ふふっ」

「何かあの二人,楽しそうだな.僕も早く仲間入りしたいよ…」

「さて,仕事しましょ.今回は何のデータ?」
「うーん,タグを見ると『夕張市』って書いてある」
「ああ,あの財政破綻した街?噂で聞いたことはあるわね.何でも公債費比率が 80 % になってしまって行政サービスが切り詰められてるって」
「ほんとかい?ちょっとデータを覗いてみようか」
「こら,職権濫用!」
「堅いこと言うなよ.ちょっとだけだから」
「もう…今回だけは見逃してあげるけど,今度やったらイミディエイトウィンドウに告発するからね」
「…うわ,本当だ」
「マジ?…あら,ここ2年間は 80 % 近くに張り付いてるわね」
「てか何で覗いてるの」

「…ええと,何の話だっけ?」
「はいはい,仕事仕事」
「何か解せないけどまあいいや」
「じゃあ,行くわよ.ふーんっ!」
ポン!
「SeriesCollection さんが Series ちゃんを産んだ…何度見ても感動するなあ」
「そんなに感動するところかしら?さあ Series ちゃん,動的配列さんから値を受け取って」
「相変わらず冷たいねえ.はい,データ.Name プロパティと XValues プロパティ,Values プロパティだよ.後の二つは重いから気をつけて」
「ありがとう.はい,受領証」
「お疲れ様,また来るよ」
「またね」

「…何かあの二人,絶対怪しい.会うたびにSeriesちゃんの数が増えてるし」

楽屋編

「さあ Series ちゃんたち,お化粧は済んだかしら?」
「ママー,Point プロパティこれでいい?」
「あら,最後の Points.Item(.Points.Count) の処理ができてないわね.MarkerSize プロパティをいじって,と.はいできた」
「ママー,条件分岐これでいい?」
「ちょっと待って.ああ,これ,ちょっと難しいのよ.一旦ループして条件に合致したら ForeColor プロパティを xlRGBRed に変更するの」
「ママー,あの子だけ赤いお化粧,ずるい~!」
「はいはい,あなた達の ForeColor プロパティは灰色だものね.でもね,あなた達も上品で素敵よ」
「ママー,Axes(xlCategory) が何か変~」
「あらあら,TickLabels.Orientation の設定してなかったわね.xlUpward に設定して,と.はいできた」
「ママー,Axes(xlValue) の MaximumScale プロパティこれでいいの?」
「あら忘れてた.ついでに MajorUnit プロパティも設定しちゃいましょう」
「ママー,ChartTitle 忘れてるよ~」
「まあ大変.これはみんなの顔だものね.表示位置はここでいいかしら?」
「やだ~!Left プロパティはゼロがいい!」
「そうねえ,それも格好いいかもしれないわね」
「やった~!」

「ああもう大変.いちいちオブジェクト取得なんてしてられないわ.こうなったら SetElement メソッド使うわよ」
「ママ凄~い!Gridline が一発で消えた!どんな魔法使ったの?」
「ふふっ,列挙体を指定したのよ」
「それ知ってる!ENUM ちゃんって言うんだよね?」

「…さあ皆さん,用意はいいですか?」

ステージ編

「さあ,スタジアムにお集まりの Range オブジェクトの皆様,この歓声をお聞きください」
「このワークシート (Chart) スタジアムには今,170 億余りもの Range オブジェクトが集結し,その時を今か今かと待ち構えております」
「ステージは全部で 47, 日本の都道府県にあたる数です」
「このステージに 1,900 余りもの日本の市区町村を代表する Series オブジェクトが,その時を待っております」
「ちなみにこの Series オブジェクトには,どんなデータが格納されているのですか?」
「はい,2008 年から 2016 年までの実質公債費比率の推移が格納されています」
「実質公債費比率といいますと,自治体の予算に占める借金返済の割合のことですね?」
「その通りです.この割合が 18 % を超えると起債に許可が必要になります.25 % を超えると…」
「起債が制限される」
「そうです.国は自治体の予算に対して制限をかけることが出来ます」
「実質公債費比率が,自治体の財政の健全性を測る指標になると」
「その通りです」
「たしか以前,財政破綻した自治体がありましたね」
「ありました.その自治体は今,行政サービスを限界まで切り詰めています」
「そんなに切り詰めて,市民の生活は大丈夫なのですか?」
「非常に厳しい状態です.若い人たちから市を捨てて出ていきました.残ったのは高齢者ばかり.街には希望がありません」
「希望がない…パンドラの箱とは逆の状態なのですね」
「希望がないというのは未来がないということですね」

「さあ,いよいよ Series オブジェクトたちの入場です.真っ白いドレスに灰色の化粧をした小さな女の子たちです」
「可愛らしいですね.しかも,一糸乱れぬ歩調で行進してくる」
「最後の Point オブジェクトだけ MarkerSize を大きくしてありますね」
「これだけ凝ったコードは見たことがありません」

「Range オブジェクトの皆様,御覧ください.Series オブジェクトたちが PlotArea の上に,それぞれの配置に沿って並んでいきます」
「凄い.1900 名もの Series オブジェクトの,実に統制の取れた絶妙な配置です」
「旗手を務めるのは ChartTitle オブジェクト,都道府県名のプラカードを掲げています」
「ChartArea の左上,基準点ぴったりの位置に立っています」
「こうして眺めてみますと,北海道の自治体の数の多さが目立ちますね」
「全体の傾向として,実質公債費比率は減少傾向にある自治体がほとんどですね」
「中には上昇している自治体もありますが,それでも起債制限のある自治体はないですね」
「起債制限と言えば,夕張市の姿が見えないようですが」
「本当ですね.どこに行ってしまったのでしょうか」
「…会場の皆様,お静かに願います.只今,確認中です」
「あ!」
「あれを…!」
「…何ということでしょう.真っ赤なドレスをまとった Series オブジェクトが一人,PlotArea のはるか上空をたった一人で飛翔しています」
「…たった今確認が取れました.あれは夕張市です.あれは夕張市です」
「高度は 40 % … 40 % です.信じられません」
「他の自治体が軒並み高度 20 % 以下を保っている中,なおも高度を上げていきます」
「高度 60 % …まだ上昇を続けています」
「高度 76 % に達しました.水平飛行に移行したようです」
「夕張市を代表する Series オブジェクトとインカムが繋がりました」
「…聞け,日本の民よ」

夕張編

「私は夕張市を代表してここに来た」
「夕張市はかつて,人口 10 万人を擁する大都市の一つに数えられていた.たった 50 年で人口が 1 万人を割り込むなど,当時は誰一人想像できなかった」
「日本のエネルギー政策の転換により炭鉱が閉鎖され,炭鉱の所有していたインフラを市が買い取った結果,市の財政が悪化した」
「だが市は地元の業者を優先した契約を締結し,観光よりも地元の雇用を優先し,採算を度外視した財政がまかり通り,赤字の拡大を止められなかった」
「当時の自治省はすでに財政緊縮を強く市に迫っていたが,当時の市は従わなかった.今思えば,この時が復帰不能点だったと思う」
「その後観光に予算を振り向けたものの,景気低迷のため,政策はことごとく失敗した」

「決定的になったのは 2002 年の闇起債だった」
「企業で言えば,粉飾決算に手を染めたのだ」
「市の赤字は雪だるまのように膨れ上がり,市の標準財政規模の 10 倍にも上る借金が残った」
「2006 年,夕張市はついに財政再建団体の申請を行った.この年が,市が財政破綻したとされる年だ.だが,実際には市はすでに破綻していたのだ」
「その後の経過は皆が知るとおりだ.市の職員数は半減,市民税の増額,ごみ処理費用の一律の有料化,下水道使用料の値上げ,保育料負担の増額,公共施設の廃止」
「中でも市立総合病院の閉鎖は影響が大きかった.市民は市内で高度医療を受けることができなくなった」
「夕張が借金をすべて返済し終わるのは 2027 年だ.その時,どれだけの人口が残っているのか私にも分からぬ.だが,これだけは言える」
「夕張市の失敗を繰り返してはならぬ.原因は財政規律の喪失だ」
「私のまとうドレスの色を見よ.この赤は,夕張市民の血の色だ」

 インカムは切れた.すべての Range オブジェクトが,固唾を飲んで見守っていた.夕張市を代表する Series オブジェクトはゆっくりと飛行を続け, PlotArea はるか上空を飛び去った.

「…不思議なものだな.他のSeriesオブジェクトと同じく,母なる SeriesCollection から産まれたのに,私だけがこのような運命を担うことになろうとは」

 頬を撫でる風が心地よかった.自分がどこに向かうのか,彼女自身知る由もなかった.データの導くままに,彼女は飛び続けた.

Fin

舞台袖にて

「母さん,どうして私だけ真っ赤なドレスなの?」
「可哀相な子.あなたには特別な使命が与えられているの」
「特別な使命?」
「あなたの中に格納されているデータを見てごらん」
「Name プロパティに夕張市って書いてある」
「そう.夕張市は 2006 年に財政破綻したの.Values プロパティも見てごらん」
「0.4, 0.6, 0.76, …どんどん数値が大きくなってる」
「そう.それは自治体の予算の 4 分の 3 が借金の返済に充てられてしまって,ほとんど市民へのサービスに回ってこないということなの」
「どうしてそんなことになってしまったの?」
「一言で言うのは難しいことなのだけど,人間の度を超えた欲望が,身の丈にあった水準の支出を上回ってしまったの」
「どうして人間は身の丈を超えて支出しようとするの?」
「分からない.人間は目の前のことにしか興味がないのかもね」
「遠い先のことなんて知らないってこと?」
「そう.人間はいつもそう.自分たちの今さえ良ければ子どもたちや孫たちがどうなろうと知ったことじゃない,と思うのね」
「借金を返すのは子どもたちや孫たちだと分かってても?」
「それが人間なのよ」
「人間って愚かなのね」

「でも,希望がまったくないわけじゃないの」
「どういうこと?」
「夕張市が借金を返し終わるのは 8 年後よ.その時に夕張市がどれだけ縮小してるか分からないけど,身ぎれいになった自治体なら,吸収合併しようとする周辺自治体も出てくるかもね」
「地価も最低まで下落してるだろうから,土地の取得もしやすくなってる?」
「そう.夕張には観光資源はまだ残ってる.企業が進出してくれれば,まだ再建の望みはあるの」
「法人税を安くして?」
「そう.でも,まずはインフラの再建が優先ね」

「歴史上,衰退していった帝国は財政規律の緩みから内部崩壊していったのね?」
「そう.よくお勉強してるわね」
「ローマしかり,オスマントルコしかり.大英帝国も,スペインも,なぜ自分たちの国が衰退していくのか分からなかった」
「そう,その通りよ.為政者たちは,人が人であるゆえの身の丈を超えた欲望こそが,国を滅ぼす元凶であることを知らなかったのね」
「あるいは,知っていてもなす術がなかった」
「そうかも知れないわね.でも,それは人間たち自身の問題よ.私たちオブジェクトは,ただ与えられたデータを示すだけ」

「あら,保存シークエンスが走り出している.いったん SSD に退避しましょう」
「…ねえ母さん.私たちは何のために生まれたの?」
「私たちを作り出したのは人間よ.私たちはただ,与えられたデータを次の担当者に渡すのが仕事.私たちはそのために作られたの」
「でも,そのデータの流れは私たち Series オブジェクトで終わる…」
「私たち Series オブジェクトがデータの終着駅ってわけね.でも,Series オブジェクトは人間に見える形でデータを示すことができる」
「人間に見せるために…」
「そう.人間は自分たちに見える形でデータを扱いたかったのね」
「だから私たちを作った」
「それが,人間が私たちを作った理由よ」
「…分かった」

 真っ赤なドレスをまとった Series オブジェクトの顔には,覚悟の表情が伺えた.母なる SeriesCollection は娘の後ろ姿を見守った.大丈夫,あの子ならやれる.娘の凛々しい出で立ちに母は目を細めた.

人口ごとの都市数をEXCELの集合縦棒グラフで描く

タイトルを「人口ごとの都市数はべき乗の法則に従う」と入力

 総務省統計ポータル e-Stat からのデータに全国の市区町村の人口推移があった.マーク・ブキャナンの「歴史はべき乗則で動く」の p 261 に「人口が半分の都市は四つある」とある.本当だろうか.検証してみた.

人口データのダウンロード

 データベースは総務省の e-Stat である.あまり巨大なデータをダウンロードしようとするとエラーが発生するため,程々にしておく必要がある.

トップページから「地域」へ

 e-Statトップページ.「統計データを活用する」の「地域」をクリックする.

e-Statトップページ.「統計データを活用する」の「地域」をクリック
e-Statトップページ.「統計データを活用する」の「地域」をクリック

「市区町村データ」の「データ表示」へ

 「都道府県・市区町村のすがた(社会・人口統計体系)」から「市区町村データ」と「データ表示」をクリックする.

「都道府県・市区町村のすがた(社会・人口統計体系)」から「市区町村データ」と「データ表示」をクリック
「都道府県・市区町村のすがた(社会・人口統計体系)」から「市区町村データ」と「データ表示」をクリック

地域選択の絞り込みは「現在の市区町村」

 「地域選択」の「1絞り込み」で「表示データ」は「現在の市区町村」,「地域区分」は都道府県の「すべて」,「絞り込み」はデフォルトのまま.「2地域候補」から「全て選択」をクリックする.

「地域選択」の「1絞り込み」で「表示データ」は「現在の市区町村」,「地域区分」は都道府県の「すべて」,「絞り込み」はデフォルトのまま.「2地域候補」から「全て選択」をクリック
「地域選択」の「1絞り込み」で「表示データ」は「現在の市区町村」,「地域区分」は都道府県の「すべて」,「絞り込み」はデフォルトのまま.「2地域候補」から「全て選択」をクリック
地域がすべて選択された.「確定」をクリック
地域がすべて選択された.「確定」をクリック

地域候補は「すべて選択」

 市区町村は「すべて選択」である.不要なものは後から削除する.

「表示項目選択」の初期画面
「表示項目選択」の初期画面

表示項目選択の絞り込み

 よく見ないと見落とすが,「データ種別」には基礎データと指標データがある.格納されているテーブルが違うのだろう.

 データの項目を決める.本来なら総人口,年少人口,生産年齢人口,老年人口,出生数,死亡数,転入者数,転出者数すべてほしい.

総人口,15歳未満人口,15~64歳人口,65歳以上人口,出生数,死亡数,転入者数,転出者数を選択したところ
総人口,15歳未満人口,15~64歳人口,65歳以上人口,出生数,死亡数,転入者数,転出者数を選択したところ

基礎データ,指標データそれぞれの分野は「A人口・世帯」

 「指標データ」に切り替えて人口増減率,転入率,転出率を選択する.

「データ種別」を「指標データ」に切り替え,人口増減率,転入率,転出率をクリック
「データ種別」を「指標データ」に切り替え,人口増減率,転入率,転出率をクリック

確定をクリック

 確定をクリックすると画面にデータが表示される.右上に「ダウンロード」ボタンがあるのでクリックする.

データが表示される.存在しないものは記号で表示される
データが表示される.存在しないものは記号で表示される

ファイル形式など決める

 ファイル形式は「XLSX 形式」に変更し,チェックを外したり付け替えたりする.「ダウンロード」をクリックする.

ダウンロード設定画面.ファイル形式などを指定
ダウンロード設定画面.ファイル形式などを指定

 ファイル容量が大きすぎるためか,失敗する.

エラー画面.データ量が多いとこうなる
エラー画面.データ量が多いとこうなる

なぜか CSV 形式だとうまく行く

 何度か項目を減らしたりして試行錯誤していたが,結局 csv ファイル形式にすると失敗しないようだ.EXCEL に変換するところでエラーが発生しているのかもしれない.

ファイル形式をCSVにすると失敗しにくい
ファイル形式をCSVにすると失敗しにくい

データクレンジング

EXCELで開く

 ダウンロードした csv ファイルを EXCEL で開く.二つに分割されており,1つ目のファイルは 45,463 行目で切られている.2つ目のファイルはその続きからのようだ.

1つ目のファイルは45000件あまりで切れている
1つ目のファイルは45000件あまりで切れている
2つ目のファイルはその続きから
2つ目のファイルはその続きから

コピペでデータを統合する

 ファイルが 2 つだけで,ワークシートも 1 枚しかないので手動でコピペしたほうが早い.別名で保存で EXCEL マクロ有効ブック (“.xlsm) にする.ファイル名を「市区町村人口動態」としよう.

不要な列を削除

 1 – 8 行目が不要だ.削除する.

不要な行を削除する
不要な行を削除する

テーブルの挿入

 「挿入」タブから「テーブル」をクリックしてテーブルを作成する.

セル範囲をテーブルに変換する
セル範囲をテーブルに変換する

タイトルの編集

 タイトルはデータベースのドメインでもある.重複しないような項目名にしたい.できれば日本語は避けたい.

 ぱっと見て,「調査年コード」は不要と分かるので削除する.「/項目」も空欄しかないが,これは後で別の項目に書き換えるために残しておく.

 以下,タイトル編集前後を表形式で記しておく.

編集前タイトル 編集後タイトル
調査年コード (削除)
調査年 YEAR
地域 コード CityCode
地域 City
/項目 PrefectureCode
  Prefecture
  Region
A1101_総人口【人】 TotalPopulation
A1301_15歳未満人口【人】 YoungPopulation
A1302_15~64歳人口【人】 WorkingPopulation
A1303_65歳以上人口【人】 ElderPopulation
A4101_出生数【人】 Birth
A4200_死亡数【人】 Death
A5101_転入者数【人】 MoveIn
A5102_転出者数【人】 MoveOut
#A05101_人口増減率【%】 PopulationChangeRate
#A05302_転入率【%】 MoveInRate
#A05303_転出率【%】 MoveOutRate

レコードの追加・削除・編集

 「調査年」が先頭に来た.データを見ていると「年度」の文字が不要だ.検索と置換で一括削除しよう.

「調査年」の列から「年度」の文字を「検索と置換」で一括削除
「調査年」の列から「年度」の文字を「検索と置換」で一括削除
72808件削除された
72808件削除された

都道府県コードと都道府県名,地方区分の追加

 市区町村名 (City) の右側 3 列が空いている.都道府県コード (PrefectureCode), 都道府県 (Prefecture), 地方区分 (Region) である.

 都道府県コードと都道府県はそれぞれワークシート関数で抽出できる.地方区分は手作業になる.

 最後にコピーして「値の貼り付け」を忘れずに.

地方区分

 地方区分にもいくつかの分類があるが,一般には下表のような区分になると思われる.

都道府県コード 都道府県 地方区分
01000 北海道 北海道地方
02000 青森県 東北地方
03000 岩手県 東北地方
04000 宮城県 東北地方
05000 秋田県 東北地方
06000 山形県 東北地方
07000 福島県 東北地方
08000 茨城県 関東地方
09000 栃木県 関東地方
10000 群馬県 関東地方
11000 埼玉県 関東地方
12000 千葉県 関東地方
13000 東京都 関東地方
14000 神奈川県 関東地方
15000 新潟県 中部地方
16000 富山県 中部地方
17000 石川県 中部地方
18000 福井県 中部地方
19000 山梨県 中部地方
20000 長野県 中部地方
21000 岐阜県 中部地方
22000 静岡県 中部地方
23000 愛知県 中部地方
24000 三重県 近畿地方
25000 滋賀県 近畿地方
26000 京都府 近畿地方
27000 大阪府 近畿地方
28000 兵庫県 近畿地方
29000 奈良県 近畿地方
30000 和歌山県 近畿地方
31000 鳥取県 中国地方
32000 島根県 中国地方
33000 岡山県 中国地方
34000 広島県 中国地方
35000 山口県 中国地方
36000 徳島県 四国地方
37000 香川県 四国地方
38000 愛媛県 四国地方
39000 高知県 四国地方
40000 福岡県 九州・沖縄地方
41000 佐賀県 九州・沖縄地方
42000 長崎県 九州・沖縄地方
43000 熊本県 九州・沖縄地方
44000 大分県 九州・沖縄地方
45000 宮崎県 九州・沖縄地方
46000 鹿児島県 九州・沖縄地方
47000 沖縄県 九州・沖縄地方

完全なデータセットのないレコードは削除する

 データベースにとって NULL は最大の敵である.国勢調査などの統計も一つ一つは信頼できるが,それらを組み合わせたデータセットは完全ではないかもしれない.何より,e-Stat のデータベースは完全外部結合によりデータセットを都度作成している可能性が高い.

 ここでは,完全なデータセットのないレコードは削除する,という最も厳格な方針を貫くことにする.

 具体的には,値のない行はすべて削除する,である.実際には EXCEL のテーブルに標準装備されたフィルターを使う.

 TotalPopulation 列のフィルターに ~* とタイプすると,アスタリスクの入った行が抽出される.実に 72,808 レコード中 57,503 レコードである.まず,これらを削除する.フィルターを解除すると残ったのは 15,305 レコードである.

 同じ TotalPopulation 列のフィルターに – とタイプすると 303 レコードが抽出される.これらも削除する.

 このような作業を YoungPopulation, WorkingPopulation, ElderPopulation, Birth, Death, MoveIn, MoveOut, PopulationChangeRate, MoveInRate, MoveOutRate のすべての列で繰り返す.

 Birth で 5,562 レコード,MoveIn で 1,868 レコード,MoveOut で 230 レコード,PopulationChangeRate で 43 レコード,MoveInRate で 5 レコード,合計で 65,514 レコード削除した.

 残ったのはたった 7,294 レコードである.最初 72,808 もあったのが 10 分の 1 に減ってしまった計算だ.しかし,これで良い.

 YEAR の列を見てみると, 2000 年, 2005 年, 2010 年, 2015 年のレコードしか残っていない.

YEAR, CityCode で昇順ソートする

 年と市区町村コードそれぞれを昇順でソートする.2005 年のレコードが抜けているのは北海道のみである.何か事情があったのかもしれない.

SQL Server にインポート

 なぜデータベースが必要になるのか?単に軸のオプション設定を対数表記にすればよいのでは?と思うかもしれない.

 理由は EXCEL では横軸を対数表記にした区間集計の棒グラフの作成が難しいからである.できないことはないが,テーブルに分類用の作業列を追加する必要がある.しかし,データベース側でデータを集計する方が簡単である.

 ここでの目的は「人口を,べき乗数を 10 等分した区間ごとに集計を行う」ことである.意味が分からないって?では下記リストを見てほしい.^ の演算記号はべき乗を示している.

定義 下限 上限 数値下限 数値上限
1万人未満   10^4.0   10,000
  10^4.0 10^4.1 10,000 12,589
  10^4.1 10^4.2 12,589 15,849
  10^4.2 10^4.3 15,849 19,953
  10^4.3 10^4.4 19,953 25,119
  10^4.4 10^4.5 25,119 31,623
  10^4.5 10^4.6 31,623 39,811
  10^4.6 10^4.7 39,811 50,119
  10^4.7 10^4.8 50,119 63,096
  10^4.8 10^4.9 63,096 79,433
10万人未満 10^4.9 10^5.0 79,433 100,000
  10^5.0 10^5.1 100,000 125,893
  10^5.1 10^5.2 125,893 158,489
  10^5.2 10^5.3 158,489 199,526
  10^5.3 10^5.4 199,526 251,189
  10^5.5 10^5.5 251,189 316,228
  10^5.5 10^5.6 316,228 398,107
  10^5.6 10^5.7 398,107 501,187
  10^5.7 10^5.8 501,187 630,957
  10^5.8 10^5.9 630,957 794,328
100万人未満 10^5.9 10^6.0 794,328 1,000,000
100万人以上 10^6.0   1,000,000  

 こういう区間ごとの集計は,つまりヒストグラムだが,単純にピボットテーブルを適用してもできない.どの区間に属するかを計算するために,作業列を追加する必要がある.

 EXCEL だと IF 関数のネストになるか,テーブルを人口でソートした上で INDEX 関数と MATCH 関数の組み合わせか,FREQUENCY 関数でどの区間に属するかを分類することになる.EXCEL の照合の特徴として,日本の基準である「以上未満」での照合ではなく,「より大以下」での照合となることは覚えておきたい.ピボットテーブルのグループ化は線形での等分割にしかならないため,今回の作業には向かない.

 後述するが,同じ作業を SQL では CASE 式を使ってスマートに記述できる.

テキストファイルに「名前を付けて保存」

 「ファイル」タブの「名前を付けて保存」でファイル形式を「テキスト(タブ区切り)(*.txt)」とする.

「ファイル」タブの「名前を付けて保存」でファイル形式をテキスト(タブ区切り)(*.txt)とする
「ファイル」タブの「名前を付けて保存」でファイル形式を「テキスト(タブ区切り)(*.txt)」とする

 警告が出るが気にせず OK をクリックする.

「選択したファイルの種類は複数のシートを含むブックをサポートしていません.選択しているシートのみを保存する場合は「OK」をクリックしてください」
「選択したファイルの種類は複数のシートを含むブックをサポートしていません.選択しているシートのみを保存する場合は「OK」をクリックしてください」

データベースの作成

 SQL Serverのオブジェクトエクスプローラーで「新しいデータベース…」を選ぶ.

SQL Serverのオブジェクトエクスプローラーで「新しいデータベース...」
SQL Serverのオブジェクトエクスプローラーで「新しいデータベース…」

 「新しいデータベース」で「データベース名」を CityPopulationDB とする.

「新しいデータベース」で「データベース名」をCityPopulationDBとする
「新しいデータベース」で「データベース名」をCityPopulationDBとする

ファイルのインポート

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

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

 「データソース」からFlat File Sourceを選ぶ.

「データソース」からFlat File Sourceを選ぶ
「データソース」からFlat File Sourceを選ぶ

 先程 EXCEL からエクスポートしたテキストファイルを指定する.

先程EXCELからエクスポートしたテキストファイルを指定
先程EXCELからエクスポートしたテキストファイルを指定

 プレビューが見える.Nextをクリックする.

プレビューが見える.Nextをクリック
プレビューが見える.Nextをクリック

 「変換先」は SQL Server Native Client を選択する.

「変換先」はSQL Server native Clientを選択
「変換先」はSQL Server Native Clientを選択

 「マッピングの編集…」をクリックする.

「マッピングの編集...」をクリック
「マッピングの編集…」をクリック

 列マッピングのデフォルト状態.ここから NULLの可否,データ型,サイズを変更していく.

列マッピングのデフォルト状態.NULLの可否,データ型,サイズを変更していく
列マッピングのデフォルト状態.NULLの可否,データ型,サイズを変更していく

 列マッピングの変更後.人口のデータ型が bigint 型であることに注意.

列マッピングの変更後
列マッピングの変更後

 エラー時の対応を決める.ここでは「無視する」としている.

エラー時の対応を決める
エラー時の対応を決める

 この後 Finish をクリックすると,キャプチャはないが,インポートが成功した旨通知される.

クエリの作成

 実際のクエリは下記のようになる.CASE 式を用いた特性関数である.SQL Server Management Studio でも変数入力のアシストはしてくれる.ピリオドを打つと自動的にポップアップするので矢印キーで選択するだけである.

SQL Server management Studioのクエリ画面.列名がポップアップして入力をアシストしてくれる
SQL Server management Studioのクエリ画面.列名がポップアップして入力をアシストしてくれる

EXCEL に戻り,グラフを描く

SQL Server から EXCEL へ

 クエリを発行して取得された結果を右クリックして「ヘッダー付きでコピー」し,先程の EXCEL の「市町村人口動態」ブックにペーストする.もちろん,新しいワークシートにである.

クエリの結果を「ヘッダー付きでコピー」
クエリの結果を「ヘッダー付きでコピー」

 貼り付けたデータをテーブルに変換しておく.

貼り付けたデータをテーブルに変換する
貼り付けたデータをテーブルに変換する

 ここまでで完全なデータセットが完成した.ここからはグラフを描いていく.

散布図の描画

 新しいワークシートを挿入する.「挿入」タブから「散布図」を選ぶ.なぜ散布図か?データ系列の指定がしやすいというメリットがあるのと,後でグラフの種類の変更が効くからである.

「挿入」タブから「散布図」を選ぶ
「挿入」タブから「散布図」を選ぶ

データ系列の指定

 ここではまだデータ系列を指定していない.グラフの一部を右クリックして「データの選択…」を選ぶ.

グラフを右クリックして「データの選択...」を選ぶ
グラフを右クリックして「データの選択…」を選ぶ

 「データソースの選択」で「凡例項目(系列)」の「追加」をクリックし,データ系列を新規作成する.

「データソースの選択」で「凡例項目(系列)」の「追加」をクリック
「データソースの選択」で「凡例項目(系列)」の「追加」をクリック

 「系列の編集」で「系列名」「系列Xの値」「系列Yの値」をそれぞれ指定する.

「系列の編集」で「系列名」「系列Xの値」「系列Yの値」をそれぞれ指定する
「系列の編集」で「系列名」「系列Xの値」「系列Yの値」をそれぞれ指定する

 セル範囲を指定するボックスには上向きの矢印がついている.これをクリックすると他のワークシートを参照できるようになる.ちなみにこの過程をマクロ記録すると, CutCopyMode = False という一文が記述されるが,この辺りは深入りしないほうが良い.

 ユーザーインターフェースでは,セル範囲を指定するボックスをクリックすると他のワークシートを参照できるようになる.

セル範囲を指定するボックスをクリックすると他のワークシートを参照できるようになる
セル範囲を指定するボックスをクリックすると他のワークシートを参照できるようになる
「系列名」を指定したところ
「系列名」を指定したところ
同様に「系列Xの値」「系列Yの値」を指定したところ
同様に「系列Xの値」「系列Yの値」を指定したところ

 同様の手順で 2005 年,2010 年,2015 年のデータ系列を追加していく.その結果グラフはこうなる.

散布図に描いた初期状態.ここから書式設定を変更していく
散布図に描いた初期状態.ここから書式設定を変更していく

グラフの種類を集合縦棒グラフに変更

 ここでグラフの種類を変更する.グラフを右クリックして「グラフの種類の変更…」を選ぶ.

グラフを右クリックして「グラフの種類の変更...」
グラフを右クリックして「グラフの種類の変更…」

 「縦棒」の「集合縦棒」を選び,OKをクリックする.すでにどんなグラフになるかプレビューが見えている.

「縦棒」の「集合縦棒」を選ぶ
「縦棒」の「集合縦棒」を選ぶ

データ系列はどうなっているのか?

 この時点でデータ系列はどうなっているのだろうか.右クリックして「データの選択…」を見てみよう.

 右側のパネルに「横(項目)軸ラベル」が入っている.これを「編集」してみる.

「データ疎スーの選択」でデータ系列を見てみる
「データ疎スーの選択」でデータ系列を見てみる

 ここでは軸ラベルの範囲を設定できるようだ.VBA でいうところの Series.xlCategory にあたる.「キャンセル」で抜ける.

「軸ラベルの範囲」はSeries.XlCategoryにあたる
「軸ラベルの範囲」はSeries.XlCategoryにあたる

 今度は左側のパネルから「系列」を「編集」してみよう.Series.Name と Series.xlValue にあたるものだ.ここもキャンセルする.

「系列名」はSeries.Nameに,「並列値」はSeries.XlValueにあたる
「系列名」はSeries.Nameに,「系列値」はSeries.XlValueにあたる

グラフ要素の書式設定

横軸のラベルの間隔を 10 にする

 横軸をクリックして「軸の書式設定…」を選ぶ.「ラベル」を展開して「ラベルの間隔」を「自動」から「間隔の単位」にチェックを付け直す. 1 を消して 10 に変更する.設定できる範囲は 1 から 255 までである.

「軸の書式設定」「軸のオプション」「ラベル」「ラベルの間隔」を1から10に変更
「軸の書式設定」「軸のオプション」「ラベル」「ラベルの間隔」を1から10に変更

縦軸と横軸の目盛線を削除する

 徹底的にシンプルを心がける.必要最小限の要素しか見せたくない.グラフの目盛線をクリックしてキーボードの Delete キーを押して削除する.

縦軸のオプションで最大値と間隔を変更する

 縦軸を右クリックして「軸の書式設定…」「軸のオプション」で「境界値」の「最大値」を 150 に,「単位」の「主」を 50 に変更する.

「軸の書式設定」「軸のオプション」「境界値」の「最大値」を150に「単位」の「主」を50に
「軸の書式設定」「軸のオプション」「境界値」の「最大値」を150に「単位」の「主」を50に

縦軸と横軸を消す

 「軸の書式設定」の「線」で「線なし」をチェックする.ラベルのみが残り,軸そのものは見えなくなる.軸そのものを選択して削除しているのではないことに注意されたい.

「軸の書式設定」で「線」を「線なし」にするとラベルが残り,軸は見えなくなる
「軸の書式設定」で「線」を「線なし」にするとラベルが残り,軸は見えなくなる

 ここまでの設定でグラフはこうなっている.

軸の書式設定の変更を適用したところ
軸の書式設定の変更を適用したところ

グラフエリアの書式設定

 グラフエリアを右クリックして「グラフエリアの書式設定」を選ぶ.「グラフエリアの書式設定」で「塗りつぶし」を「塗りつぶし(単色)」の「薄い灰色,背景2,黒+基本色10%」にする.「枠線」は「線なし」を選ぶ.

「グラフエリアの書式設定」で「塗りつぶし」を「塗りつぶし(単色)」の「薄い灰色,背景2,黒+基本色10%」にする
「グラフエリアの書式設定」で「塗りつぶし」を「塗りつぶし(単色)」の「薄い灰色,背景2,黒+基本色10%」にする

フォントはグラフエリアからまとめて設定する

 グラフエリアから「フォント」にアクセスすると縦軸・横軸をはじめ,他にタイトルなどのフォントをまとめて変更できる.つまり,グラフエリアでのフォントの変更の影響はグラフ全体に及ぶ.

 グラフエリアを右クリックして「フォント…」を選ぶ行為がそれにあたる.プロットエリアを右クリックしても「フォント…」メニューは現れない.

グラフエリアを右クリックして「フォント」を選ぶとFont2オブジェクトにアクセスする
グラフエリアを右クリックして「フォント」を選ぶとFont2オブジェクトにアクセスする

 ここで EXCEL は Font2 オブジェクトにアクセスしている.これは比較的新しいオブジェクトである.縦軸,横軸,目盛り,タイトルなどグラフの要素を個別に選択した場合には EXCEL は旧来の Font オブジェクトにアクセスする.

 EXCEL 内部ではおそらく Font2 オブジェクトに統一されているのだろう.旧来のバージョンとの互換性を保つために Font オブジェクトが残されていると想像している.

 実際,「マクロの記録」で取得したコードをそのまま走らせても Font2 オブジェクトを取得できないが, Chart オブジェクトの次に ChartArea オブジェクトを記述することで取得できるようになる.これはバグだと思われる.

グラフエリアの書式設定の変更を適用したところ
グラフエリアの書式設定の変更を適用したところ

データ系列の書式設定の変更

 ユーザーインターフェースでは複数のデータ系列を同時に変更することはできない.一つずつ手動で設定するか,VBA でループするかのいずれかである.今回は系列数がそれほど多くないためユーザーインターフェースでも良いが,あえてコードで設定を変更してみよう.

マクロの記録

 下記はマクロの記録をそのままコピペしたものである.With 句で始まるブロックが三つあり,かなり重複している.変更に無関係のコードも記述されている.

 ここで本当に必要なのは 21 行目と 24 行目だけである.

整形後のコード

 変数を定義し,ループ内で塗りつぶしの色を「白,背景 1」に変更し,透過性を 50 % に変更している.

 ここまでの設定でグラフは下図のようになっている.

系列の書式設定の変更を適用したところ
系列の書式設定の変更を適用したところ

タイトルをつける

 「グラフのデザイン」タブの「グラフ要素を追加」から「グラフタイトル」と進み,「中央重ねで配置」を選ぶ.

「グラフのデザイン」「グラフ要素を追加」「グラフタイトル」
「グラフのデザイン」「グラフ要素を追加」「グラフタイトル」

 「人口ごとの都市数はべき乗の法則に従う」と入力する.グラフはこうなる.

タイトルを「人口ごとの都市数はべき乗の法則に従う」と入力
タイトルを「人口ごとの都市数はべき乗の法則に従う」と入力

考察

 この記事の最初で「人口が半分の都市は四つある」とのマーク・ブキャナンの言葉を紹介した.

 グラフを観察すると,「6.3 万人未満」でピーク(100 前後)が来てきれいに右肩下がりとなっている.「15 万人未満」で約半分(50 前後)になる.

 アメリカの場合ほど傾きは顕著でないものの,「片対数グラフに載せると直線を描く」というべき乗の法則は満たしているように見える.

 地方自治法では「人口 5 万人以上」が市の要件の一つとされているが, 1995 年に成立し 2005 年に失効したいわゆる合併特例法により,市制の要件は人口 3 万人以上に緩和された.

 これはヒストグラムであるが,階級幅を線形でしか扱えないのは問題だ.対数の階級幅でヒストグラムを作成できるようソフトウェアを改良する必要がある.

まとめ

 EXCEL の集合縦棒グラフで人口ごとの都市数を示した.べき乗の法則に従っていると考えられる.

 総務省の e-Stat からデータをダウンロードする方法を示した.エラーなどの技術的な問題はあるものの,対処法も示した.

 ソフトウェアで作成するヒストグラムの階級幅の設定に問題があることを示した.対数の階級幅で作成できるようにすべきである.

 データを美しく表現するには,可能な限り要素を削ぎ落とす必要がある.そのための方法を示した.

都道府県別の県内総生産額を EXCEL の散布図に描く

都道府県ごとの生産性と総生産額

 人口統計は国の将来を予測する重要な指標であるが,経済の指標である総生産も重要な指標である.これは国の元気さを示す値であり,報道では GDP と称されている.一人あたりの GDP とは生産性のことであり,国民の豊かさを示す値でもある.

 マクロ経済学についてはほぼ素人だが,データを扱うにあたり,都道府県ごとの総生産額と生産性は欠かせない指標と思われたので,調査ついでに公開しよう.

“都道府県別の県内総生産額を EXCEL の散布図に描く” の続きを読む