pgr_dijkstra()で算出したノードの座標を得る方法

2022年5月6日

まず基本形

utsu_db=# SELECT seq, node, edge, cost, agg_cost FROM pgr_dijkstra('SELECT gid as id, source, target,length_m as cost FROM ways',100, 600, false);

seq | node | edge | cost | agg_cost
-----+-------+-------+--------------------+--------------------
1 | 100 | 41 | 27.7006591508524 | 0
2 | 18996 | 21479 | 63.36986127215735 | 27.7006591508524
3 | 6119 | 24518 | 17.68514641657604 | 91.07052042300975

"node"を、"source"というキーで扱えるようにする

utsu_db=# SELECT node as source FROM pgr_dijkstra('SELECT gid as id, source, target,length_m as cost FROM ways',100, 600, false);
source
--------
100
18996
6119

さらに、"source"を検索キーとして、座標x1, y1を出力する

utsu_db=# select x1,y1 from ways where source in (SELECT node as source FROM pgr_dijkstra('SELECT gid as id, source, target,length_m as cost FROM ways',100, 600, false));
x1 | y1
-------------+------------
139.8867509 | 36.5570485
139.8842018 | 36.5574191
139.9070779 | 36.5521857

と思ったけど、この最後のやつ、ダイクストラの順番が壊れることが分かりました。
今、修正の検討中です。


なんとかSQL1文で片付けたかったのですが、どうにも上手くいきませんので、こういうコードで対応するこにしました。

//rows, err = db.Query("select x1,y1 from ways where source in (SELECT node as source FROM pgr_dijkstra('SELECT gid as id, source, target,length_m as cost FROM ways', $1::bigint , $2::bigint , false))", o_source, d_source)
	rows, err = db.Query("SELECT node as source FROM pgr_dijkstra('SELECT gid as id, source, target,length_m as cost FROM ways', 100 , 600 , false)")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	x1, y1 := -1.0, -1.0

	for rows.Next() {

		if err := rows.Scan(&source); err != nil {
			fmt.Println(err)
		}
		// 同じ内容の緯度軽度が2行以上表示される場合があるので、"limit 1"で1行に制限
		rows2, err := db.Query("SELECT source, x1, y1 from ways where source = $1::bigint limit 1", source)
		if err != nil {
			log.Fatal(err)
		}

		for rows2.Next() {
			if err := rows2.Scan(&source, &x1, &y1); err != nil {
				fmt.Println(err)
			}
			fmt.Println(source, x1, y1)
		}

	}

まあ、これでダイクストラの順番は守られるようです。

100 139.9308613 36.5364704
18996 139.9305846 36.536582
6119 139.9299632 36.5368549
19331 139.9297872 36.5369272
18976 139.9287925 36.5373735
6120 139.9285284 36.5375426
2595 139.9284283 36.5376435
(中略)
13510 139.8719095 36.558477
13495 139.8713668 36.5586478
556 139.8699614 36.5584908
548 139.868783 36.5585636
600 139.8683731 36.5585918

不細工ですが、仕方ないですね。
(どなたか、"where in" "order by seq" 等で対処する方法を思いつかれた方は、ご連絡下さい) 

SQL文で、ずっと悩んでいるわけにもいきませんので

2022年5月6日2022/05,江端さんの技術メモ

Posted by ebata