Create homogenous road segments based on road characteristics (1 of 3)

Hello, World! It’s been a long time, hasn’t it? If my blog was a bear, it would be stretching and yawning after years of hibernation, wondering why on earth it decided to sleep through the equivalent of several ice ages in the digital era. Well, I don’t really have a good excuse for that, except, well, life? Anyway, here it goes. This first post, (actually a series of 3 posts), returning back from this years-long hiatus came about because of a project brief that included, among others, “digitize the road network of area X, but make sure that it splits wherever any of the network characteristics change”. These network characteristics – 15 of them – included slope over 15%, road width, whether it was visible on some ancient (‘50s) orthophotos, etc.

This took a bit of thinking. The obvious way would be to do exactly that. The digitizing team would have to go through each network segment and check where any of these 15 properties change, and then split the network at these points and populate the relevant attribute(s). A (very) long and tedious task and prone to mistakes.

So, instead, I turned it on its head. Properties could be ‘events’. These events could be digitised on top of the network one by one. Each of them will have a single attribute which would be the relevant network property. A much easier task. And then, use the first one to split the network and ‘move’ that attribute onto the split network. And on that split network, use the second event to split it further and then use the 3rd event to split that twice-split network, and so on… The screenshot below shows the general concept for the first two iterations:

To achieve this task, I turned to my favorite database of all time, PostgreSQL and of course, PostGIS. In this post, I will only use 3 events (slope, width, orthophotos). So, without further ado, this is the DDL of the initial road network table:

CREATE TABLE roads
(
id          bigserial NOT null,
slope15     Boolean,
width      integer,
ortho50  Boolean,
geom public.geometry(multilinestringM, 2100) NULL,
CONSTRAINT pk_roads PRIMARY KEY (id)
);
-- Create spatial index
CREATE INDEX sidx_roads_geom ON public.roads USING gist (geom);

Note lines 4,5 and 6. Since I will be using a lot of dynamic sql, I have named these fields with the same name as the relevant events. In the final split roads table, these values will be populated with the values of the underlying events.

And this is the DDL for the event tables.

CREATE TABLE slope15  
(id bigserial NOT NULL, 
 slope15   Boolean, 
 road_id bigint, 
 start_offset numeric, 
 end_offset numeric, 
 geom public.geometry(linestring,2100) NULL,
     CONSTRAINT pk_slope15  PRIMARY KEY (id)
);
CREATE INDEX sidx_slope15_geom ON public.slope15 USING gist (geom);
--
CREATE TABLE road_width        
(id bigserial NOT NULL, 
 road_width      integer, 
 road_id bigint, 
 start_offset numeric, 
 end_offset numeric, 
 geom public.geometry(linestring,2100) NULL,
     CONSTRAINT pk_road_width  PRIMARY KEY (id)
);
CREATE INDEX sidx_road_width_geom ON public.road_width USING gist (geom);
--
CREATE TABLE ortho50  
(id bigserial NOT NULL, 
 ortho50      Boolean, 
 road_id bigint, 
 start_offset numeric, 
 end_offset numeric, 
 geom public.geometry(linestring,2100) NULL,
     CONSTRAINT pk_ortho50  PRIMARY KEY (id)
);
CREATE INDEX sidx_ortho50_geom ON public.ortho50  USING gist (geom);

You will notice, that all event tables have some ‘fixed’ fields (id, road_id, start and end offsets, and geometry) but also a field name that is the same as the table name (the highlighted rows). Again, this is because of the dynamic SQL we will be using down the line.

Next, we will need to create some trigger functions and triggers to be used when creating the events or editing the network using dynamic segmentation. The idea is that when creating a linear event, which of course should be located along the network, the user won’t have to trace along the segment but instead click on the start and end point and the trigger will use some dynamic segmentation function in PostGIS to create its geometry. Something like this (example from QGIS with an assigned offset to the red event layer):

But more on this in the next post. Stay tuned!