pgr_findCloseEdges
¶
pgr_findCloseEdges
-查找点几何图形的闭合边。
可用性
Version 3.8.0
Error messages adjustment.
partial
option is removed.Function promoted to official.
版本 3.4.0
New proposed function.
描述¶
pgr_findCloseEdges
- 一个用于找到离点几何最近的边的实用函数。
几何图形必须位于同一坐标系中(具有相同的 SRID)。
可以获取计算代码,以便根据应用需要进行进一步的具体调整。
EMPTY SET
is returned on dryrun executions
签名¶
总结
[cap, dryrun]
(edge_id, fraction, side, distance, geom, edge)
一个点¶
[cap, dryrun]
(edge_id, fraction, side, distance, geom, edge)
- 示例:
Get two close edges to points of interest with \(pid = 5\)
cap => 2
SELECT
edge_id, fraction, side, distance,
distance, ST_AsText(geom) AS point, ST_AsText(edge) As edge
FROM pgr_findCloseEdges(
$$SELECT id, geom FROM edges$$,
(SELECT geom FROM pointsOfInterest WHERE pid = 5),
0.5, cap => 2);
edge_id | fraction | side | distance | distance | point | edge
---------+----------+------+----------+----------+----------------+---------------------------
5 | 0.8 | l | 0.1 | 0.1 | POINT(2.9 1.8) | LINESTRING(2.9 1.8,3 1.8)
8 | 0.9 | r | 0.2 | 0.2 | POINT(2.9 1.8) | LINESTRING(2.9 1.8,2.9 2)
(2 rows)
多点¶
[cap, dryrun]
(edge_id, fraction, side, distance, geom, edge)
- 示例:
For each points of interests, find the closest edge.
SELECT
edge_id, fraction, side, distance,
ST_AsText(geom) AS point,
ST_AsText(edge) AS edge
FROM pgr_findCloseEdges(
$$SELECT id, geom FROM edges$$,
(SELECT array_agg(geom) FROM pointsOfInterest),
0.5);
edge_id | fraction | side | distance | point | edge
---------+----------+------+----------+----------------+---------------------------
1 | 0.4 | l | 0.2 | POINT(1.8 0.4) | LINESTRING(1.8 0.4,2 0.4)
6 | 0.3 | r | 0.2 | POINT(0.3 1.8) | LINESTRING(0.3 1.8,0.3 2)
12 | 0.6 | l | 0.2 | POINT(2.6 3.2) | LINESTRING(2.6 3.2,2.6 3)
15 | 0.4 | r | 0.2 | POINT(4.2 2.4) | LINESTRING(4.2 2.4,4 2.4)
5 | 0.8 | l | 0.1 | POINT(2.9 1.8) | LINESTRING(2.9 1.8,3 1.8)
4 | 0.7 | r | 0.2 | POINT(2.2 1.7) | LINESTRING(2.2 1.7,2 1.7)
(6 rows)
参数¶
参数 |
类型 |
描述 |
---|---|---|
|
Edges SQL 如下所述。 |
|
point |
|
点几何 |
points |
|
点几何数组 |
tolerance |
|
几何图形之间的最大距离 |
可选参数¶
参数 |
类型 |
默认 |
描述 |
---|---|---|---|
|
|
\(1\) |
限制输出行数 |
|
|
|
|
内部查询¶
Edges SQL¶
列 |
类型 |
描述 |
---|---|---|
|
ANY-INTEGER |
边的标识符。 |
|
|
边的 |
结果列¶
返回集合 (edge_id, fraction, side, distance, geom, edge)
列 |
类型 |
描述 |
---|---|---|
|
|
边的标识符。
|
|
|
Value in <0,1> that indicates the relative position from the first end-point of the edge. |
|
|
|
|
|
Distance from the point to the edge. |
|
|
Original |
|
|
|
其他示例¶
One point in an edge¶
SELECT edge_id, fraction, side, distance, ST_AsText(geom) geom, ST_AsText(edge) edge
FROM pgr_findCloseEdges(
$$SELECT 1 AS id, ST_MakeLine(ST_makePoint(2,0), ST_MakePoint(2,3)) AS geom$$,
ST_MakePoint(0,1.5), 5);
edge_id | fraction | side | distance | geom | edge
---------+----------+------+----------+--------------+-------------------------
1 | 0.5 | l | 2 | POINT(0 1.5) | LINESTRING(0 1.5,2 1.5)
(1 row)
![digraph D {
subgraph cluster0 {
label="data";
point [shape=circle;style=filled;color=green;fontsize=8;width=0.3;fixedsize=true];
point [pos="0,1.5!"]
sp, ep [shape=circle;fontsize=8;width=0.3;fixedsize=true];
sp[pos="2,0!"]
ep[pos="2,3!"]
sp -> ep:s
}
subgraph cluster1 {
label="results";
geom [shape=circle;style=filled;color=green;fontsize=8;width=0.3;fixedsize=true];
geom [pos="3,1.5!"]
np11 [shape=point;color=black;size=0;fontsize=8;fixedsize=true];
np11 [pos="5,1.5!";xlabel="fraction=0.5"];
sp1, ep1 [shape=circle;fontsize=8;width=0.3;fixedsize=true];
sp1[pos="5,0!"]
ep1[pos="5,3!"]
sp1:n -> np11:s [dir=none]
np11:n -> ep1:s
geom -> np11 [color=red,label="edge"];
}
}](_images/graphviz-3ffa1dee8d42ee90a88ffba10eebddf0b7a60f25.png)
The green node is the original point.
geom
has the value of the original point.The geometry
edge
is a line that connects the original point with the edge \(sp \rightarrow ep\) edge.The point is located at the left of the edge.
单点模拟执行¶
Using the query from the previous example:
返回
EMPTY SET
。dryrun => true
Generates a PostgreSQL
NOTICE
with the code used.The generated code can be used as a starting base code for additional requirements, like taking into consideration the SRID.
SELECT edge_id, fraction, side, distance, ST_AsText(geom) geom, ST_AsText(edge) edge
FROM pgr_findCloseEdges(
$$SELECT 1 AS id, ST_MakeLine(ST_makePoint(2,0), ST_MakePoint(2,3)) AS geom$$,
ST_MakePoint(0,1.5), 5, dryrun => true);
NOTICE:
WITH
edges_sql AS (SELECT 1 AS id, ST_MakeLine(ST_makePoint(2,0), ST_MakePoint(2,3)) AS geom),
point_sql AS (SELECT unnest('{01010000000000000000000000000000000000F83F}'::geometry[]) AS point),
results AS (
SELECT
id::BIGINT AS edge_id,
ST_LineLocatePoint(geom, point) AS fraction,
CASE WHEN ST_Intersects(ST_Buffer(geom, 5, 'side=right endcap=flat'), point)
THEN 'r'
ELSE 'l' END::CHAR AS side,
geom <-> point AS distance,
point,
ST_MakeLine(point, ST_ClosestPoint(geom, point)) AS new_line
FROM edges_sql, point_sql
WHERE ST_DWithin(geom, point, 5)
ORDER BY geom <-> point),
prepare_cap AS (
SELECT row_number() OVER (PARTITION BY point ORDER BY point, distance) AS rn, *
FROM results)
SELECT edge_id, fraction, side, distance, point, new_line
FROM prepare_cap
WHERE rn <= 1
edge_id | fraction | side | distance | geom | edge
---------+----------+------+----------+------+------
(0 rows)
Many points in an edge¶
绿色节点为 原始点
标为 g1 和 g2 的几何体
geom
是 原始点标为 edge1 和 edge2 的几何图形
edge
是一条连接 ** 原始点** 和 \(sp \rightarrow ep\) 边上最近点的线。
SELECT edge_id, fraction, side, distance, ST_AsText(geom) geom, ST_AsText(edge) edge
FROM pgr_findCloseEdges(
$$SELECT 1 AS id, ST_MakeLine(ST_makePoint(1,0), ST_MakePoint(1,3)) AS geom$$,
ARRAY[ST_MakePoint(0,2), ST_MakePoint(3,1)]::GEOMETRY[], 5);
edge_id | fraction | side | distance | geom | edge
---------+----------------+------+----------+------------+---------------------
1 | 0.666666666667 | l | 1 | POINT(0 2) | LINESTRING(0 2,1 2)
1 | 0.333333333333 | r | 2 | POINT(3 1) | LINESTRING(3 1,1 1)
(2 rows)
![digraph G {
subgraph cluster0 {
p1,p2 [shape=circle;style=filled;color=green;fontsize=8;width=0.3;fixedsize=true];
p1 [pos="0,2!"]
p2 [pos="3,1!"]
sp, ep [shape=circle;fontsize=8;width=0.3;fixedsize=true];
sp[pos="1,0!"]
ep[pos="1,3!"]
sp -> ep:s
}
subgraph cluster1 {
g1, g2 [shape=circle;style=filled;color=green;fontsize=8;width=0.3;fixedsize=true];
g1 [pos="4,2!"]
g2 [pos="7,1!"]
np11,np21 [shape=point;color=black;size=0;fontsize=8;fixedsize=true];
np11 [pos="5,2!";xlabel="fraction=0.63"];
np21 [pos="5,1!";xlabel="fraction=0.33"];
sp1, ep1 [shape=circle;fontsize=8;width=0.3;fixedsize=true];
sp1[pos="5,0!"]
ep1[pos="5,3!"]
sp1:n -> np21:s [dir=none]
np21:n -> np11:s [dir=none]
np11:n -> ep1:s
g1 -> np11 [color=red;label="edge"];
g2 -> np21 [color=red;label="edge"]
}
}](_images/graphviz-113cd343a60605166f45d1c605be02f223c9c1a1.png)
多点模拟执行¶
返回
EMPTY SET
。dryrun => true
不处理查询
生成一个包含用于计算所有列的代码的 PostgreSQL
NOTICE
SELECT edge_id, fraction, side, distance, ST_AsText(geom) geom, ST_AsText(edge) edge
FROM pgr_findCloseEdges(
$$SELECT 1 AS id, ST_MakeLine(ST_makePoint(1,0), ST_MakePoint(1,3)) AS geom$$,
ARRAY[ST_MakePoint(0,2), ST_MakePoint(3,1)]::GEOMETRY[], 5, dryrun=>true);
NOTICE:
WITH
edges_sql AS (SELECT 1 AS id, ST_MakeLine(ST_makePoint(1,0), ST_MakePoint(1,3)) AS geom),
point_sql AS (SELECT unnest('{010100000000000000000000000000000000000040:01010000000000000000000840000000000000F03F}'::geometry[]) AS point),
results AS (
SELECT
id::BIGINT AS edge_id,
ST_LineLocatePoint(geom, point) AS fraction,
CASE WHEN ST_Intersects(ST_Buffer(geom, 5, 'side=right endcap=flat'), point)
THEN 'r'
ELSE 'l' END::CHAR AS side,
geom <-> point AS distance,
point,
ST_MakeLine(point, ST_ClosestPoint(geom, point)) AS new_line
FROM edges_sql, point_sql
WHERE ST_DWithin(geom, point, 5)
ORDER BY geom <-> point),
prepare_cap AS (
SELECT row_number() OVER (PARTITION BY point ORDER BY point, distance) AS rn, *
FROM results)
SELECT edge_id, fraction, side, distance, point, new_line
FROM prepare_cap
WHERE rn <= 1
edge_id | fraction | side | distance | geom | edge
---------+----------+------+----------+------+------
(0 rows)
最多找到两条到达给定点的路线¶
SELECT * FROM pgr_withPoints(
$e$ SELECT * FROM edges $e$,
$p$ SELECT edge_id, fraction, side
FROM pgr_findCloseEdges(
$$SELECT id, geom FROM edges$$,
(SELECT geom FROM pointsOfInterest WHERE pid = 5),
0.5, cap => 2)
$p$,
1, ARRAY[-1, -2]);
seq | path_seq | end_pid | node | edge | cost | agg_cost
-----+----------+---------+------+------+------+----------
1 | 1 | -2 | 1 | 6 | 1 | 0
2 | 2 | -2 | 3 | 7 | 1 | 1
3 | 3 | -2 | 7 | 8 | 0.9 | 2
4 | 4 | -2 | -2 | -1 | 0 | 2.9
5 | 1 | -1 | 1 | 6 | 1 | 0
6 | 2 | -1 | 3 | 7 | 1 | 1
7 | 3 | -1 | 7 | 8 | 1 | 2
8 | 4 | -1 | 11 | 9 | 1 | 3
9 | 5 | -1 | 16 | 16 | 1 | 4
10 | 6 | -1 | 15 | 3 | 1 | 5
11 | 7 | -1 | 10 | 5 | 0.8 | 6
12 | 8 | -1 | -1 | -1 | 0 | 6.8
(12 rows)
兴趣点表¶
处理图外的点。
兴趣点¶
有时应用程序会“即时执行”,从不是图中顶点的位置开始。在pgRouting中,这些位置被称为兴趣点。
兴趣点所需的信息包括 pid
, edge_id
, side
, fraction
。
在这份文档中,将有6个固定的兴趣点,并且它们将被存储在一个表中。
列 |
描述 |
---|---|
|
唯一标识符。 |
|
Identifier of the nearest segment. |
|
Is it on the left, right or both sides of the segment |
|
该点位于边的哪个位置。 |
|
点的几何形状。 |
|
The distance between |
|
A segment that connects the |
|
A point on segment |
CREATE TABLE pointsOfInterest(
pid BIGSERIAL PRIMARY KEY,
edge_id BIGINT,
side CHAR,
fraction FLOAT,
distance FLOAT,
edge geometry,
newPoint geometry,
geom geometry);
IF v > 3.4 THEN
Points of interest fill up¶
Inserting the points of interest.
INSERT INTO pointsOfInterest (geom) VALUES
(ST_Point(1.8, 0.4)),
(ST_Point(4.2, 2.4)),
(ST_Point(2.6, 3.2)),
(ST_Point(0.3, 1.8)),
(ST_Point(2.9, 1.8)),
(ST_Point(2.2, 1.7));
Filling the rest of the table.
UPDATE pointsofinterest SET
edge_id = poi.edge_id,
side = poi.side,
fraction = round(poi.fraction::numeric, 2),
distance = round(poi.distance::numeric, 2),
edge = poi.edge,
newPoint = ST_EndPoint(poi.edge)
FROM (
SELECT *
FROM pgr_findCloseEdges(
$$SELECT id, geom FROM edges$$,(SELECT array_agg(geom) FROM pointsOfInterest), 0.5) ) AS poi
WHERE pointsOfInterest.geom = poi.geom;
Any other additional modification: In this manual, point \(6\) can be reached from both sides.
UPDATE pointsOfInterest SET side = 'b' WHERE pid = 6;
The points of interest:
SELECT
pid, ST_AsText(geom) geom,
edge_id, fraction AS frac, side, distance AS dist,
ST_AsText(edge) edge, ST_AsText(newPoint) newPoint
FROM pointsOfInterest;
pid | geom | edge_id | frac | side | dist | edge | newpoint
-----+----------------+---------+------+------+------+---------------------------+--------------
1 | POINT(1.8 0.4) | 1 | 0.4 | l | 0.2 | LINESTRING(1.8 0.4,2 0.4) | POINT(2 0.4)
4 | POINT(0.3 1.8) | 6 | 0.3 | r | 0.2 | LINESTRING(0.3 1.8,0.3 2) | POINT(0.3 2)
3 | POINT(2.6 3.2) | 12 | 0.6 | l | 0.2 | LINESTRING(2.6 3.2,2.6 3) | POINT(2.6 3)
2 | POINT(4.2 2.4) | 15 | 0.4 | r | 0.2 | LINESTRING(4.2 2.4,4 2.4) | POINT(4 2.4)
5 | POINT(2.9 1.8) | 5 | 0.8 | l | 0.1 | LINESTRING(2.9 1.8,3 1.8) | POINT(3 1.8)
6 | POINT(2.2 1.7) | 4 | 0.7 | b | 0.2 | LINESTRING(2.2 1.7,2 1.7) | POINT(2 1.7)
(6 rows)
另请参阅¶
索引和表格