基本は、Postgresデータベースを使って、テーブル作成し、CSVファイルをインポートするまで、
端的に言うと、新潟市Opendataをうまく使って公開してゆく取組のメモです。
まずは接続し、環境確認
$ psql -h 172.18.0.3 -U opendata opendata Password for user opendata: ・・・・（色々表示がある）・・・ Nopendata=> \l opendata List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- opendata | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | opendata=CTc/postgres (1 rows)
テーブル作成（入れ物）、議員名簿ID用のシーケンス作成
CSVファイルは、以下のようになっているので、必要なものだけ
会派,氏名,選出区,郵便番号,住所,自宅電話番号,事務所電話番号,ファクス番号
追加で、コメント（MSG)、更新日（UPDATE)を付ける
CREATE SEQUENCE GIINMEIBO_ID START 10000; #10000から作成
CREATE TABLE GIINMEIBO
(
ID char(5) NOT NULL DEFAULT nextval('GIINMEIBO_ID'),
KAIHA varchar NOT NULL,
NAME varchar NOT NULL,
SENSYUTUKU varchar NOT NULL,
ZIPCODE char(8) NOT NULL,
MSG varchar default 'NULL',
UPDATE timestamp default 'now',
PRIMARY KEY (id)
);
ここのCSVファイルをインポートする。
基本は、
# \copy <Table name> from <File name> with csv header
必要なカラムだけなら、
# \copy GIINMEIBO(NAME,ZIPCODE) from ‘.csv’ with csv
でもこれは、superuserでなければならいので、一般ユーザーは、STDINを利用して流します。
でも
$ cat '議員名簿 od-20210409giinmeibo.csv' |\ awk -F, '{print $1","$2","$3","$4}' | \ psql -h 172.18.0.2 -U opendata opendata \ -c "copy GIINMEIBO(KAIHA,NAME,SENSYUTUKU,ZIPCODE) from STDIN with csv header" Password for user opendata:｛パスワード入力｝ COPY 50 (確認) $ psql -h 172.18.0.2 -U opendata opendata -c "SELECT * FROM GIINMEIBO" (一覧が表示される。)
同様に、政務活動費執行状況も実施します。
CREATE SEQUENCE KAIHAKOUFU_ID START 10000; #10000から作成
---
会派名,収入額[円] ,
支出内訳（調査研究費） [円] ,支出内訳（研修費） [円] ,支出内訳（広報費） [円] ,支出内訳（広聴費） [円]
,支出内訳（要請・陳情活動費） [円] ,支出内訳（会議費） [円] ,支出内訳（資料作成費） [円] ,
支出内訳（資料購入費） [円] ,支出内訳（人件費） [円] ,支出内訳（事務所費） [円] ,
支出額 [円], 残額（収入額－支出額）[円]
CREATE TABLE KAIHAKOUFU ( ID char(5) NOT NULL DEFAULT nextval('KAIHAKOUFU_ID'), NENDO int, KAIHA varchar NOT NULL, INCOME numeric default 0, CYOUSA_KENKYUU_HI numeric default 0, KENSYUU_HI numeric, KOUHOU_HI numeric, KOUCYOU_HI numeric, YOUSEI_CHINZYOUKATUDOU_HI numeric default 0, KAIGI_HI numeric default 0,SIRYOU_SAKUSEI_HI numeric default 0, SIRYOU_KOUNYU_HI numeric default 0, ZINKEN_HI numeric default 0, ZIMUSYO_HI numeric default 0, EXP_ALL numeric default 0, ZANGAKU numeric default 0, UPDATE timestamp default 'now', PRIMARY KEY (id) );
cat od-kaihakouhu_r1.csv | \
awk -F, {'print 2019","$1","$2","$3","$4","$5","$6","$7","$8","$9","$10","$11","$12","$13","$14'} |\
psql -h 172.18.0.2 -U opendata opendata -c "COPY KAIHAKOUFU(NENDO, KAIHA, INCOME, CYOUSA_KENKYUU_HI,\
KENSYUU_HI, KOUHOU_HI, KOUCYOU_HI, YOUSEI_CHINZYOUKATUDOU_HI, KAIGI_HI, SIRYOU_SAKUSEI_HI,\
SIRYOU_KOUNYU_HI, ZINKEN_HI, ZIMUSYO_HI, EXP_ALL, ZANGAKU) from STDIN with csv header"
CREATE SEQUENCE KAIGIKEKKA_ID START 10000; #10000から作成
---
議決日（年）,議決日（月）,議決日（日）,種別,年度,議案番号,件名,結果,翔政会25人,日本共産党新潟市議会議員団5人,民主にいがた4人（欠席1人）,新潟市公明党4人,
新市民クラブ4人,市民ネットにいがた3人,会派に属さない議員1人,会派に属さない議員1人,会派に属さない議員1人,
CREATE TABLE KAIGIKEKKA ( ID char(5) NOT NULL, GIKETUBI date NOT NULL, SYUBETU varchar NOT NULL, NENDO int NOT NULL, GIANNO varchar NOT NULL, KENMEI varchar NOT NULL, KEKKA varchar NOT NULL, UPDATE timestamp NOT NULL, PRIMARY KEY (id) );
CREATE TABLE KAIGIKEKKA_KAIHA ( ID char(5) NOT NULL,NO char(2) NOT NULL, KAIHA varchar NOT NULL, SANPI varchar NOT NULL, UPDATE timestamp NOT NULL, PRIMARY KEY (id,no) );
１．TABLE KAIGIKEKKA（親）、TABLE KAIGIKEKKA_KAIHA（子）として、IDは共通
２．最初の見出行から、会議結果の会派を取得して変数に保管する。
３．ファイルを読んで、”会議結果”だけをTABLE KAIGIKEKKA（親）に入れる。
４．ファイルの後半の会派の賛否を読んで、NULLになるまで、繰り返す。
項番２で読んだ変数の順番で、”会議結果の会派”部分だけをTABLE KAIGIKEKKA_KAIHA（子）に入れる。
あああ