Database Description#
Database Structure#
The general scheme of table relations in the CARS Analytics database (Figure 3).
Database Tables#
This section provides information about database tables and their relationships with other tables.
1․ Table content_app_camera
Table 3. Description of table content_ app_camera
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Camera ID | DB – content_app_cameraregion, Key – camera_id |
| DB – content_app_event, Key – camera_id | |||
| DB – content_app_incident, Key – camera_id | |||
| DB – scenario_manager_handler, Key – camera_id | |||
| created | timestamp | Date and time of camera creation | - |
| updated | timestamp | Date and time of camera update | - |
| car_stream_url | varchar | Camera URL | - |
| name | varchar | Camera name | - |
| input_location | varchar | RTSP video stream address or video file location. | - |
| input_type | varchar | Source type - video file or RTSP. | - |
| input_protocol | varchar | Video streaming protocol | - |
| roi_x | int4 | The value of the horizontal coordinate of the the detection area | - |
| roi_y | int4 | The value of the vertical coordinate of the detection area | - |
| roi_width | int4 | Detection area length | - |
| roi_height | int4 | Detection area width | - |
| rotation | int4 | Video stream rotation angle | - |
| stream_uuid | varchar | Video stream ID | - |
| watch_url | varchar | Link to CARS Stream visual mode | - |
| preview_image | varchar | Image displayed on camera preview | - |
| is_active | bool | Camera activity status | - |
| is_enabled | bool | Camera operation status | - |
| is_made_automatically | bool | Camera creation method - manual or automatic | - |
| status | int4 | Video stream status | - |
| address | varchar | Physical address of the camera location | - |
| lat | float8 | Camera geolocation latitude | - |
| lon | float8 | Camera location longitude | - |
| extra_data | jsonb | Field for additional information | - |
| Is_restart_auto | bool | Camera auto restart status | - |
| parameters | jsonb | Camera parameters (e.g. detector size, thresholds, FGS usage, etc.) | - |
2․ Table camera_app_cameraregion
Table 4. Description of table camera_app_cameraregion
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Recognition area ID | - |
| tag_name | varchar | Recognition area name | - |
| anchor_points | float8 | Coordinates of the corners of the recognition area | - |
| display_color | varchar | Display Color | - |
| event_registration | bool | Event recognition status | - |
| threshold_intersection | float8 | The value of the threshold for crossing the vehicle and the recognition area | - |
| camera_id | uuid | Camera ID | DB – content_app_camera, Key – id |
3․ Table content_app_incident
Table 5. Description of table content_app_incident
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Incident ID | - |
| timestamp | timestamp | Date and time of incident creation | - |
| track_id | varchar | Track ID | - |
| frame_id | varchar | Event frame ID | - |
| object_type | varchar | Type of incident | - |
| detections | jsonb | Information about vehicle detection | - |
| extra_data | jsonb | Field for additional information | - |
| comment | text | Incident comment | - |
| bestshot | varchar | Best shot ID | - |
| car_model_score | numeric | The value of the quality of the assessment of the vehicle model | - |
| car_brand_score | numeric | The value of the quality of the vehicle brand assessment | - |
| vehicle_type_score | numeric | The value of quality assessment of the vehicle type | - |
| vehicle_type_emergency_score | numeric | The value of quality assessment of the emergency | - |
| color_score | numeric | The value of the quality of the vehicle color estimation | - |
| country_score | numeric | The value of quality assessment of the country registration | - |
| license_plate | varchar | LP symbols | - |
| license_plate_score | numeric | The value of the quality of the assessment of recognition of LP | - |
| vehicle_plate | varchar | LP image file name | - |
| vehicle_descriptor | text | Vehicle descriptor | - |
| is_unread | bool | Incident status: | - |
| - new; | |||
| - viewed. | |||
| camera_id | uuid | Camera ID | DB – content_app_camera, Key – id |
| car_model_id | uuid | Vehicle Model ID | DB – content_app_carmodel, Key – id |
| color_id | uuid | Vehicle color ID | DB – content_app_vehiclecolor, Key – id |
| country_id | uuid | Country ID | DB – content_app_licenseplatecountry, Key – id |
| scenario_id | uuid | Scenario ID | DB – scenario_manager_scenario, Key – id |
| vehicle_emergency_type_id | uuid | Emergency Type ID | DB – content_app_vehicleemergencytype, Key – id |
| vehicle_type_id | uuid | Vehicle Type ID | DB – content_app_vehicletype, Key – id |
| vehicle_type_id | uuid | Vehicle type identifier | DB – content_app_vehicletype, key– id |
| service_data | jsonb | Incident Information | - |
| stream_id | uuid | Video stream ID | - |
| stream_track_id | integer | Track ID | - |
| public_transport_type_id | uuid | Public transport type ID | DB - content_app_publictransporttype, key - id |
| public_transport_type_score | numeric | The value of quality assessment of public transport type | - |
| special_transport_type_id | uuid | Special transport type ID | DB - content_app_specialtransporttype, key – id |
| special_transport_type_score | numeric | The value of quality assessment of special transport type | - |
| vehicle_axles_amount | integer | Axles amount of vehicle | - |
| vehicle_axles_amount_score | numeric | The value of quality assessment of axles amount | - |
| license_plate_symbols_score | numeric | The value of quality assessment of LP symbols | - |
| patched_with_track_end | boolean | Flag indicating that a track end event has been received from CARS Stream for this incident | - |
4․ Table content_app_carmodel
Table 6. Description of table content_app_carmodel
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Vehicle Model ID | DB – content_app_event, Key – car_model_id |
| DB – content_app_incident, Key – car_model_id | |||
| DB – content_app_platelistitem_car_models, Key – car_model_id | |||
| title | varchar | Vehicle model name | - |
| brand_id | uuid | Vehicle brand ID | - |
5․ Table content_app_carbrand
Table 7. Description of table content_app_carbrand
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Vehicle brand ID | DB – content_app_carmodel, Key – brand_id |
| DB – content_app_platelistitem_car_brand, Key – carbrand_id | |||
| title | varchar | Vehicle brand name | - |
6․ Table content_app_platelistitem_car_brands
Table 8. Description of table content_app_platelistitem_car_brands
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | ID of the vehicle brand associated with the event | - |
| platelistitem_id | uuid | LP ID | DB – content_app_platelistitem, Key – id |
| carbrand_id | uuid | Vehicle brand ID | DB – content_app_carbrand, Key – id |
7․ Table content_app_incident_avg_speed
Table 9. Description of table content_app_incident_avg_speed
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Incident ID for speed calculation | - |
| created | timestamp | Date and time of incident appearance | - |
| group | uuid | ID of group where the incident was detected | - |
| speed | int | Vehicle speed value | - |
| distance_to_previous | numeric | Distance value | - |
| handler_id | uuid | Used handler ID | DB – scenario_manager_handler, key – id |
| record_id | uuid | Record ID | DB – content_app_incident, key – id |
8․ Table content_app_platelistitem_car_models
Table 10. Description of table content_app_platelistitem_car_models
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | ID of the vehicle model associated with the event | - |
| platelistitem_id | uuid | LP ID | DB – content_app_platelistitemб Key – id |
| carmodel_id | uuid | Vehicle Model ID | DB – content_app_carmodelб Key – id |
9․ Table content_app_platelistitem
Table 11. Description of table content_app_platelistitem
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | LP ID | DB – content_app_platelistitem_car_brandsб Key – platelistitem_id |
| DB – content_app_platelistitem_car_models, Key – platelistitem_id | |||
| DB – content_app_platelistitem_colors, Key – platelistitem_id | |||
| DB – content_app_platelistitem_countries, Key – platelistitem_id | |||
| DB – content_app_platelistitem_vehicle_emergency_type, Key – platelistitem_id | |||
| DB – content_app_platelistitem_ vehicle_types, Key – platelistitem_id | |||
| created | timestamp | Vehicle detection event creation time | - |
| car_grz | varchar | LP symbols | - |
| image_item | varchar | LP images | - |
| image_accuracy | float8 | LP recognition accuracy | - |
| image_descriptor | text | LP descriptor | - |
| extra_data | jsonb | Field for additional information | - |
| list_id | uuid | List ID | DB – content_app_list, Key – id |
10․ Table content_app_platelistitem_countries
Table 12. Description of table content_app_platelistitem_countries
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | ID of country registration of the LP associated with the event | - |
| platelistitem_id | uuid | Event ID | DB – content_app_platelistitem, Key – id |
| licenseplatecountry_id | uuid | ID of the country registration of the LP | DB – content_app_licenseplatecountry, Key – id |
11․ Table content_app_licenseplatecountry
Table 13. Description of table content_app_licenseplatecountry
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Identifier of the country registration of the LP | DB – content_app_event, Key – country_id |
| DB – content_app_incident, Key – country_id | |||
| DB – content_app_platelistitem_countries, Key – country_id | |||
| varchar | Country name | - | |
| code | varchar | Country number in the system | - |
12․ Table scenario_manager_handler
Table 14. Description of table scenario_manager_handler
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Handler ID | - |
| dt_created | timestamp | Date and time of handler creation | - |
| handler_type | varchar | Handler type | - |
| comment | varchar | Handler comment | - |
| is_enabled | bool | Handler activity status | - |
| parameters | jsonb | Handler options | - |
| camera_id | uuid | Camera ID | DB – content_app_camera, Key – id |
| scenario_id | uuid | Scenario ID | DB – scenario_manager_scenario, Key – id |
| command | text | Command name | - |
13․ Table content_app_event
Table 15. Description of table content_app_event
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Event ID | - |
| timestamp | timestamp | Date and time the event was created | - |
| track_id | varchar | Track ID | - |
| frame_id | varchar | Event frame ID | - |
| object_type | varchar | Event type | - |
| detections | jsonb | Information about vehicle detection | - |
| extra_data | jsonb | Field for additional information | - |
| comment | text | Event Comment | - |
| bestshot | varchar | Best Frame ID | - |
| car_model_score | numeric | The value of quality assessment of the vehicle model recognition | - |
| car_brand_score | numeric | The value of quality assessment of the vehicle brand recognition | - |
| vehicle_type_score | numeric | The value of quality assessment of the vehicle type recognition | - |
| vehicle_type_emergency_score | numeric | The value of quality assessment of the emergency type recognition | |
| color_score | numeric | The value of quality assessment of the vehicle color | - |
| country_score | numeric | The value of quality assessment of the LP country recognition | - |
| license_plate | varchar | LP symbols | - |
| license_plate_score | numeric | The value of quality assessment of the LP country recognition | - |
| vehicle_plate | varchar | LP image file name | - |
| vehicle_descriptor | text | Vehicle descriptor | - |
| camera_id | uuid | Camera ID | DB – content_app_camera, Key – id |
| car_model_id | uuid | Vehicle Model ID | DB – content_app_carmodel, Key – id |
| color_id | uuid | Vehicle color ID | DB – content_app_vehiclecolor, Key – id |
| country_id | uuid | LP country ID | DB – content_app_licenseplatecountry, Key – id |
| scenario_id | uuid | Scenario ID | DB – scenario_manager_scenario, Key – id |
| vehicle_emergency_type_id | uuid | Emergency Type ID | DB – content_app_vehicleemergencytype, Key – id |
| vehicle_type_id | uuid | Vehicle Type ID | DB – content_app_vehicletype, Key – id |
| service_data | jsonb | Incident Information | - |
| stream_id | uuid | Video stream ID | - |
| stream_track_id | integer | Track ID | - |
| public_transport_type_id | uuid | Public transport type ID | DB - content_app_publictransporttype, key - id |
| public_transport_type_score | numeric | The value of quality assessment of public transport type | - |
| special_transport_type_id | uuid | Special transport type ID | DB - content_app_specialtransporttype, key – id |
| special_transport_type_score | numeric | The value of quality assessment of special transport type | - |
| vehicle_axles_amount | integer | Axles amount of vehicle | - |
| vehicle_axles_amount_score | numeric | The value of quality assessment of axles amount | - |
| license_plate_symbols_score | numeric | The value of quality assessment of LP symbols | - |
| patched_with_track_end | boolean | Flag indicating that a track end event has been received from CARS Stream for this event | - |
14․ Table content_app_event_avg_speed
Table 16. Description of table content_app_event_avg_speed
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Event ID for speed calculation | - |
| created | timestamp | Date and time of event appearance | |
| group | uuid | ID of group where the event was detected | |
| speed | int | Vehicle speed value | |
| distance_to_previous | numeric | Distance value | |
| handler_id | uuid | Used handler ID | DB – scenario_manager_handler, key – id |
| record_id | uuid | Record ID | DB – content_app_event, key – id |
15․ Table content_app_vehicletype
Table 17. Description of table content_app_vehicletype
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Vehicle Type ID | DB – content_app_event, Key – vehicle_type_id |
| DB – content_app_incident, Key – vehicle_emergency_type_id | |||
| DB – content_app_platelistitem_vehicle_emergency_type, Key – vehicleemergencytype_id | |||
| title | varchar | Vehicle type name | - |
16․ Table content_app_platelistitem_vehicle_types
Table 18. Description of table content_app_platelistitem_vehicle_emergency_types
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | ID of the Vehicle Type associated with the event | - |
| platelistitem_id | uuid | Event ID | DB – content_app_platelistitem, Key – id |
| vehicletype_id | uuid | Vehicle Type ID | DB – content_app_vehicletype, Key – id |
17․ Table content_app_platelistitem_vehicle_emergency_types
Table 19. Description of table content_app_platelistitem_vehicle_emergency_types
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | The emergency service ID associated with the event | - |
| platelistitem_id | uuid | Event ID | DB – content_app_platelistitem, Key – id |
| vehicleemergencytype_id | uuid | Emergency ID | DB – content_app_vehicleemergencytype, Key – id |
18․ Table content_app_vehicleemergencytype
Table 20. Description of table content_app_vehicleemergencytype
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Emergency type ID | DB – content_app_event, Key – vehicle_emergency_type_id |
| DB – content_app_incident, Key – vehicle_emergency_type_id | |||
| DB – content_app_platelistitem_vehicle_emergency_type, Key – vehicleemergencytype_id | |||
| title | varchar | Name of the emergency service | - |
| code | varchar | Emergency number in system | - |
19․ Table content_app_vehiclecolor
Table 21. Description of table content_app_vehiclecolor
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Vehicle color ID | DB – content_app_event, Key – color_id |
| DB – content_app_incident, Key – color_id | |||
| DB – content_app_platelistitem_color, Key – color_id | |||
| title | varchar | Name colors | - |
| code | varchar | Color number | - |
20․ Table content_app_specialtransporttype
Table 22. Description of table content_app_specialtransporttype
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Special transport type ID | DB - content_app_event, key - special_transport_type_id |
| DB – content_app_incident, key - special_transport_type_id | |||
| title | varchar | Name of special transport | - |
| code | varchar | Code of special transport | - |
21․ Table content_app_platelistitem_color
Table 23. Description of table content_app_platelistitem_color
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Color ID of the vehicle associated with the event | - |
| platelistitem_id | uuid | LP ID | DB – content_app_platelistitem, Key – id |
| vehiclecolor_id | uuid | Vehicle brand ID | DB – content_app_vehiclecolor, Key – id |
22․ Table content_app_incident_license_plate_features
Table 24. Description of table content_app_incident_license_plate_features
| Field | Type | Description | Relations |
|---|---|---|---|
| id | integer | LP feature related to the incident ID | - |
| incident_id | uuid | Related incident ID | DB - content_app_incident, key - id |
| licenseplatefeature_id | uuid | LP feature ID | DB - content_app_licenseplatefeature, key - id |
23․ Table content_app_publictransporttype
Table 25. Description of table content_app_publictransporttype
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Public transport type ID | DB - content_app_event, key - public_transport_type_id |
| DB - content_app_incident, key - public_transport_type_id | |||
| title | varchar | Public transport type title | - |
| code | varchar | Public transport type code | - |
24․ Table content_app_event_license_plate_features
Table 26. Description of table content_app_event_license_plate_features
| Field | Type | Description | Relations |
|---|---|---|---|
| id | integer | LP feature related to event ID | - |
| event_id | uuid | Related event ID | DB - content_app_event, key - id |
| licenseplatefeature_id | uuid | LP feature ID | DB - content_app_licenseplatefeature, key - id |
25․ Table content_app_licenseplatefeature
Table 27. Description of table content_app_licenseplatefeature
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | LP feature ID | DB – content_app_incident_license_plate_features, key – licenseplatefeature_id |
| DB – content_app_event_license_plate_features, key - licenseplatefeature_id | |||
| score | numeric | The value of quality assessment of LP feature | - |
| value_id | uuid | LP feature value ID | DB - content_app_license_plate_feature_value, key - id |
26․ Table content_app_license_plate_feature_value
Table 28. Description of table content_app_license_plate_feature_value
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | LP feature value ID | DB – content_app_licenseplatefeature, key – value_id |
| type | varchar | LP feature type | - |
| title | varchar | LP feature title | - |
27․ Table content_app_list
Table 29. Description of table content_app_list
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Scenario ID | DB – scenario_manager_scenariolistextra, Key – list_id |
| DB – content_app_platelistitem, Key – list_id | |||
| Name | varchar | Scenario name | - |
| created | timestamp | Scenario creation date and time | - |
| extra_data | json | Field for additional information | - |
28․ Table scenario_manager_scenariolistextra
Table 30. Description of table scenario_manager_scenariolistextra
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Scenario ID | - |
| dt_added | timestamp | Date and time of list creation | - |
| is_except | varchar | List belonging to a scenario | - |
| list_id | uuid | List ID | DB – content_app_list, Key – id |
| scenario_id | uuid | Scenario ID | DB – scenario_manager_scenario, Key – id |
29․ Table scenario_manager_scenario
Table 31. Description of table scenario_manager_scenario
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Scenario ID | DB – content_app_event, Key – scenario_id |
| DB – content_app_incident, Key – scenario_id | |||
| DB – scenario_manager_handler, Key – scenario_id | |||
| DB – scenario_manager_scenariolistextra, Key – scenario_id | |||
| dt_created | timestamp | Date and time of script creation | - |
| title | varchar | Scenario name | - |
| scenario_type | varchar | Scenario type | - |
| is_enable | bool | Scenario activity status | - |
| indicator_type_id | uuid | Scenario Type ID | DB – scenario_manager_indicatortype, key – id |
30․ Table scenario_manager_indicatortype
Table 32. Description of table scenario_manager_indicatortype
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Scenario ID | DB – scenario_manager_scenario, Key – indicator_type_id |
| title | varchar | Scenario names | - |
| code | varchar | Scenario number | - |
| for_type | varchar | Scenario type | - |
31․ Table content_app_incident_groups
Table 33. Description of table content_app_incident_groups
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Scenario ID | - |
| Incident_id | uuid | Incident ID | DB – content_app_incident, key – id |
| fragmentsgroup_id | uuid | Group Fragment ID | DB – content_app_fragmentsgroup, key – id |
32․ Table content_app_fragmentsitem
Table 34. Description of table content_app_fragmentsitem
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Scenario ID | - |
| timestamp | timestamp | Date and time the incident was created | - |
| object_type | varchar | Incident type | - |
| frame_id | uuid | Frame ID | - |
| file_name | varchar | Saved frame name | - |
| detections | jsonb | Information about detections | - |
| group_id | uuid | Group ID | DB – content_app_fragmentsgroup, key – id |
33․ Table content_app_event_fragmentsgroup
Table 35. Description of table content_app_event_fragmentsgroup
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Scenario ID | DB – content_app_event_groups, key – fragmentsgroup_id |
| DB – content_app_incident_groups, key – fragmentsgroup_id | |||
| DB – content_app_fragmentsitem, key – group_id | |||
| created | timestamp | Date and time the event was created | - |
| object_type | varchar | Object type | - |
34․ Table content_app_event_groups
Table 36. Description of table content_app_event_group
| Field | Type | Description | Relations |
|---|---|---|---|
| id | uuid | Scenario ID | - |
| event_id | uuid | Event ID | DB – content_app_event, key – id |
| fragmentsgroup_id | uuid | Fragment group ID | DB – content_app_fragmentsgroup, key – id |