In this example, we will use an example of a routable shapefile downloaded from GeoFabrik.de - https://www.geofabrik.de/data/shapefiles_routable_vienna.zip. A routable shapefile, as GeoFabrik describes it, is a standard shapefile that contains OSM data preprocessed with routing in mind. This means it contains only road data with lines split at intersections, with some speed limits information, road line lengths, and so on.

We have already addressed importing shapefiles to PostGIS, so you can use a tool of your choice; in this case, I am using osm2pgsql:

shp2pgsql -s 4326 roads pgr.shp_roads | psql -h localhost -p 5434 -U postgres -d mastering_postgis 

Once our vector makes it to the database, we need to do some further processing before it is possible to issue pgRouting-specific queries against the dataset.

pgRouting requires the network edges (road lines) to be LineStrings. You may have noticed that shp2pgsql imported our lines as MultiLineStrings, but let's verify this:

select distinct st_GeometryType(geom) from pgr.shp_roads; 

The result should be ST MulitLineString and we have to fix that.

In order to turn the MultiLineStrings into LineStrings (and split them when required) a ST_Dump function will come in handy. Basically, it extracts the geometry paths off a MultiGeometry:

select 
   gid, osm_id, code, fclass, name, ref, oneway, maxspeed, layer, ete, speed, length, bridge, tunnel, 
   (ST_Dump(geom)).geom as geom 
into pgr.shp_roads_fixed 
from 
   pgr.shp_roads; 

Let's verify our fixed data - this time, the expected output is ST LineString:

select distinct st_GeometryType(geom) from pgr.shp_roads_fixed; 

pgRouting works with its own specific flavor of topology, so we need to create pgRouting topology for our data. We will use a pgr_CreateTopology function to prepare the data. It requires two extra columns in the roads dataset, both integers - one for the start point identifier and the other one for the endpoint identifier; their default names are source and target, respectively. Let's modify our roads data model to cater for the requirements:

ALTER TABLE pgr.shp_roads_fixed ADD COLUMN source integer; 
ALTER TABLE pgr.shp_roads_fixed ADD COLUMN target integer; 

At this stage, we should be able to create the pgRouting topology. So, let's do just that:

select pgr_createTopology( 
   'pgr.shp_roads_fixed', --edge_table; network table name 
   0.000001, --tolerance; snapping tolerance with disconnected edges 
   'geom', --name of the geometry column in the network table 
   'gid', --name of the identifier column in the network table 
   'source', --name of the source identifier column in the network table 
   'target' --name of the target identifier column in the network table 
   --rows_where: condition to select a subset of records; defaults to true to process all the rows where the source / target are nulls; otherwise the subset of rows is processed 
   --clean: boolean - clean any previous topology; defaults to false 
); 

In the preceding code, I included the explanation of each parameter. Basically, this code creates pgRouting topology for our roads dataset. The roads table gets modified - source and target columns are filled with new data; indexes are created for ID, geom, source, and target columns.

If the topology has been created, you should see an OK result; FAIL otherwise. If the operation failed and you happen to use pgAdmin to run queries, review the Messages tab for details on what the reason for the failure.

You should notice that a new table has been created: shp_roads_fixed_vertices_pgr.

We can now verify the graph by using the pgr_analyzeGraph function:

select pgr_analyzeGraph('pgr.shp_roads_fixed', 0.00001, 'geom', 'gid'); 

It will also show either OK or FAIL, but when you look at the notifications (if you run the command via psql, you will see the output in the CMD; in pgAdmin you will need to switch to the Messages tab) you should see a similar output:

NOTICE:  Performing checks, please wait ...
NOTICE:  Analyzing for dead ends. Please wait...
NOTICE:  Analyzing for gaps. Please wait...
NOTICE:  Analyzing for isolated edges. Please wait...
NOTICE:  Analyzing for ring geometries. Please wait...
NOTICE:  Analyzing for intersections. Please wait...
NOTICE:              ANALYSIS RESULTS FOR SELECTED EDGES:
NOTICE:                    Isolated segments: 59
NOTICE:                            Dead ends: 7534
NOTICE:  Potential gaps found near dead ends: 17
NOTICE:               Intersections detected: 1064
NOTICE:                      Ring geometries: 220
 pgr_analyzegraph
------------------
 OK
(1 row)

Dead ends and potential gap problems are also identified in the vertices table in the cnt and chk columns:

select  
   (select count(*) FROM pgr.shp_roads_fixed_vertices_pgr WHERE cnt = 1) as deadends, 
   (select count(*) FROM pgr.shp_roads_fixed_vertices_pgr WHERE chk = 1) as gaps