Contents
実行環境
- Windows 7 64
bit + PostgreSQL 9.5.0 - コマンドプロンプトの代わりにConEmu 151208 + psql
どんな
QGIS地理学や生態学でなくジャーナリズムの研究教育でもオープンソース
QGIS
微妙に扱いにくいと思う理由は次の
- 列数が多い → インポート先テーブルの各列を定義するのが面倒
- 列の一部（
street ）にカンマがあり、この列だけ二重引用符で囲まれている - 空白を含む列、空の列もあるが、それは二重引用符で囲まれていない
- 行を一意に区別する
ID などの列がない
（1）普通にインポート先テーブルを定義し
CSVcreate table alameda_1
(
school_name text,
county text,
street text,
city text,
state text,
zip_code text,
charter text,
magnet text,
title1school text,
title1schoolwide text,
students int,
teachers float,
studentteach_ratio float,
"Geocode Score" float,
"Geocode Precision" text,
latitude float,
longitude float
);
copy alameda_1 from 'alameda-schools.csv' (format csv, header true);
↓ 結果の確認。列が多いので
\pset format aligned Output format is aligned. \pset expanded on Expanded display is on. select * from alameda_1; -[ RECORD 1 ]------+--------------------------------------------------- school_name | ACHIEVE ACADEMY county | ALAMEDA street | 303 HEGENBERGER RD., STE. 301 city | OAKLAND state | CA zip_code | 94621 charter | Yes magnet | title1school | Yes title1schoolwide | Yes students | 221 teachers | 10 studentteach_ratio | 22.1 Geocode Score | 0.73 Geocode Precision | range latitude | 37.733459 longitude | -122.198363 -[ RECORD 2 ]------+--------------------------------------------------- school_name | ACORN WOODLAND ELEMENTARY county | ALAMEDA street | 1025 81ST AVE. (...)
取り込みはできたものの、先ほど書いたように「行を一意に区別する
alter table alameda_1 add column rowid serial; alter table alameda_1 add primary key (rowid);
普通は最初のテーブル定義時に
copy alameda_1 (school_name, county, street, ............... ) from 'alameda-schools.csv' (format csv, header true);
（2）CSV
今回はselect 'create table alameda_2 (' || string_agg('"' || colnames || '" text', ', ') || ')'
from unnest(string_to_array('ここにCSVのヘッダ列をコピペ', ',')) as colnames;
列名ヘッダのカンマは全て列区切りだったので、PostgreSQL
select 'create table alameda_2 (' || string_agg('"' || colnames || '" text', ', ') || ')'
from unnest(string_to_array('school_name,county,street,city,state,zip_code,charter,magnet,
title1school,title1schoolwide,students,teachers,studentteach_ratio,Geocode Score,Geocode P
recision,latitude,longitude', ',')) as colnames;
create table alameda_2 ("school_name" text, "county" text, "street" text, "city" text, "st
ate" text, "zip_code" text, "charter" text, "magnet" text, "title1school" text, "title1sch
oolwide" text, "students" text, "teachers" text, "studentteach_ratio" text, "Geocode Score
" text, "Geocode Precision" text, "latitude" text, "longitude" text)
後は、何らかの関数でこのクエリ文字列を動的に実行すれば
do $$
declare
header text := 'ここにCSVのヘッダ列をコピペ';
tbname text := 'alameda_2'; -- インポート先テーブル名
csvfln text := 'alameda-schools.csv';
begin
execute 'create table ' || tbname ||
'(' || string_agg('"' || colnames || '" text', ', ') || ')'
from unnest(string_to_array(header, ',')) as colnames;
execute 'copy ' || tbname || ' from ''' || csvfln || ''' (format csv, header true)';
execute 'alter table ' || tbname || ' add column rowid serial';
execute 'alter table ' || tbname || ' add primary key (rowid)';
end;
$$;
最初の動的クエリは
（3）とりあえずテキスト型で入れた列の型を変換する
列の型変換は、CSV-- ひな型 -- alter table テーブル名 alter 列名 type 型名 using 列名 :: 型名; alter table alameda_2 alter students type int using students :: int; alter table alameda_2 alter teachers type float using teachers :: float; alter table alameda_2 alter studentteach_ratio type float using studentteach_ratio :: float; alter table alameda_2 alter "Geocode Score" type float using "Geocode Score" :: float; alter table alameda_2 alter latitude type float using latitude :: float; alter table alameda_2 alter longitude type float using longitude :: float;
もう一つ、テキストから
\pset null <NULL> Null display is "<NULL>". select distinct charter, magnet, title1school, title1schoolwide from alameda_2; +---------+--------+--------------+------------------+ | charter | magnet | title1school | title1schoolwide | +---------+--------+--------------+------------------+ | Yes | <NULL> | No | <NULL> | | Yes | <NULL> | Yes | Yes | | No | <NULL> | No | <NULL> | | No | No | <NULL> | <NULL> | | No | No | No | <NULL> | | No | <NULL> | Yes | No | | Yes | <NULL> | Yes | No | | No | <NULL> | Yes | Yes | | Yes | <NULL> | <NULL> | <NULL> | +---------+--------+--------------+------------------+ (9 rows)
空文字があると
alter table alameda_2 alter charter type bool using charter :: bool; alter table alameda_2 alter magnet type bool using magnet :: bool; alter table alameda_2 alter title1school type bool using title1school :: bool; alter table alameda_2 alter title1schoolwide type bool using title1schoolwide :: bool;
