Solving equations graphically with PostGIS

By Monday November 2nd, 2020Non classé

Oslandia supports the “Direction de Etudes Amont et de l’Innovation” (DEAI) of Egis Rail in the overhaul and the integration of its business tools.

For the feasibility study of the new design of public transportation lines, the objective is to have a web application allowing:

  • to draw public transport lines on a base plan,
  • to study the impact of the lines in terms of the population served,
  • to estimate their construction and operating costs.

These needs are currently met by some desktop GIS applications, some specialized tools (for example calculation of pedestrian isochrones) and business tools developed internally.

The solution being implemented is based on a PostGIS database, by fully leveraging its rich set of features to integrate processing into the data model.

This approach makes it possible to develop the model, test it and consolidate the ergonomics using QGIS as a prototype interface before developing the web application (#thickdatabase).

Prototype interface in QGIS. Isochrones 10 min walk from a station are calculated with pg_routing.

The context having been set, this article focuses on the calculation of travel time which is involved in estimating the operating costs of a line.

The train driver chooses between three options: speed up, slow down or maintain speed. This choice is subject to several constraints:

  • station stops
  • the maximum authorized speed on a section which depends on the environment (in the middle of a boulevard or in a pedestrian area)
  • maximum curve speed
  • maximum speed at intersections and pedestrian crossings
  • stops at traffic lights (when they are red)

To determine the travel time, you need to know the speed at any point on the line. We’ll assume constant acceleration and deceleration. The problem that arises is therefore to determine, on the line, the areas of acceleration A, of constant speed O and of deceleration D.

Calculation of travel time in the simple case

Between two stations with a maximum speed constraint, the driver accelerates then maintains his speed when the maximum speed is reached and finally decelerates to stop.

In the simple case where the length of the section is sufficient to reach the maximum authorized speed V, the time tA to wait for the maximum authorized speed is V/A, the length sA traversed during tA is:

sA = AtA2/2

or :

sA = V2/2A

likewise the deceleration distance sD = V2/2D and the length traveled at constant speed sO = stotal – sA – sD.

In the case where the section is not long enough to reach the maximum authorized speed, the maximum reached speed v is such that v = AtA = DtD where t A and tD are the acceleration and deceleration times respectively. To determine tA and tD the second equation is the total length of the section stotal = AtA2/2 + DtD2/2… tA = sqrt(2Dstotal< /sub>/(AD+A2))…

It is almost trivial when only these two cases are to be considered:

  • we assume a section long enough to calculate sA and sD,
  • we check that sO is positive or zero,
  • if this is not the case, we use the formulas for short sections.

But there is also the case where the maximum speed is not constant between two stations, the case where it is necessary to slow down when approaching a pedestrian crossing… In short, many special cases and associated conditional structures.

It can be coded, but it lacks a bit of elegance.

Calculation of travel time from the distance-speed curve

Example of a line with maximum speed constraints on segments and constraints defined punctually.

The distance-speed diagram gives the speed of the train at any point on the line.

In this diagram we can position the stations: we know their abscissas (the distances of the stations from the origin of the line) and their ordinates (zero speed).

Distance – speed diagram with stations A, C and D as imposed crossing points for the curve.

It is also possible to draw an area in which the speed curve does not pass because of the speed limits on the sections.

Area of the distance-speed diagram inaccessible because of the speed constraints on the sections.

The velocity is a function of time, when accelerating constantly: v(t) = At + v(0) and s(t) = At2/2 + v(0)t + s(0 ).

We know how to draw this curve with PostGIS:

select st_makeline( st_makepoint( 0.5 * A *t^2, A*t ) ) from generate_series(0, 100, 0.5) t;
Acceleration curve with distance traveled as abscissa and the speed on the ordinate… plotted in a completely arbitrary carto reference frame, to illustrate the point

With that it is enough to trace the accelerations and decelerations from the stations:

Accelerations and decelerations from stations.

Accelerations and decelerations linked to authorized speed limit changes:

Changes paces.

You can easily add points (e.g. pedestrian crossing) to segments (e.g. curve), where the speed is limited with the associated acceleration and deceleration:

Constraints additional speed on curves and at pedestrian crossings.

To extract the velocity curve from this set of limits, we divide the curves by each other, with st_split in order to obtain topologically clean borders, we use st_polygonize to extract the polygons defined by these borders. We only keep the polygons that are glued to the abscissa axis, in other words those for which st_length( st_intersection( polygon, axe_x ) ) > 0. The distance-velocity curve is the set of contours of these polygons from which we subtract the abscissa axis:

st_limemerge( st_difference( st_exterior_ring( polygon, x_axis ) ) )
The optimal velocity curve is the upper part of the contours of the polygons touching the abscissa axis.

Once the distance-speed curve has been obtained, a numerical integration is carried out to determine the travel times.

I hope that, like me, you found this diversion of PostGIS interesting which, beyond the storage and processing of geographic information, is an excellent tool for doing geometry.

If you are interested in these topics, do not hesitate to contact us (