前回pgroutingの使い方を説明しました。

pgroutingの経路検索はラインの端点を始点終点とするものです。
ライン内の点を終点とすることはできません。
なので、ラインを分割することにより、ラインの端点とすることで分割して距離を出すという方法の紹介です。

参考サイト
第8章 PostGISリファレンス
pgRouting – GeoPacific.org － ジオパシフィック

前回関数にできたものを紹介できたらいいなっと言っていましたが、できませんでした。
参考にしていたサイトでは、関数にされているものが、置かれています。
ですが、PostGISのバージョン違いで私の環境では実行できません。
関数名を変更すれば実行可能です。

st_line_locate_point > st_linelocatepoint
st_line_substring > st_linesubstring

参考サイトと動作はほぼ同一です。

1.ポイントに対して最短のラインジオメトリを取得する(ｓｔ_dwithinとｓｔ_shortestlineを使用)
2.最短のラインジオメトリに対してのポイント位置を取得する(st_linelocatepointを使用)
3.ラインジオメトリごとに取得したポイント位置でラインを作成(st_linesubstringを使用)

動作的に違うところはst_linelocatepointで取得した値が0と1の場合、無視しているところ位です。

簡単に使用した関数の説明
bool st_dwithin(a.geometry, b.geometry, numeric)
bのジオメトリがaのジオメトリのnumericの範囲内にはいっていれば、TRUE

geometry st_shortestline(a.geometry, b.geometry)
aのジオメトリとbのジオメトリの最短のラインを作成

numeric st_linelocatepoint(a.geometry, b.geometry)
aのラインジオメトリでbのジオメトリとの一番近い位置(0-1)を返します。

geometry st_linesubstring(geometry, numeric, numeric)
ラインジオメトリの始点位置・終点位置を指定すると、その区間のジオメトリを返します

使用したクエリ

-- ラインとポイントの組み合わせ作成
drop table if exists temp_point;
select
	p.id as pid,
	l.id as id, -- line id
	l.geom, -- line geometry
	st_length(st_shortestline(l.geom, p.geom)) as len, -- line との距離
	st_linelocatepoint(l.geom, p.geom) as locate_point-- lineでの最短位置
into temp temp_point 
from point as p
left join line as l on st_dwithin(p.geom, l.geom, 100); -- 100m内のラインとポイントを結びつける

-- ポイントと一番近いラインジオメトリの組み合わせ以外を削除する
drop table if exists temp2_point;
SELECT *
into temp2_point
FROM temp_point AS p 
WHERE NOT EXISTS (
    SELECT 1
    FROM temp_point AS t
    WHERE p.pid = t.pid
    AND p.len > t.len
) and locate_point not in (0,1);

-- ラインごとに配列を回してラインを作成していきます
drop table if exists route;
 SELECT id,
     st_linesubstring(geom, a[s], a[s+1]) as geom
 into route
FROM (
select
    id,
    geom,
    generate_subscripts(
        array_prepend(
            0::double precision,
            array_append(
                array_agg(locate_point order by locate_point),
                1::double precision)), 1) as s,
                array_prepend(0::double precision, array_append(array_agg(locate_point order by locate_point),1::double precision)) as a
from (
    -- 位置の重複を削除
    select
        DISTINCT
        id,
        geom,
        locate_point
    from
        temp2_point
    ) a
group by id, geom
) foo
where s <> array_length(a, 1);

-- ポイントの関与しなかった部分を追加します
insert into route
select
    id, geom as geom
from line as l
where
 NOT EXISTS (
    SELECT 1
    FROM route AS t
    WHERE t.id = l.id
);