手動で収集した地物データのCSVからgeoentitydbを作るまでの手順

2024年11月30日2024年12月2日

改めてGeoEntityDBの作り方の手順を示して下さい。

以下に、GeoEntityDB を作成する手順を改めて示します。

GeoEntityDBの作成手順

1. GeoEntityDBの作成

1. PostgreSQLに接続します。
   psql -U postgres

    

2. 新しいデータベースを作成し、PostGISを有効化します。
   CREATE DATABASE GeoEntityDB; \c GeoEntityDB CREATE EXTENSION postgis; CREATE EXTENSION pgrouting; -- 必要に応じて有効化

    

2. 地物テーブルの作成

地物データを格納するためのテーブルを作成します。

1. テーブルスキーマを定義します。以下はカテゴリ付きの地物データ用のスキーマです。
   CREATE TABLE geo_entities ( id SERIAL PRIMARY KEY, -- 自動生成の一意識別子 category VARCHAR(10), -- カテゴリ (例: A1, B1, D2) stay_time INTEGER, -- 滞在時間 (分) geom GEOMETRY(POINT, 4326) -- 座標データ (EPSG:4326の緯度経度) );

   # id SERIAL PRIMARY KEY の部分は自動的にインポートされるので、csvの先頭の番号は行削除しておくこと

2. インデックスを作成して検索性能を向上させます。
   CREATE INDEX geo_entities_geom_idx ON geo_entities USING GIST (geom);

    

3. CSVデータのインポート

CSVファイルから地物データをインポートします。

1. 一時テーブルを作成（緯度・経度データを変換するために使用）。
   CREATE TEMP TABLE temp_geo_entities ( category VARCHAR(10), stay_time INTEGER, latitude DOUBLE PRECISION, longitude DOUBLE PRECISION );

    

2. CSVをインポート（ファイルのパスは実際のCSVファイルの場所を指定してください）。
   COPY temp_geo_entities (category, stay_time, latitude, longitude) FROM '/path/to/geo_entities.csv' DELIMITER ',' CSV HEADER;

   # 実は、'/path/to/geo_entities.csv'の部分で酷く転けた記憶があるが、結果的にインポートできてしまったので、忘れた。

3. 本テーブルに挿入（緯度・経度を POINT 型に変換して挿入）。
   INSERT INTO geo_entities (category, stay_time, geom) SELECT category, stay_time, ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) FROM temp_geo_entities;

    

4. データが正しくインポートされたか確認します。
   SELECT * FROM geo_entities LIMIT 10;

   #ここから先は試していないが、メモに残しておく。

4. 既存DBとの連携設定

方法 1: postgres_fdw を使った連携

hakata_ex_cost_db（道路ネットワークDB）と GeoEntityDB を連携させます。

1. postgres_fdw を有効化（GeoEntityDBで実行）。
   CREATE EXTENSION postgres_fdw;

    

2. 外部サーバーを登録。
   CREATE SERVER hakata_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'hakata_ex_cost_db', host 'localhost');

    

3. 外部DBへのユーザーマッピングを作成。
   CREATE USER MAPPING FOR current_user SERVER hakata_server OPTIONS (user 'postgres', password 'your_password');

    

4. 必要なテーブルをインポート（例: 道路ネットワークテーブル）。
   IMPORT FOREIGN SCHEMA public FROM SERVER hakata_server INTO public;

    

5. テーブルが使用可能か確認。
   SELECT * FROM ways LIMIT 10;

    

5. 経路計算の設定

最短経路計算の基本例

エージェントの現在地から目的地（地物）までの経路を計算します。

1. エージェントの現在地に最も近いノードを取得。
   SELECT id FROM ways_vertices_pgr ORDER BY geom <-> ST_SetSRID(ST_MakePoint(130.3980676, 33.59543503), 4326) LIMIT 1;

    

2. 地物の位置に最も近いノードを取得。
   SELECT id FROM ways_vertices_pgr ORDER BY geom <-> (SELECT geom FROM geo_entities WHERE id = 1) LIMIT 1;

    

3. Dijkstraアルゴリズムで経路計算。
   SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra( 'SELECT id, source, target, cost FROM ways', start_node_id, end_node_id, directed := false );

    

6. 地物データと経路計算の組み合わせ

以下のようなクエリで、地物データの位置と経路計算を連携させます。

1. エージェントの現在地から15メートル以内の地物を検索(これ、上手く稼動しません。下の方は使って下さい)。
   SELECT id, category, stay_time, ST_Distance(geom, ST_SetSRID(ST_MakePoint(130.3980676, 33.59543503), 4326)) AS distance FROM geo_entities WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(130.3980676, 33.59543503), 4326), 15);

    

2. 発見した地物への最短経路を計算。
   SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra( 'SELECT id, source, target, cost FROM ways', start_node_id, (SELECT id FROM ways_vertices_pgr ORDER BY geom <-> (SELECT geom FROM geo_entities WHERE id = found_geo_id) LIMIT 1), directed := false );

    

まとめ

1. GeoEntityDBの作成とデータインポート:
   • PostGISとpgRoutingを有効化し、CSVデータを地物テーブルに格納。
2. 既存DBとの連携:
   • postgres_fdw を使用して、hakata_ex_cost_db の道路ネットワークを参照。
3. 経路計算の実装:
   • エージェントの現在地と地物データを連携させ、道路ネットワーク上で最短経路を計算。

これにより、エージェントが嗜好に応じて地物に向かう動きをリアルにシミュレーションする仕組みを構築できます。

ーーーーー
こういうミスもやったので、以下のメモに残しておく。

SELECT * FROM geo_entities WHERE category = 'B1'; としたらエントリーがでてきませんが、
SELECT * FROM geo_entities WHERE category = ' B1'; としたらエントリーがでてきました。
categoryの要素でえ最初に空白が入っているものを修正したいのですが、どうしたら良いでしょうか

category の要素の先頭に空白が含まれている場合、それを修正するには TRIM 関数を使用して先頭と末尾の空白を取り除く必要があります。以下に、データを修正する方法を説明します。

手順

1. データの確認

まず、空白が含まれているデータを確認します。

• LIKE ' %' は先頭に空白があるデータを検索します。
• LIKE '% ' は末尾に空白があるデータを検索します。

2. 空白を修正

TRIM 関数を使用して、category カラムから空白を取り除きます。

すべてのエントリを修正する場合

以下のクエリで、空白を削除した値に更新します。

3. 修正結果の確認

修正後に、データが正しく更新されたか確認します。

• クエリの結果が空であれば、すべての空白が正常に削除されたことを意味します。

4. 再度クエリを実行

修正後、以下のようにクエリを実行して正しいデータが取得できるか確認します。

補足

• TRIM の動作: TRIM 関数は、先頭と末尾の両方から空白を削除します（不要なスペースも含む）。
• データの一時バックアップ: データ更新は破壊的変更となるため、念のため更新前にバックアップを取得しておくことをおすすめします。

これで問題が解決するはずです！もしさらなるサポートが必要であればお知らせください。

ざっくりとした使い方

こんな感じででてくる。

以下は、130.4064827799044, 33.591348965846656 から 100メートル以内のジオエンティティを抽出するSQLです。(こっちが正しい)

geoentitydb=# SELECT id, category, stay_time, ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint(130.4064827799044, 33.591348965846656), 4326)::geography) AS distance FROM geo_entities WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(130.4064827799044, 33.591348965846656), 4326)::geography, 100);

id | category | stay_time | distance
------+----------+-----------+-------------
304 | A2 | 60 | 68.03775787
357 | A3 | 120 | 75.80618279
366 | A3 | 120 | 76.48825161
414 | D2 | 180 | 80.65210758
415 | D2 | 180 | 79.93815148
429 | D2 | 180 | 61.23626794
435 | D2 | 180 | 62.50441807
546 | E1 | 480 | 77.36793898
550 | E1 | 480 | 90.09868468
813 | E2 | 300 | 54.40053345
1174 | F2 | 10 | 92.85196166
1217 | F2 | 10 | 89.44133931
1933 | H2 | 30 | 64.5772006
1942 | H2 | 30 | 84.70916514
(14 rows)