How does assign_vertex_id() in pgRouting work?

Question

I'm trying to follow this tutorial,

http://www.pgrouting.org/docs/foss4g2008/ch06.html

but on the step with assign_vertex_id() it shows an error for executing the SQL query:

SQL error:

ERROR:  column "’ways’" does not exist
LINE 1: SELECT assign_vertex_id(’ways’, 0.00001, ’the_geom’, ’gid’);
                            ^
In statement:
SELECT assign_vertex_id(’ways’, 0.00001, ’the_geom’, ’gid’);

I have not found any documentation or forums regarding how assign_vertex_id() should actually work.

The only thing that I know is that the first parameter should be a table_name and not a column name.

Answer 1

Remember that assign_vertex_id is just a PLPGSQL procedure so you can read through the code easily and if you really need to, you can create your own version of assign_vertex_id and do "printf debugging" via "RAISE NOTICE".

What assign_vertex_id does, given a table of geometries, is to fill in the source and target columns of this table in such a way that connected geometries share vertex ids.

Considering the geometries described in the diagram, i.e.:

Name        | Geometry                             | Source     | Target
-------------------------------------------------------------------------
A           | LINESTRING((1,3),(2,3))              |            |
B           | LINESTRING((2,3),(3,3))              |            |
C           | LINESTRING((2,3),(2,2),(4,2),(4,4))  |            |
D           | LINESTRING((1,3),(1,1),(3,1),(3,3))  |            |

assign_vertex_id will, for each start and end node of A-D, compute vertexes and fill in source and target references.

Note that although (1,1) is a POINT in the LINESTRING geometry of D, it cannot be used for routing because it's not a terminal node.

That means this diagram features 4 nodes (namely (1,3), (2,3), (3,3) and (4,4)). assign_vertex_id numbers the vertices based on a sequence, so they will be numbered 1 to 4.

When assigning source and target nodes for edges A-D, assign_vertex_id will take the start and end nodes one at a time, i.e.

• For edge A, start node is (1,3) which is not already in the vertex table. We assign it with vertex id 1 and add it in the vertex table.
• For edge A, end node is (2,3) which is not already in the vertex table. Add it with vertex id 2.
• For edge B, start node is (2,3), already in the vertex table. Thus the source value of edge B is 2 (i.e. the target of A is connected to the source of B).

If you continue the algorithm above, you will get:

vertices table (vertices_tmp):

Id       | Geometry
--------------------------------
1        | POINT(1,3)
2        | POINT(2,3)
3        | POINT(3,3)
4        | POINT(4,4)

edges table:

Name        | Geometry                             | Source     | Target
-------------------------------------------------------------------------
A           | LINESTRING((1,3),(2,3))              |    1       |    2
B           | LINESTRING((2,3),(3,3))              |    2       |    3
C           | LINESTRING((2,3),(2,2),(4,2),(4,4))  |    2       |    4
D           | LINESTRING((1,3),(1,1),(3,1),(3,3))  |    1       |    3

Answer 2

ERROR:  column "’ways’" does not exist

tells you that there is no column called 'ways'. Thats because it's called ways (without ').

Your query should instead be:

SELECT assign_vertex_id("ways", 0.00001, "the_geom", "gid");

" is not equal to ' in PostGIS.