Open25
Recent Queries
Yamakyupgadmin4で書いたクエリを置いていきます。
漁る用のゴミ箱とも言います。
Yamakyulinestringを近くのlinestringの両端で切断して最も近いものを取得
select
st_astext((st_dump(st_split(l1.path, st_multi(l2.pt1)))).geom)
from
(values('linestring(0 0, 0 2)')) as l1(path),
(values('point(0 1)', 'point(0 3)', 'linestring(0 1, 0 3)')) as l2(pt1, pt2, path)
order by st_distance(l1.path, l2.path)
limit 1;
st_unionはnullを入れたが最後全てがnullになる。emptyなgeometryはいけるので、coalesce(綴り覚えられない)を併用しましょう。
select st_union(coalesce(
hoge.nullablepolygon,
'polygon empty'::geometry(polygon, 4326)
), 'polygon(0 0, 0 1, 1 1, 1 0)') from hoge;
select
st_astext(
st_collect(
(select st_exteriorring(
(
st_dump(
t.path
)
).geom
)
)
)
)
from (values
('multipolygon(((0 0, 0 1, 1 1, 1 0, 0 0)))'),
('multipolygon(((0 2, 0 3, 1 3, 1 2, 0 2)))')
) as t(path);
st_exteriorringのmulti系のテスト。st_collectの中でselect挟まないといけないみたい。
あと、サブクエリは親のfrom見られるの初めて知ったかも。
Yamakyuselect
st_astext((st_dump(st_difference(
'polygon((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry,
st_translate('polygon((0 0, 0 1, 1 0, 0 0))', 0, 0)::geometry
))).geom)
ここで、multipolygonの外環をとるのに一度dumpしてst_exteriorringした後st_collectするというめんどくさい手段を使っていたけど、もしかしてst_bufferのゼロ距離でいいのでは。(1/36500)世紀の大発見なのでは。
select st_astext(st_buffer('multipolygon(((0 0, 0 1, 1 1, 1 0, 0 0)), ((0 2, 0 3, 1 3, 1 2, 0 2)))', 0))
select
st_astext(((st_dumpsegments(st_buffer('linestring(0 0, 0 2)', 1, 1))).geom)) as geom;
create table if not exists kari(
id integer,
geom geometry(LineString, 4326)
);
insert into kari (id, geom) values
(1, 'linestring(0 0, 4 4)'),
(2, 'linestring(3 3, 5 5)'),
(3, 'linestring(0 2, 2 0)')
;
select id, sub_id, st_astext(geom)
from pgr_separatecrossing('select id, geom from kari')
order by id, sub_id
;
drop table if exists kari;
insertの3行目を消すとなにもでない。
重複linestringがあると機能しない。
Yamakyuselect st_crosses('linestring(0 0, 4 4)', 'linestring(3 3, 5 5)');
st_crossesは空間的にクロスしている(?)時にtrueになるのでこれはfalseのようだ。
Yamakyucreate table if not exists kari(
id integer,
geom geometry(LineString, 4326)
);
insert into kari (id, geom) values
(1, 'linestring(0 0, 4 4)'),
(2, 'linestring(3 3, 5 5)'),
(3, 'linestring(0 2, 2 0)'),
(4, 'linestring(-1 -1, 0 0)')
;
WITH edges_table as (
select id, geom from kari
),
get_crossings as (
select
e1.id as id1,
e2.id as id2,
e1.geom as g1,
e2.geom as g2,
st_intersection(e1.geom, e2.geom) as intersecting
from edges_table as e1, edges_table as e2
where e1.id < e2.id and ST_intersects(e1.geom, e2.geom) -- 重なっていたらとりあえず取得
),
point_intersections as ( -- 交点
select
*,
intersecting as point
from get_crossings
where
st_geometrytype(intersecting) = 'ST_Point' and
st_crosses(g1, g2) -- 交点が双方ともに線端である場合を除外
),
linestring_intersections as ( -- 重複線分
select
*,
intersecting as geom
from get_crossings
where st_geometrytype(intersecting) = 'ST_LineString'
),
crossings AS (
SELECT id1, g1, point FROM point_intersections
UNION
SELECT id2, g2, point FROM point_intersections
),
blades AS (
SELECT id1, g1, ST_UnaryUnion(ST_Collect(point)) AS blade
FROM crossings
GROUP BY id1, g1
),
collection AS (
select id1, (st_dump(st_split(st_snap(g1, blade, 0.01), blade))).*
from blades
),
bbb as (
select id1, path[1], geom
from collection
union all
select id1, 10000, geom
from linestring_intersections
),
aaa as (
SELECT
row_number() over()::integer as seq,
id1::bigint, path, geom
FROM bbb
)
select *, st_astext(geom) from aaa;
-- select * from aaa;
drop table if exists kari;
-- select st_crosses('linestring(0 0, 4 4)', 'linestring(3 3, 5 5)');
本当にこれはごみ
Yamakyudrop table if exists edges;
drop table if exists vertices;
CREATE TABLE edges (
id integer generated always as IDENTITY,
source BIGINT,
target BIGINT,
cost FLOAT,
reverse_cost FLOAT,
capacity BIGINT,
reverse_capacity BIGINT,
x1 FLOAT,
y1 FLOAT,
x2 FLOAT,
y2 FLOAT,
geom geometry
);
create table vertices (
id integer generated always as IDENTITY,
in_edges BIGINT[],
out_edges BIGINT[],
x FLOAT,
y FLOAT,
geom geometry
);
insert into edges (geom) values
('linestring(0 0, 4 4)'),
-- ('linestring(3 3, 5 5)'),
('linestring(0 2, 2 0)'),
('linestring(-1 -1, 0 0)')
;
insert INTO vertices (in_edges, out_edges, x, y, geom)
select in_edges, out_edges, x, y, geom from pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
with costs as (select st_length(geom) as cost, st_length(geom) * -1 as reverse_cost from edges)
update edges set
(cost, reverse_cost) =
(c.cost, c.reverse_cost)
from costs c;
ALTER TABLE edges ADD old_id BIGINT;
INSERT INTO edges (old_id, geom)
SELECT id, geom
FROM pgr_separateCrossing('SELECT id, geom FROM edges');
WITH
costs AS (
SELECT
e2.id,
ST_Length(e2.geom) AS cost,
-1 * ST_Length(e2.geom) AS reverse_cost
FROM edges e1 JOIN edges e2 ON (e1.id = e2.old_id)
)
UPDATE edges e
SET (cost, reverse_cost) = (c.cost, c.reverse_cost)
FROM costs AS c WHERE e.id = c.id;
WITH
new_vertex AS (
SELECT ev.*
FROM pgr_extractVertices('SELECT id, geom FROM edges WHERE old_id IS NOT NULL') ev
LEFT JOIN vertices v using(geom)
WHERE v IS NULL)
INSERT INTO vertices (in_edges, out_edges,x,y,geom)
SELECT in_edges, out_edges,x,y,geom FROM new_vertex;
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE source IS NULL AND ST_StartPoint(e.geom) = v.geom;
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE target IS NULL AND ST_EndPoint(e.geom) = v.geom;
select * from vertices;
select *, st_astext(geom) from edges;
drop table if exists edges;
drop table if exists vertices;
pgr_separatecrossingを用いた交差しているだけの場所に点を追加する。
conceptsの内容をコピペしてうごくようにした。
Yamakyu上のクエリはidを自動カウントアップさせるようにしないといけないので、integer generated always as identifyを指定した。old_idの設置も忘れずに。
Yamakyudrop table if exists edges;
drop table if exists vertices;
CREATE TABLE edges (
id integer generated always as IDENTITY,
source BIGINT,
target BIGINT,
cost FLOAT,
reverse_cost FLOAT,
capacity BIGINT,
reverse_capacity BIGINT,
x1 FLOAT,
y1 FLOAT,
x2 FLOAT,
y2 FLOAT,
geom geometry
);
create table vertices (
id integer generated always as IDENTITY,
in_edges BIGINT[],
out_edges BIGINT[],
x FLOAT,
y FLOAT,
geom geometry
);
insert into edges (geom) values
('linestring(0 0, 4 4)'),
('linestring(4.0001 4.0001, 5 5)')
;
insert INTO vertices (in_edges, out_edges, x, y, geom)
select in_edges, out_edges, x, y, geom from pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
with costs as (select st_length(geom) as cost, st_length(geom) * -1 as reverse_cost from edges)
update edges set
(cost, reverse_cost) =
(c.cost, c.reverse_cost)
from costs c;
ALTER TABLE edges ADD old_id BIGINT;
INSERT INTO edges (old_id, geom)
SELECT id, geom
FROM pgr_separateTouching('SELECT id, geom FROM edges');
WITH
costs AS (
SELECT
e2.id,
ST_Length(e2.geom) AS cost,
-1 * ST_Length(e2.geom) AS reverse_cost
FROM edges e1 JOIN edges e2 ON (e1.id = e2.old_id)
)
UPDATE edges e
SET (cost, reverse_cost) = (c.cost, c.reverse_cost)
FROM costs AS c WHERE e.id = c.id;
WITH
new_vertex AS (
SELECT ev.*
FROM pgr_extractVertices('SELECT id, geom FROM edges WHERE old_id IS NOT NULL') ev
LEFT JOIN vertices v using(geom)
WHERE v IS NULL)
INSERT INTO vertices (in_edges, out_edges,x,y,geom)
SELECT in_edges, out_edges,x,y,geom FROM new_vertex;
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE source IS NULL AND ST_StartPoint(e.geom) = v.geom;
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE target IS NULL AND ST_EndPoint(e.geom) = v.geom;
select *, st_astext(geom) from edges;
select st_overlaps('linestring(0 0, 4 4)', 'linestring(3 3, 5 5)');
select st_overlaps('linestring(0 0, 4 4)', 'linestring(0 2, 2 0)');
st_overlapsは完全に同じ位置を走る線はtrueであるが、linestringが交差しているだけや、point,linestring,polygonの異種ジオメトリタイプ同士はfalseのようだ。
この辺ただでさえ難しいのに、リファレンス文書特有の文体のせいで余計わからない。
しかも最後の文書がなぜかst_crossesについて言及しているので混乱する。
Yamakyu| id | geom |
|---|---|
| 1 | LINESTRING(0 0,4 4) |
| 2 | LINESTRING(2 0,0 2) |
| 3 | LINESTRING(0 4,4 0) |
| 4 | LINESTRING(1 0,1 2) |
| id1 | id2 | g1 | g2 | point |
|---|---|---|---|---|
| 1 | 2 | LINESTRING(0 0,4 4) | LINESTRING(2 0,0 2) | POINT(1 1) |
| 1 | 3 | LINESTRING(0 0,4 4) | LINESTRING(0 4,4 0) | POINT(2 2) |
| 1 | 4 | LINESTRING(0 0,4 4) | LINESTRING(1 0,1 2) | POINT(1 1) |
| 2 | 4 | LINESTRING(2 0,0 2) | LINESTRING(1 0,1 2) | POINT(1 1) |
| id1 | g1 | g2 |
|---|---|---|
| 1 | LINESTRING(0 0,4 4) | POINT(2 2) |
| 3 | LINESTRING(0 4,4 0) | POINT(2 2) |
| 2 | LINESTRING(2 0,0 2) | POINT(1 1) |
| 4 | LINESTRING(1 0,1 2) | POINT(1 1) |
| 1 | LINESTRING(0 0,4 4) | POINT(1 1) |
| id1 | st_astext | st_astext |
|---|---|---|
| 1 | LINESTRING(0 0,4 4) | POINT(1 1) |
| 1 | LINESTRING(0 0,4 4) | POINT(2 2) |
| 1 | LINESTRING(0 0,4 4) | POINT(1 1) |
| 2 | LINESTRING(2 0,0 2) | POINT(1 1) |
| 2 | LINESTRING(2 0,0 2) | POINT(1 1) |
| 3 | LINESTRING(0 4,4 0) | POINT(2 2) |
| 4 | LINESTRING(1 0,1 2) | POINT(1 1) |
| 4 | LINESTRING(1 0,1 2) | POINT(1 1) |
pgr_separatecrossings途中テストの結果。
YamakyuWITH
edges_table AS (
SELECT id, geom FROM edges
),
-- 分割しなければならないジオメトリとその分割点を取得
get_crossings AS (
SELECT e1.id id1, e2.id id2, e1.geom AS g1, e2.geom AS g2, ST_Intersection(e1.geom, e2.geom) AS point
FROM edges_table e1, edges_table e2
WHERE e1.id < e2.id AND ST_crosses(e1.geom, e2.geom)
),
-- 一交差点につき一つしか生成されないget_crossingsをlinestring毎に分割するのと同時に、同じカラムは一つにまとめる
crossings AS (
SELECT id1, g1, point FROM get_crossings
UNION
SELECT id2, g2, point FROM get_crossings
),
-- 一つのジオメトリを複数の点で分割する際に複数のpointをmultipointにまとめて、bladeをひとつにする。
blades AS (
SELECT id1, g1, ST_UnaryUnion(ST_Collect(point)) AS blade
FROM crossings
GROUP BY id1, g1
),
-- bladeで切断
collection AS (
SELECT id1, (st_dump(st_split(st_snap(g1, blade, 0.01), blade))).*
FROM blades
)
-- select id1, id2, st_astext(g1), st_astext(g2), st_astext(point) from get_crossings;
-- select id1, st_astext(g1), st_astext(point) from crossings;
-- select id1, st_astext(g1), st_astext(blade) from blades;
SELECT row_number() over()::INTEGER AS seq, id1::BIGINT, path[1], geom
FROM collection;
;
pgr_separatecrossings超適当解釈。(imeは超適当を朝敵等で変換した)
Yamakyudrop table if exists edges;
CREATE TABLE edges (
id integer generated always as IDENTITY,
source BIGINT,
target BIGINT,
cost FLOAT,
reverse_cost FLOAT,
capacity BIGINT,
reverse_capacity BIGINT,
x1 FLOAT,
y1 FLOAT,
x2 FLOAT,
y2 FLOAT,
geom geometry
);
insert into edges (geom) values
('linestring(0 0, 4 4)'),
('linestring(2 0, 0 2)'),
('linestring(0 4, 4 0)'),
('linestring(1 0, 1 2)')
;
pgr_separatecrossingsテスト用テーブルとデータ。
Yamakyupgr_separatecrossingsは新たに追加すべきlinestringを出力するだけであり、元テーブルの内容を削除したり、更新の必要のないlinestringを出力に含めたりはしない。その辺の処理はsqlを別途書く。いろいろあったのもあって、ここがわかるまでひと月ぐらいかかった気がする。
Yamakyuちなみにこれ(zennのスクラップ)ってどれぐらい書いていいんだろう。でも制作記録を残しておくのはすごくいいことだと思う。本当はプログラムとかに関係のない雑記とかも付けたいけど、それこそtwitterとかやれよってなるのだろうか。自分のブログを作るべきだろうか。
Yamakyugithubと連携したけどなんだよ、スクラップかけないじゃん。たぶん戻す。
Yamakyudrop table if exists edges;
drop table if exists vertices;
CREATE TABLE edges (
id integer generated always as IDENTITY,
source BIGINT,
target BIGINT,
cost FLOAT,
reverse_cost FLOAT,
capacity BIGINT,
reverse_capacity BIGINT,
x1 FLOAT,
y1 FLOAT,
x2 FLOAT,
y2 FLOAT,
geom geometry
);
create table vertices (
id integer generated always as IDENTITY,
in_edges BIGINT[],
out_edges BIGINT[],
x FLOAT,
y FLOAT,
geom geometry
);
insert into edges (geom) values
('linestring(0 0, 4 4)'),
('linestring(2 2, 6 6)')
;
insert INTO vertices (in_edges, out_edges, x, y, geom)
select in_edges, out_edges, x, y, geom from pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
with costs as (select st_length(geom) as cost, st_length(geom) * -1 as reverse_cost from edges)
update edges set
(cost, reverse_cost) =
(c.cost, c.reverse_cost)
from costs c;
ALTER TABLE edges ADD old_id BIGINT;
WITH
edges_table AS (
SELECT id, geom FROM edges
),
get_endpoints as (
select
e1.id as id1, e2.id as id2,
e1.geom as g1, e2.geom as g2,
(st_dumppoints(ST_Intersection(e1.geom, e2.geom))).geom as point
from edges_table as e1, edges_table as e2
where e1.id < e2.id and ST_overlaps(e1.geom, e2.geom)
),
crossings AS (
SELECT id1, g1, point FROM get_endpoints
UNION
SELECT id2, g2, point FROM get_endpoints
),
blades AS (
SELECT id1, g1, ST_UnaryUnion(ST_Collect(point)) AS blade
FROM crossings
where not(point = st_startpoint(g1) or point = st_endpoint(g1))
GROUP BY id1, g1
),
collection AS (
SELECT id1, (st_dump(st_split(st_snap(g1, blade, 0.01), blade))).*
FROM blades
)
SELECT row_number() over()::INTEGER AS seq, id1::BIGINT, path[1], geom, st_astext(geom)
FROM collection;
重なる線分を分割するやつ。
Yamakyuごみ
drop table if exists map.edges;
drop table if exists map.vertices;
-- drop table if exists map.results;
CREATE TABLE map.edges (
id integer generated always as IDENTITY,
source BIGINT,
target BIGINT,
cost FLOAT,
reverse_cost FLOAT,
capacity BIGINT DEFAULT 100,
reverse_capacity BIGINT DEFAULT 100,
x1 FLOAT,
y1 FLOAT,
x2 FLOAT,
y2 FLOAT,
geom geometry,
old_id BIGINT,
type varchar(16),
multiplier float DEFAULT 1.0,
pattern_id integer,
feed_id integer,
route_id varchar(256),
-- p1 geometry(Point, 4326),
-- p2 geometry(Point, 4326),
deg float
);
create table map.vertices (
id integer generated always as IDENTITY,
in_edges BIGINT[],
out_edges BIGINT[],
x FLOAT,
y FLOAT,
geom geometry
);
do $$
declare
ptn1 record;
bdist float := 0.001; -- バッファ距離
begin
select 0.001 into bdist;
for ptn1 in (
select *
from trip_patterns
-- where route_name in (
-- -- '府７５'
-- '武７１'
-- )
where pattern_id = 410
) loop
-- 路線
insert into map.edges (
type,
pattern_id,
feed_id,
route_id,
geom,
-- p1,
-- p2,
deg,
cost,
reverse_cost
)
with points as (
select
'路線' as type,
stop_patterns.pattern_id,
stop_patterns.feed_id,
stop_patterns.route_id,
st_point(s1.stop_lon, s1.stop_lat, 4326) as p1,
st_point(s2.stop_lon, s2.stop_lat, 4326) as p2,
1.00 as multiplier
from stop_patterns
inner join stops as s1 on stop_patterns.feed_id = s1.feed_id and stop_patterns.stop_id = s1.stop_id
inner join stops as s2 on stop_patterns.feed_id = s2.feed_id and stop_patterns.next_stop_id = s2.stop_id
where
stop_patterns.next_stop_id is not null and
stop_patterns.pattern_id = ptn1.pattern_id
)
select
type,
pattern_id,
feed_id,
route_id,
st_makeline(p1, p2) as geom,
-- p1,
-- p2,
st_azimuth(p1, p2) as deg,
st_length(st_makeline(p1, p2)) * multiplier as cost,
st_length(st_makeline(p1, p2)) * multiplier as reverse_cost
from points;
-- 路線外郭
insert into map.edges (
type,
pattern_id,
feed_id,
route_id,
geom,
-- p1,
-- p2,
deg,
cost,
reverse_cost
)
with segm as (
select
'外郭' as type,
map.edges.pattern_id,
map.edges.route_id,
map.edges.feed_id,
((st_dumpsegments(st_buffer(geom, bdist, 1))).geom) as geom,
0.75 as multiplier
from map.edges
where pattern_id = ptn1.pattern_id
)
select
type,
segm.pattern_id,
feed_id,
route_id,
geom as geom,
-- st_startpoint(geom) as p1,
-- st_endpoint(geom) as p2,
st_azimuth(st_startpoint(geom), st_endpoint(geom)) as deg,
st_length(geom) * multiplier as cost,
st_length(geom) * multiplier as reverse_cost
from segm;
-- エッジ処理
-- 重複エッジ分割
with edges_table as (
select id, geom from map.edges
),
get_endpoints as (
select
e1.id as id1, e2.id as id2,
e1.geom as g1, e2.geom as g2,
(st_dumppoints(ST_Intersection(e1.geom, e2.geom))).geom as point
from edges_table as e1, edges_table as e2
where e1.id < e2.id and ST_overlaps(e1.geom, e2.geom)
),
crossings as (
select id1, g1, point from get_endpoints
union
select id2, g2, point from get_endpoints
),
blades as (
select id1, g1, ST_UnaryUnion(ST_Collect(point)) AS blade
from crossings
where not(point = st_startpoint(g1) or point = st_endpoint(g1))
group by id1, g1
),
collection AS (
SELECT id1, (st_dump(st_split(st_snap(g1, blade, 0.000001), blade))).*
FROM blades
)
insert into map.edges (
old_id,
-- id,
geom
)
SELECT
row_number() over()::integer as seq,
-- id1::bigint,
-- path[1],
geom
FROM collection;
-- 交差エッジ分割
insert into map.edges (old_id, geom, type)
select id, geom, '分割'
from pgr_separateCrossing('SELECT id, geom FROM map.edges');
-- insert into map.edges (old_id, geom)
-- select id, geom
-- from pgr_separatetouching('SELECT id, geom FROM map.edges WHERE pattern_id = ' || ptn1.pattern_id);
-- 新規エッジ通過コスト挿入
with costs as (
select
e2.id,
ST_Length(e2.geom) as cost,
ST_Length(e2.geom) as reverse_cost
from map.edges as e1
inner join map.edges as e2 on (e1.id = e2.old_id)
)
UPDATE map.edges e
SET (cost, reverse_cost) = (c.cost, c.reverse_cost)
FROM costs AS c WHERE e.id = c.id;
-- 不足する頂点を新規に作成
with new_vertex as (
select ev.*
-- from pgr_extractvertices('SELECT id, geom FROM map.edges WHERE old_id IS NOT NULL') ev
from pgr_extractVertices('select id, geom from map.edges where source is null or target is null') ev
left join map.vertices v using(geom)
where v is null
)
insert into map.vertices (in_edges, out_edges, x, y, geom)
select in_edges, out_edges,x,y,geom from new_vertex;
-- エッジ始点側頂点情報更新
update map.edges as e
set
source = v.id,
x1 = x,
y1 = y
from map.vertices as v
where source is null and ST_StartPoint(e.geom) = v.geom;
-- エッジ終点側頂点情報更新
update map.edges as e
set
target = v.id,
x2 = x,
y2 = y
from map.vertices as v
where target is null and ST_EndPoint(e.geom) = v.geom;
-- 経路探索
drop table if exists map.results;
create table map.results as
with starting as (
select
st_point(stop_lat, stop_lon, 4326) as point
from stop_patterns
inner join stops using (feed_id, stop_id)
where pattern_id = ptn1.pattern_id
order by stop_sequence asc
limit 1
),
ending as (
select
st_point(stop_lat, stop_lon, 4326) as point
from stop_patterns
inner join stops using (feed_id, stop_id)
where pattern_id = ptn1.pattern_id
order by stop_sequence desc
limit 1
)
select *, st_astext(point) from ending;
drop table if exists map.results;
create table map.results as
select route.*, geom from pgr_bdDijkstra(
'SELECT id, source, target, cost, reverse_cost, capacity, reverse_capacity FROM map.edges', 1, 2
-- (select id from vertices, starting where st_dwithin(starting.point, geom, 0.001) limit 1), -- 出発点の座標
-- (select id from vertices, ending where st_dwithin(ending.point, geom, 0.001) limit 1) -- 到着点の座標
-- -- false, -- bidirectional
-- -- false, -- directed
-- -- false, -- use vertex ids
-- -- true -- return path
) as route
inner join map.edges on (edge = map.edges.id);
end loop;
end;
$$;
-- select * , st_astext(geom) from map.edges;
-- select * from map.vertices where id = 1 ;
-- select * from map.vertices where st_dwithin(geom, (select geom from map.vertices where id = 1), 0.0005);
select * from map.edges where id = 23;
select * from map.results;
select * from map.edges where old_id = 22;
-- WITH
-- edges_table AS (
-- select id, geom from map.edges where id in (22, 13)
-- -- SELECT id, geom FROM map.edges where old_id is null and st_dwithin(geom, (select geom from map.edges where id = 23), 0.0005)
-- ),
-- get_crossings AS (
-- SELECT e1.id id1, e2.id id2, e1.geom AS g1, e2.geom AS g2, ST_Intersection(e1.geom, e2.geom) AS point
-- FROM edges_table e1, edges_table e2
-- WHERE e1.id < e2.id AND ST_Crosses(e1.geom, e2.geom)
-- ),
-- crossings AS (
-- SELECT id1, g1, point FROM get_crossings
-- UNION
-- SELECT id2, g2, point FROM get_crossings
-- ),
-- blades AS (
-- SELECT id1, g1, ST_UnaryUnion(ST_Collect(point)) AS blade
-- FROM crossings
-- GROUP BY id1, g1
-- ),
-- collection AS (
-- SELECT id1, (st_dump(st_split(st_snap(g1, blade, 0.00001), blade))).*
-- FROM blades
-- )
-- -- select * from ;
-- SELECT row_number() over()::INTEGER AS seq, id1::BIGINT, path[1], geom
-- FROM collection;
-- select * from pgr_separateCrossing('SELECT id, geom FROM map.edges where old_id is null and st_dwithin(geom, (select geom from map.edges where id = 23), 0.0005)');
pgr_separatecrossings()は途中にst_snapを使用しているものの、snap距離が0.01が初期値なので注意すること。toleranceをしっかり設定。そうしないといまいち分割されなかったりする。
Yamakyudo $$
declare
rec record;
begin
for rec in (select * from agency) loop
raise notice '%', rec.agency_name;
if (rec.agency_name='都営バス') THEN
return;
end if;
raise notice '%', rec.agency_name;
end loop;
end;
$$;
他と同様にfor内でreturnするとforが終了するようだ。exitも同様。
continueで次に移る。
Yamakyu
drop table if exists kari;
create table if not exists kari(
id integer,
source integer,
target integer,
geom geometry(LineString, 4326),
cost float,
reverse_cost float,
multiplier float default 1.0,
capacity float default 1.0,
reverse_capacity float default 1.0
);
insert into kari (id, geom, cost, reverse_cost, source, target) values
(1, 'linestring(0 0, 4 4)', 4.0, 4.0, 1, 2),
(2, 'linestring(4 4, 6 6)', 2.0, 2.0, 2, 3)
;
select *
from pgr_dijkstra('select id, source, target, cost * multiplier, reverse_cost, capacity, reverse_capacity from kari', 1, 3)
;
drop table if exists kari;
なんかえらーでる。