m_conn->prepare( // HACK 1. 2文を1文に(PQExec仕様)、2. JOIN周り、 3. ORDER BYを消せないか？
"find_shortest_path_by_dijkstra_calc",
R"(
INSERT INTO s_dijkstra_memoization (source, target, node, edge, length_m)
SELECT
$1, $2, d.node, d.edge, coalesce(ways.length_m, 0) AS length_m
FROM
pgr_dijkstra (
'SELECT gid AS id, source, target, length_m AS cost from ways',
start_vid:=$1::bigint, end_vid:=$2::bigint, directed:=false::boolean
) as d LEFT JOIN ways ON ways.gid = d.edge
WHERE NOT EXISTS (SELECT source, target FROM s_dijkstra_memoization WHERE source = $1 AND target = $2);
)",
//RETURNING node, edge, length_m;
2
);
のところで、問題が発生しているのは分かるのだけど、私にとっては上記な呪文であり、さっぱり分からない。
とりあえず、psql から地道に部分的に動かしてみて、問題点を探っている。
ca_sim=# SELECT gid, source, target, length_m AS cost from ways;
gid | source | target | cost
------+--------+--------+--------------------
1 | 55 | 23 | 121.17749638577058
2 | 844 | 1 | 710.1279290280786
3 | 4807 | 2 | 40.26044319993349
4 | 6296 | 3 | 251.21876036796243
5 | 22 | 4 | 2181.7785758382133
6 | 18 | 6 | 435.2439693722031
7 | 4304 | 6 | 950.7269653866767
8 | 6289 | 7 | 29.068747479235597
となるが、
ca_sim=# SELECT * FROM pgr_dijkstra(
'SELECT gid, source, target, length_m AS cost FROM ways',
2, 3
);
ERROR: Column 'id' not Found
CONTEXT: SQL function "pgr_dijkstra" statement 1
となる。pgr_dijkstraを実行する為には、パラメータの名前まで注意しなければなならないらしい。
ca_sim=# SELECT * FROM pgr_dijkstra(
'SELECT gid AS id, source, target, length_m AS cost FROM ways',
2, 3
);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+--------------------+--------------------
1 | 1 | 2 | 342 | 224.26753559377073 | 0
2 | 2 | 229 | 8773 | 295.36268951709405 | 224.26753559377073
3 | 3 | 6359 | 8771 | 41.04204131422657 | 519.6302251108648
4 | 4 | 6357 | 344 | 136.12897946273213 | 560.6722664250914
5 | 5 | 231 | 8688 | 58.55503032553214 | 696.8012458878235
6 | 6 | 6294 | 8690 | 191.73693713817062 | 755.3562762133556
7 | 7 | 6296 | 4 | 251.21876036796243 | 947.0932133515262
8 | 8 | 3 | -1 | 0 | 1198.3119737194886
(8 rows)
くるしまぎれに、
start_vid:=$1::bigint, end_vid:=$2
の部分に、
1、2
を、実数でベタ書きしたら、エラーが取れて、シミュレーションが走りだした(原因不明)
しかし、
start_vid:=$1::bigint, end_vid:=$2 → $1, $2
では、
ERROR: function pgr_dijkstra(unknown, unknown, unknown, directed => boolean) is not unique
LINE 7: pgr_dijkstra(
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
Error:ERROR: function pgr_dijkstra(unknown, unknown, unknown, directed => boolean) is not unique
LINE 7: pgr_dijkstra(
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
となってしまう。
ブログを読んで頂いたからから、以下のアドバイスを頂いたので、その観点より挑戦。
上の箇所の$1や$2は、SQLインジェクションされないためのprepare文のプレースホルダーです。
ドキュメントの該当箇所は次のものです。
https://www.postgresql.org/docs/current/sql-prepare.html
$1と$2に該当するものをうしろから代入してあげれば動く（はず）と思います。
MySQLは、たしか「?」がプレースホルダで、SQLiteも「?」がプレースホルダ（https://docs.python.org/ja/3/library/sqlite3.html をみたら、合ってました）だったように覚えています。PostgreSQLは$+数字をつかうことになっています。
そこで、このページを印刷しながら、比較して、色々試してみた
pgr_dijkstra(
(SELECT gid AS id, source, target, length_m AS cost from ways),
start_vid:=$1::bigint, end_vid:=$2::bigint, directed:=false::boolean
) as d LEFT JOIN ways ON ways.gid = d.edge
ERROR: subquery must return only one column
LINE 8: (SELECT gid AS id, source, target, length_m AS cost fr...
^
Error:ERROR: subquery must return only one column
LINE 8: (SELECT gid AS id, source, target, length_m AS cost fr...
FROM
pgr_dijkstra(
'SELECT gid AS id, source, target, length_m AS cost from ways',
(start_vid:=$1::bigint), (end_vid:=$2::bigint), directed:=false::boolean
) as d LEFT JOIN ways ON ways.gid = d.edge
ERROR: syntax error at or near ":="
LINE 9: (start_vid:=$1::bigint), (end_vid:=$2::bigint), direct...
^
Error:ERROR: syntax error at or near ":="
LINE 9: (start_vid:=$1::bigint), (end_vid:=$2::bigint), direct...
すでに試してみたが、これもやってみた
pgr_dijkstra(
'SELECT gid AS id, source, target, length_m AS cost from ways',
$1, $2, directed:=false::boolean
) as d LEFT JOIN ways ON ways.gid = d.edge
ERROR: function pgr_dijkstra(unknown, unknown, unknown, directed => boolean) is not unique
LINE 7: pgr_dijkstra(
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
Error:ERROR: function pgr_dijkstra(unknown, unknown, unknown, directed => boolean) is not unique
LINE 7: pgr_dijkstra(
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
なんかエラーが増えてきたようだ(unknownが3つになった)。
他の行と比較してみながら、start_vid と end_vid を消してみた(これが記載されていない別の行があったので)
FROM
pgr_dijkstra(
'SELECT gid AS id, source, target, length_m AS cost from ways',
$1::bigint, $2::bigint, directed:=false::boolean
) as d LEFT JOIN ways ON ways.gid = d.edge
これで動いた。
けど、理由はさっぱり分かりません。$1::bigint, $2::bigint がキャストの書式？
トライアンド&エラーでやろうとすると、こういう目にあう、という良い教訓として下さい。
