jore4-digiroad-import

Overview

This repository provides scripts to download Digiroad shapefiles for road and public transport stop geometries and make transformations required by JORE4 microservices.

Firstly, Digiroad road links, public transport stops and other related data is downloaded and imported from shapefiles into a PostGIS database (contained in a Docker container) by executing the import script (import_digiroad_shapefiles.sh). During execution of the import script data is further processed in the database after which the data can be exported in a couple of formats relevant to various JORE4 services.

The database is used only locally for processing data. Each invocation of the import script will recreate the Docker container and reset the state of database used for data processing.

Building Docker image

Almost all the of the data processing will be done inside a Docker container. The Docker container contains a PostGIS database enabled with pgRouting extension.

The Docker image for the container is built with:

./build_docker_image.sh

Importing data from Digiroad

Initially, Digiroad shapefiles are downloaded and imported into PostGIS database of the Docker container by running:

./import_digiroad_shapefiles.sh

The shapefiles are, by default, imported into a database schema named digiroad. The schema name can be changed in set_env_vars.sh script.

Within the script execution further processing for Digiroad data is done as well.

JORE4 fix layer on top of Digiroad links (a.k.a. QGIS fixup layer)

The JORE4 project involves a infrastructure "fixup layer" in the form of a QGIS project. The QGIS project file (fixup/jore4-digiroad-fix-project.qgs) contains several layers or layer groups:

• Background map tiles from an online service (currently Digitransit)
• Digiroad infrastructure links (DR_LINKKI) from shapefiles covering Uusimaa (administrative region)
• Digiroad public transport stops (DR_PYSAKKI) from shapefiles covering Uusimaa (administrative region)
• JORE4 fix layer

With the JORE4 fix layer HSL-specific customisations to infrastructure network can be achieved. In JORE4, there is a need for more fine-grained infrastructure link modeling at some places (e.g. public transport terminals) than what Digiroad (eventually Maastotietokanta of MML) currently provides.

The JORE4 fix layer in the QGIS project is actually a QGIS layer group consisting of the QGIS layers described in the table below. The data for these layers is stored in and read from a separate GeoPackage file (fixup/digiroad/fixup.gpkg). This GeoPackage file must be updated and maintained during the operational use of JORE4.

QGIS layer	Description
add_links	Contains data about HSL supplementary infrastructure links that are added on top of or to replace existing Digiroad links. The LINESTRING geometry is a mandatory part of each added link. All geometries defined in this layer must be seamlessly connected to each other and/or existing Digiroad links. Seamlessness (or topological integrity) can be achieved by using the QGIS Snapping Tool when drawing line features.
remove_links	Contains geometries that are used to mark intersecting Digiroad links for removal. The links marked for removal will be filtered out when exporting data in later stages. The Digiroad public transport stops along the links marked for removal are also filtered out in data exports. It is recommended to use LINESTRING type in intersection geometries but currently this is not strictly required.
add_stop_points	Contains data for HSL-defined public transport stop points that are mainly used to replace the ones available in Digiroad. A POINT geometry is a mandatory part of each stop point to be added as well as valtak_id attribute that denotes so called ELY number. The closest infrastructure link to each stop point and other information is resolved automatically during import process.

GeoPackage add_links layer contents

The table below describes the columns of the add_links layer in the QGIS project. The data types are as they appear in the GeoPackage format (SQLite).

Column name	Data type	Not null	Description
fid	INTEGER	X	The primary key generated internally in GeoPackage.
geom	LINESTRING	X	The LINESTRING geometry describing the shape of this infrastructure link
kuntakoodi	MEDIUMINT	-	Official Finnish municipality code
linkkityyp	MEDIUMINT	-	The link type as code value from the corresponding Digiroad code set
ajosuunta	MEDIUMINT	X	The direction of traffic flow as code value from the corresponding Digiroad code set
silta_alik	MEDIUMINT	-	Is this infrastructure link a bridge, tunnel or underpass? The value must be selected from the corresponding Digiroad code set.
link_tila	MEDIUMINT	-	The state of infrastructure link in case it is not yet ready for traffic. The value must be selected from the corresponding Digiroad code set.
link_id	TEXT	X	The Digiroad ID of infrastructure link. In the case of HSL supplementary links, the prefix hsl: is used to prevent collisions with identifiers in the Digiroad link ID space.
original_link_id	TEXT	-	NULL for original Digiroad links. If a Digiroad link is replaced with an HSL supplementary link, this contains the ID of the original Digiroad link. It should be given at least in the case when HSL's supplementary links are created programmatically.
tienimi_su	TEXT	-	The name of infrastructure link in Finnish
tienimi_ru	TEXT	-	The name of infrastructure link in Swedish
is_generic_bus	BOOLEAN	-	Is this infrastructure link safely traversable by generic_bus vehicle type?
is_tall_electric_bus	BOOLEAN	-	Is this infrastructure link safely traversable by tall_electric_bus vehicle type?
is_tram	BOOLEAN	-	Is this infrastructure link traversable by tram?
is_train	BOOLEAN	-	Is this infrastructure link traversable by train?
is_metro	BOOLEAN	-	Is this infrastructure link traversable by metro?
is_ferry	BOOLEAN	-	Is this infrastructure link traversable by ferry?
description	TEXT	-	Description text provided by the author in the case of a supplementary link from HSL.

GeoPackage remove_links layer contents

The table below describes the columns of the remove_links layer in the QGIS project. The data types are as they appear in the GeoPackage format (SQLite).

Column name	Data type	Not null	Description
fid	INTEGER	X	The primary key generated internally in GeoPackage
geom	GEOMETRY	X	The geometry used to find all infrastructure links whose geometry intersects with it. The affected infrastructure links will be marked for removal and will not be included in data exports.

GeoPackage add_stop_points layer contents

The table below describes the columns of the add_stop_points layer in the QGIS project. The data types are as they appear in the GeoPackage format (SQLite).

Column name	Data type	Not null	Description
fid	INTEGER	X	The primary key generated internally in GeoPackage.
geom	POINT	X	The POINT geometry describing the location of this public transport stop point
valtak_id	INTEGER	X	The national ID for the stop point that is also known as ELY number. The column has a unique constraint.
matk_tunn	TEXT	-	The passenger ID for the stop point e.g. H1234
yllapitaja	INTEGER	-	A code value defined by Digiroad for the party responsible for maintaining the stop point.
yllap_tunn	TEXT	-	The identifier of the stop point, as defined by the party responsible for maintaining the stop point.
nimi_su	TEXT	-	The name of stop point in Finnish
nimi_ru	TEXT	-	The name of stop point in Swedish
link_id	TEXT	-	An optional reference to the infrastructure link along which the stop point is located. In case of NULL or unknown link ID, the link association will be calculated automatically during the import process.
vaik_suunt	INTEGER	-	Whether the stop point is effective in the direction of the LINESTRING of the associated infrastructure link or its inverse. The value must be selected from the corresponding Digiroad code set. The field is optional. In case of NULL value, the direction code is computed automatically during the import process.
sijainti_m	REAL	-	The distance in meters from the location of the stop point to the start point of the associated infrastructure link. The field is optional. In case of NULL value, the distance is calculated automatically during the import process.
notes	TEXT	-	Notes for/from traffic planner

Overwriting GeoPackage file

If you have made changes to the JORE4 fix layer by altering the table metadata or data in the database, you can write them to the GeoPackage file with the following script call.

./rewrite_fixup_geopkg.sh

Exporting Digiroad data

./export_pgdump_digiroad.sh

Before importing pg_dump file for Digiroad schema into target database the database must have postgis extension. E.g. the following commands create a database and user named digiroad and add postgis extension to the newly-created database. Remember to set up passwords as you wish.

CREATE DATABASE digiroad;
CREATE USER digiroad;
GRANT ALL PRIVILEGES ON DATABASE digiroad TO digiroad;
\c digiroad
CREATE EXTENSION postgis;

At the moment, there is no specific use case within JORE4 for the dump generated with the above command. However, it is planned that a separate schema will be generated later that will contain the infrastructure tables and columns used in JORE4 database.

Exporting routing schema

One can export the schema definitions and/or table data for JORE4 navigation and map-matching backend.

By executing export_routing_schema.sh, a separate routing schema is created in the database. The data is read from the Digiroad schema and is transformed into a table structure defined in and used by the JORE4 map-matching backend. As a result, two database dump files will be created: one in SQL format, named digiroad_r_routing_<date>.sql, and another in PostgreSQL's custom format, named digiroad_r_routing_<date>.pgdump. Both files will be written into workdir/pgdump subdirectory.

The SQL dump artifact can be uploaded to Azure Blob Storage with the command below. An active Azure subscription associated with JORE4 is required. Azure CLI is also required to be installed. In addition, the SQL dump file needs to be created on current day.

./upload_routing_dump_to_azure.sh

A couple of toc list (table of contents) files are generated as sidecars to the custom-format dump file. A toc file may be passed as an argument to pg_restore command. The toc files can be used to selectively apply what is being restored from the dump, e.g. the entire schema with data or table data for selected tables only.

The table below describes the contents of each toc file generated.

ToC file	Description
<date>_create_routing_schema_digiroad_r.pgdump.list	Contains entire routing schema and data.
<date>_create_routing_schema_digiroad_r.no-enums.links-and-stops.list	No schema item definitions at all. Contains table data for infrastructure links, topology and public transport stops. Does not include data for enum tables which is already included in the database migration scripts of the map-matching backend.
<date>_create_routing_schema_digiroad_r.pgdump.no-enums.only-links.list	No schema item definitions at all. Contains table data for infrastructure links and topology. Does not include public transport stops. Does not include data for enum tables which is already included in the database migration scripts of the map-matching backend.

Which one should be used will depend on what deployment strategy with regard to database migrations and data population is currently chosen in the map-matching backend. Have a look at README of map-matching backend for more details.

The target database is required to have postgis and pgrouting extensions.

Exporting infrastructure links for JORE4

To export a CSV containing intrastructure network links' data, run:

./export_infra_network_csv.sh

You may import this CSV data into an existing database adhering to JORE4 schema, using the command below. Note that infrastructure_network.infrastructure_link table (and schema) has to exist in the target database. Also note that the importer user must have read-write permissions to this table.

The script will interactively ask for the connection parameters of the target database. They default to the parameters defined in the jore4-tools repository for the jore4e2e database. You may set up the jore4e2e database locally with the ./start_dependencies.sh script.

Note: This script is currently only a proof of concept. It will create new links if they didn't exist or update them if they do. But links deleted in digiroad won't be deleted here.

./import_infra_network_csv.sh

Exporting stops for JORE3 Importer

If you want to export Digiroad public transport stops as a CSV file to used with JORE3 Importer, you have to run:

./export_stops_csv.sh

This command reads selected data items from filtered public transport stop data imported from Digiroad and writes it to the workdir/csv/digiroad_stops.csv file.

Exporting vector tiles

An MBTiles files containing filtered Digiroad road links can be exported with (assuming Digiroad shapefiles have already been imported):

./export_mbtiles_dr_linkki.sh

An MBTiles files containing filtered Digiroad public transport stops can be exported with (assuming Digiroad shapefiles have already been imported):

./export_mbtiles_dr_pysakki.sh

License

The project license is in LICENSE.

Digiroad data has been licensed with Creative Commons BY 4.0 license by the Finnish Transport Infrastructure Agency.

The Jore4 fix layer in ./fixup is licensed under Creative Commons BY 4.0 by Helsinki Regional Transport Authority (HSL).