How to obtain data from license plate recognition database
Inna Kabanova (Unlicensed)
To obtain meta data from VIT LPR database, use the following functions: itv_plate_attr_get_ex() and itv_plate_attr_get().
Note
This article implies that the reader has the basic SQL knowledge.
itv_plate_attr_get_ex()
This function searches records by the specified time range. It does not change the exported records.
Syntax
SELECT * FROM dbo.itv_plate_attr_get_ex ( @plate NVARCHAR(16), @from_ts DATETIME, @to_ts DATETIME, @image nvarchar(8) );
Input arguments
| Parameter | Description |
|---|---|
| plate | license number ( * is for "any" number) |
| from_ts | "from" date&time; NULL can be used to not specify the "from" date&time |
| to_ts | "to" date&time; NULL can be used to not specify the "to" date&time |
| image | image type:
|
Returned data
| Parameter | Description |
|---|---|
| [GUID] UNIQUEIDENTIFIER | GUID of the LPR event |
| [TIME] DATETIME | event timestamp |
| [LPR_ID] NVARCHAR(50) | ID of the LPR software instance |
| [PLATE] NVARCHAR(50) | license number |
| [frame] NVARCHAR(450) | full frame ID |
| [auto_img] NVARCHAR(450) | cropped vehicle image ID |
| [plate_img] NVARCHAR(450) | cropped license plate image ID |
| [framedata] VARBINARY(MAX) | binary data of the full image |
| [hash] XML | event hash; format: <key>value</key> |
Example
To get images (binary data), use their IDs in the following query:
SELECT [dbo].[bo_get_node_data] (@uid NVARCHAR(450));
itv_plate_attr_get()
In the database, this function marks selected LPR records as already exported and does not return them again. The function can not be used when having multiple export clients.
Syntax
SELECT * FROM dbo.itv_plate_attr_get( @plate NVARCHAR(16), @image nvarchar(8) );
Input arguments
| Parameter | Description |
|---|---|
| plate | string template for a license number; * ... * is used for LIKE query, NULL is used to specify all the numbers |
| image | image type:
|
Returned data
| Parameter | Description |
|---|---|
| [GUID] UNIQUEIDENTIFIER | GUID of the LPR event |
| [TIME] DATETIME | event timestamp |
| [LPR_ID] NVARCHAR(50) | ID of the LPR software instance |
| [cam_name] NVARCHAR(100) | camera name |
| [PLATE] NVARCHAR(50) | license number |
| [frame] NVARCHAR(450) | full frame ID |
| [VALIDITY] INT | recognition accuracy |
| [auto_img] NVARCHAR(450) | cropped vehicle image ID |
| [plate_img] NVARCHAR(450) | cropped license plate image ID |
| [framedata] VARBINARY(MAX) | binary data of the full image |
| [plate_imgdata] VARBINARY(MAX) | binary data of the cropped license plate image |
Note
After meta data are obtained, each of the LPR events should be marked as "already exported" with the following query:
UPDATE dbo.PLATE_NUMBERS SET sent_st = 1 WHERE GUID = '{LPR_event_GUID}'