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

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

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

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

以 <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 是连接原始点与边线 spep 的线段。

  • 该点位于边线的左侧。

单点模拟执行

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

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

一条边上有许多点

  • 绿色节点为 原始点

  • 标为 g1g2 的几何体 geom原始点

  • 标为 edge1edge2 的几何图形 edge 是一条连接 ** 原始点** 和 spep 边上最近点的线。

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)

另请参阅

索引和表格