社畜ゲートウェイ

京成・京急を中心に取り上げる阪急ファンのブログです。日本一遅い速報を届けます。

MENU

【スポンサーリンク】

『駅データ.jp』のデータをOracleで使用可能にするスクリプトを公開

【スポンサーリンク】

f:id:neko_britannia:20200417145314j:plain

私もエンジニアの端くれなので、たまにはエンジニアの立場として鉄道関係のお役に立てればなと。先日、『駅データ.jp』というサイトについて紹介させて頂いたのですが、公開スクリプトと提供データをちょっと変えてみたいなと思っていました。

www.stellacreate.com

『駅データ.jp』の公開スクリプトと駅データcsvの内容

公開スクリプト

公開スクリプトの内容を見る限り、MySQLで書かれたものです。なので、使用したいDBMSの環境に合わせてスクリプトの内容を書き換える必要があります。筆者はOracleに書き換えます。

 

駅データcsv

駅データcsvの内容ですが、恐らく、『駅データ.jp』が保有しているDBMSからナチュラルにcsvにエクスポートしたのかと。DB接続ツールを使用して、クリップボード経由やツールの機能でファイル出力を使用した可能性も考えられます。このcsvをそのまま使ってDBMSには投入するのは難しいです(MySQLなら可能?)。

そんなわけで、公開スクリプトをOracleに書き換えるのに合わせて、駅データcsvも加工します。というか、駅データcsvをそのままは無理です。カンマをタブに変えてエクセル上に貼り付けるのは誰でも出来ますが、DBMS上で扱うとなると加工が必要です。提供する側も使用者が不特定多数になることを見越して、割といじりやすいcsvとして提供しているのだと思います。ありがたや。

 

Oracleで『駅データ.jp』の公開スクリプトと駅データcsvを使用する

テーブル作成

Oracle用のスクリプトはこちらです。公式提供のスクリプトをOracleで使用可能なように修正しています。

--事業者データ
CREATE TABLE m_company (
company_cd NUMBER(11) DEFAULT 0 NOT NULL,
rr_cd NUMBER(6) DEFAULT 0 NOT NULL,
company_name VARCHAR2(256) DEFAULT '' NOT NULL,
company_name_k VARCHAR2(256) DEFAULT NULL,
company_name_h VARCHAR2(256) DEFAULT NULL,
company_name_r VARCHAR2(256) DEFAULT NULL,
company_url VARCHAR2(512) DEFAULT NULL,
company_type NUMBER(6) DEFAULT 0,
e_status NUMBER(6) DEFAULT 0,
e_sort NUMBER(11) DEFAULT 0,
CONSTRAINT pk_m_company PRIMARY KEY(company_cd)
)
/
CREATE INDEX m_company_rr_cd ON m_company(rr_cd)
/
CREATE INDEX m_company_company_type ON m_company(company_type)
/
CREATE INDEX m_company_e_sort ON m_company(e_sort)
/

--路線データ
CREATE TABLE m_line (
line_cd NUMBER(11) DEFAULT 0 NOT NULL,
company_cd NUMBER(11) DEFAULT 0 NOT NULL,
line_name VARCHAR2(256) DEFAULT '' NOT NULL,
line_name_k VARCHAR2(256) DEFAULT NULL,
line_name_h VARCHAR2(256) DEFAULT NULL,
line_color_c VARCHAR2(8) DEFAULT NULL,
line_color_t VARCHAR2(32) DEFAULT NULL,
line_type NUMBER(6) DEFAULT 0,
lon FLOAT(24) DEFAULT 0,
lat FLOAT(24) DEFAULT 0,
zoom NUMBER(6) DEFAULT 0,
e_status NUMBER(6) DEFAULT 0,
e_sort NUMBER(11) DEFAULT 0,
CONSTRAINT pk_m_line PRIMARY KEY(line_cd)
)
/
CREATE INDEX m_line_company_cd ON m_line(company_cd)
/
CREATE INDEX m_line_e_sort ON m_line(e_sort)
/

--駅データ
CREATE TABLE m_station (
station_cd NUMBER(11) DEFAULT 0 NOT NULL,
station_g_cd NUMBER(11) DEFAULT 0 NOT NULL,
station_name VARCHAR2(256) DEFAULT '' NOT NULL,
station_name_k VARCHAR2(256) DEFAULT NULL,
station_name_r VARCHAR2(256) DEFAULT NULL,
station_name2 VARCHAR2(256) DEFAULT NULL,
station_number VARCHAR2(256) DEFAULT NULL,
station_u VARCHAR2(256) DEFAULT NULL,
line_cd NUMBER(11) DEFAULT 0 NOT NULL,
pref_cd NUMBER(6) DEFAULT 0,
post VARCHAR2(32) DEFAULT NULL,
address VARCHAR2(1024) DEFAULT NULL,
lon FLOAT(24) DEFAULT 0,
lat FLOAT(24) DEFAULT 0,
open_ymd DATE DEFAULT NULL,
close_ymd DATE DEFAULT NULL,
e_status NUMBER(6) DEFAULT 0,
e_sort NUMBER(11) DEFAULT 0,
CONSTRAINT pk_m_station PRIMARY KEY(station_cd)
)
/
CREATE INDEX m_station_station_g_cd ON m_station(station_g_cd)
/
CREATE INDEX m_station_line_cd ON m_station(line_cd)
/
CREATE INDEX m_station_pref_cd ON m_station(pref_cd)
/
CREATE INDEX m_station_e_sort ON m_station(e_sort)
/

--接続駅データ
CREATE TABLE m_station_join (
line_cd NUMBER(11) DEFAULT 0 NOT NULL,
station_cd1 NUMBER(11) DEFAULT 0 NOT NULL,
station_cd2 NUMBER(11) DEFAULT 0 NOT NULL,
CONSTRAINT pk_m_station_join PRIMARY KEY(line_cd,station_cd1,station_cd2)
)
/

一応、コピペで連続実行できるようにしていますが、もし無理だった場合は、スラッシュをセミコロンに変えて、一文ずつ実行してみて下さい。

 

駅データcsvのテーブル登録

駅データのcsvですが、ちょっとサイズが大きいのと、どこかにインポート用のアップローダーが無いものかなと探しているのですが、なかなか良いものが見つかりませんので、csvをテーブルに登録するステップを紹介します。SQL文の基本的な説明は省きます。

VARCHAR2型はシングルクォーテーションで囲む

csvをそのまま使用すると、INSERT文でエラーになります。ローダーでも同じです。なので、VARCHAR2型のデータはシングルクォーテーションで囲ってあげます。

日付データはTO_DATEしてあげる

csvの日付データの形式として、「YYYY-MM-DD」になっていますが、日付型のカラムにINSERTする時にこのままだと怒られるので、TO_DATEしてあげて下さい。こんな感じです。

TO_DATE('2019-10-01','YYYY-MM-DD')

「0000-00-00」の日付データはNULLにする。

上記でDATE型はTO_DATEすることを述べていますが、日付データで「0000-00-00」があり、そのままINSERTしようとすると、「ORA-01843: 指定した月が無効です。」と怒られます。まあ、「00月」なんてものはこの世に存在しないですし、「0000-00-00」というデータ自体、日付が不明なのでダミーで用意しているものと考えられるので、NULL扱いで良いと思います。

INSERT文について

例文としてこんな感じになります。csvを頑張って加工して、このINSERT文を真似てみれば各種データが登録可能です。

--事業者データ
INSERT ALL
into m_company (company_cd,rr_cd,company_name,company_name_k,company_name_h,company_name_r,company_url,company_type,e_status,e_sort) VALUES (1,11,'JR北海道','ジェイアールホッカイドウ','北海道旅客鉄道株式会社','JR北海道','http://www.jrhokkaido.co.jp/',1,0,1)

SELECT * FROM DUAL;

--路線データ
INSERT ALL
INTO m_line (line_cd,company_cd,line_name,line_name_k,line_name_h,line_color_c,line_color_t,line_type,lon,lat,zoom,e_status,e_sort) VALUES (1001,3,'中央新幹線','チュウオウシンカンセン','中央新幹線',null,null,null,137.4938965,35.41143805,8,1,1001)

SELECT * FROM DUAL;

--駅データ
INSERT ALL
INTO m_station (station_cd,station_g_cd,station_name,line_cd,pref_cd,post,address,lon,lat,open_ymd,close_ymd,e_status,e_sort) VALUES (1110101,1110101,'函館',11101,1,'040-0063','北海道函館市若松町12-13',140.726413,41.773709,to_date('1902-12-10','YYYY-MM-DD'),null,0,1110101)

SELECT * FROM DUAL;

--接続駅データ
INSERT ALL
INTO m_station_join (line_cd,station_cd1,station_cd2) VALUES (1002,100201,100202)

SELECT * FROM DUAL;

どこかちょうどいいアップローダーがあれば、そちらにINSERTスクリプトをアップしてきますので…

 

編集後記

とまあ、こんな感じに『駅データ.jp』が提供してくれているデータをOracleに登録してみました。鉄道に興味があるエンジニアさんなら有効活用してくれると思いますし、鉄道に興味が無くても、DBMS(データベース)の練習でテストデータ使いたいよって方にも役立つのではと思っています。

今後1週間の鉄道イベント(鉄道コムより)

日付 イベント
17日(金)
18日(土)
19日(日)
20日(月)
21日(火)  特になし
22日(水)  特になし
23日(木)
24日(金)
25日(土)
26日(日)