Full ALPR Database System for Blue Iris!

smallik · Apr 19, 2025

MikeLud1 said:
You can set the ALPR module to remove spaces

View attachment 219378

Ask and ye shall receive. Thank you much!

Janhaus · Tuesday at 12:14 PM

Just set this up - had a few alerts in BlueIris but not seeing anything in the ALPR Database just yet, followed the instructions shown here: GitHub - algertc/ALPR-Database: Fully-Featured Automated License Plate Recognition Database Platform for Blue Iris + CodeProject AI Server for the docker container setup.
In the web interface, the MQTT/HTTP section is not modified, do I need to do anything here? i.e. do I need to fill out MQTT Broker URL/IP?
I edited the Blue Iris Configuration to point to the Blue Iris IP address and port number, that should probably be added to the instructions.

In the logs, I see this:
4/29/2025, 12:08:41 PM [INFO] Checking IP: 172.19.0.1 <- this is not an IP in my local network, did I miss a setting somewhere?

Janhaus · Tuesday at 12:42 PM

Ok I saw a plate come through in the logs but it's not hitting the dashboard etc. just yet. Here's what the logs show:

Vettester · Tuesday at 4:06 PM

Janhaus said:
In the web interface, the MQTT/HTTP section is not modified, do I need to do anything here? i.e. do I need to fill out MQTT Broker URL/IP?
I edited the Blue Iris Configuration to point to the Blue Iris IP address and port number, that should probably be added to the instructions.

In the logs, I see this:
4/29/2025, 12:08:41 PM [INFO] Checking IP: 172.19.0.1 <- this is not an IP in my local network, did I miss a setting somewhere?

There are two ways of sending data to the database. One is via MQTT and the other is to use the API key. The API key is the preferred method so if you have it configured correctly you don't need anything in the MQTT section. The 172.19.0.1 you are seeing in your log file is the internal IP of your docker application so this is normal.

The errors in your log file indicates the app is not receiving plate data. Are you seeing plate captures in BI?

A normal log file should look like this:

Screen Shot 2025-04-29 at 2.01.16 PM.png

Janhaus · Tuesday at 5:10 PM

Thanks for the clarification! It seems that the default docker image has some permission issues with the database, see thread here:

Error processing request: error: there is no unique or exclusion constraint matching the ON CONFLICT specification · algertc ALPR-Database · Discussion #55

Seems like everything is working except the data being added to the database. The App receives and saves the image. Not sure what I'm doing wrong I followed the install instructions exactly. I can ...

github.com

At first I was getting this error:
[ERROR] Error processing request: error: there is no unique or exclusion constraint matching the ON CONFLICT specification
After running psql -d postgres -U postgres -f /docker-entrypoint-initdb.d/schema.sql
I now get this error in the logs:
Error processing request: error: null value in column "plate_number" of relation "plate_reads" violates not-null constraint

Both In CodeProject and BI, I'm getting the plate recognized, i.e. the plate number is showing up in my LPR alert, so it's just an issue of saving to the ALPR database. Unfortunately I'm not familiar enough with postgres to figure out how to fix this.

Vettester · Tuesday at 5:41 PM

Janhaus said:
Thanks for the clarification! It seems that the default docker image has some permission issues with the database, see thread here:

Error processing request: error: there is no unique or exclusion constraint matching the ON CONFLICT specification · algertc ALPR-Database · Discussion #55

Seems like everything is working except the data being added to the database. The App receives and saves the image. Not sure what I'm doing wrong I followed the install instructions exactly. I can ...

github.com

At first I was getting this error:
[ERROR] Error processing request: error: there is no unique or exclusion constraint matching the ON CONFLICT specification
After running psql -d postgres -U postgres -f /docker-entrypoint-initdb.d/schema.sql
I now get this error in the logs:
Error processing request: error: null value in column "plate_number" of relation "plate_reads" violates not-null constraint

Both In CodeProject and BI, I'm getting the plate recognized, i.e. the plate number is showing up in my LPR alert, so it's just an issue of saving to the ALPR database. Unfortunately I'm not familiar enough with postgres to figure out how to fix this.

Try downloading the update script to the same directory your app is in and run it using PowerShell with admin rights.

Janhaus · Tuesday at 11:18 PM

Vettester said:
Try downloading the update script to the same directory your app is in and run it using PowerShell with admin rights.

Thanks for the tip! I ran it earlier and updated successfully but didn't solve the error, trying it now to upgrade to nightly release and see if that works.

PeteJ · 2025-04-30T13:02:05-0400

Janhaus said:
Thanks for the tip! I ran it earlier and updated successfully but didn't solve the error, trying it now to upgrade to nightly release and see if that works.

Post in thread 'Full ALPR Database System for Blue Iris!'

Mar 25, 2025

svalvasori said:

I've done this:

Bash:

root@alpr:/opt/ALPR_Database# docker compose down
[+] Running 3/3
 ✔ Container alpr_database-app-1  Removed         [URL='https://github.com/algertc/ALPR-Database']GitHub Repository[/URL]                                                                             1.2s
 ✔ Container alpr_database-db-1   Removed                                                                                     11.2s
 ✔ Network alpr_database_default  Removed                                                                                      0.3s
root@alpr:/opt/ALPR_Database# docker compose pull
[+] Pulling 2/2
 ✔ db Pulled...

Did you see this post, and the ones surrounding it?

VideoDad · 2025-04-30T13:20:55-0400

And this:

Post in thread 'Full ALPR Database System for Blue Iris!'

Mar 25, 2025

svalvasori said:
I downloaded directly from GitHub and compared md5 between the new and existing files, no difference.

Code:

root@alpr:/opt/ALPR_Database# md5sum new/* 68299a0cc953f95cf27525b3c3023d41 new/migrations.sql 9caab0fb987b904a13f3e325f5fa5fbe new/schema.sql root@alpr:/opt/ALPR_Database# md5sum *.sql 68299a0cc953f95cf27525b3c3023d41 migrations.sql 9caab0fb987b904a13f3e325f5fa5fbe schema.sql

The error does seem DB related:

3/25/2025, 11:32:31 AM [INFO] POST /api/plate-reads
3/25/2025, 11:32:31 AM [INFO] Received plate read data: [object Object]
3/25/2025, 11:32:31 AM [INFO] Database connection...

Janhaus · 2025-04-30T15:01:55-0400

Thanks so much @PeteJ @VideoDad - I did not see these earlier, but I did find the discussions on the Git project with several similar comments and same issues on my end. I'll go through a sequence of testing to see if I can get this to work, I'm running the current version of Docker Desktop, on Windows 10.
For beginners, I started with a completely new directory and installed the docker image again. Verified that my .sql files are identical to the ones in the latest branch
migrations.sql: 68299A0CC953F95CF27525B3C3023D41
schema.sql: 9560C7EA414F7787F82E704FF379A69D

Saw a plate come through just now, the good news is that I'm not seeing any database errors this time around with my fresh install, but the bad news is that the images are not being stored in the folders. My previous install had database errors, but did get the images stored...

Log from CodeProjectAI:
14:58:22:Response rec'd from License Plate Reader command 'alpr' (...bed481) ['Found Plate: 68770K'] took 653ms

Log from ALPR Database:

VideoDad · 2025-04-30T15:12:45-0400

Janhaus said:
Thanks so much @PeteJ @VideoDad - I did not see these earlier, but I did find the discussions on the Git project with several similar comments and same issues on my end. I'll go through a sequence of testing to see if I can get this to work, I'm running the current version of Docker Desktop, on Windows 10.
For beginners, I started with a completely new directory and installed the docker image again. Verified that my .sql files are identical to the ones in the latest branch
migrations.sql: 68299A0CC953F95CF27525B3C3023D41
schema.sql: 9560C7EA414F7787F82E704FF379A69D

Saw a plate come through just now, the good news is that I'm not seeing any database errors this time around with my fresh install, but the bad news is that the images are not being stored in the folders. My previous install had database errors, but did get the images stored...

Log from CodeProjectAI:
14:58:22:Response rec'd from License Plate Reader command 'alpr' (...bed481) ['Found Plate: 68770K'] took 653ms

Log from ALPR Database:
View attachment 220163

So you've confirmed the storage directory exists, I assume. If not manually create it.

TheWaterbug · 2025-04-30T15:25:28-0400

^^
I had to tear down my installation a few weeks ago, and I found that it wasn't enough just docker-compose down, which deletes the Container(s). I also had to go into Docker Desktop and delete all the Images and Volumes, manually. Then docker-compose pull and docker-compose up -d worked.

n=1, YMMV.

th182 · 2025-04-30T17:07:10-0400

Janhaus said:
Ok I saw a plate come through in the logs but it's not hitting the dashboard etc. just yet. Here's what the logs show:
View attachment 220097

Just wanted to add that I was playing with this all last night trying to get it installed and running. I am getting the same error despite running the update script several times, some docker compose pull, and even scrapping everything and starting fresh.

I set up the MQTT and confirmed BI is sending the valid JSON information. On first install I had an issue where I was presented the "update" page after first login and it wouldn't allow me beyond it with an error of "relation plate_reads does not exist". After starting fresh and running the update script a few times, that was resolved. I am just stuck on this apparent database issue now.

Won't have time to troubleshoot anymore myself until this weekend but wanted to mention that you are not alone in this!

Janhaus · 2025-05-01T00:22:37-0400

Ok so on my subsequent wipe (full delete of directory, removal of containers/images/volumes in docker before re-installing using the docker image and also checking the MD5 of schema.sql and migrations.sql to match the official ones from the latest main Github), I forgot to update the API key in the ALPR Database settings, which was what led to the:
Received plate read data: [object Object]
error of not getting plates saved to the database nor being saved in ALPR Database.

After updating the API key, I'm back to where I was on my first install, where the file is stored, but not saved to the database:
4/30/2025, 4:53:43 PM [INFO] POST /api/plate-reads
4/30/2025, 4:53:43 PM [INFO] Received plate read data: [object Object]
4/30/2025, 4:53:43 PM [INFO] Database connection established
4/30/2025, 4:53:43 PM [INFO] [FileStorage] Successfully saved image
4/30/2025, 4:53:43 PM [ERROR] Error processing request: error: there is no unique or exclusion constraint matching the ON CONFLICT specification

Can somebody with a working install verify a couple of items? In the Web Request or MQTT, the Post/Payload I have is copied from the instructions:
{ "ai_dump":&JSON, "Image":"&ALERT_JPEG", "camera":"&CAM", "ALERT_PATH": "&ALERT_PATH", "ALERT_CLIP": "&ALERT_CLIP", "timestamp":"&ALERT_TIME" }
Is this what you have?

Secondly, the stored file names I have appear like so:
undefined_1746046806816_d5cf3c51.jpg
Is this what you get as well?

Trying to see if the issue lies in a config item within BlueIris vs within the ALPR Database config...

@th182 - how do I verify if the JSON is correct?

VideoDad · 2025-05-01T00:31:42-0400

Where you have "undefined", I have the plate name.
A quick way to check the contents of the &JSON is to send an email alert when a plate is detected that contains &JSON in the body of the email.

PeteJ · 2025-05-01T11:04:22-0400

Janhaus said:
Ok so on my subsequent wipe (full delete of directory, removal of containers/images/volumes in docker before re-installing using the docker image and also checking the MD5 of schema.sql and migrations.sql to match the official ones from the latest main Github), I forgot to update the API key in the ALPR Database settings, which was what led to the:
Received plate read data: [object Object]
error of not getting plates saved to the database nor being saved in ALPR Database.

After updating the API key, I'm back to where I was on my first install, where the file is stored, but not saved to the database:
4/30/2025, 4:53:43 PM [INFO] POST /api/plate-reads
4/30/2025, 4:53:43 PM [INFO] Received plate read data: [object Object]
4/30/2025, 4:53:43 PM [INFO] Database connection established
4/30/2025, 4:53:43 PM [INFO] [FileStorage] Successfully saved image
4/30/2025, 4:53:43 PM [ERROR] Error processing request: error: there is no unique or exclusion constraint matching the ON CONFLICT specification

Can somebody with a working install verify a couple of items? In the Web Request or MQTT, the Post/Payload I have is copied from the instructions:
{ "ai_dump":&JSON, "Image":"&ALERT_JPEG", "camera":"&CAM", "ALERT_PATH": "&ALERT_PATH", "ALERT_CLIP": "&ALERT_CLIP", "timestamp":"&ALERT_TIME" }
Is this what you have?

Secondly, the stored file names I have appear like so:
undefined_1746046806816_d5cf3c51.jpg
Is this what you get as well?

Trying to see if the issue lies in a config item within BlueIris vs within the ALPR Database config...

@th182 - how do I verify if the JSON is correct?

This is what I have:

{"ai_dump":&JSON, "Image":"&ALERT_JPEG", "camera":"&CAM", "ALERT_PATH":"&ALERT_PATH", "ALERT_CLIP":"&ALERT_CLIP", "timestamp":"&ALERT_TIME"}

PeteJ · 2025-05-01T14:21:20-0400

Janhaus said:
4/30/2025, 4:53:43 PM [INFO] [FileStorage] Successfully saved image
4/30/2025, 4:53:43 PM [ERROR] Error processing request: error: there is no unique or exclusion constraint matching the ON CONFLICT specification

If you are getting this, your schema doesn't seem to be applied. See this thread on how to do it manually:

P

Post in thread 'Full ALPR Database System for Blue Iris!'

Mar 25, 2025

@svalvasori Can you connecting to your sql container, and then issuing this command:

psql -d postgres -U postgres -f /docker-entrypoint-initdb.d/schema.sql

On my test system, I was seeing some errors that look sort of similar to yours:

app-1 | Database connection established
app-1 | [FileStorage] Successfully saved image
db-1 | 2025-03-25 18:26:54.744 GMT [11787] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
db-1 | 2025-03-25 18:26:54.744 GMT [11787] STATEMENT: WITH new_plate AS (
db-1 | INSERT INTO plates (plate_number)
db-1...

Janhaus · 2025-05-01T14:27:47-0400

I tried the suggestions in this thread: Full ALPR Database System for Blue Iris!
But no joy, that led to no . I did start dumping the JSON to a file and it looks ok:

Json: [{"api":"license-plate","found":{"message":"Found DayPlate","count":1,"predictions":[{"label":"DayPlate","confidence":0.4658358,"y_min":473,"x_min":203,"y_max":523,"x_max":294}],"inferenceDevice":"CPU","inferenceMs":104,"processMs":104,"analysisRoundTripMs":106,"success":true,"moduleName":"Object Detection (YOLOv5 .NET)","moduleId":"ObjectDetectionYOLOv5Net","command":"custom","requestId":"6a410d6f-3943-4661-b7e4-c10884cf25a1","processedBy":"localhost","timestampUTC":"Thu, 01 May 2025 17:21:25 GMT"}},{"api":"alpr","found":{"success":true,"processMs":215,"inferenceMs":167,"predictions":[{"confidence":0.964375913143158,"label":"Plate: RC7989","plate":"RC7989","x_min":203,"y_min":473,"x_max":294,"y_max":523}],"message":"Found Plate: RC7989","moduleId":"ALPR","moduleName":"License Plate Reader","code":200,"command":"alpr","requestId":"27f97b53-225e-4092-b013-7ecc1c10f53b","inferenceDevice":"CPU","analysisRoundTripMs":232,"processedBy":"localhost","timestampUTC":"Thu, 01 May 2025 17:21:25 GMT"}}]

@PeteJ - Got it, figured out how to do that part now and I got the same output from running the psql command as svalvasori so far. Let me wait for a car to drive by and see how it goes

Full ALPR Database System for Blue Iris!

Young grasshopper

n3wb

n3wb

Getting comfortable

n3wb

Getting comfortable

n3wb

Getting the hang of it

Getting comfortable

n3wb

Getting comfortable

Known around here

BIT Beta Team

n3wb

Getting comfortable

Getting the hang of it

Getting the hang of it

n3wb