pgr_createVerticesTable¶

pgr_createVerticesTable — 根据 source和target信息重建顶点表。

可用性

版本2.0.0
- 从版本1.X重命名
- 官方函数

描述¶

函数返回：

OK 重建顶点表后。
FAIL 当由于错误而未重建顶点表时。

签名¶

pgr_createVerticesTable(edge_table, [the_geom, source, target, rows_where])

RETURNS VARCHAR

参数¶

顶点表重建函数接受以下参数：

edge_table:: text 网络表的名称（可能包含模式名称）
the_geom:: text``网络表的几何列名称。默认值为``the_geom。
source:: text``网络表的Source列名称。默认值为``source。
target:: text``网络表的Target列名称。默认值为 ``target。
rows_where:: text 用于选择行的条件。默认值为 true 表示选择所有行。

Warning

edge_table 将受到影响

如果索引不存在，则会创建一个索引，以加快以下列的处理速度：
- the_geom
- source
- target

函数返回：

OK 重建顶点表后。
- 创建一个顶点表：<edge_table>_vertices_pgr。
- 根据边表的源点（source）和目标点（target）列，填充顶点表的 id 和``the_geom`` 列。
FAIL 当由于错误而未重建顶点表时。
- 未找到网络表所需的列或该列的类型不正确。
- 条件尚未形成。
- source, target的名称相同。
- 无法确定几何图形的 SRID。

顶点表

顶点表是 pgr_analyzeGraph 和 pgr_analyzeOneWay 函数的必需条件。

顶点表的结构为：

id:: bigint 顶点的标识符。
cnt:: integer edge_table 中引用该顶点的顶点数。参见 pgr_analyzeGraph。
chk:: integer 指示顶点可能有问题。请参阅 pgr_analyzeGraph。
ein:: integer edge_table 中引用该顶点作为传入的顶点数。请参阅 pgr_analyzeOneWay。
eout:: integer Edge_table 中引用该顶点作为传出的顶点数。请参阅 pgr_analyzeOneWay。
the_geom:: geometry 顶点的点几何。
示例1:: 使用 pgr_createVerticesTable 最简单的方法

SELECT  pgr_createVerticesTable('edges', 'geom');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.edges_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  17 VERTICES
NOTICE:                                         FOR   18  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 18
NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

其他示例¶

示例2:: 当参数按照参数中描述的顺序给出时：

SELECT  pgr_createVerticesTable('edges', 'geom', 'source', 'target');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.edges_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  17 VERTICES
NOTICE:                                         FOR   18  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 18
NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

我们得到与使用该函数的最简单方法相同的结果。

Warning

当参数没有按照适当的顺序提供时，将会发生错误：在这个例子中，表 mytable 的源列 source 被错误地传递给函数作为几何列，而几何列``the_geom`` 被错误地传递给函数作为源列。

SELECT  pgr_createVerticesTable('edges', 'source', 'geom', 'target');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('edges','source','geom','target','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  ----> PGR ERROR in pgr_createVerticesTable: Wrong type of Column source: geom
HINT:    ----> Expected type of geom is integer, smallint or bigint but USER-DEFINED was found
NOTICE:  Unexpected error raise_exception
 pgr_createverticestable
-------------------------
 FAIL
(1 row)

当使用命名符号时

示例3:: 参数的顺序并不重要：

SELECT  pgr_createVerticesTable('edges', the_geom:='geom', source:='source', target:='target');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.edges_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  17 VERTICES
NOTICE:                                         FOR   18  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 18
NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

示例 4:: 使用不同的排序

SELECT  pgr_createVerticesTable('edges', source:='source', target:='target', the_geom:='geom');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.edges_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  17 VERTICES
NOTICE:                                         FOR   18  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 18
NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

示例 5:: 定义有默认值的参数可以省略，只要该值与默认值匹配即可：

SELECT  pgr_createVerticesTable('edges', 'geom', source:='source');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('edges','geom','source','target','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.edges_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  17 VERTICES
NOTICE:                                         FOR   18  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 18
NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

使用 rows_where 参数选择行

例 6:: 根据 id 选择行。

SELECT  pgr_createVerticesTable('edges', 'geom', rows_where:='id < 10');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('edges','geom','source','target','id < 10')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.edges_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  9 VERTICES
NOTICE:                                         FOR   10  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 10
NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

示例 7:: 选择几何与具有 id =5的行的几何接近的行。

SELECT  pgr_createVerticesTable('edges', 'geom',
    rows_where:='geom && (select st_buffer(geom,0.5) FROM edges WHERE id=5)');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('edges','geom','source','target','geom && (select st_buffer(geom,0.5) FROM edges WHERE id=5)')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.edges_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  9 VERTICES
NOTICE:                                         FOR   9  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 9
NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

示例 8:: 选择几何形状接近表 othertable 的``gid`` = 100 的行的几何形状的行。

DROP TABLE IF EXISTS otherTable;
NOTICE:  table "othertable" does not exist, skipping
DROP TABLE
CREATE TABLE otherTable AS  (SELECT 100 AS gid, st_point(2.5,2.5) AS other_geom) ;
SELECT 1
SELECT  pgr_createVerticesTable('edges', 'geom',
    rows_where:='geom && (select st_buffer(other_geom,0.5) FROM otherTable WHERE gid=100)');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('edges','geom','source','target','geom && (select st_buffer(other_geom,0.5) FROM otherTable WHERE gid=100)')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.edges_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  10 VERTICES
NOTICE:                                         FOR   12  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 12
NOTICE:  Vertices table for table public.edges is: public.edges_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

当边表的列与默认值不匹配时的用法：¶

使用下表

DROP TABLE IF EXISTS mytable;
NOTICE:  table "mytable" does not exist, skipping
DROP TABLE
CREATE TABLE mytable AS (SELECT id AS gid, geom AS mygeom, source AS src ,target AS tgt FROM edges) ;
SELECT 18

使用位置符号：

示例 9:: 参数需要按照参数中描述的顺序给出：

SELECT  pgr_createVerticesTable('mytable', 'mygeom', 'src', 'tgt');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.mytable_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  17 VERTICES
NOTICE:                                         FOR   18  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 18
NOTICE:  Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

Warning

当参数没有按照适当的顺序提供时，会发生错误。在这个例子中，表 mytable 的``src`` 列被错误地传递给函数作为几何列，而几何列 mygeom 被错误地传递给函数作为源列。

SELECT  pgr_createVerticesTable('mytable', 'src', 'mygeom', 'tgt');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','src','mygeom','tgt','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  ----> PGR ERROR in pgr_createVerticesTable: Wrong type of Column source: mygeom
HINT:    ----> Expected type of mygeom is integer, smallint or bigint but USER-DEFINED was found
NOTICE:  Unexpected error raise_exception
 pgr_createverticestable
-------------------------
 FAIL
(1 row)

当使用命名符号时

示例 10:: 参数的顺序并不重要：

SELECT  pgr_createVerticesTable('mytable',the_geom:='mygeom',source:='src',target:='tgt');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.mytable_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  17 VERTICES
NOTICE:                                         FOR   18  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 18
NOTICE:  Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

示例 11:: 使用不同的排序

在这种情况下，省略参数会产生错误，因为列名的默认值与表的列名不匹配。

SELECT  pgr_createVerticesTable(
    'mytable', source:='src', target:='tgt',
    the_geom:='mygeom');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','true')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.mytable_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  17 VERTICES
NOTICE:                                         FOR   18  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 18
NOTICE:  Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

使用 rows_where 参数选择行

示例 12:: 根据 gid 选择行（使用位置表示法）

SELECT  pgr_createVerticesTable(
    'mytable', 'mygeom', 'src', 'tgt',
    rows_where:='gid < 10');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','gid < 10')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.mytable_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  9 VERTICES
NOTICE:                                         FOR   10  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 10
NOTICE:  Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

示例 13:: 根据 gid 选择行。（使用命名表示法）

SELECT  pgr_createVerticesTable(
    'mytable', source:='src', target:='tgt', the_geom:='mygeom',
    rows_where:='gid < 10');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','gid < 10')
NOTICE:  Performing checks, please wait .....
NOTICE:  Populating public.mytable_vertices_pgr, please wait...
NOTICE:    ----->   VERTICES TABLE CREATED WITH  9 VERTICES
NOTICE:                                         FOR   10  EDGES
NOTICE:    Edges with NULL geometry,source or target: 0
NOTICE:                              Edges processed: 10
NOTICE:  Vertices table for table public.mytable is: public.mytable_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createverticestable
-------------------------
 OK
(1 row)

示例 14:: 选择几何与具有 gid =5的行的几何接近的行。

SELECT  pgr_createVerticesTable(
    'mytable', 'mygeom', 'src', 'tgt',
    rows_where := 'the_geom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE gid=5)');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','the_geom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE gid=5)')
NOTICE:  Performing checks, please wait .....
NOTICE:  Got column "the_geom" does not exist
NOTICE:  ERROR: Condition is not correct, please execute the following query to test your condition
NOTICE:  select * from public.mytable WHERE true AND (the_geom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE gid=5)) limit 1
 pgr_createverticestable
-------------------------
 FAIL
(1 row)

示例 15:: TBD

SELECT  pgr_createVerticesTable(
    'mytable', source:='src', target:='tgt', the_geom:='mygeom',
    rows_where:='mygeom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE id=5)');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','mygeom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE id=5)')
NOTICE:  Performing checks, please wait .....
NOTICE:  Got column "id" does not exist
NOTICE:  ERROR: Condition is not correct, please execute the following query to test your condition
NOTICE:  select * from public.mytable WHERE true AND (mygeom && (SELECT st_buffer(mygeom,0.5) FROM mytable WHERE id=5)) limit 1
 pgr_createverticestable
-------------------------
 FAIL
(1 row)

示例 16:: 选择几何形状接近表 othertable 的``gid`` = 100 的行的几何形状的行。

DROP TABLE IF EXISTS otherTable;
DROP TABLE
CREATE TABLE otherTable AS  (SELECT 100 AS gid, st_point(2.5,2.5) AS other_geom) ;
SELECT 1

SELECT pgr_createVerticesTable(
    'mytable', 'mygeom', 'src', 'tgt',
    rows_where:='the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)')
NOTICE:  Performing checks, please wait .....
NOTICE:  Got column "the_geom" does not exist
NOTICE:  ERROR: Condition is not correct, please execute the following query to test your condition
NOTICE:  select * from public.mytable WHERE true AND (the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)) limit 1
 pgr_createverticestable
-------------------------
 FAIL
(1 row)

示例 17:: TBD

SELECT  pgr_createVerticesTable(
    'mytable',source:='src',target:='tgt',the_geom:='mygeom',
    rows_where:='the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)');
NOTICE:  PROCESSING:
NOTICE:  pgr_createVerticesTable('mytable','mygeom','src','tgt','the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)')
NOTICE:  Performing checks, please wait .....
NOTICE:  Got column "the_geom" does not exist
NOTICE:  ERROR: Condition is not correct, please execute the following query to test your condition
NOTICE:  select * from public.mytable WHERE true AND (the_geom && (SELECT st_buffer(othergeom,0.5) FROM otherTable WHERE gid=100)) limit 1
 pgr_createverticestable
-------------------------
 FAIL
(1 row)

本例使用示例数据网络。

另请参阅¶

Topology - 函数族用于路由算法的拓扑概述。
pgr_createTopology <pgr_create_topology>` 根据几何图形创建拓扑。
pgr_analyzeGraph 分析边表的边和顶点。
pgr_analyzeOneWay 分析边缘的方向性。

索引和表格