Author: Jethro

Speed Camera Background

System Description

This system uses an original concept from Greg Tinkers, subsequently enhanced by Tim Hodges. Both authors give excellent descriptions of how computer vision can be used to track moving objects in their project write-ups.

Written in Python3, running on a Raspberry Pi 4B, the program uses Eclipse Mosquitto to handle network data transfers with MQTT. The drifting web server stores the received MQTT data in a MySQL database. Charts embedded in the site’s WordPress pages are served by a locally hosted version of Grafana. Grafana is reverse proxied behind the Apache2 web server.

The traffic survey is only indicative and is also conservative. The monitor stops measuring speed when vehicles cross over or pedestrians enter the camera field of view. Speed calculations continue as vehicles cross the camera’s field of view. The program rejects any calculations which, when compared, have a high standard deviation.

The program stores images of vehicles exceeding a set speed. The image has a speed, date and time stamp. Currently, the threshold speed is high to limit the number of images stored.

PHP To Extract Latest Data From MySQL

PHP Script uses custom CSS to generate HTML styled table to contain last nine database rows from the vehicle speed system.

  • Converts MySQL DateTime entry
    • YYYY/MM/DD H:i:s:xxx to h:i:s
  • Converts Direction Integer to Text
    • 1 = Up, 2=Down

$servername = “xxxxxxx”;
$username = “xxx”;
$password = “xxxxx”;
$dbname = “xxxxx”;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}

$sql = “SELECT * FROM traffic ORDER BY Time DESC LIMIT 9”;
$result = $conn->query($sql);

if ($result->num_rows > 0) {
echo “<table class=styled-table>”;
echo “<thead><tr><th>Time</th><th>Speed</th><th>Direction</th><th>Count</th><th>SD</th></tr></thead>”;
// output data of each row
echo “<tbody>”;
while($row = $result->fetch_assoc()) {

$secvar = “”;
{$secvar = strtotime((trim($row[“Time”])));}
{$secvar2 = date(“H:i:s”, $secvar);}
$firstvar = “”;
if (trim($row[‘Direction’]) ==1)
{$firstvar = “Up”;}
if (trim($row[‘Direction’]) ==2)
{$firstvar = “Down”;}
echo “<tr><td>”.$secvar2.”</td><td>”.$row[“Speed”].”</td><td>”.$firstvar.”</td><td>”.$row[“Count”].”</td><td>”.$row[“SD”].”</td></tr>”;
}
echo “</tbody>”;
echo “</table>”;
} else {
echo “0 results”;
}
$conn->close();

WP Theme Custom CSS

Custom CSS used in Theme for a styled HTML Table

.styled-table {
border-collapse: collapse;
margin: 25px 0;
font-size: 0.9em;
font-family: sans-serif;
min-width: 400px;
box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);
}

.styled-table thead tr {
background-color: #686868;
color: #ffffff;
text-align: left;
}
.styled-table th,
.styled-table td {
padding: 12px 15px;
}

.styled-table tbody tr {
border-bottom: 1px solid #dddddd;
}

.styled-table tbody tr:nth-of-type(even) {
background-color: #f3f3f3;
}

.styled-table tbody tr:last-of-type {
border-bottom: 2px solid #686868;
}

.styled-table tbody tr.active-row {
font-weight: bold;
color: #686868;
}