Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that contains number plate data with a date and time stamp, collected by a plate-reading CCTV camera at a site entrance. Vehicles stopping at the site entrance will create multiple entries of the same plate for a short period, with slightly different entry times, as in the example below:

PLATE  | Date_in   | TIME_IN
DE54RPY|2013-08-29 | 14-24-30
DE54RPY|2013-08-29 | 14-24-36
DE54RPY|2013-08-29 | 14-24-42
DE54RPY|2013-08-29 | 14-24-48

I found another solution on this site that suggested using this to filter out only the minimum timestamp for each plate:

select b.* from 
(select plate,date_in,MIN(time_in) time_in
from tblin group by plate,date_in) A
inner join tblin B using (plate,date_in,time_in)

The issue arises when a vehicle leaves the site and returns a while later, producing more entries in the table. These later entries will also be removed by the example solution above.

I need to find a way of filtering out a single instance (minimum or maximum) of a plate detection within a certain time period (e.g: a few minutes), and I'm not sure how to approach this. Could anybody here help me?

share|improve this question
add comment

1 Answer

The easy way to make this work is to add an AUTO_INCREMENT to your transaction table and then to look for instances where the license plate value changes. Consider this schema and query: (you can also see it on SQL Fiddle)

This solution works by taking advantage of the fact that records are inserted in order and that a sequential number can be used to find adjacent records. The records you're interested in are the ones where the sequence is disrupted. In your case, that happens when the license plate value changes from one record to the next.

CREATE TABLE PLATE_VIEWS
( `SYSKEY` int not null AUTO_INCREMENT 
, `PLATE` varchar(7)
, `DATE_IN` datetime
, `TIME_IN` varchar(10)
, PRIMARY KEY (SYSKEY)
)
;

INSERT INTO PLATE_VIEWS
    (`PLATE`, `Date_in`, `TIME_IN`)
VALUES
    ('DE54RPY', '2013-08-29', '14-24-30'),
    ('DE54RPY', '2013-08-29', '14-24-36'),
    ('DE54RPY', '2013-08-29', '14-24-42'),
    ('BDDX987', '2013-08-29', '15-55-05'),
    ('DE54RPY', '2013-08-29', '17-05-33'),
    ('DE54RPY', '2013-08-29', '17-05-48')
;

Now this query gets you the first capture of each plate:

SELECT 
  A.SYSKEY 
, A.PLATE
, A.DATE_IN
, A.TIME_IN
FROM PLATE_VIEWS A
LEFT OUTER JOIN PLATE_VIEWS B
ON A.SYSKEY = B.SYSKEY + 1
WHERE A.PLATE <> B.PLATE
   OR B.PLATE IS NULL

If you can't change the schema of your table to add an AUTO_INCREMENT column, then start by creating a temporary table that includes the auto increment and fill it with the range of plate scans that you are interested in. Then run the above query against the temporary table.


EDIT: In and Out

OP says there are two lanes, one for coming in and one for going out. If each lane has a camera and both capture transactions to the same table, you can catch the situation where a car leaves and comes back without any different cars coming in the meantime. If we change the column names to be more generic (DATE_SEEN instead of DATE_IN etc.) and add a LANE column which says "in" or "out", then you can change the query like so:

SELECT 
  A.SYSKEY 
, A.PLATE
, A.DATE_SEEN
, A.TIME_SEEN
, A.LANE
FROM PLATE_VIEWS A
LEFT OUTER JOIN PLATE_VIEWS B
ON A.SYSKEY = B.SYSKEY + 1
WHERE A.PLATE <> B.PLATE
   OR A.LANE <> B.LANE
   OR B.PLATE IS NULL

This will catch a car that leaves and comes back. (See SQL Fiddle)

This query also shows when cars leave. If you want to ignore when cars leave, just change the WHERE clause like so:

WHERE (A.PLATE <> B.PLATE
   OR A.LANE <> B.LANE
   OR B.PLATE IS NULL)
  AND A.LANE = 'IN'
share|improve this answer
    
There are a number of physical factors to consider. If there are multiple lanes, you need to distinguish by lane as well. During low traffic periods it might be that the same vehicle can leave and enter more than once with no other traffic in between. So you might also need to include date math in your answer by casting your date_in and time_in into a DATETIME value so that you can easily calculate intervals between scans even across midnight. Use whatever interval makes sense to you. And so on. –  RLF Aug 29 '13 at 14:51
    
@RLF - Agreed, there are a lot of other possible complications, but OP's question didn't specify any of those. Including lanes is necessary if there is more than one camera reading plates. Similarly, if you want to avoid missing "in and out" situations, you might have to capture when cars leave or make an assumption, as you suggest, about how long a gap should force the presumption of a new visit. –  Joel Brown Aug 29 '13 at 14:57
    
My mistake, I meant the post for almg. –  RLF Aug 29 '13 at 15:23
    
Thanks for the responses. there is a lot to think about here! There are 2 lanes total: one for entrance and one for exit. they both function in the same way. –  almg Aug 30 '13 at 10:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.