# Working with LPR data in MySQL database

**URL:** <https://discourse.nodered.org/t/working-with-lpr-data-in-mysql-database/45100>\
**Category:** General\
**Created:** [3 May 2021 09:12 UTC](https://discourse.nodered.org/t/working-with-lpr-data-in-mysql-database/45100 "2021-05-03T09:12:51Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![niehans](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/niehans/32/41104_2.png) [@niehans](https://discourse.nodered.org/u/niehans)\
**Post date:** [3 May 2021 09:12 UTC](https://discourse.nodered.org/t/working-with-lpr-data-in-mysql-database/45100/1 "2021-05-03T09:12:51Z")

</div>

Hi there.

I'm new to this world of Node Red and coding in general so bare with me if ask silly questions.  
What I have done so far:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/f/5fd73eaa3665e63a0d5fd2b70c1e6cf425c79ef3.png)

```auto
[{"id":"d8b6cc0d.873be8","type":"tab","label":"Plate","disabled":false,"info":""},{"id":"351cc5c9.0664ba","type":"inject","z":"d8b6cc0d.873be8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"/share/","payload":"/share/BB12345.090930.plate.jpg","payloadType":"str","x":230,"y":200,"wires":[["99e8d71c.058b38"]]},{"id":"eb064ca1.021da","type":"watch","z":"d8b6cc0d.873be8","name":"plate","files":"/share/","recursive":"","x":230,"y":300,"wires":[["99e8d71c.058b38"]]},{"id":"868f4aef.566f98","type":"mysql","z":"d8b6cc0d.873be8","mydb":"bb63b04c.348868","name":"","x":730,"y":180,"wires":[["a3d9d80.21f6b28"]]},{"id":"74d49650.e7f9a8","type":"change","z":"d8b6cc0d.873be8","name":"","rules":[{"t":"delete","p":"topic","pt":"msg"},{"t":"change","p":"payload","pt":"msg","from":"/share/","fromt":"str","to":"","tot":"str"},{"t":"change","p":"payload","pt":"msg","from":".plate.jpg","fromt":"str","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":600,"y":260,"wires":[["f6500b9e.4a6cd"]]},{"id":"828fbf52.eb7f88","type":"change","z":"d8b6cc0d.873be8","name":"plate","rules":[{"t":"set","p":"topic","pt":"msg","to":"","tot":"date"},{"t":"move","p":"payload","pt":"msg","to":"payload.plate","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":450,"y":180,"wires":[["a71cc407.822a8"]]},{"id":"f6500b9e.4a6cd","type":"function","z":"d8b6cc0d.873be8","name":"","func":"var plate = msg.payload\nvar date =msg.payload\nvar msg1 = { payload: plate.slice(0, 7)};\n\n\nreturn [msg1];","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":760,"y":260,"wires":[["828fbf52.eb7f88"]]},{"id":"99e8d71c.058b38","type":"delay","z":"d8b6cc0d.873be8","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"2","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":400,"y":260,"wires":[["74d49650.e7f9a8"]]},{"id":"a3d9d80.21f6b28","type":"debug","z":"d8b6cc0d.873be8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":910,"y":180,"wires":[]},{"id":"a71cc407.822a8","type":"function","z":"d8b6cc0d.873be8","name":"","func":"var Plate = msg.payload.plate\nmsg.topic = \"INSERT INTO plate(`Plate`) VALUES ('\"+Plate+\"')\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":180,"wires":[["868f4aef.566f98"]]},{"id":"bb63b04c.348868","type":"MySQLdatabase","name":"Plate","host":"127.0.0.1","port":"3306","db":"data","tz":"","charset":"UTF8"}]

```

I take the snapshot file from the LPR camera that ends in my /share folder. Send it to the delay node to kill some double snaps the camera pass (I don't know why) .  
Then I remove /share/ and xxxxx.plate.jpg from the payload and then pass it to the SQL.

Now I'm trying to insure that only payload with 2 letters and 5 numbers (AB12345) are sent to the database but I can't figure out how to  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/b/1b307c5ee6c5ee5cf4a9b39ffed86fb33e348439.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [3 May 2021 10:09 UTC](https://discourse.nodered.org/t/working-with-lpr-data-in-mysql-database/45100/2 "2021-05-03T10:09:47Z")

</div>

Hi, I would do it all in one function node, splitting the payload and then checking with regex that the final value is BB12345 format.  
e.g.

```auto
[{"id":"351cc5c9.0664ba","type":"inject","z":"d8b6cc0d.873be8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"/share/","payload":"/share/BB12345.090930.plate.jpg","payloadType":"str","x":230,"y":200,"wires":[["99e8d71c.058b38"]]},{"id":"99e8d71c.058b38","type":"delay","z":"d8b6cc0d.873be8","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"2","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":390,"y":200,"wires":[["a71cc407.822a8"]]},{"id":"a71cc407.822a8","type":"function","z":"d8b6cc0d.873be8","name":"","func":"var Plate = msg.payload.split(\"/\");\nPlate = Plate[Plate.length-1].split(\".\")[0]\nif(Plate.search(/^[A-Z]{2}[0-9]{5}$/) != -1){\nmsg.topic = \"INSERT INTO plate(`Plate`) VALUES ('\"+Plate+\"')\";\nreturn msg;\n}\nreturn null;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":570,"y":200,"wires":[["a3d9d80.21f6b28"]]},{"id":"a3d9d80.21f6b28","type":"debug","z":"d8b6cc0d.873be8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":710,"y":200,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![niehans](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/niehans/32/41104_2.png) [@niehans](https://discourse.nodered.org/u/niehans)\
**Post date:** [3 May 2021 10:18 UTC](https://discourse.nodered.org/t/working-with-lpr-data-in-mysql-database/45100/3 "2021-05-03T10:18:40Z")

</div>

Thanks m8.  
Wooooooaaaaw it looks so easy but for me its not. I really need to learn some basic javascript.

**Next question.**  
I want to calculate the time between two timestamps with the same license plate in the database so I can figure out how long a trip would take. Is it possible and where do I start?

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [3 May 2021 10:29 UTC](https://discourse.nodered.org/t/working-with-lpr-data-in-mysql-database/45100/4 "2021-05-03T10:29:43Z")

</div>

Difficult to give a definite answer as i know nothing about you Db and the table columns. But i would query the database for the last two entries of the license plate, with their timestamps and then calculate the difference. This will all depend on how you store the time stamp etc. You could also do all this in the database with the query.

> <https://stackoverflow.com/questions/25863144/calculate-the-time-difference-between-two-timestamps-in-mysql/25863597#25863597>

---

<div class="post-metadata">

**Author:** ![niehans](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/niehans/32/41104_2.png) [@niehans](https://discourse.nodered.org/u/niehans)\
**Post date:** [3 May 2021 14:11 UTC](https://discourse.nodered.org/t/working-with-lpr-data-in-mysql-database/45100/5 "2021-05-03T14:11:53Z")

</div>

My SQL looks like this

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/3/a32d24fc7d98e2cfc75177b4ea251178aec9634f.png)

and I found two functions I want to merge together but I don't know how

`SELECT Plate, COUNT(Plate) FROM plate GROUP BY Plate HAVING COUNT(Plate) = 2`

And then:  
`SELECT TIMESTAMPDIFF(MINUTE,MIN(plateDate),MAX(plateDate)) FROM plate WHERE Plate = `  
WHERE Plate = should be the other function that found the groups of plates.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [3 May 2021 17:08 UTC](https://discourse.nodered.org/t/working-with-lpr-data-in-mysql-database/45100/6 "2021-05-03T17:08:36Z")

</div>

I could make a guess as to the format, but I think you will get a better answer to your question on a MySQL forum .

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [17 May 2021 17:09 UTC](https://discourse.nodered.org/t/working-with-lpr-data-in-mysql-database/45100/7 "2021-05-17T17:09:36Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.