root/branches/workshop/FOSS4G2008/Docs/06_pgrouting.topology.chapter

= Load your network data and create a network topology =

Some network data already comes with a network topology that can be used with
pgRouting immediately. But usually the data is in a different format than we 
need for pgRouting. Often network data is stored in the Shape file format (.shp) 
and we can use PostGIS' shape2postgresql converter to import the data into the 
database. OpenStreetMap stores its data as XML and it has its own importing 
tools for PostgreSQL database.

Later we will use the osm2pgrouting converter. But it does much more than the 
basic steps for simple routing, so we will start this workshop with the minimum
required attributes. 

== Load the network data ==

After creating the workshop database and adding the PostGIS and pgRouting 
functions to this database (see previous chapter), we load the sample data to
our database:
{{{
psql -U postgres routing
\i /home/foss4g/ways_without_topology.sql
}}}

Note: The SQL dump file was made from a database which already had PostGIS 
functions loaded, so it will report errors during import that these functions 
already exist. You can ignore these errors.

Let's see witch tables have been created:
{{{
\d

              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | geometry_columns | table | postgres
 public | spatial_ref_sys  | table | postgres
 public | ways             | table | postgres
(3 rows)

\d ways

           Table "public.ways"
  Column  |       Type       | Modifiers 
----------+------------------+-----------
 gid      | integer          | not null
 length   | double precision | 
 name     | character(200)   | 
 the_geom | geometry         | 
Indexes:
    "ways_pkey" PRIMARY KEY, btree (gid)
Check constraints:
    "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
    "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
}}}

== Create network topology ==

Having your data imported into a PostgreSQL database usually requires one more
step for pgRouting. You have to make sure that your data provides a correct 
network topology, which consists of links with source and target ID each.

If your network data doesn't have such network topology information already
you need to run the "assign_vertex_id" function. This function assigns a source
and a target ID to each link and it can "snap" nearby vertices within a
certain tolerance.

{{{
assign_vertex_id('<table>', float tolerance, '<geometry column', '<gid>')
}}}

First we have to add source and target column, then we run the assign_vertex_id
function ... and wait. 
{{{ 
ALTER TABLE ways ADD COLUMN source integer;
ALTER TABLE ways ADD COLUMN target integer;
SELECT assign_vertex_id('ways', 0.00001, 'the_geom', 'gid');
}}}

Note: The dimension of the tolerance parameter depends on your data projection.
Usually it's either "degrees" or "meters". Because OSM data has a very good 
quality for Cape town we can choose a very small "snapping" tolerance: 
0.00001 degrees

== Add indices ==

Fortunately we didn't need to wait too long because the data is small. But your 
network data might be very large, so it's a good idea to add an index on source, 
target and geometry column.
{{{
CREATE INDEX source_idx ON ways(source);
CREATE INDEX target_idx ON ways(target);
CREATE INDEX geom_idx ON ways USING GIST(the_geom GIST_GEOMETRY_OPS);
}}}

After these steps our routing database look like this:
{{{
\d

                 List of relations
 Schema |        Name         |   Type   |  Owner   
--------+---------------------+----------+----------
 public | geometry_columns    | table    | postgres
 public | spatial_ref_sys     | table    | postgres
 public | vertices_tmp        | table    | postgres
 public | vertices_tmp_id_seq | sequence | postgres
 public | ways                | table    | postgres
(5 rows)

\d ways

           Table "public.ways"
  Column  |       Type       | Modifiers 
----------+------------------+-----------
 gid      | integer          | not null
 length   | double precision | 
 name     | character(200)   | 
 the_geom | geometry         | 
 source   | integer          | 
 target   | integer          | 
Indexes:
    "ways_pkey" PRIMARY KEY, btree (gid)
Check constraints:
    "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
    "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
}}}

Now we are ready for routing with Dijkstra algorithm!