Load your network data and create a network topology

Important

The content of this page is outdated and is for archiving purposes only.

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:

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:

Now we are ready for routing with Dijkstra algorithm!