Working with LPR data in MySQL database

[niehans]

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:

[{"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

[E1cid]

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.

[{"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":[]}]

[niehans]

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?

[E1cid]

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.

Calculate the time difference between two timestamps in mysql

[niehans]

My SQL looks like this

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.

[E1cid]

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 .