このcsvファイルをPostgresqlのDBのテーブルの一つとして追加したいと思います。また、X_CODE, Y_CODEは、それぞれ、経度・緯度を現わしており、X_CODE, Y_CODEの後ろには、将来のpostGISの利用を想定して、X_CODE, Y_CODEを示すthe_geomという列を作って、そこにgeometryを入力しておきたいです。

このcsvファイルをPostgresqlのDBのテーブルの一つとして追加したいと思います。また、X_CODE, Y_CODEは、それぞれ、経度・緯度を現わしており、X_CODE, Y_CODEの後ろには、将来のpostGISの利用を想定して、X_CODE, Y_CODEを示すthe_geomという列を作って、そこにgeometryを入力しておきたいです。

 

goc_KAWASAKI.csv

tsubame_db=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+----------
public | configuration | table | postgres
public | log_pass_tsubame | table | postgres
public | pointsofinterest | table | postgres
public | resi_tsubame | table | postgres
public | spatial_ref_sys | table | postgres
public | stop_tsubame | table | postgres
public | tsubame24_logs | table | postgres
public | ways | table | postgres
public | ways_vertices_pgr | table | postgres
(9 rows)

以下、goc_KAWASAKI (1).csv を tsubame_db に 新規テーブルとして取り込み、X_CODE(経度)・Y_CODE(緯度) から PostGIS の geometry(Point) 列 the_geom を作る手順です。
（このCSVは cp932/Shift-JIS系 の可能性が高いので、その前提で書きます。ヘッダは PREF, CITY, PREF_NAME, CITY_NAME, S_NAME, JINKO, SETAI, X_CODE, Y_CODE, the_geom でした。）

1) 事前確認（PostGISが有効か）

spatial_ref_sys が見えているので通常はPostGIS導入済みですが、念のため。

\c tsubame_db

-- PostGISが入っているか確認
SELECT postgis_full_version();

-- もし未導入なら（エラーになったら）導入
CREATE EXTENSION IF NOT EXISTS postgis;

2) 取り込み先テーブルを作成

テーブル名は例として goc_kawasaki にしています（任意で変更）。

ポイント：

  • CSV側に the_geom 列が存在するが、中身は空の想定

  • COPY時に空文字を NULL 扱いにできれば、最初から geometry 型で受けられる

  • まずは素直に the_geom geometry(Point,4326) を作っておき、空はNULLで入れる運用にする

DROP TABLE IF EXISTS public.goc_kawasaki;

CREATE TABLE public.goc_kawasaki (
pref integer,
city integer,
pref_name text,
city_name text,
s_name text,
jinko integer,
setai integer,
x_code double precision, -- 経度
y_code double precision, -- 緯度
the_geom geometry(Point, 4326)
);

※ SRID は、経度緯度（WGS84）前提で 4326 にしています。

3) CSVを取り込む（\copy 推奨）

psql で \copy を使うと「psqlを実行しているマシンのローカルファイル」を読めます。
いまのあなたの状況（Ubuntuでpsql実行）なら、そのUbuntu上にCSVを置いてパス指定が簡単です。

3-1) CSVをUbuntu上の分かりやすい場所へ

例：

cp "goc_KAWASAKI (1).csv" /tmp/goc_KAWASAKI.csv

3-2) 取り込み（Shift-JIS系）

psqlで：

\copy public.goc_kawasaki (pref, city, pref_name, city_name, s_name, jinko, setai, x_code, y_code, the_geom)
FROM '/tmp/goc_KAWASAKI.csv'
WITH (FORMAT csv, HEADER true, ENCODING 'SJIS', NULL '', QUOTE '"', DELIMITER ',');

  • NULL ''：空文字をNULLとして扱う（the_geom が空でもNULLで入る）

  • もし ENCODING 'SJIS' でコケたら、次を試す：

    • ENCODING 'WIN932'

    • ENCODING 'CP932'（環境により不可のことがあります）

4) the_geom を X_CODE/Y_CODE から生成（空欄を埋める）

CSVの the_geom が空で入っている前提で、経度緯度から Point を作って入れます。

UPDATE public.goc_kawasaki
SET the_geom = ST_SetSRID(ST_MakePoint(x_code, y_code), 4326)
WHERE the_geom IS NULL
AND x_code IS NOT NULL
AND y_code IS NOT NULL;

念のため件数確認：

SELECT
COUNT(*) AS total,
COUNT(the_geom) AS geom_filled
FROM public.goc_kawasaki;

5) 空間インデックス（GiST）を作る（将来のPostGIS利用を見据えて）

CREATE INDEX goc_kawasaki_the_geom_gix
ON public.goc_kawasaki
USING GIST (the_geom);ANALYZE public.goc_kawasaki;

6) 動作確認（例：近傍検索ができるか）

例として「宮前区役所あたり」の点を入れて、近い順に出す例：

SELECT s_name, jinko, setai,
ST_Distance(the_geom::geography, ST_SetSRID(ST_MakePoint(139.588, 35.585), 4326)::geography) AS dist_m
FROM public.goc_kawasaki
WHERE the_geom IS NOT NULL
ORDER BY dist_m
LIMIT 10;

tsubame_db=# select * from goc_kawasaki ;
tsubame_db=# SELECT s_name, jinko, setai,
tsubame_db-# ST_Distance(the_geom::geography, ST_SetSRID(ST_MakePoint(139.588, 35.585), 4326)::geography) AS dist_m
tsubame_db-# FROM public.goc_kawasaki
tsubame_db-# WHERE the_geom IS NOT NULL
tsubame_db-# ORDER BY dist_m
MIT tsubame_db-# LIMIT 10;
s_name | jinko | setai | dist_m
--------------+-------+-------+--------------
宮前平一丁目 | 3251 | 1782 | 96.2444246
宮前平三丁目 | 5415 | 2224 | 320.07881687
馬絹一丁目 | 4139 | 1978 | 399.75807695
宮崎二丁目 | 4705 | 2500 | 478.52073818
宮崎一丁目 | 3832 | 1867 | 481.01455816
馬絹二丁目 | 1499 | 683 | 575.12586008
小台二丁目 | 4653 | 2200 | 637.42953694
宮前平二丁目 | 4857 | 1838 | 650.81722322
宮崎五丁目 | 2053 | 992 | 707.74457766
宮崎三丁目 | 4114 | 1908 | 757.94757272
(10 rows)

tsubame_db=#

つまずきやすい点（最短で切り分け）

  • \copy のパスは「psqlを動かしている側」のファイルパス。サーバ側 COPY とは違う

  • 文字化け・取り込み失敗はエンコーディング起因が多い（SJIS / WIN932 を切替）

  • the_geom をCSVから直接入れる運用にしたい場合は、CSV側をWKT（例 POINT(139.58 35.58)）にして取り込む設計に変えるのが確実

必要なら、このCSVの列型（JINKO/SETAI が空文字を含む等）に合わせて、**stagingテーブル（全部textで受ける→型変換して本テーブルへINSERT）**の安全手順も提示できます。

 

未分類

Posted by ebata