Download "user manual"

Routing Plugin
26.2.2.2. Graph Importation
The first step is to add needed columns to the table roads_europe. To do so, you can
type:
$ ALTER
$ ALTER
$ ALTER
-- next
$ ALTER
TABLE roads_europe ADD COLUMN source_id int;
TABLE roads_europe ADD COLUMN target_id int;
TABLE roads_europe ADD COLUMN edge_id int;
line is to work around a pgRouting bug in update_cost_from_distance (fixed in latest CVS)
TABLE roads_europe RENAME id TO id_old;
You can then fill the columns source_id and target_id with the "assign_vertex_id"
function.
$ SELECT assign_vertex_id('roads_europe', 1);
Here is the content of the roads_europe table:
$ SELECT gid, source_id, target_id, edge_id, AStext(the_geom) FROM roads_europe limit 3;
gid | source_id | target_id | edge_id |
AStext
-------+-----------+-----------+---------+---------------------------------------------13115 |
11051 |
11099 |
14 | MULTILINESTRING((1062096.06 4861316.234,...))
12869 |
10918 |
10916 |
267 | MULTILINESTRING((250681.597 4779596.532,...))
12868 |
10918 |
10913 |
268 | MULTILINESTRING((250681.597 4779596.532,...))
(3 lignes)
But if the data quality is poor, you need to delete the duplicates edges (they have the
same source-target pairs of vertices). For example, to check that you have duplicated
edges, you can type:
$ SELECT * FROM (SELECT source_id, target_id, count(*) AS c FROM roads_europe group by
source_id, target_id order by c)
AS foo where foo.c = 2;
If there is duplicated edges, to delete one of two rows, you can type:
$ CREATE TABLE doublons AS SELECT * FROM roads_europe WHERE gid in
(SELECT gid FROM (SELECT DISTINCT on (source_id, target_id) source_id, gid
FROM roads_europe) AS doublon);
$ DELETE FROM roads_europe;
$ INSERT INTO roads_europe (SELECT * FROM doublons);
$ DROP TABLE doublons;
The following step is to create and fill the edges and vertices tables of the resulting
graph. To do so, you can use "create_graph_tables" function.
$ SELECT create_graph_tables('roads_europe', 'int4');
SELECT * FROM roads_europe_edges LIMIT 3;
id | source | target | cost | reverse_cost
----+--------+--------+------+-------------1 |
1 |
2 |
|
2 |
3 |
3 |
|
4 |
2 |
2 |
|
154