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

2022年7月2日

まず基本形

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文で、ずっと悩んでいるわけにもいきませんので

------

その後、この本のことを思い出して、p.34の記載を参考にして、やってみました。

utsu_tram_db3=# SELECT seq, node, ST_AsText(b.the_geom) AS "Coordinates" FROM pgr_dijkstra('SELECT gid as id, source, target, cost FROM ways', 2, 59, directed:=false) a INNER JOIN ways b ON (a.node = b.gid) ORDER BY seq;

1 | 2 | LINESTRING(140.01202169824 36.57871025496,140.01210960589 36.57850940596)
2 | 3 | LINESTRING(140.01210960589 36.57850940596,140.0145021 36.5730429,140.0146136 36.5727757,140.0147074 36.5725284,140.01472847292 36.57242810487)
3 | 6 | LINESTRING(140.01472847292 36.57242810487,140.01474259746 36.57236088004)
4 | 7 | LINESTRING(140.01474259746 36.57236088004,140.0147498 36.5723266,140.01474930095 36.57229596036,140.0147445 36.5720012,140.0147357 36.5718519,140.0147215 36.5716942,140.0146525 36.5713687,140.0146383 36.5712934,140.0142569 36.5705335,140.0132318 36.5684315,140.0125081 36.5670258,140.0114588 36.5649136)
5 | 41995 | LINESTRING(140.0114588 36.5649136,140.0113901 36.5648283,140.0112966 36.5647907,140.0112113 36.5647891,140.0109652 36.5648276,140.01083814578 36.56486337469)
6 | 10 | LINESTRING(140.01083814578 36.56486337469,140.01068264711 36.56490715847)

(攻略)

というところまでできました。
で、これを参考にして、

utsu_tram_db3=# SELECT seq, source, edge, x1, y1 FROM pgr_dijkstra('SELECT gid as id, source, target, cost FROM ways', 2, 59, directed:=false) a INNER JOIN ways b ON (a.edge = b.gid) ORDER BY seq;
seq | source | edge | x1 | y1
-----+--------+-------+-----------------+----------------
1 | 2 | 39626 | 140.01202169824 | 36.57871025496
2 | 3 | 39627 | 140.01210960589 | 36.57850940596
3 | 6 | 42190 | 140.01472847292 | 36.57242810487
4 | 7 | 58678 | 140.01474259746 | 36.57236088004
5 | 41995 | 48370 | 140.0114588 | 36.5649136
6 | 10 | 42191 | 140.01083814578 | 36.56486337469
7 | 11 | 42192 | 140.01068264711 | 36.56490715847
8 | 14 | 39629 | 140.00672391747 | 36.56601660123
9 | 16 | 42193 | 140.00534360203 | 36.56639931881
10 | 18 | 39631 | 139.99881746427 | 36.56760356174
11 | 20 | 42194 | 139.99785322163 | 36.56762649318
12 | 22 | 42195 | 139.99322640365 | 36.56769789956
13 | 23 | 42197 | 139.99279862059 | 36.56770561742
14 | 26 | 39632 | 139.9873020073 | 36.56744076372
15 | 27 | 58694 | 139.98637655778 | 36.56719243017
16 | 42011 | 58693 | 139.9849076 | 36.5669615
17 | 42010 | 59395 | 139.9834987 | 36.5659784
18 | 42653 | 58677 | 139.9833653 | 36.5651397
19 | 41994 | 58676 | 139.9831305 | 36.5639836
20 | 41993 | 58675 | 139.9828407 | 36.5628279
21 | 41992 | 48369 | 139.9823791 | 36.5562514
22 | 30 | 58674 | 139.98257030036 | 36.55573422149
23 | 41991 | 48368 | 139.9826931 | 36.5551828
24 | 31 | 39646 | 139.98315132981 | 36.55393844125
25 | 62 | 39633 | 139.98318348014 | 36.55383009795
26 | 34 | 39634 | 139.98429467116 | 36.5475784165
27 | 36 | 44770 | 139.97637588623 | 36.54492018008
28 | 33441 | 59396 | 139.9758737 | 36.5448171
29 | 42654 | 58691 | 139.97571 | 36.5447853
30 | 42008 | 58673 | 139.9727346 | 36.5443785
31 | 41990 | 58692 | 139.9723398 | 36.5443964
32 | 42009 | 58689 | 139.9719943 | 36.5444584
33 | 42006 | 58690 | 139.9688728 | 36.5456984
34 | 42007 | 58683 | 139.9684653 | 36.5458879
35 | 42000 | 58682 | 139.9656822 | 36.5472156
36 | 41999 | 58688 | 139.9644358 | 36.5478259
37 | 42005 | 48960 | 139.9641365 | 36.5479761
38 | 33444 | 58681 | 139.96349045574 | 36.54828227973
39 | 41998 | 58672 | 139.9619295 | 36.5490366
40 | 41989 | 58671 | 139.9600165 | 36.5499581
41 | 41988 | 58846 | 139.9540272 | 36.5527689
42 | 42143 | 59676 | 139.9519912 | 36.5538036
43 | 42943 | 59406 | 139.9492115 | 36.5546546
44 | 42665 | 48588 | 139.9490753 | 36.5546663
45 | 38 | 58685 | 139.94481625362 | 36.5547848398
46 | 42002 | 58684 | 139.9412636 | 36.5548806
47 | 42001 | 58680 | 139.940733 | 36.5548939
48 | 41997 | 48373 | 139.9399581 | 36.5549218
49 | 39 | 58669 | 139.93905845231 | 36.55494655098
50 | 41986 | 59675 | 139.9384363 | 36.5549609
51 | 42942 | 59674 | 139.9380896 | 36.5549625
52 | 42941 | 58687 | 139.9368167 | 36.5558301
53 | 42004 | 58686 | 139.936282 | 36.5567211
54 | 42003 | 48374 | 139.9353468 | 36.5569036
55 | 41 | 39637 | 139.92919004082 | 36.55704773078
56 | 44 | 39639 | 139.92333810575 | 36.55725460701
57 | 46 | 44769 | 139.9223235539 | 36.55728625609
58 | 33440 | 39640 | 139.9182188 | 36.5574198
59 | 48 | 39641 | 139.91623289881 | 36.55749040041
60 | 49 | 60589 | 139.9150276012 | 36.55753526085
61 | 43716 | 60590 | 139.9137825 | 36.5575688
62 | 43717 | 48847 | 139.9092969 | 36.5578313
63 | 52 | 39643 | 139.90814263916 | 36.55792109617
64 | 54 | 39644 | 139.90721660195 | 36.55798632222
65 | 56 | 42199 | 139.90390213698 | 36.55824095683
66 | 57 | 39645 | 139.90305460077 | 36.55830618413
(66 rows)

で、やっとできました。soruceのところは、nodeだのedgeだの入れてみましたが、sourceが正解のようです。なお最後のnode 59は出てきません。edgeなので、終端は表示されない、ということでしょう。

エクセルで、x1,y1を表示してみました。

このSQL文の意味ですが、こんな感じです。

SELECT
seq, # ダイクストラの計算結果"a"から出てくる"seq" ("a.seq"と記載してもいい)
source, # ダイクストラの計算結果"a"から出てくる"source"("a.source"と記載してもいい)
edge, # ダイクストラの計算結果"a"から出てくる"edge"("a.edge"と記載してもいい)
x1, # ダイクストラの計算結果"a"から出てくる"x1"("a.x1"と記載してもいい)
y1, # ダイクストラの計算結果"a"から出てくる"y1"("a.y1"と記載してもいい)
# の4つを表示してね
FROM
pgr_dijkstra('SELECT gid as id, source, target, cost FROM ways', 2, 59) a
# ダイクストラの計算結果(テーブル扱い)を"a"とする
INNER JOIN ways b
# "ways"のテーブルを"b"とする
ON
(a.edge = b.gid)
# "a"の"node"の値と、"b"の"gid"の値が一緒の場合
ORDER BY
seq;
# 出力結果をseqの値でソートする

で、注意ですが、上記のedgeとnodeを取り違えると、こんな感じになってしまいますので、注意して下さい。

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

Posted by ebata