Now use a PostgreSQL client such as psql or pgAdmin to explore the properties of the generated trajectories. We start by obtaining some statistics about the number, the total duration, and the total length in Km of the trips.

SELECT COUNT(*), SUM(timespan(Trip)), SUM(length(Trip)) / 1e3
FROM Trips;
-- 1686  "618:34:23.478239"  20546.31859281626

We continue by further analyzing the duration of all the trips

SELECT MIN(timespan(Trip)), MAX(timespan(Trip)), AVG(timespan(Trip))
FROM Trips;
-- "00:00:29.091033"  "01:13:21.225514"  "00:22:02.365486"

or the duration of the trips by trip type.

SELECT
  CASE
    WHEN T.source = V.home AND date_part('dow', T.day) BETWEEN 1 AND 5 AND
      date_part('hour', startTimestamp(trip)) < 12 THEN 'home_work'
    WHEN T.source = V.work AND date_part('dow', T.day) BETWEEN 1 AND 5 AND
      date_part('hour', startTimestamp(trip)) > 12  THEN 'work_home'
    WHEN date_part('dow', T.day) BETWEEN 1 AND 5 THEN 'leisure_weekday'
    ELSE 'leisure_weekend'
  END AS TripType, COUNT(*), MIN(timespan(Trip)), MAX(timespan(Trip)), AVG(timespan(Trip))
FROM Trips T, Vehicle V
WHERE T.vehicle = V.id
GROUP BY TripType;
-- "leisure_weekday"    558    "00:00:29.091033"  "00:57:30.195709"  "00:10:59.118318"
-- "work_home"          564    "00:02:04.159342"  "01:13:21.225514"  "00:27:33.424924"
-- "home_work"          564    "00:01:57.456419"  "01:11:44.551344"  "00:27:25.145454"

As can be seen, no weekend leisure trips have been generated, which is normal since the data generated covers four days starting on Monday, June 1^st 2020.

We can analyze further the length in Km of the trips as follows.

SELECT MIN(length(Trip)) / 1e3, MAX(length(Trip)) / 1e3, AVG(length(Trip)) / 1e3
FROM Trips;
-- 0.2731400585134866  53.76566616928331  12.200901777206806

As can be seen the longest trip is more than 56 Km long. Let's visualize one of these long trips.

SELECT vehicle, seq, source, target, round(length(Trip)::numeric / 1e3, 3),
  startTimestamp(Trip), timespan(Trip)
FROM Trips
WHERE length(Trip) > 50000 LIMIT 1;
-- 90  1  23078  11985  53.766  "2020-06-01 08:46:55.487+02"  "01:10:10.549413"

We can then visualize this trip in PostGIS. As can be seen, in Figure 2.1, “Visualization of a long trip.”, the home and the work nodes of the vehicle are located at two extremities in Brussels.

We can obtain some statistics about the average speed in Km/h of all the trips as follows.

SELECT MIN(twavg(speed(Trip))) * 3.6, MAX(twavg(speed(Trip))) * 3.6,
  AVG(twavg(speed(Trip))) * 3.6
FROM Trips;
-- 14.211962789552468  53.31779380411017  31.32438581663778

A possible visualization that we could envision is to use gradients to show how the edges of the network are used by the trips. We start by determining how many trips traversed each of the edges of the network as follows.

CREATE TABLE HeatMap AS
SELECT E.id, E.geom, count(*)
FROM Edges E, Trips T
WHERE st_intersects(E.geom, T.trajectory)
GROUP BY E.id, E.geom;

This is an expensive query since it took 42 min in my laptop. In order to display unused edges in our visualization we need to add them to the table with a count of 0.

INSERT INTO HeatMap
SELECT E.id, E.geom, 0 FROM Edges E WHERE E.id NOT IN (
  SELECT id FROM HeatMap );

We need some basic statistics about the attribute count in order to define the gradients.

SELECT min(count), max(count), round(avg(count),3), round(stddev(count),3) FROM HeatMap;
-- 0 204 4.856 12.994

Although the maximum value is 204, the average and the standard deviation are, respectively, around 5 and 13.

In order to display in QGIS the edges of the network with a gradient according to the attribute count, we use the following expression.

ramp_color('RdGy', scale_linear(count, 0, 10, 0, 1))

The scale_linear function transforms the value of the attribute count into a value in [0,1], as stated by the last two parameters. As stated by the two other parameters 0 and 10, which define the range of values to transform, we decided to assign a full red color to an edge as soon as there are at least 10 trips that traverse the edge. The ramp_color function states the gradient to be used for the display, in our case from blue to red. The usage of this expression in QGIS is shown in Figure 2.2, “Assigning in QGIS a gradient color from blue to red according to the value of the attribute count.” and the resulting visualization is shown in Figure 2.3, “Visualization of the edges of the graph according to the number of trips that traversed the edges.”.

Another possible visualization is to use gradients to show the speed used by the trips to traverse the edges of the network. As the maximum speed of edges varies from 20 to 120 Km/h, what would be interesting to compare is the speed of the trips at an edge with respect to the maximum speed of the edge. For this we issue the following query.

DROP TABLE IF EXISTS EdgeSpeed;
CREATE TABLE EdgeSpeed AS
SELECT P.edge, twavg(speed(atGeometry(T.trip, ST_Buffer(P.geom, 0.1)))) * 3.6 AS twavg
FROM Trips T, Paths P
WHERE T.source = P.start_vid AND T.target = P.end_vid AND P.edge > 0
ORDER BY P.edge;

This is an even more expensive query than the previous one since it took more than 2 hours in my laptop. Given a trip and an edge, the query restricts the trip to the geometry of the edge and computes the time-weighted average of the speed. Notice that the ST_Buffer is used to cope with the floating-point precision. After that we can compute the speed map as follows.

CREATE TABLE SpeedMap AS
WITH Temp AS (
  SELECT edge, avg(twavg) FROM EdgeSpeed GROUP BY edge )
SELECT id, maxspeed_forward AS maxspeed, geom, avg, avg / maxspeed_forward AS perc
FROM Edges E, Temp T
WHERE E.id = T.edge;

Figure 2.4, “Visualization of the edges of the graph according to the speed of trips that traversed the edges.” shows the visualization of the speed map without and with the base map.