PostgresqlのGIS DBからとみおかーとルート情報のみを削除する
背景
現在、tomioka_db_cを中心にコーディングを展開していますが、とみおかーとのルートの入っていない(バスのルートだけが入っている)地図が必要となりました。
課題
JSONからとみおかーとのルートを取り除いて地図を作り直すこともできますが、ノード番号ずれが発生するという問題が生じます
課題を解決する手段
つまるところ、とみおかーとのルート情報だけを、tomioka_db_cから取り除ければいいのです。
実施例
まず、
で、実験用のtomioka_db_c_trialを作成しました。
現時点で分かっていること
(1)とみおかーとルート用のnode番号は、1～181の連番である。
(2)とみおかーとルート用のwayには、"TomioCart"の属性情報が入っている
(3)これを、単に、
public | ways_vertices_pgr | table | postgres
からDeleteすれば良いのかどうかは不明
nodeの削除(最初のトライアル) →失敗
DELETE FROM ways_vertices_pgr WHERE id >= 1 AND id <= 181;
すると、こんなメッセージを受けて、失敗します。
tomioka_db_c_trial=# DELETE FROM ways_vertices_pgr WHERE id >= 1 AND id <= 181;
ERROR: update or delete on table "ways_vertices_pgr" violates foreign key constraint "ways_source_fkey" on table "ways"
DETAIL: Key (id)=(1) is still referenced from table "ways".
削除操作を行う前に、この外部キー制約を解除する必要があります。"
とのことのようなので、最初はwaysの削除に変更をします。
wayの削除(最初のトライアル)
DELETE FROM ways WHERE name = 'TomioCart';
とこちらはサクっと消えました。
あれ？ TomioCartが消えていない？
QGISからエントリーを削除して、再度読み込みをしたら
ちゃんとwayが消えていました。
nodeの削除(2回目のトライアル) →やはり失敗
chatGPTに相談したら、以下のような指示を受けました。
ALTER TABLE ways DROP CONSTRAINT ways_source_fkey;
しかし、
tomioka_db_c_trial-# ALTER TABLE ways DROP CONSTRAINT ways_source_fkey; ERROR: syntax error at or near "ALTER" LINE 2: ALTER TABLE ways DROP CONSTRAINT ways_source_fkey; と言われました
SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_name = 'ways_vertices_pgr';
ここから手をやきました。
多分、まだ1～181のノードを参照しているwaysのエントリーが残っているので、消せないのだろうと推測しました。
で、まずは、sourceに21番を持っているwayのエントリーを探してみました。
tomioka_db_c_trial=# select * from ways where source = 21;
gid | osm_id | tag_id | length | length_m | name | source | target | source_osm | target_osm | cost | reverse_cost | cost_s | reverse_cost_s | rule | one_way | oneway | x1 | y1 | x2 | y2 | maxspeed_forward | maxspeed_backward | priority | the_geom
------+--------+--------+------------------------+-------------------+------+--------+--------+------------+------------+------------------------+------------------------+--------------------+--------------------+------+---------+---------+-----------------+----------------+-------------+------------+------------------+-------------------+----------+--------------------------------------------------------------------------------------------
1258 | 104020 | 112 | 3.9727662820583996e-05 | 3.612272689729353 | | 21 | 899 | 102079 | 2958989369 | 3.9727662820583996e-05 | 3.9727662820583996e-05 | 0.2600836336605134 | 0.2600836336605134 | | 0 | UNKNOWN | 139.62560711503 | 35.36760885871 | 139.6256468 | 35.3676107 | 50 | 50 | 2.5 | 0102000020E6100000020000005C6636F9047461408EF09CCE0DAF41401B1B704C0574614070140FDE0DAF4140
(1 row)
で、このエントリーを消去しました。
tomioka_db_c_trial=# DELETE FROM ways WHERE source = 21;
DELETE 1
tomioka_db_c_trial=# select * from ways where osm_id = 102058;
gid | osm_id | tag_id | length | length_m | name | source | target | source_osm | target_osm | cost | reverse_cost | cost_s | reverse_cost_s | rule | one_way | oneway | x1 | y1 | x2 | y2 | maxspeed_forward | maxspeed_backward | priority | the_geom
-----+--------+--------+--------+----------+------+--------+--------+------------+------------+------+--------------+--------+----------------+------+---------+--------+----+----+----+----+------------------+-------------------+----------+----------
(0 rows)
たしかに消えています。
で、今度は、ways_vertices_pgr の方を消してみました
tomioka_db_c_trial=# DELETE FROM ways_vertices_pgr WHERE id = 21;
DELETE 1
tomioka_db_c_trial=# SELECT * FROM ways_vertices_pgr;
id | osm_id | eout | lon | lat | cnt | chk | ein | the_geom
------+------------+------+--------------+-------------+-----+-----+-----+----------------------------------------------------
(前略)
20 | 102078 | | 139.62563228 | 35.36755641 | | | | 0101000020E6100000C1D1FD2D05746140C4EAA8160CAF4140
22 | 102080 | | 139.62561830 | 35.36767727 | | | | 0101000020E61000000979AB1005746140ABEC760C10AF4140
(後略)
あっさり消えていました。
ということは、source またはtargetが 1～181に関するwayを全部消せば、ways_vertices_pgr の方も消せるはず
ちょっと怖かったけどやってみることにしました。
tomioka_db_c_trial=# DELETE FROM ways WHERE source >= 1 and source <=181;
DELETE 96
tomioka_db_c_trial=# DELETE FROM ways WHERE target >= 1 and target <=181;
DELETE 81
おお！ 完全にwayが消えました。
では、ふたたび
DELETE FROM ways_vertices_pgr WHERE id >= 1 AND id <= 181;
を実施します
tomioka_db_c_trial=# DELETE FROM ways_vertices_pgr WHERE id >= 1 AND id <= 181;
DELETE 180
やっと消えました
tomioka_db_c_trial=# SELECT * FROM ways_vertices_pgr;
id | osm_id | eout | lon | lat | cnt | chk | ein | the_geom
------+------------+------+--------------+-------------+-----+-----+-----+----------------------------------------------------
182 | 102254 | | 139.62956343 | 35.36605302 | | | | 0101000020E61000007690336225746140082C51D3DAAE4140
183 | 102255 | | 139.62952386 | 35.36602637 | | | | 0101000020E6100000F1EB3A0F257461401A07C7F3D9AE4140
184 | 102256 | | 139.62949515 | 35.36599335 | | | | 0101000020E6100000F65C05D3247461403A74BFDED8AE4140
総括
postgresqlのpostGISのDBから新規に作成したnodeとwayを削除するには、以下の手順を踏むこと
(Step.1) waysから、新規node,wayに関する全部エントリーを削除すること。今回の場合は、以下の3つ
DELETE FROM ways WHERE name = 'TomioCart';
DELETE FROM ways WHERE source >= 1 and source <=181;
DELETE FROM ways WHERE target >= 1 and target <=181;
(Step.2)
ways_vertices_pgrからターゲットのNode情報を削除すること。今回の場合は以下の1つ。
DELETE FROM ways_vertices_pgr WHERE id >= 1 AND id <= 181;
(Step.3)
最後に、tomioka_db_c_trial を、tomioka_db_d に変更する(コピーしてから、tomioka_db_c_trialを消すこと)