国土交通省が提供している国土数値情報の行政区域データを、Oracle APEX 21.1のマップ・リージョンのレイヤとして表示させてみました。
使用したデータは以下のサイトよりダウンロードしています。
国土交通省のGISホームページの国土数値情報ダウンロードにアクセスし、行政区域(ポリゴン)のページを開きます。
全国の情報をダウンロードしました。
国土数値情報ダウンロードサイトコンテンツ利用規約はこちらになります。データを利用する前に目を通しておきましょう。
作業はVirtualBox上に作成したOracle Database 18c Express Edition + Oracle APEX 21.1の環境で実施しています。
ダウンロードしたデータを取り込む
全国のデータはN03-20210101_GML.zipとしてダウンロードされました。このZIP圧縮ファイルに含まれるGeoJSON形式のファイルN03-21_210101.geojsonをデータベースに取り込みます。ダウンロードしたZIPファイルはあらかじめ解凍しておきます。
GeoJSONのファイルを以下の手順でデータベースに取り込みます。
- BLOB列を持つ表を作成する。
- GeoJSONのファイルをBLOB列に保存する。
- GeoJSONのファイルに含まれるポリゴンを、Oracle SpatialのSDO_GEOMETRY型で保存する表を作成する。
- 保存したGeoJSONのファイルに含まれるポリゴンをOracle SpatialのSDO_GEOMETRY型に変換し、ポリゴン１つを１行として表に書き込む。
以下より、作業手順を記述します。
最初にGeoJSONのファイルをアップロードする表を作成します。SQLワークショップのクイックSQLより、表JAR_DATAを定義します。クイックSQLの定義は以下です。
# prefix: jar
# semantics: default
data
content file
SQLの生成、SQLスクリプトを保存、レビューおよび実行を行います。
続く画面ではアプリケーションの作成ではなく実行を行い、遷移した画面で即時実行をクリックします。
表JAR_DATAが作成された時点で、アプリケーションの作成を実行します。確認画面がポップアップするので、そこでもアプリケーションの作成をクリックします。
アプリケーション作成ウィザードのページが開きます。名前を行政区域とします。アプリケーションの作成を実行します。
アプリケーションが作成されたら、すぐに実行します。とりあえずGeoJSONのファイルをデータベースにアップロードします。ワークスペースにサインインしたユーザーにてアプリケーションにサインインし、Dataのページから作成を実行します。
Contentとして、GeoJSONのファイルN03-01_210101.geojsonを選択し、作成をクリックします。
SQLワークショップのSQLコマンドより以下のDDLを実行し、表JAR_JAPAN_REGION_ADMINSを作成します。GeoJSONのファイルに含まれているポリゴンを保存する表になります。
create table jar_japan_region_admins
(
id number primary key,
prefecture_name varchar2(20) not null, -- N03_001
branch_in_hokkaido varchar2(40), -- N03_002
major_city varchar2(20), -- N03_003
city_name varchar2(40), -- N03_004
admin_code varchar2(6) not null, -- N03_007
geometry sdo_geometry,
vertices number
);
表が作成されたら以下のSQLを実行し、GeoJSONファイルに含まれるポリゴンの情報を、表JAR_JAPAN_REGION_ADMINSにロードします。
insert into jar_japan_region_admins
(
id,
prefecture_name,
branch_in_hokkaido,
major_city, city_name,
admin_code,
geometry
)
select
rownum
,jt.N03_001 prefecture_name
,jt.N03_002 branch_in_hokkaido
,jt.N03_003 major_city
,jt.N03_004 city_name
,nvl(jt.N03_007,'N/A') admin_code
,jt.geometry geometry
from jar_data,
json_table(content, '$.features[*]'
columns(
N03_001 varchar2(255) path '$.properties.N03_001',
N03_002 varchar2(255) path '$.properties.N03_002',
N03_003 varchar2(255) path '$.properties.N03_003',
N03_004 varchar2(255) path '$.properties.N03_004',
N03_007 varchar2(255) path '$.properties.N03_007',
geometry sdo_geometry path '$.geometry'
)
) jt
where jar_data.id = 1;
最後にwhere jar_data.id = 1としてアップロードしたファイルを選択しています。複数回ファイルをアップロードしている場合は1でない場合もあるので、そのような場合は適時変更します。
json_tableのカラムgeometryとして、geometry sdo_geometry path '$.geometry'を指定しています。この指定によって、GeoJSONファイル内のgeometryとして与えられている座標の配列を、Oracle SpatialのSDO_GEOMETRY型の列に取り込んでいます。
今回の実行では121,158行のデータが、表JAR_JAPAN_REGION_ADMINSに取り込まれました。
ロードされたデータを検査します。マニュアルのこちらに記載されている手順に従いSDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXTファンクションを実行します。許容差は0.05とします。マニュアルによると、測地データの場合は0.05m、つまり5cmより近ければ同じ点と認識されます。
とりあえず、最初の10行だけ検査します。
begin
for r in (
select geometry from jar_japan_region_admins order by id
fetch first 10 rows only
)
loop
dbms_output.put_line(sdo_geom.validate_geometry_with_context(r.geometry,0.05));
end loop;
end;
結果は以下のようになります。
13356 [Element <1>] [Coordinate <661>][Ring <1>] 13367 [Element <1>] [Ring <1>] 13367 [Element <1>] [Ring <1>] 13367 [Element <1>] [Ring <1>] 13367 [Element <1>] [Ring <1>] 13356 [Element <1>] [Coordinate <448>][Ring <1>] 13356 [Element <1>] [Coordinate <514>][Ring <1>] 13367 [Element <1>] [Ring <1>] 13367 [Element <1>] [Ring <1>] 13367 [Element <1>] [Ring <1>]
先頭の数値はORAエラーの番号です。ORA-13356、ORA-13367など、対象としたすべての行でエラーが発生しています。実は、ロードしたすべての行でエラーが発生しています。
エラーを解消するために、SDO_UTIL.RECTIFY_GEOMETRYを、すべてのロードしたデータに対して実行します。元のデータを残すため、列GEOM_Rを追加します。
alter table jar_japan_region_admins add (geom_r sdo_geometry);
データの修正を実行します。処理に時間がかかるのでsqlplusなどのコマンドラインのツール、もしくはSQLワークショップのSQLスクリプトより実行することが望ましいです。
update jar_japan_region_admins set geom_r = sdo_util.rectify_geometry(geometry,0.05);
再度、検査を行います。今度は全行を対象にします。
(sqlplusより実行する場合はset serveroutput onの実行や、最後の/を忘れずに。)
declare
cnt number := 0;
l_message varchar2(4000);
begin
for r in (select id, geom_r geometry from jar_japan_region_admins order by id)
loop
l_message := sdo_geom.validate_geometry_with_context(r.geometry,0.05);
if l_message <> 'TRUE' then
dbms_output.put_line('ID: ' || r.id || ' ' || l_message);
cnt := cnt + 1;
end if;
end loop;
dbms_output.put_line('Error Count: ' || cnt);
end;
Error Countとして0が返ってくれば、データの修正は完了です。
領域を表すポリゴンがどの程度複雑なのかを確認するため、頂点の数を列VERTICESとして記録しておきます。
update jar_japan_region_admins set vertices = sdo_util.getnumvertices(geom_r);
マップを表示するページの作成
ロードされたデータを、一旦、マップ・リージョンで表示させてみます。アプリケーション・ビルダーにて、先ほど作成したアプリケーション、行政区域を開きます。
ページ作成ウィザードを起動します。アプリケーション・ビルダーよりページの作成をクリックします。
ページ・タイプはコンポーネントとし、マップをクリックします。
ページ名は行政区域コードとします。次へ進みます。
ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。次へ進みます。
表/ビューの名前として、先ほどデータをロードした表JAR_JAPAN_REGION_ADMINSを選択します。次へ進みます。
レイヤのタイプはポリゴン、ジオメトリ列はGEOM_R (Sdo_Geometry)、ツールチップ列はPREFECTURE_NAME (Varchar2)を選択します。ファセット検索ページの作成はONにします。作成をクリックします。
マップ・リージョンを含んだページが作成されます。このままでは、すべてのポリゴンをマップに表示してしまいます。ファセットを追加することにより、マップ上に表示するポリゴンを制限します。
最初に都道府県を指定するファセットを作成します。左ペインのファセット上でコンテキスト・メニューを表示させ、ファセットの作成を実行します。識別の名前はP4_PREFECTURE_NAME、タイプとしてチェック・ボックス・グループを選択します。ラベルは都道府県とします。LOVのタイプを個別値、NULLオプションを含めるはOFFにします。このファセットのソースとして、ファセットの名前P4_PREFECTURE_NAMEより、PREFECTURE_NAMEが自動的に選ばれます。
続いて支庁のファセットを作成します。識別の名前をP4_BRANCH_IN_HOKKAIDO、タイプはチェック・ボックス・グループです。ラベルは支庁とします。LOVのタイプは個別値、NULLオプションを含めるはONにします。支庁は北海道だけにある行政区域なので、ほとんどの場合はNULLです。NULL表示値として- 指定なし -を入力します。ソースは自動的に選択されます。依存先のファセットとしてP4_PREFECTURE_NAME、タイプは次と等しいを選択し、値に北海道を入力します。
続いて郡・政令指定都市を選択するファセットを作成します。識別の名前をP4_MAJOR_CITYとします。その他の設定は支庁とほとんど同じですが、依存先は設定しません。
市区町村のファセットを作成します。識別の名前をP4_CITY_NAMEとします。それ以外はP4_MAJOR_CITYと同じです。
最後に行政区域コードのファセットを作成します。識別の名前をP4_ADMIN_CODEとします。列ADMIN_CODEにはデータロード時に、NULLの代わりにN/Aを投入しています。そのためNULLであることはないので、LOVのNULLオプションを含めるはOFFにします。
ファセットの設定については以上です。
マップの初期表示として、世界地図ではなく日本全体が表示されるようにします。マップのAttributesを開き、初期位置およびズームとして、タイプを静的値、経度に139.69167、緯度に35.68944、ズーム・レベルを3.8を指定します。
マップ上のポリゴンの表示を調整します。外観の塗りつぶしの不透明度を0.3にします。ストロークの色は赤(#ff0000)とします。加えて、ツールチップの拡張フォーマットをONにし、HTML式として以下を設定します。
<b>都道府県</b>: &PREFECTURE_NAME.
{if BRANCH_IN_HOKKAIDO/}<br><b>支庁</b>: &BRANCH_IN_HOKKAIDO.{endif/}
{if MAJOR_CITY/}<br><b>郡・政令指定都市</b>: &MAJOR_CITY.{endif/}
{if CITY_NAME/}<br><b>市区町村</b>: &CITY_NAME.{endif/}
{if ADMIN_CODE/}<br><b>行政区域コード</b>: &ADMIN_CODE.{endif/}
表示する項目がNULLの場合には、項目自体をツールチップから除いています。
全体で12万行を超えるデータがあるため、検索条件がないと表示に時間がかかります。パフォーマンスを調整するため、レイヤーの行政区域コードの処理する最大行数を10000に制限します。行政区域がもっとも多い北海道のすべての行政区域が表示される行数です。
以上で一旦完了です。ページを実行して、作成したマップを表示してみます。検索条件がないと、検索件数の上限に達します。
検索条件を指定することにより、その地域の行政区域が地図に重なって表示されます。
国土交通省から入手したデータをそのまま表示することはできました。
行政区域が内陸だったり離島などが含まれていなければ、行政区域コードひとつに付き、ひとつのポリゴンが定義されています。
そうでない場合、例えば長崎県の五島市では1,674のポリゴンがひとつの行政区域に含まれています。
ジオメトリを簡略化する
行政区域の詳細な表示より表示速度を重視する場合は、SDO_UTIL.SIMPLIFY(またはSIMPLIFYVW)ファンクションを適用することができます。ポリゴンに含まれる頂点の数を少なくする(間引く)ことにより、表示のパフォーマンスが向上します。
簡略化したジオメトリを保存する列GEOM_Sを追加します。
alter table jar_japan_region_admins add (geom_s sdo_geometry);
ジオメトリの簡略化を行い、結果を列GEOM_Sへ保存します。閾値として10m、許容差は0.05を指定しました。Oracle Database 18c Express Editonで2分程度の時間がかかっています。
update jar_japan_region_admins set geom_s = sdo_util.simplify(geom_r,10,0.05);
簡略化された結果をマップに重ね合わせて表示することにより、結果を確認します。
ページ・デザイナにて先ほど作成した行政区域コードのページを開きます。マップのレイヤーの行政区域コード上でコンテキスト・メニューを表示させ、重複を実行します。重複の結果作成されたレイヤーの識別の名前を簡略化とし、列のマッピングのジオメトリ列をGEOM_Sへ変更します。
レイヤーを追加したページを実行し、簡略化された表示を確認します。青森県を表示してみます。
五所川原市の一部と八戸市の部分の表示が薄いことが確認できます。
行政区域コードの表示を外すと、簡略化されたレイヤーでは上記の行政区域が表示されていないことがわかります。
<b>都道府県</b>: &PREFECTURE_NAME.
{if BRANCH_IN_HOKKAIDO/}<br><b>支庁</b>: &BRANCH_IN_HOKKAIDO.{endif/}
{if MAJOR_CITY/}<br><b>郡・政令指定都市</b>: &MAJOR_CITY.{endif/}
{if CITY_NAME/}<br><b>市区町村</b>: &CITY_NAME.{endif/}
{if ADMIN_CODE/}<br><b>行政区域コード</b>: &ADMIN_CODE.{endif/}
<br><b>ID</b>: &ID.
ページを実行し、表示されない領域のIDを確認します。
簡略化の結果、全国では表示されない領域が27ありました。これらは個別にデータの修正が必要になります。
表示されない簡略化されたデータを修正する
簡略化された結果を確認してみます。最初にジオメトリを頂点に分解して、マップ・リージョンに表示させてみます。
頂点のデータを保存する表JAR_VARTICESを作成します。列X, Y, IDを含みます。
create table jar_vertices(x number, y number, id number);
テーブル・ファンクションのSDO_UTIL.GETVERTICESを呼び出し、問題のあるIDのデータを表にロードします。先程の五所川原市の一部の領域(ID: 9898)を対象にします。
insert into jar_vertices(x,y,id)
select p.x, p.y, p.id
from jar_japan_region_admins r,
table(sdo_util.getvertices(r.geom_s)) p
where r.id = 9898;
434行の点のデータがロードされました。このデータを表示するマップ・リージョンのページを作成します。先ほどと同様に、ページの作成を開始し、タイプとしてマップを選択します。ページ名は頂点の確認とします。次へ進みます。
ナビゲーション・メニュー・エントリの作成は行うように指定します。続いて表の指定が求められます。表/ビューの名前としてJAR_VERTICESを選択します。次へ進みます。
ポイントを選択します。ジオメトリ列タイプは2つの数値列、経度列はXで緯度列はYになります。ツールチップ列としてID(Number)を選択します。作成をクリックするとページが作成されます。実行すると以下のように表示されます。
領域は表示されませんが、領域を形成できる頂点のデータは含まれていることが確認できます。
始点と思われる位置のIDは19です。
ではIDが1の頂点はどこにあるのかというと、ここでした。
ジオメトリに不明な点があるので、ジオメトリの内容を直接確認します。簡略化したジオメトリに含まれるエレメント数をSDO_UTIL.GETNUMELEMファンクションにより取得し、それぞれのエレメントのジオメトリ・タイプを確認します。
declare
l_geom sdo_geometry;
l_geom_s sdo_geometry;
l_elem_cnt number;
begin
select geom_s into l_geom from jar_japan_region_admins where id = 9898;
l_elem_cnt := sdo_util.getnumelem(l_geom);
for i in 1..l_elem_cnt
loop
l_geom_s := sdo_util.extract(l_geom, i);
dbms_output.put_line('type: ' || l_geom_s.sdo_gtype);
end loop;
end;
以下の結果が得られます。
type: 2002 type: 2003 type: 2003 type: 2003 type: 2003 文が処理されました。
先頭のSDO_GTYPEが2002というのは線であり、ポリゴンではありません。Oracle APEXのマップ・リージョンのレイヤーにはポリゴンが指定されているため、ジオメトリが点や線を含むと問題が起こるようです。
国土交通省のデータをロードした直後のデータのジオメトリのタイプはすべて2003、つまり単純なポリゴンです。SDO_UTIL.RECTIFY_GEOMETRYファンクションを実行したり、SDO_UTIL.SIMPLIFYファンクションを実行すると、結果のタイプは2004(COLLECTION)や2007(MULTIPOLYGON)に変わることがあります。ジオメトリのタイプがコレクション(2004)に変わった際に点(2001)や線(2002)が含まれることがあると、Oracle APEXのマップ・リージョンでは(ポリゴンではないため)、地図上にポリゴンとして表示されないようです。
国土交通省のデータをロードした直後のデータのジオメトリのタイプはすべて2003、つまり単純なポリゴンです。SDO_UTIL.RECTIFY_GEOMETRYファンクションを実行したり、SDO_UTIL.SIMPLIFYファンクションを実行すると、結果のタイプは2004(COLLECTION)や2007(MULTIPOLYGON)に変わることがあります。ジオメトリのタイプがコレクション(2004)に変わった際に点(2001)や線(2002)が含まれることがあると、Oracle APEXのマップ・リージョンでは(ポリゴンではないため)、地図上にポリゴンとして表示されないようです。
SDO_UTIL.RECTIFY_GEOMETRYやSDO_UTIL.SIMPLIFYの結果はジオメトリとしては正しいため、SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXTの結果はTRUEとなります。Oracle APEXのマップ・リージョンはSDO_GEOMETRY型として正しいかどうかに関係なく、座標の数値を読み出すことさえできれば描画できます。実際にGeoJSONをロードした直後、ジオメトリとしては不正であっても、ポリゴンは描画できていました。
少し強引ですが、ジオメトリがコレクション(2004)の場合、ジオメトリにポリゴンだけが含まれるように修正します。set serveroutput on、/による実行、commitなども忘れずに実行します。
declare
l_geom sdo_geometry;
l_geom_s sdo_geometry;
l_geom_t sdo_geometry;
l_elem_cnt integer;
b_polygon_only boolean;
l_message varchar2(4000);
begin
for r in (
select * from jar_japan_region_admins order by id
)
loop
l_geom := r.geom_s;
if l_geom.sdo_gtype = 2004 then
l_elem_cnt := sdo_util.getnumelem(l_geom);
-- コレクションにポリゴン以外が含まれているか確認する。
b_polygon_only := true;
for i in 1..l_elem_cnt
loop
l_geom_s := sdo_util.extract(l_geom, i);
if l_geom_s.sdo_gtype <> 2003 then
b_polygon_only := false;
exit;
end if;
end loop;
-- ポリゴン以外が含まれているときは、ジオメトリからそれらを取り除く。
if not b_polygon_only then
dbms_output.put_line('id:' || r.id);
l_geom_t := null;
for i in 1..l_elem_cnt
loop
l_geom_s := sdo_util.extract(l_geom, i);
if l_geom_s.sdo_gtype = 2003 then
if l_geom_t is null then
l_geom_t := l_geom_s;
else
l_geom_t := sdo_util.append(l_geom_t,l_geom_s);
end if;
end if;
end loop;
if l_message <> 'TRUE' then
dbms_output.put_line('ID:' || r.id || ' ' || l_message);
else
dbms_output.put_line('ID:' || r.id || ' fixed.');
update jar_japan_region_admins set geom_s = l_geom_t where id = r.id;
end if;
end if;
end if;
end loop;
end;
/
修正したデータをマップから確認します。
青森県の五所川原市と八戸市が、簡略化したデータで領域が表示されるようになっています。
青森県以外で表示がされていなかった領域についても、簡略化したデータで領域が表示されています。
最後に簡略化したジオメトリが持っている頂点の数をアップデートします。列VERT_Sを追加します。
alter table jar_japan_region_admins add (vert_s number);
頂点の数をアップデートします。
update jar_japan_region_admins set vert_s = sdo_util.getnumvertices(geom_s);
全体として削減された頂点の数を確認してみます。
select sum(vertices) "元の頂点数", sum(vert_s) "簡略化後", sum(vertices)/sum(vert_s) "比率"
from jar_japan_region_admins
結果として、1/8強の頂点が削減されています。
| 元の頂点数 | 簡略化後 | 比率 |
|---|---|---|
| 14748036 | 1779267 | 8.28882680339712926727691796678070239037 |
行政区域コード単位で集約する
ひとつの行政区域を表示するたびに何千行もデータベースから行を取り出すのも効率がよくないため、行政区域コードごとにまとめてみます。同一の行政区域コードのポリゴンにはほとんど重なりがないため（重なりがあるなら、最初から１つのポリゴンになっている）、
SDO_UTIL.APPENDファンクションを使用することができます。
以下のDDLを実行し、まとめた結果を保存する表JAR_JAPAN_REGION_CITIESを作成します。
create table jar_japan_region_cities
(
prefecture_name varchar2(20) not null,
branch_in_hokkaido varchar2(40),
major_city varchar2(20),
city_name varchar2(40),
admin_code varchar2(6) not null,
geometry sdo_geometry,
row_count number,
vertices number
);
最初に元々、行政区域コード１つあたり１行、つまりポリゴンがひとつだけ登録されているデータを表JAR_JAPAN_REGION_ADMINSより見つけ、表JAR_JAPAN_REGION_CITIESへコピーします。
簡略化したジオメトリ(列GEOM_S)をソースとして処理を行います。
コピーを行うために、以下のMERGE文を実行しました。
merge into jar_japan_region_cities c
using
(
select
prefecture_name, branch_in_hokkaido, major_city, city_name
,admin_code, geom_s geometry, vert_s vertices
from jar_japan_region_admins
where
(prefecture_name, admin_code)
in
(
select prefecture_name, admin_code
from jar_japan_region_admins
group by prefecture_name, admin_code
having count(*) = 1
)
) a
on (c.prefecture_name = a.prefecture_name and c.admin_code = a.admin_code)
when matched then
update set
branch_in_hokkaido = a.branch_in_hokkaido
,major_city = a.major_city
,city_name = a.city_name
,geometry = a.geometry
,row_count = 1
,vertices = a.vertices
when not matched then
insert (prefecture_name, branch_in_hokkaido, major_city, city_name
,admin_code, geometry, row_count, vertices)
values (a.prefecture_name, a.branch_in_hokkaido, a.major_city, a.city_name
,a.admin_code, a.geometry, 1, a.vertices)
;
1213行が表JAR_JAPAN_REGION_CITIESに挿入されました。
残りとなる、ひとつ以上のポリゴンを含む行政区域コードを一行にまとめます。
普通に実行すると非常に長い時間がかかるため、まとめる対象を削減します。10,000平方メートル(100m四方)以上の領域のみを対象とします。そのために面積を計算します。
面積を保持する列AREAを追加します。
alter table jar_japan_region_admins add (area number);
面積を計算して、列AREAをアップデートします。
update jar_japan_region_admins set area = sdo_geom.sdo_area(geom_s);
作業ログを記録する表JAR_VALIDATE_CITIESを作成します。
create table jar_validate_cities
(
prefecture_name varchar2(20) not null,
branch_in_hokkaido varchar2(40),
major_city varchar2(20),
city_name varchar2(40),
admin_code varchar2(6),
message varchar2(4000)
);
実際には重なり合う領域も存在します。そのため、重なりが無いときはSDO_UTIL.APPENDを使い、重なりがあるときはSDO_GEOM.SDO_UNIONを使うようにコードを書いています。重なりの確認には、SDO_GEOM.RELATEファンクションを使用します。
以下のPL/SQLコードを実行します。処理に長時間かかるため、sqlplusでデータベースに繋いで実行しました。SQLワークショップのSQLスクリプトとしても実行できるでしょう。
declare
l_geom sdo_geometry;
l_geom_a sdo_geometry;
l_row_count number;
l_vertices number;
l_message varchar2(4000);
begin
delete from jar_validate_cities;
commit;
for c in (
-- まとめる数が少ない行政区分からひとつひとつまとめていく。
select prefecture_name, branch_in_hokkaido, major_city, city_name, admin_code
from jar_japan_region_admins
where (prefecture_name, admin_code) not in (
-- すでにまとめ済みの行政区分は除外する
select prefecture_name, admin_code from jar_japan_region_cities
)
group by prefecture_name, branch_in_hokkaido, major_city, city_name, admin_code
order by count(*) asc
)
loop
l_geom := null;
for r in
(
select prefecture_name, admin_code, geom_s geometry
from jar_japan_region_admins
where prefecture_name = c.prefecture_name
and admin_code = c.admin_code
and area > 10000
)
loop
if l_geom is null then
-- 最初の１行は初期化に使用する。
l_geom := r.geometry;
l_row_count := 1;
continue;
end if;
-- ポリゴン、行数、頂点の数を集計する
if sdo_geom.relate(l_geom, 'disjoint', r.geometry, 0.05) = 'TRUE' then
l_geom_a := sdo_util.append(l_geom, r.geometry);
else
l_geom_a := sdo_geom.sdo_union(l_geom, r.geometry, 0.05);
end if;
l_geom := l_geom_a;
l_row_count := l_row_count + 1;
end loop;
l_vertices := sdo_util.getnumvertices(l_geom);
-- 結果を保存する。途中で停止できるよう毎回commitする。
l_message := sdo_geom.validate_geometry_with_context(l_geom, 0.05);
insert into jar_validate_cities(prefecture_name, branch_in_hokkaido,
major_city, city_name, admin_code, message)
values(
c.prefecture_name, c.branch_in_hokkaido,
c.major_city, c.city_name, c.admin_code, l_message);
insert into jar_japan_region_cities
(prefecture_name, branch_in_hokkaido, major_city, city_name
,admin_code, geometry, row_count, vertices)
values(
c.prefecture_name, c.branch_in_hokkaido, c.major_city, c.city_name,
c.admin_code, l_geom, l_row_count, l_vertices);
commit;
end loop;
end;
/
処理が終了したら、エラーがないか確認します。表JAR_VALIDATE_CITIESのmessageとしてTRUE以外が保存されている行を検索します。
select count(*) from jar_validate_cities where message <> 'TRUE';
結果が0であれば、データのまとめは正常に完了しています。
まとめたデータを使って、マップを表示するページを作成します。先ほど作成したページのコピーを作成します。作成メニューより、コピーとしてのページを実行します。
次のコピーとしてのページを作成として、このアプリケーションのページを選択します。次へ進みます。
新規ページ名はAPPENDとしました。次へ進みます。
ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。次へ進みます。
データ・ソースとなる表はページ作成後に変更します。この時点でのアイテムなどの変更は不要です。何も変更せず、コピーを実行します。
ページがコピーされたら、マップ・リージョンのソースの表名をJAR_JAPAN_REGION_CITIESへ変更します。
レイヤーの行政区域コードの列のマッピングのジオメトリ列をGEOMETRYに変更します。レイヤーの簡略化は削除します。
以上で完了です。作成したページを実行します。全体の行数が減ったため、全国の地図が表示されるようになりました。
都道府県単位で集約する
Oracle Spatialが提供する空間集計ファンクションのSDO_AGGR_UNIONを使用して、都道府県ごとにポリゴンを集約してみます。
以下のDDLを実行し、集約した結果を保存する表JAR_JAPAN_REGION_PREFECTURESを作成します。
create table jar_japan_region_prefectures
(
prefecture_name varchar2(20) not null,
geometry sdo_geometry,
row_count number,
vertices_sum number,
vertices number
);
declare
l_pref_name varchar2(20);
l_geom sdo_geometry;
l_row_count number;
l_vertices_sum number;
l_vertices number;
l_errm varchar2(4000);
begin
for c in (
select prefecture_name
from jar_japan_region_cities
where prefecture_name not in (
select prefecture_name from jar_japan_region_prefectures
)
group by prefecture_name
order by sum(vertices) asc
)
loop
begin
select
prefecture_name
,sdo_aggr_union(sdoaggrtype(geometry, 0.05))
,count(*)
,sum(vertices)
into l_pref_name, l_geom, l_row_count, l_vertices_sum
from jar_japan_region_cities
where prefecture_name = c.prefecture_name
group by prefecture_name;
exception
when others then
l_errm := sqlerrm;
dbms_output.put_line(l_errm);
continue;
end;
--
l_vertices := sdo_util.getnumvertices(l_geom);
insert into jar_japan_region_prefectures
(prefecture_name, geometry, row_count, vertices_sum, vertices)
values(l_pref_name, l_geom, l_row_count, l_vertices_sum, l_vertices);
commit;
end loop;
end;
/メモリに余裕がある(PGA_AGGREGATE_TARGETの値が大きい)環境であれば、都道府県ごとにFORループで回す必要はないと思われます。
先ほどと同様にコピーとしてのページを実行し、マップを表示するページを作成します。都道府県単位で集約したデータをソースとすることにより、集約したポリゴンをマップに表示させます。
手順として異なる部分のみを示します。
コピー元ページとして6.APPENDを選択し、新規ページ名は都道府県とします。
ページが作成されたら、マップ・リージョンのソースの表名をJAR_JAPAN_REGION_PREFECTURESへ変更します。
レイヤーの名前は行政区域コードから都道府県に変更します。
ファセットP7_PREFECTURE_NAMEの属性を調整します。リスト・エントリの件数の計算をOFFにします。集約後は、都道府県の件数はつねに１になるためです。また、最大表示エントリを47にします。
以上でページを実行してみます。
以上で、作業は完了です。
Oracle Spatialというか地理情報処理に詳しい方によると、今まで紹介してきた処理は平面直角座標系に変換したうえで実施すると良い、とのことでした。
Oracle Spatialは色々な機能を提供しています。地理情報処理の知識が必要な部分はあり簡単ではありませんが、学ぶだけの価値はありそうです。本記事内では、ポリゴンの結合、面積の計算、ジオメトリの位置関係の確認などの機能を使用しています。これらの機能を本当に簡単に、SQLから呼び出すことが出来ています。
本記事で作成したアプリケーションを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/japan-region-map.sql
Oracle APEXによるアプリケーション開発の参考になれば幸いです。
完
追記
記事中にも記載していますが、国土交通省国土数値情報ダウンロードサイトからダウンロードした行政区域データを使用して、こちらの記事を書いています。
データのダウンロードは以下のURLから行なっています。
https://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N03-v3_0.html
参考
行政区域コードのまとめ処理の進捗を確認するために使用したスクリプトは以下です。
set pages 1000 lines 180 trims on trimo on
col cnt format 99999999
col prefecture_name format a12
col major_city format a20
col city_name format a30
col admin_code format a6
select
prefecture_name
,branch_in_hokkaido
,major_city
,city_name
,admin_code
,count(*) cnt, sum(vertices)
from jar_japan_region_admins
where (prefecture_name, admin_code) not in (
select prefecture_name, admin_code from jar_japan_region_cities
)
and
area > 10000
group by
prefecture_name
,branch_in_hokkaido
,major_city
,city_name
,admin_code
order by 6 desc;
都道府県のまとめ処理の進捗を確認するために使用したスクリプトは以下です。北海道のまとめはデータ数が多いため、長時間(３０分程度)はかかります。
set pages 1000 lines 120 trims on trimo on col cnt format 99999999 col prefecture_name format a12 col admin_code format a6 select prefecture_name, row_count, vertices_sum, vertices from jar_japan_region_prefectures;
