`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

Boost Graph Inside

签名¶

总结

pgr_findCloseEdges(Edges SQL, point, tolerance, [options])
pgr_findCloseEdges(Edges SQL, points, tolerance, [options])
options: [cap, dryrun]

返回集合 (edge_id, fraction, side, distance, geom, edge)

OR EMPTY SET

一个点¶

pgr_findCloseEdges(Edges SQL, point, tolerance, [options])

options: [cap, dryrun]

返回集合 (edge_id, fraction, side, distance, geom, edge)

OR EMPTY SET

示例:: 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)

多点¶

pgr_findCloseEdges(Edges SQL, points, tolerance, [options])

options: [cap, dryrun]

返回集合 (edge_id, fraction, side, distance, geom, edge)

OR EMPTY SET

示例:: 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	`TEXT`	Edges SQL 如下所述。
point	`POINT`	点几何
points	`POINT[]`	点几何数组
tolerance	`FLOAT`	几何图形之间的最大距离

可选参数¶

参数	类型	默认	描述
`cap`	`INTEGER`	$1$	限制输出行数
`dryrun`	`BOOLEAN`	`false`	当为 `false` 时，执行计算。当为 `true` 时，不执行计算，并在 PostgreSQL 的 `NOTICE` 中显示执行计算的查询。

内部查询¶

Edges SQL¶

列	类型	描述
`id`	ANY-INTEGER	边的标识符。
`geom`	`geometry`	边的 `LINESTRING` 几何。

结果列¶

返回集合 (edge_id, fraction, side, distance, geom, edge)

列	类型	描述
`edge_id`	`BIGINT`	边的标识符。当 $cap = 1$ 时，它是最近的边。
`fraction`	`FLOAT`	Value in <0,1> that indicates the relative position from the first end-point of the edge.
`side`	`CHAR`	`[r, l]` 中的值指示该点是否为： At the right `r` of the segment. 当点在直线上时，它被认为是在右边。 At the left `l` of the segment.
`distance`	`FLOAT`	Distance from the point to the edge.
`geom`	`geometry`	Original `POINT` geometry.
`edge`	`geometry`	`LINESTRING` geometry that connects the original point to the closest point of the edge with identifier `edge_id`

其他示例¶

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"]; } }$

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"] } }$

多点模拟执行 ¶

返回 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)

最多找到两条到达给定点的路线 ¶

使用 pgr_withPoints

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个固定的兴趣点，并且它们将被存储在一个表中。

列	描述
`pid`	唯一标识符。
`edge_id`	Identifier of the nearest segment.
`side`	Is it on the left, right or both sides of the segment `edge_id`.
`fraction`	该点位于边的哪个位置。
`geom`	点的几何形状。
`distance`	The distance between `geom` and the segment `edge_id`.
`edge`	A segment that connects the `geom` of the point to the closest point on the segment `edge_id`.
`newPoint`	A point on segment `edge_id` that is the closest to `geom`.

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)

另请参阅¶

索引和表格

参数	类型	默认	描述
`cap`	`INTEGER`	\(1\)	限制输出行数
`dryrun`	`BOOLEAN`	`false`	当为 `false` 时，执行计算。当为 `true` 时，不执行计算，并在 PostgreSQL 的 `NOTICE` 中显示执行计算的查询。

列	类型	描述
`edge_id`	`BIGINT`	边的标识符。当 \(cap = 1\) 时，它是最近的边。
`fraction`	`FLOAT`	Value in <0,1> that indicates the relative position from the first end-point of the edge.
`side`	`CHAR`	`[r, l]` 中的值指示该点是否为： At the right `r` of the segment. 当点在直线上时，它被认为是在右边。 At the left `l` of the segment.
`distance`	`FLOAT`	Distance from the point to the edge.
`geom`	`geometry`	Original `POINT` geometry.
`edge`	`geometry`	`LINESTRING` geometry that connects the original point to the closest point of the edge with identifier `edge_id`

pgr_findCloseEdges¶

描述¶

签名¶

一个点¶

多点¶

参数¶

可选参数¶

内部查询¶

Edges SQL¶

结果列¶

其他示例¶

另请参阅¶

`pgr_findCloseEdges`¶