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 图内部 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

  • 绿色节点为 原始点

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

  • 标为 edge1edge2 的几何图形 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)

另请参阅

索引和表格