Supported versions: latest (3.8) 3.7 3.6 3.5 3.4 main dev

`pgr_findCloseEdges`¶

pgr_findCloseEdges - 查找点几何图形的闭合边。

可用性

Version 3.8.0

错误信息调整。
partial 选项已被移除。
函数正式发布。

版本 3.4.0

新提议的函数。

描述¶

pgr_findCloseEdges - 一个用于查找距离点状geometry最近边的工具函数。

几何图形必须位于同一坐标系中（具有相同的 SRID）。
可以获取计算代码，以便根据应用需要进行进一步的具体调整。
空结果集 EMPTY SET 会在试运行中返回

Boost 图内部

签名¶

总结

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

示例:: 用 $p i d = 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

示例:: 为每个兴趣点找出最近的边。

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`	边的标识符。当 $c a p = 1$ 时，它是最近的边。
`fraction`	`FLOAT`	以 <0,1> 范围内的值，表示相对于边线第一个端点的相对位置。
`side`	`CHAR`	`[r, l]` 中的值指示该点是否为：位于线段右侧 `r` 。当点在直线上时，它被认为是在右边。位于线段左侧 `l` 。
`distance`	`FLOAT`	点到边线的距离。
`geom`	`geometry`	原始的 `POINT` 几何形状。
`edge`	`geometry`	连接原始 point 和具有标识符 `edge_id` 的边的最近点的 `LINESTRING` 几何体

其他示例¶

边线上的一个点 ¶

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

绿色节点为原始点。
geom 包含原始点的坐标值。
几何图形 edge 是连接原始点与边线 $s p \to e p$ 的线段。
该点位于边线的左侧。

单点模拟执行 ¶

使用上一个示例中的查询：

返回 EMPTY SET 。
dryrun => true
- 生成一个包含用于计算所有列的代码的 PostgreSQL NOTICE 。
- 生成的代码可用作满足其他要求（如考虑 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)

一条边上有许多点 ¶

绿色节点为 原始点
标为 g1 和 g2 的几何体 geom 是 原始点
标为 edge1 和 edge2 的几何图形 edge 是一条连接 ** 原始点** 和 $s p \to e p$ 边上最近点的线。

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], 'r');
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         1 |      -2 |    1 |    6 |    1 |        0
   2 |        2 |         1 |      -2 |    3 |    7 |    1 |        1
   3 |        3 |         1 |      -2 |    7 |    8 |  0.9 |        2
   4 |        4 |         1 |      -2 |   -2 |   -1 |    0 |      2.9
   5 |        1 |         1 |      -1 |    1 |    6 |    1 |        0
   6 |        2 |         1 |      -1 |    3 |    7 |    1 |        1
   7 |        3 |         1 |      -1 |    7 |    8 |    1 |        2
   8 |        4 |         1 |      -1 |   11 |    9 |    1 |        3
   9 |        5 |         1 |      -1 |   16 |   16 |    1 |        4
  10 |        6 |         1 |      -1 |   15 |    3 |    1 |        5
  11 |        7 |         1 |      -1 |   10 |    5 |  0.8 |        6
  12 |        8 |         1 |      -1 |   -1 |   -1 |    0 |      6.8
(12 rows)

兴趣点表 ¶

处理图外的点。

兴趣点 ¶

有时应用程序会“即时执行”，从不是图中顶点的位置开始。在pgRouting中，这些位置被称为兴趣点。

兴趣点所需的信息包括 pid, edge_id, side, fraction 。

在这份文档中，将有6个固定的兴趣点，并且它们将被存储在一个表中。

列	描述
`pid`	唯一标识符。
`edge_id`	最近边线的标识符。
`side`	位于边线 `edge_id` 的左侧、右侧还是两侧。
`fraction`	该点位于边的哪个位置。
`geom`	点的几何形状。
`distance`	`geom` 与边线 `edge_id` 之间的距离。
`edge`	一个连接点的 `geom` 到边 `edge_id` 上最近点的线段。
`newPoint`	边 `edge_id` 上距离 `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

兴趣点填充 ¶

插入兴趣点。

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));

填充表格的其余部分。

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;

任何其他额外的修改：在本手册中，可从两侧到达点 $6$ 。

UPDATE pointsOfInterest SET side = 'b' WHERE pid = 6;

兴趣点：

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)

另请参阅¶

索引和表格

pgr_findCloseEdges¶

描述¶

签名¶

一个点¶

多点¶

参数¶

可选参数¶

内部查询¶

Edges SQL¶

结果列¶

其他示例¶

另请参阅¶

`pgr_findCloseEdges`¶