My cameras are positioned on the entry and exit gates of a parking garages. As cars pass the cameras, they capture the license plate and other attributes and load it into a database in real time. I want to perform a single query that will tell me the license plates of all cars that are still in a given property at the time of the query. This would be a real-time, on-site inventory of all that are on site now.
So, if at 10:00am I see ABC123 come in the "In" camera (direction = "In"), it should be included in the results unless I see a later event when that same plate was recorded leaving the property (direction = "Out").
I have a table called "events" where each time a license plate enters or leaves, I collect the timestamp (camtimestamp), the license plate (plate), the property (propUID) and the direction (direction = either "In" or "Out").
To make matters interesting, I have multiple properties, but I want to be able to query only one property at a time.
Partial table structure
Here is a partial structure for that table:
CREATE TABLE events ( eventUID INT NOT NULL AUTO_INCREMENT, progtimestamp DATETIME, camtimestamp DATETIME, lplate VARCHAR(255) NOT NULL, picfilename VARCHAR(255) NOT NULL, camname VARCHAR(255) NOT NULL, direction VARCHAR(10), lane VARCHAR(10), propUID INT, ownerUID INT, ...... );
How would I perform a self-join SELECT that would give me the list of license plates that are on a given property? Again, it seems that if the most recent event for a given plate is
direction="In" then it is on site. If the most recent event for a plate is
direction="Out" then it is not on site.
My considerations so far
My first thought was to run a query that gave me just the events at the property and load that whole result into an array and then run through the array event by event in reverse chronological order take the plate and query a table for that plate’s presence in the table. If the new information is that the plate is out, then put that into the table. If the new information is that the plate is in, then update the event time. To me this is clunky and resource intensive. It would be better if I could get a single query to give the result, but I am new to the concept of join queries.
I need the most recent event per plate (which I think I have above). But if the most recent event is “Out” then I need to eliminate that plate from the list of currently on site license plates. I could SELECT only the most recent events that are “In” (e.g. non-Out), but that would get me the list of all plates that have ever been on the property from the beginning. I need only those that are “In” with no successive matching “Out” to find those on site now.
Basically I believe that if I could redirect the output from the query above into another SELECT query that would eliminate the “Out”s then I would have what I need.
The logic for this is as follows:
- List all events on a given property (
where propUID = X)
- Take only the most recent entry (
max(camtimestamp)) for each plate
where Direction = "Out".
The result of the three above is the list of license plates on site right now.
This query below takes care of points one and two. I just need to get rid of all the ones where direction = "Out" from this list of results.
SELECT * FROM events WHERE (lplate,camtimestamp) IN ( SELECT lplate, MAX(camtimestamp) FROM events WHERE propUID = 1 GROUP BY lplate ) ORDER BY camtimestamp DESC
Can I nest/join the above query into another
SELECT ... where direction="In"?
Query that didn't work
This didn't work:
SELECT * from (SELECT * FROM events WHERE (lplate,camtimestamp) IN ( SELECT lplate, MAX(camtimestamp) FROM events WHERE propUID = 1 GROUP BY lplate ) ORDER BY camtimestamp DESC) WHERE direction='In'
It produced this error:
ERROR: Could not able to execute SELECT * FROM events WHERE (lplate,camtimestamp) IN ( SELECT lplate, MAX(camtimestamp) FROM events WHERE propUID = 1 GROUP BY lplate ) ORDER BY camtimestamp DESC. Every derived table must have its own alias