SQL Challenge – Calculating the legal maximum speed measured by the laser speed gun

The speed of cars can be detected at large distance and with great accuracy using laser speed guns. How the measured speeds translate into speeding tickets depends on the legislation around speeding and of course the maximum allowed speed for cars. Most laws state that the maximum speed for which the driver can be ticketed has to be measured for a somewhat prolonged period of time. A single sub-second finding is not enough. When a certain speed has been measured for say 2 seconds straight.

That means that in order to establish the effective maximum speed of a certain car, many measurements have to be take into account and the highest speed that has been found over that 2 second time span is the result. This graph shows what we are after: time on the x-axis, measured speed (in km/h) on the y-axis and the red bar indicates a two second period. We try to find the highest that this red bar will go with speed markers on or above it.

A small example with some data:

(* the driver spotted the police office with the laser gun

In this example, the effective highest speed that is derived is 114 km/h – measured between 1.1 seconds and 3.1 seconds. At no time during this period did the speed go below 114 km/h. The higher speeds in that period or indeed in any of the other periods has not been maintained for two seconds, so does not legally count.

A laser speed gun measures the round-trip time for light to reach a car and reflect back. Light from a laser speed gun moves a lot faster than sound — about 300,000,000 meters per second, or roughly 30 cm per nanosecond. A laser speed gun shoots a very short burst of infrared laser light and then waits for it to reflect off the vehicle. The gun counts the number of nanoseconds it takes for the round trip, and by dividing by 2 it can calculate the distance to the car. If the gun takes 1,000 samples per second, it can compare the change in distance between samples and calculate the speed of the car.

This article describes a SQL solution to the challenge of finding the highest speed (maintained for at least 2 seconds) of a car, based on a series of laser speed fun derived distance measurements.

At first, we will create a graph of distance and then of speed (using the article https://technology.amis.nl/2014/05/28/sql-based-charts-scatterplot-for-xy-coordinate-data-sets-growth-curve-eurodollar-ratio/) to gain some insight in the challenge at hand. Next we will craft the SQL query that returns the speed that the fine can be based on.

Taking a data set from a laser speed gun session:

create type gun_measurement_t as object (time number(5,2), distance number(12,5));

create type gun_measurement_tbl_t as table of gun_measurement_t;

with laser_gun_measurements as
( select time, distance
from table( gun_measurement_tbl_t
( gun_measurement_t(0, 850.0)
, gun_measurement_t(0.1, 846.8)
, gun_measurement_t(0.2, 843.5)
, gun_measurement_t(0.3, 840.0)
, gun_measurement_t(0.5, 833.0)
, gun_measurement_t(0.6, 829.5)
...
, gun_measurement_t(4.2, 701.2)
, gun_measurement_t(4.3, 697.8)
, gun_measurement_t(4.4, 694.4)
, gun_measurement_t(4.5, 691.3)
)
)
)
, chart_query as -- the query that drives the chart;
( select time x
, distance y
from laser_gun_measurements
)

we can create a scatterplot using the approach discussed in the referenced article. It looks like this:

The car was first spotted at a distance of some 840 meters. For about 4.5 seconds, measurements were taken until it was a little less than 700 meters away.

The query that produces the distance measurements can easily be extended to produce the speed measurements – using a the LAG function that compares a record (of distance and time) with the previous record (of distance and time). Delta distance divided by delta in time yields the speed over the time interval or (to simplify things) at the second point in time:

...
, gun_measurement_t(4.4, 694.4)
, gun_measurement_t(4.5, 691.3)
)
)
)
, distances_covered as
( select time
, time - lag(time) over (order by time) period
, distance - lag(distance) over (order by time) delta_distance
from laser_gun_measurements
)
, chart_query as -- the query that drives the chart
( select time x
, abs(delta_distance/period * 3.6) y
from distances_covered
)

Taking this data to create the scatterplot results in:

The car was driving at variable speeds, most of which was between 120 and 130 km/h. The question to address is: how can we derive the highest speed of the car that for 2 seconds it does not come under. If for example we draw a horizontal line at 125 km/h: do we have a stretch of two seconds in that line where all measurements are on or above that line? If so, how much higher can we draw this horizontal line and still have this 2 second stretch?

Instead of trying to derive this highest speed visually from the chart it would be much better to have a SQL query produce the result. I set out with that challenge in mind – and frankly I expected this to be quite a challenge. I thought perhaps the Oracle Database 12c pattern matching functionality would be required.

After giving it some thought, it turned out to be almost disappointingly simple. Using an analytic function – MIN – with a Windowing Clause (a two second window), it is easy to find for each measurement the minimum speed in the two second interval following that measurement. That will produce the minimum speed for all two second intervals in the 4.5 second measuring period.

with laser_gun_measurements as
( select time, distance -- 3 = 110, 2 = 75, 2.5 = 93, 3,5=124,4 = 145 4.5 = 168
from table( gun_measurement_tbl_t
( gun_measurement_t(0, 850.0)
, gun_measurement_t(0.1, 846.8)
, gun_measurement_t(0.2, 843.5)
, gun_measurement_t(0.3, 840.0)
....
, gun_measurement_t(4.4, 694.4)
, gun_measurement_t(4.5, 691.3)
)
)
)
, distances_covered as
( select time
, time - lag(time) over (order by time) period
, distance - lag(distance) over (order by time) delta_distance
from laser_gun_measurements
)
, speed_measurements as
( select time
, abs(delta_distance/period * 3.6) speed
, min(abs(delta_distance/period * 3.6)) over (order by time range between 0 preceding and 2 following) min_speed_in_window
from distances_covered
)
select time
, speed
, min_speed_in_window
from speed_measurements

If now we take the highest of those minimum speeds we find the interval we are after, and the speed that can be used to determine whether or not a ticket should be issued. The not overly well known function KEEP (in combination with FIRST and ORDER BY) (very well described by Rob van Wijk in this article: http://rwijk.blogspot.nl/2012/09/keep-clause.html) can be used to get from the results obtained in the previous query the one result for the highest speed and the time at which it was first clocked:

...
( select time
, abs(delta_distance/period * 3.6) speed
, min(abs(delta_distance/period * 3.6)) over (order by time range between 0 preceding and 2 following) min_speed_in_window
from distances_covered
)
select min(time) keep (dense_rank first order by min_speed_in_window desc)
, min(min_speed_in_window) keep (dense_rank first order by min_speed_in_window desc) maximum_two_sec_speed
from speed_measurements

The result of this final query is shown below: we nailed this speeding sucker at 122.4 km/h (where 100 km/h is allowed). At 1.3 seconds started the first 2 second interval in which this speed was the minimum.

Resources

Source code for this article: Lasergun.sql

Blog article on the KEEP clause by Rob van Wijk: http://rwijk.blogspot.nl/2012/09/keep-clause.html

Blog article on creating scatterplot charts in SQL: https://technology.amis.nl/2014/05/28/sql-based-charts-scatterplot-for-xy-coordinate-data-sets-growth-curve-eurodollar-ratio/

Oracle Magazine article by Melanie Caffrey on Windowing Clause in Analytic SQL functions: http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23sql-1906475.html