pgr_findCloseEdges

pgr_findCloseEdges - Encuentra las aristas cercanas a una geometría puntual.

Disponibilidad

Version 3.8.0

• Error messages adjustment.

• partial option is removed.

• Function promoted to official.

Versión 3.4.0

• New proposed function.

Descripción

pgr_findCloseEdges - Función de utilidad que encuentra la arista más cercana a una geometría de puntos.

• Las geometrías deben estar en el mismo sistema de coordenadas (tener el mismo SRID).

• El código para realizar los cálculos puede obtenerse para realizar los ajustes específicos que necesite la aplicación.

• EMPTY SET is returned on dryrun executions

Boost Graph Inside

Firmas

Resumen

pgr_findCloseEdges(Edges SQL, punto, tolerancia, [options])

pgr_findCloseEdges(Edges SQL, puntos, tolerancia, [options])

options: [cap, dryrun]

Regresa conjunto de (edge_id, fraction, side, distance, geom, edge)

O CONJUNTO VACÍO

Un punto

pgr_findCloseEdges(Edges SQL, punto, tolerancia, [options])

options: [cap, dryrun]

Regresa conjunto de (edge_id, fraction, side, distance, geom, edge)

O CONJUNTO VACÍO

Ejemplo:

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)

Muchos puntos

pgr_findCloseEdges(Edges SQL, puntos, tolerancia, [options])

options: [cap, dryrun]

Regresa conjunto de (edge_id, fraction, side, distance, geom, edge)

O CONJUNTO VACÍO

Ejemplo:

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)

Parámetros

Parámetro	Tipo	Descripción
SQL de aristas	TEXT	SQL de aristas descritas más adelante.
punto	POINT	La geometría del punto
puntos	POINT	Una matriz de geometrías de puntos
tolerancia	FLOAT	Distancia máxima entre geometrías

Parámetros opcionales

Parámetro	Tipo	x Defecto	Descripción
cap	INTEGER	\(1\)	Limitar las filas de salida
dryrun	BOOLEAN	false	Cuando false se realizan los cálculos. Cuando true los cálculos no se realizan y la consulta para hacer los cálculos se expone en un NOTICE de PostgreSQL.

Consultas Internas

SQL aristas

Columna	Tipo	Descripción
id	ENTEROS	Identificador de la arista.
geom	geometry	La geometría LINESTRING de la arista.

Columnas de resultados

Regresa conjunto de (edge_id, fraction, side, distance, geom, edge)

Columna	Tipo	Descripción
edge_id	BIGINT	Identificador de la arista. Cuando \(cap = 1\), es la arista más cercana.
fraction	FLOAT	Value in <0,1> that indicates the relative position from the first end-point of the edge.
side	CHAR	Valor en [r, l] que indica si el punto es: At the right r of the segment. Cuando el punto está en la línea se considera que está a la derecha. At the left l of the segment.
distancia	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

Ejemplos Adicionales

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)

• 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.

Ejecución en seco de un punto

Using the query from the previous example:

• Devuelve CONJUNTO VACÍO.

• 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

• Los nodos verdes son los puntos originales

• La geometría geom, marcada como g1 y g2 son los puntos originales

• La geometría edge, marcada como edge1 y edge2 es una línea que conecta el punto original con el punto más cercano de la arista \(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)

Ejecución en seco de muchos puntos

• Devuelve CONJUNTO VACÍO.

• dryrun => true

  • No procesar la consulta

  • Generar un NOTICE de PostgreSQL con el código utilizado para calcular todas las columnas

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)

Encontrar como máximo dos rutas a un punto dado

Usando pgr_withPoints - Propuesto

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)

Una tabla de puntos de interés

Manejo de puntos fuera del gráfico.

Puntos de interés

Algunas veces las aplicaciones trabajan «sobre la marcha» comenzando desde una localización que no es un vértice en el grafo. Esas localizaciones, en pgRrouting se llaman puntos de interés.

La información necesaria en los puntos de interés es pid, edge_id, side, fraction.

En esta documentación habrá unos 6 puntos de interés fijos y se almacenarán en una tabla.

Columna	Descripción
pid	Un identificador único.
edge_id	Identifier of the nearest segment.
side	Is it on the left, right or both sides of the segment edge_id.
fraction	En qué parte del segmento se encuentra el punto.
geom	La geometría de los puntos.
distancia	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)

Ver también

• withPoints- Categoría

• Datos Muestra

Índices y tablas

• Índice

• Página de Búsqueda