OpenALPR Advice?

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
The link to get a CSV download from the cloud for the previous 2 days, starting at 5:54 AM, is:

"https: . . . //cloud.openalpr.com/api/search/group?topn=20000&start=2019-02-02T05%3A54%3A00-08%3A00&end=2019-02-04T05%3A54%3A00-08%3A00&order=desc&format=csv"

Where the red fonts are the dates and the blue fonts are the hours and minutes of time, and the green fonts are the ":" character in HTML. The 'topn' is just the maximum records to be returned, and 2,000 is the default.
 
Last edited:

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Support at OpenALPR sent me a link on how to query images from the local database, just as wthimothy pointed out above. Their link goes on to explain how the plate segment alone can be extracted from the whole vehicle image for a smaller transfer of image data, but I have no idea how to acquire this metadata in my platform.

On-Premises ALPR — openalpr 2.6.101 documentation
 

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Here is the .NET code I wrote to import the CSV file, which has already been downloaded by the Cloud Stream dashboard's 'Details' widget.

The process is in three steps:
  1. List the CSV files residing in the target directory
  2. Pick and load one of the CSV files to be imported
  3. Import the selected CSV file into the Database
'======================================================================================
Sub listFiles()
Dim root As String = lblLocationCSV.Text
Dim fileEntries As String() = Directory.GetFiles(root)
lstFiles.Items.Clear()
Application.DoEvents()
For Each fileName As String In fileEntries
If Microsoft.VisualBasic.Right(fileName, 4) = ".csv" Then
lstFiles.Items.Add(fileName)
End If
Next
End Sub
'======================================================================================
Sub readFile(ByVal thisFile As String)
Dim fileName As String = thisFile
lstFile.Items.Clear()
Application.DoEvents()
If System.IO.File.Exists(fileName) = True Then
Dim objReader As New System.IO.StreamReader(fileName)
Do While objReader.Peek() <> -1
lstFile.Items.Add(objReader.ReadLine())
Loop
Else
MessageBox.Show("File Does Not Exist")
End If
If lstFile.Items.Count = 0 Then '
bttnImportFile.Enabled = False
Else
bttnImportFile.Enabled = True
End If
End Sub
'======================================================================================
Sub importfile()
Dim i As Integer
Dim arr() As String
Dim intPK As Integer
If lstFile.Items.Count = 0 Then
Exit Sub
End If
intAdded = 0
For i = 0 To lstFile.Items.Count - 1
lblAdding.Text = ""
Application.DoEvents()
lstFile.SelectedIndex = i
arr = Split(lstFile.Text, ",")
If arr(0) = "pk" Then
Else
intPK = CInt(arr(0))
'FIRST QUERY IF pk ALREADY EXISTS:
If gotPK(intPK) = False Then
'add to DB:
Call importToDB(lstFile.Text)
End If
End If
Next
lblRecordsAdded.Text = "Records added: " & intAdded
lblAdding.Text = ""
Application.DoEvents()
Call copyFile()
End Sub
'======================================================================================
Function gotPK(ByVal thisPK As Integer) As Boolean
gotPK = True
thisSQL = "SELECT * FROM tblLPR WHERE pk = " & thisPK & " "
Application.DoEvents()
Dim cmd As New SqlClient.SqlCommand
With cmd
.CommandText = thisSQL
.CommandType = CommandType.Text
.CommandTimeout = 30
.Connection = conn
End With
Dim dtwait As New DataTable
dtwait.Load(cmd.ExecuteReader)
If dtwait.Rows.Count = 0 Then
gotPK = False
Else
gotPK = True
End If
dtwait = Nothing
cmd = Nothing
End Function

'======================================================================================
Sub importToDB(ByVal thisLine As String)
Dim arr() As String
Dim cmd As New SqlClient.SqlCommand
bolImported = True
arr = Split(thisLine, ",")
'SKIP: 1, 2, 7, 8, 16
thisSQL = "INSERT INTO tblLPR(pk,best_confidence,best_index,best_plate,best_uuid,direction_of_travel_degrees,direction_of_travel_id,epoch_time_end,epoch_time_start,hit_count,region,region_confidence,vehicle_body_type,vehicle_body_type_confidence,vehicle_color,vehicle_color_confidence,vehicle_make,vehicle_make_confidence,vehicle_make_model,vehicle_make_model_confidence,vehicle_region_height,vehicle_region_width,vehicle_region_x,vehicle_region_y) "
thisSQL = thisSQL & "VALUES (" & arr(0) & "," & arr(3) & "," & arr(4) & ",'" & arr(5) & "','" & arr(6) & "'," & arr(9) & "," & arr(10) & ",'" & arr(11) & "','" & arr(12) & "'," & arr(13) & ",'" & arr(14) & "'," & arr(15) & ",'" & arr(17) & "'," & arr(18) & ",'" & arr(19) & "'," & arr(20) & ",'" & arr(21) & "'," & arr(22) & ",'" & arr(23) & "'," & arr(24) & "," & arr(25) & "," & arr(26) & "," & arr(27) & "," & arr(28) & ") "
With cmd
.CommandText = thisSQL
.CommandType = CommandType.Text
.CommandTimeout = 30
.Connection = conn
.ExecuteNonQuery()
End With
cmd = Nothing
intAdded = intAdded + 1
lblAdding.Text = intAdded & " - Adding " & arr(5)
Application.DoEvents()
End Sub
'======================================================================================
UPDATE: Here's the function which converts OpenALPR's universal time to local time:

Function convertUtoL(ByVal thisDate As String) As DateTime
Dim strDate As String
Dim strTime As String
Dim eDate As String

'SAMPLE thisDate, from the database . . .
'2019-01-26T19:14:06.136Z

strDate = Microsoft.VisualBasic.Left(thisDate, 10)
strTime = Mid(thisDate, 12, 8)
eDate = strDate & " " & strTime

Dim expenddt As DateTime = DateTime.ParseExact(eDate, "yyyy-MM-dd HH:mm:ss",
System.Globalization.DateTimeFormatInfo.InvariantInfo)
dteUTC = expenddt

Dim d As DateTime = DateTime.Parse(dteUTC) 'this is universal
Dim u As DateTime = d.ToLocalTime 'this is is local central daylight
d = u.ToUniversalTime 'double check

dteLocal = u
convertUtoL = dteLocal

End Function

'======================================================================================
 
Last edited:

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
The 'lstFiles' ListBox is the small one on the top. The 'lstFile' ListBox is the large one. Each of the three buttons calls their respective subs.
 

Attachments

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
I've added a chart to show the number of times a plate has been captured per hour, going back to when the collection started around New Years . . . the goal is to ignore those plates of known neighbors and to have a list of unknown plates only, or where the number of records is low.


LPRviewer_1.png
 

catcamstar

Known around here
Joined
Jan 28, 2018
Messages
1,659
Reaction score
1,193
Wauw, great stuff @DLONG2! Might be an option to let the program give you these "irregular" license plates (eg. calculate the 2 percentile of plate not being frequent flyers). I see you can "hide neighbors", but I'm guessing this is static programmed on the "known neighbors" license plates. I've seen once a short movie where they put ALPR cams at both intrance and exit of a neighborhood, and their "tool" (like yours) calculates the average "passing time" (eg. how much time between enter and exit). By doing so, they spotted all the cars using their roads as "bypass" of traffic jams on the highway intersection further down the road because they drove through very fast. Cops off course didn't want to do anything, but city now has installed additional speed bumps.
Well done!
CC
 

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Yes, a neighbor's plate would need to be manually tagged. I still have to figure out the SQL join to exclude the neighbors, as I use an additional table for status and notes.

Here's the splash screen I created for the program. Finding Buster Keaton holding a camera with a monkey on his back seems apropos to this LPR hobby.

OPENALPR_Plate.png
 

catcamstar

Known around here
Joined
Jan 28, 2018
Messages
1,659
Reaction score
1,193
Yes, a neighbor's plate would need to be manually tagged. I still have to figure out the SQL join to exclude the neighbors, as I use an additional table for status and notes.

Here's the splash screen I created for the program. Finding Buster Keaton holding a camera with a monkey on his back seems apropos to this LPR hobby.

View attachment 40561
Not sure if your SQL supports it, but I used this article the other day to "filter out" stuff that's in another table. It might help you out too!

Query to exclude rows whose ID is listed in another table
 

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Thanks, catcamstar, the LEFT JOIN was what I had needed. Now I can exclude known and tagged neighbors from the search.
 

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
Thanks @DLONG2 for this post and your private communications as well. While I have a long way to go to get the interface as slick as what you have - you had some great ideas. Here is where mine currently sits. Behind the scenes, I've got a configurable timer to do the automatic download. That timer also checks any new entries against an alert list and e-mails the picture to the configured recipient if configured.
Definitely a fun weekend project that got a little out of hand!
upload_2019-6-9_18-15-10.png
 

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Way cool to see, Gymratz! Wondering if you used a new joined table of plate to neighbor? Or are you tagging all known neighbor's plates in the same main table with extra 'status' and 'description' fields?
 

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
Joined table, store Plate (as PK), Description, Status, Alert_Address (stores e-mail address(es) to e-mail when seen).
I've got almost all SQL logic in a Stored Procedure, and pass in any variables as parameters (even if this is just internal to me, trying to keep best practice to sanitize data and reduce chances of SQL injection).
Here's my code to pull from SQL based on the current filters I have (not as many as you, yet).
upload_2019-6-9_21-34-8.png
 

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
Ohhhh...Emmm....Geee.....This is exactly what I had been searching for and hoping that cloud.openalpr.com would do, but it didn't.

@DLONG2 ad @Gymratz is there anything I could do to help? Anyway to donate to your beer fund to get a test drive of this? The "tagging" and excluding, and alerts and everything is EXACTLY what is needed that I couldn't figure out! Please let me know
 

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
What I've done isn't anywhere close to being distributable. If you've got any knowledge of SQL and .NET coding, you could install free SQL Sever and Visual Studio and I'd be happy to offer up advice/tips/tricks.
Between DLONG and myself we've added some additional tweaks and streamlined some processes.

I'm not sure if his is set up to easily be distributed though. In some ways the way we implemented things were very different, in some ways quite similar. He definitely has the upper hand on aesthetics!
 

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
What I've done isn't anywhere close to being distributable. If you've got any knowledge of SQL and .NET coding, you could install free SQL Sever and Visual Studio and I'd be happy to offer up advice/tips/tricks.
Between DLONG and myself we've added some additional tweaks and streamlined some processes.

I'm not sure if his is set up to easily be distributed though. In some ways the way we implemented things were very different, in some ways quite similar. He definitely has the upper hand on aesthetics!
I have “some” knowledge of coding. But I usually am better off taking and adjusting code that is already there as I don’t know how to start from the beginning (if that makes sense). Like if your GUI is setup to grab “link” and store “folder” and parse “info”.

I definitely can code link and folder and info.

But setting it up to grab link parse info I’d have trouble with like from scratch
 
Last edited:

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Hi Nyghthawk,

With the OpenALPR cloud connected to BI and collecting all the plates, and using their free tier service, the idea is to download the CSV files within the allotted 48 hours in order to assure that all plates are saved and not lost. These CSV files have dozens of columns of data fields, such as 'best-plate' and 'region' etc.

  1. The first step is to create a database with all the fields you wish to keep.
  2. The next step is to build a Win10 application which can download, open and parse the CSV files, and connect to the database and import the data.
  3. The last step is to build a Win10 viewing application to query the database and view the plate images and various data.

The alerts can occur on the importation process built in step 2, but are tagged in viewing application from step 3.

The skills required are VB.NET programming in C# or VB, building database tables and SQL statements, and file handling.

Please let me know how that sounds. Microsoft offer a free download of their Visual Studio 2017 or Visual Studio Community, and you can subscribe to a 'pay-as-you-go' Microsoft Azure account to host a database in the cloud which might cost a few dollars a month (as I have), or perhaps install a free local SQL server on your PC.
 

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
Hi Nyghthawk,

With the OpenALPR cloud connected to BI and collecting all the plates, and using their free tier service, the idea is to download the CSV files within the allotted 48 hours in order to assure that all plates are saved and not lost. These CSV files have dozens of columns of data fields, such as 'best-plate' and 'region' etc.

  1. The first step is to create a database with all the fields you wish to keep.
  2. The next step is to build a Win10 application which can download, open and parse the CSV files, and connect to the database and import the data.
  3. The last step is to build a Win10 viewing application to query the database and view the plate images and various data.

The alerts can occur on the importation process built in step 2, but are tagged in viewing application from step 3.

The skills required are VB.NET programming in C# or VB, building database tables and SQL statements, and file handling.

Please let me know how that sounds. Microsoft offer a free download of their Visual Studio 2017 or Visual Studio Community, and you can subscribe to a 'pay-as-you-go' Microsoft Azure account to host a database in the cloud which might cost a few dollars a month (as I have), or perhaps install a free local SQL server on your PC.
1. Seems easy enough, I am assuming its SQL database as you keep mentioning that over MS Access. So it might take a bit more to get the tables organized on what I want, but I might be able to manage.
2. "build a application" might be the difficult part. I probably can write a script/crontab of some sort, to possibly grab the url every 24 hours or 47hours (before the 48th hour) and dump the CSV file into a folder.....the problem I would have is "open and parse the CSV files" and "connect to the DB and import the Data"
3. and "build a win 10 application" again, appears to be where I would fail.......

Unless its WYSIWYG......have minimal VB/C# coding skills....dabbled with python mainly, and have little experience with DBs.

Which is why I usually do better with the coding in front of me, and can adjust and manipulate that. Cause then I can go "gfmt" means "getfilemoveto" or whatever. Like reading a new language and figuring it out.

I don't need the "cloud" for the DB so I can host that on my own server at home and make that "cloud" accessible if I wanted.

So it seems I need

* SQL Server/DB....

* Visual Studio

Lets start there.
 

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Hi Nyghthawk,

It might take a few weeks or more because of family and work commitments, but we'll provide to the public the full code for an application to collect and view captured plates. Are you currently downloading and storing the CSV files from the webpage? If so please continue to do so, as you'll eventually be able to import them into your own app.
 

nhs128

Young grasshopper
Joined
Jul 8, 2017
Messages
91
Reaction score
21
Any progress on this application? I've started working on something prior to seeing this post. I'm not the greatest programmer either but wanted to see where this was before I pressed on.
 

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
Because I am so late to the party lol

I use wget with date appended + crontab to grab the CSV files every 24 hours. (I dont care about dupes right now)....

I have been messing with the ability to use just the beanstalked data and trying to grab from that directly, so that we do not have to download the CSV, import it into a DB then have a program read said DB....

If we can just create the program to read the beanstalked data directly, and save it to its own database, we won't have to worry about the 48 hour limit 'free' account, as our program will save it all indefinitely (or to our own liking)
 
Top