Database importing and topological relationships

Now that we've prepared the OSM data, we need to actually load it into the database. Here, we can generate the topological relationships based on geographic relationships as determined by PostGIS.

Connecting to the database

Although we will be working from the Database Manager when dealing with the database in QGIS, we will first need to connect to the database through the normal "Add Layer" dialog. Perform the following steps:

Navigate to Layer | Add Layer | Add PostGIS Layers.
Click on New.
In the Create a New PostGIS connection dialog, enter the following parameters, accepting others as their defaults:
- Name: packt_c4
- Host: localhost
- Database: packt_c4
- Username/password: As configured earlier in this chapter
Click on Test Connect to make sure you've entered the correct information.
You may wish to save your credentials, as shown here:

Importing into PostGIS with DB Manager

Once we've added the database connection, DB Manager is where we'll be interacting with the database. DB Manager provides query access via the SQL syntax as well as the facility to add results as a virtual (in memory, not on disk) layer. We can also use DB Manager to import or export data to/from the database when necessary. Perform the following steps:

Go to Database | DB Manager | DB Manager.
You may need to navigate to Database | Refresh to have a new database appear.
Select the database to be updated (for example, packt_c4). The following is an image of the Database Manager and tables, which were generated when you created your new PostGIS database:
Navigate to Table | Import layer/file.
Input the following parameters:
- Input: Split lines
- Table: newark_osm
- Source SRID: 2880
- Target SRID: 2880
- Select Create single-part geometries instead of multi-part
- Select Create spatial index, as shown in the following screenshot:

Repeat these steps with the students layer:

Add the students layer from c4/data/original/students.shp.
Repeat the previous Import vector layer steps with the students layer, as shown in the following screenshot:

The imported tables and the associated schema and metadata information will now be visible in DB Manager, as shown in the following screenshot:

Creating the topological network data

Next, run a query that adds the necessary fields to the newark_osm table, updating these with the topological information, and create the related table of the network vertices, newark_osm_vertices. These field names and types, expected by pgRouting, are added by the alter queries and populated by the pgr_createTopology pgRouting function. The length_m field is populated with the segment length using an update query with the st_length function (and st_transform here to control the spatial reference). This field will be used to help determine the cost of the shortest path (minimum cost) routing. Perform the following steps:

Navigate to Database | DB Manager | DB Manager.
Select the database to be updated.

Go to Database | SQL window. Enter the following code:

alter table newark_osm add column source integer;
alter table newark_osm add column target integer;
select pgr_createTopology('newark_osm', 0.0001, 'geom', 'id');

alter table newark_osm add column length_m float8;
update newark_osm set length_m = st_length(st_transform(geom,2880));

An alternate workflow: topology with osm2po

The osm2po program performs many topological dataset preparation tasks that might otherwise require a longer workflow—such as the preceding task. As the name indicates, it is specifically used for the OpenStreetMap data. The osm2po program must be downloaded and installed separately from the osm2po website, http://osm2po.de. Once the program is installed, it is used as follows:

[..] > cd c:\packt\c4\data\output
c:\packt\c4\data\output>java -jar osm2po-5.0.0\osm2po-core-5.0.0-signed.jar cmd=tj
sp newark_osm.osm

This command will create a .sql file that you can run in your database to add the topological table to your database, producing something very similar to what we did in the preceding section.

Using the pgRouting Layer plugin to test

Let's use the pgRouting Layer plugin to test whether the steps we've performed up to this point have produced a functioning topological network to find the shortest path. We will find the shortest path between two arbitrary points on the network: 1 and 1000. Perform the following steps:

Install the pgRoutingLayer plugin.
If the shortest path panel is not displayed, turn it on under View | Panels | pgRouting Layer.
Enter the following parameters:
- Database: packt_c4.
- Ensure that you are already connected to the database, as shown in the previous section. You may need to restart QGIS for a new database connection to show up here.
- edge_table: newark_osm
- geometry: geom
- id: id
- source: source
- target: target
- cost: length_m
- source_id: 1
- target_id: 1000

Your output will look similar to the following image:

Using the pgRouting Layer plugin to test

Previous Chapter

OpenStreetMap data for topology

Next Chapter

Creating the travel time isochron polygons