Please help me create a traffic accident mqtt sensor in Node Red

PlayedIn · February 23, 2019, 1:34am

So I’m trying pull current local traffic accident data from a table on this website:

http://afc5102.scdps.gov/SCDPS_Exweb/SCDPS/HighwayPatrol/WebCad_External?Id=3

I’m grabbing the html using the http node and then feeding it to an html node:

The first html node config looks like:

The second html node looks like:

The debug output of the first few messages:

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[6]

"Status"

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[4]

"Time"

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[6]

"County"

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[8]

"Location"

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[18]

"COLLISION:INJURIES"

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[107]

"↵ IN PROGRESS↵ "

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[101]

"↵ 18:53↵ "

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[104]

"↵ ANDERSON↵ "

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[134]

"↵ THREE BRIDGES RD x[POWDERSVILLE MAIN] ↵ "

2/22/2019, 8:18:02 PM[node: 56df4fe4.abf59](https://xxxxxx.duckdns.org:1880/#)msg.payload : string[18]

So at this point I start realizing I’m a little over my head an unsure how best to go about generating the json to send to an mqtt sensor in HA. I’m also not sure whether I should have allowed the html nodes to create an array instead of single messages or not. My inclination is to take each message and build the json with some function node using string functions. Am I correct in assuming that I should create an array with each message being a record in the array? I need someone to point me in the right direction, please?

I also notice this “↵” character in many of the fields which I’m not sure where that came from. Is that a carriage return character? I’m assuming I should just strip that out unless there’s a way to prevent it from occurring in the first place?

Here is the exported flow in case anyone wants to throw me a lifeline:

[{"id":"94903c36.26e6e","type":"tab","label":"Experimentation","disabled":false,"info":""},{"id":"56df4fe4.abf59","type":"debug","z":"94903c36.26e6e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":650,"y":160,"wires":[]},{"id":"537bd8c6.6120e8","type":"inject","z":"94903c36.26e6e","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":40,"wires":[["d50c357f.57ace8"]]},{"id":"d3734630.287d58","type":"html","z":"94903c36.26e6e","name":"","property":"payload","outproperty":"payload","tag":"tr","ret":"html","as":"multi","x":350,"y":160,"wires":[["5d275ae1.2fb874"]]},{"id":"d50c357f.57ace8","type":"http request","z":"94903c36.26e6e","name":"access traffic website","method":"GET","ret":"txt","url":"http://afc5102.scdps.gov/SCDPS_Exweb/SCDPS/HighwayPatrol/WebCad_External?Id=3","tls":"","x":160,"y":160,"wires":[["d3734630.287d58"]]},{"id":"5d275ae1.2fb874","type":"html","z":"94903c36.26e6e","name":"","property":"payload","outproperty":"payload","tag":"td","ret":"text","as":"multi","x":490,"y":160,"wires":[["56df4fe4.abf59"]]}]

Kermit · February 23, 2019, 6:03am

You didn’t say what part of the information you wanted to pass to the mqtt. Here’s an updated flow that will give you a object for each row in the table.

[{"id":"cb230e5f.3907c","type":"debug","z":"e7dedcb6.fe7ad","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1094,"y":336,"wires":[]},{"id":"4955061b.5e6938","type":"inject","z":"e7dedcb6.fe7ad","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":156,"y":288,"wires":[["e814c691.85bb98"]]},{"id":"e9f778d7.186bf8","type":"html","z":"e7dedcb6.fe7ad","name":"","property":"payload","outproperty":"payload","tag":"table[id=DataGrid1]>tr:not(:first-child)","ret":"html","as":"multi","x":462,"y":336,"wires":[["4d88675b.7294d8"]]},{"id":"e814c691.85bb98","type":"http request","z":"e7dedcb6.fe7ad","name":"access traffic website","method":"GET","ret":"txt","url":"http://afc5102.scdps.gov/SCDPS_Exweb/SCDPS/HighwayPatrol/WebCad_External?Id=3","tls":"","x":172,"y":336,"wires":[["e9f778d7.186bf8"]]},{"id":"4d88675b.7294d8","type":"html","z":"e7dedcb6.fe7ad","name":"","property":"payload","outproperty":"payload","tag":"td","ret":"text","as":"multi","x":690,"y":336,"wires":[["7753e0f6.7f92e"]]},{"id":"5fb68509.43247c","type":"join","z":"e7dedcb6.fe7ad","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":946,"y":336,"wires":[["cb230e5f.3907c"]]},{"id":"7753e0f6.7f92e","type":"function","z":"e7dedcb6.fe7ad","name":"","func":"const keys = [\n    \"IncidentType\",\n    \"Status\",\n    \"Time\",\n    \"County\",\n    \"Location\"\n];\n\nmsg.topic = keys[msg.parts.index];\nmsg.payload = msg.payload.trim();\nreturn msg;","outputs":1,"noerr":0,"x":818,"y":336,"wires":[["5fb68509.43247c"]]}]

PlayedIn · February 23, 2019, 6:27pm

Wow, thank you for putting that together! That looks great.

Yes, I wanted to send all five fields to an mqtt sensor in HA so I can display that table in a list card such as this:

I created an mqtt sensor in configuration.yaml:

  - platform: mqtt
    name: Traffic Accidents
    state_topic: "accidents"

I then added an mqtt node to the flow you posted and set it to public the results of your flow to the topic of “accidents”

Then I added the custom:list-card:

type: custom:list-card
entity: sensor.traffic_accidents
title: Traffic Accidents
columns:
- title: ‘Type’
  field: IncidentType
- title: ‘Status’
  field: Status
- title: “Time”
  field: Time
- title: “County”
  field: County
- title: “Location”
  field: Location

I’m guessing there must be something fundamentally wrong with how I created the mqtt sensor as the data doesn’t display in the list card. When I go to states under developers tools to see the state of sensor.traffic_accidents, the state shows the json of the last row of the table:

{"IncidentType":"COLLISION:NO INJURY","Status":"IN PROGRESS","Time":"12:52","County":"SPARTANBURG","Location":"985 NEW CUT RD x[SIMUEL RD]"}

So I’m not sure if the problem is with how I set up the mqtt sensor or if I have to combine the message objects into one payload in nodered before publishing to that mqtt topic?

PlayedIn · February 24, 2019, 2:11am

While I try to figure out what the payload should be, I thought I would try to simply inject json to a mqtt node to see if the attributes showed up for the sensor I created. I published the following to topic accidents/attributes:

[
 {
   "Incident Type": "COLLISION:NO INJURY",
   "Status": "IN PROGRESS",
   "Time": "18:08",
   "County": "ANDERSON",
   "Location": "MICHELIN BLVD x[DOBBINS BRIDGE RD]"
 },
 {
   "Incident Type": "COLLISION:INJURIES",
   "Status": "ROADWAY BLKD",
   "Time": "20:23",
   "County": "ANDERSON",
   "Location": "E RIVER ST x[ESTES DR]"
 },
 {
   "Incident Type": "COLLISION:PRIV PROP",
   "Status": "IN PROGRESS",
   "Time": "20:38",
   "County": "ANDERSON",
   "Location": "4123 CLEMSON BLVD [X2[SC28]]  x[US76]"
 },
 {
   "Incident Type": "COLLISION:NO INJURY",
   "Status": "IN PROGRESS",
   "Time": "17:43",
   "County": "GREENVILLE",
   "Location": "8874 N TIGERVILLE RD x[BELK RD]"
 },
 {
   "Incident Type": "COLLISION:NO INJURY",
   "Status": "IN PROGRESS",
   "Time": "18:19",
   "County": "GREENVILLE",
   "Location": "WADE HAMPTON BLVD x[EDWARDS MILL RD]"
 },
 {
   "Incident Type": "HIT & RUN:NO INJURY",
   "Status": "IN PROGRESS",
   "Time": "19:40",
   "County": "GREENVILLE",
   "Location": "BRUCE RD x[E DORCHESTER BLVD]"
 },
 {
   "Incident Type": "ASSIST MOTORIST",
   "Status": "ROADWAY BLKD",
   "Time": "19:52",
   "County": "GREENVILLE",
   "Location": "MARTIN RD x[GREENPOND RD]"
 },
 {
   "Incident Type": "DEBRIS IN ROADWAY",
   "Status": "IN PROGRESS",
   "Time": "19:55",
   "County": "GREENVILLE",
   "Location": "I85 [49SB]"
 },
 {
   "Incident Type": "COLLISION:NO INJURY",
   "Status": "IN PROGRESS",
   "Time": "20:12",
   "County": "GREENVILLE",
   "Location": "BLAKELY AVE x[US25]"
 },
 {
   "Incident Type": "HIT & RUN:NO INJURY",
   "Status": "IN PROGRESS",
   "Time": "20:28",
   "County": "GREENVILLE",
   "Location": "I85 [51SB]"
 },
 {
   "Incident Type": "HIT & RUN:PRIV PROP",
   "Status": "IN PROGRESS",
   "Time": "20:22",
   "County": "SPARTANBURG",
   "Location": "3605 BOILING SPRINGS RD [X2[SC9]]  x[SC9]"
 }
]

Looking at the mqtt sensor configuration docs, I configured the mqtt sensor in configuration.yaml as:

  - platform: mqtt
    name: Traffic Accidents
    state_topic: 'accidents/state'
    json_attributes_topic: 'accidents/attributes'

However none of the json data made its way to the attributes of sensor.traffic_accidents. I guess I should start a new topic to help me with the configuration of my mqtt sensor.

subzero79 · February 24, 2019, 4:11am

the attributes topic payload needs to be a json object containing keys and values, not an array of objects.

Following @Kermit flow, this takes the last entry from the table, the incidenttype goes to state of the sensor, the rest of the values to the attributes topic except for incidenttype

[{"id":"f20391cf.96ac9","type":"inject","z":"8206fd6.8eaf4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":200,"wires":[["a04b6166.2f2d3"]]},{"id":"a04b6166.2f2d3","type":"http request","z":"8206fd6.8eaf4","name":"access traffic website","method":"GET","ret":"txt","url":"http://afc5102.scdps.gov/SCDPS_Exweb/SCDPS/HighwayPatrol/WebCad_External?Id=3","tls":"","x":180,"y":260,"wires":[["d958cd96.0b3b7"]]},{"id":"8b24e7d.c68e018","type":"html","z":"8206fd6.8eaf4","name":"","property":"payload","outproperty":"payload","tag":"td","ret":"text","as":"multi","x":450,"y":340,"wires":[["8fc3aae.98c4b58"]]},{"id":"799e0c1b.b14664","type":"join","z":"8206fd6.8eaf4","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":590,"y":420,"wires":[["8153fc4b.d713d"]]},{"id":"8fc3aae.98c4b58","type":"function","z":"8206fd6.8eaf4","name":"","func":"const keys = [\n    \"IncidentType\",\n    \"Status\",\n    \"Time\",\n    \"County\",\n    \"Location\"\n];\n\nmsg.topic = keys[msg.parts.index];\nmsg.payload = msg.payload.trim();\nreturn msg;","outputs":1,"noerr":0,"x":510,"y":380,"wires":[["799e0c1b.b14664"]]},{"id":"8153fc4b.d713d","type":"change","z":"8206fd6.8eaf4","name":"","rules":[{"t":"set","p":"sensorAttr","pt":"flow","to":"payload","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"payload.IncidentType","tot":"msg"},{"t":"delete","p":"sensorAttr.IncidentType","pt":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":460,"wires":[["6673f00a.d382b","90152237.06e42"]]},{"id":"6673f00a.d382b","type":"mqtt out","z":"8206fd6.8eaf4","name":"","topic":"accidents/state","qos":"","retain":"","broker":"8ae6b449.b778e8","x":860,"y":520,"wires":[]},{"id":"456587da.320ce8","type":"mqtt out","z":"8206fd6.8eaf4","name":"","topic":"accidents/attributes","qos":"","retain":"","broker":"8ae6b449.b778e8","x":1050,"y":380,"wires":[]},{"id":"90152237.06e42","type":"change","z":"8206fd6.8eaf4","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"sensorAttr","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":840,"y":380,"wires":[["456587da.320ce8"]]},{"id":"d958cd96.0b3b7","type":"html","z":"8206fd6.8eaf4","name":"","property":"payload","outproperty":"payload","tag":"table[id=DataGrid1]>tr:last-child","ret":"html","as":"multi","x":310,"y":300,"wires":[["8b24e7d.c68e018"]]},{"id":"8ae6b449.b778e8","type":"mqtt-broker","z":"","name":"MosQuiTTo SRV","broker":"hass.wlzero.pw","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

PlayedIn · February 24, 2019, 5:00am

@subzero79 and @Kermit thank you for helping me with this. The problem is in trying to get the attributes to show all rows from the table. I want to use this card by @iantrich.

In his example, he has a sensor called sensor.engineering_feed that has several entries in the attributes. If you look at his list-card for this sensor, it looks like this:

The config for his card:

  - type: custom:list-card
    entity: sensor.engineering_feed
    title: Engineering Feed
    columns:
      - title: 'Title'
        field: title
      - title: 'Link'
        field: link

If I go to dev tools, state to look at his sensor:

It appears his state value is the number of records in the table.
@subzero79 Your flow did yield attributes for one row of that table of accidents, but I don’t know how to alter it to get all rows in the table to appear there so that the list-card will show all accidents in the area. His list-card would not show that one record from @subzero79 's flow.

This is what I currently have:

subzero79 · February 24, 2019, 5:09am

Ok, so then each attribute will be an accident?, so each object (accident) needs to be a string associated with a key in the attributes payload

[{"id":"f20391cf.96ac9","type":"inject","z":"8206fd6.8eaf4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":200,"wires":[["a04b6166.2f2d3"]]},{"id":"a04b6166.2f2d3","type":"http request","z":"8206fd6.8eaf4","name":"access traffic website","method":"GET","ret":"txt","url":"http://afc5102.scdps.gov/SCDPS_Exweb/SCDPS/HighwayPatrol/WebCad_External?Id=3","tls":"","x":180,"y":260,"wires":[["d958cd96.0b3b7"]]},{"id":"8b24e7d.c68e018","type":"html","z":"8206fd6.8eaf4","name":"","property":"payload","outproperty":"payload","tag":"td","ret":"text","as":"multi","x":450,"y":340,"wires":[["8fc3aae.98c4b58"]]},{"id":"799e0c1b.b14664","type":"join","z":"8206fd6.8eaf4","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"2","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":590,"y":420,"wires":[["ab7f52fd.01aed"]]},{"id":"8fc3aae.98c4b58","type":"function","z":"8206fd6.8eaf4","name":"","func":"const keys = [\n    \"IncidentType\",\n    \"Status\",\n    \"Time\",\n    \"County\",\n    \"Location\"\n];\n\nmsg.topic = keys[msg.parts.index];\nmsg.payload = msg.payload.trim();\nreturn msg;","outputs":1,"noerr":0,"x":510,"y":380,"wires":[["799e0c1b.b14664"]]},{"id":"6673f00a.d382b","type":"mqtt out","z":"8206fd6.8eaf4","name":"","topic":"accidents/state","qos":"","retain":"","broker":"8ae6b449.b778e8","x":1000,"y":400,"wires":[]},{"id":"456587da.320ce8","type":"mqtt out","z":"8206fd6.8eaf4","name":"","topic":"accidents/attributes","qos":"","retain":"","broker":"8ae6b449.b778e8","x":1010,"y":320,"wires":[]},{"id":"d958cd96.0b3b7","type":"html","z":"8206fd6.8eaf4","name":"","property":"payload","outproperty":"payload","tag":"table[id=DataGrid1]>tr:not(:first-child)","ret":"html","as":"multi","x":330,"y":300,"wires":[["8b24e7d.c68e018"]]},{"id":"ab7f52fd.01aed","type":"join","z":"8206fd6.8eaf4","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"2","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":670,"y":360,"wires":[["5629e14f.0af7c"]]},{"id":"5629e14f.0af7c","type":"function","z":"8206fd6.8eaf4","name":"","func":"var msg1 = {\"payload\":{}};\nvar msg2 = {\"payload\":{}};\nfor (var i = msg.payload.length - 1; i >= 0; i--) {\n    msg1['payload']['accident'+i] = JSON.stringify(msg.payload[i]).slice(1,-1);\n}\n\nmsg2.payload = String(msg.payload.length)\nreturn [msg1,msg2];","outputs":2,"noerr":0,"x":810,"y":380,"wires":[["456587da.320ce8"],["6673f00a.d382b"]]},{"id":"8ae6b449.b778e8","type":"mqtt-broker","z":"","name":"MosQuiTTo SRV","broker":"hass.wlzero.pw","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

iantrich · February 24, 2019, 6:11am

Yup, that should work. Just needs to be an array of objects

PlayedIn · February 24, 2019, 6:45am

@subzero79 Thank you! (very much)

It’s not showing anything in the list-card. The sensor attributes are:

accident7: "IncidentType":"COLLISION:NO INJURY","Status":"IN PROGRESS","Time":"22:29","County":"SPARTANBURG","Location":"WALNUT GROVE RD x[BENNETT FARM RD]"
accident6: "IncidentType":"COLLISION:INJURIES","Status":"IN PROGRESS","Time":"01:20","County":"SPARTANBURG","Location":"3697 CHESNEE HWY [X2[US221]]  x[US221]"
accident5: "IncidentType":"COLLISION:INJURIES","Status":"IN PROGRESS","Time":"22:34","County":"PICKENS","Location":"SC11 x[CROWE CREEK RD]"
accident4: "IncidentType":"COLLISION:INJURIES","Status":"IN PROGRESS","Time":"23:56","County":"GREENVILLE","Location":"SC11 x[AIRLINE RD]"
accident3: "IncidentType":"COLLISION:INJURIES","Status":"IN PROGRESS","Time":"23:03","County":"GREENVILLE","Location":"332 MT LEBANON CHURCH RD [X2[DUSTY OAK LN]]  x[CRIPPLE CREEK RD]"
accident2: "IncidentType":"COLLISION:PRIV PROP","Status":"IN PROGRESS","Time":"01:14","County":"GREENVILLE","Location":"CEDAR LANE RD x[CLARK DR]"
accident1: "IncidentType":"COLLISION:INJURIES","Status":"IN PROGRESS","Time":"00:49","County":"GREENVILLE","Location":"1836 W GEORGIA RD [X2[RIVEREEN WAY]]  x[LEAFMORE CT]"
accident0: "IncidentType":"COLLISION:INJURIES","Status":"IN PROGRESS","Time":"20:23","County":"ANDERSON","Location":"E RIVER ST x[ESTES DR]"
friendly_name: Traffic Accidents

My list-card config looks like this:

 - type: custom:list-card
    entity: sensor.traffic_accidents
    title: Traffic Accidents
    columns:
      - title: 'Incident'
        field: IncidentType
      - title: 'Time'
        field: Time
      - title: 'County'
        field: County
      - title: 'Location'
        field: Location

Any ideas? The only thing I see different in your sensor.engineering_feed attributes is you have curly braces (see screenshot a few posts back).

subzero79 · February 24, 2019, 7:41am

Seems like the problem the mqtt sensor accepts an object with keys and values defined only. The feed parser sensor is a custom sensor that accepts a list of attributes. If i try to send the attributes payload as an array ha complains “[homeassistant.components.mqtt] JSON result was not a dictionary”.

Maybe you can generate an rss feed from node red from the output of the flow so then you can use it in that custom component

priyesh1112 · October 30, 2019, 2:19pm

Hello everyone, can anyone advice me how can I fetch the traffic accident data for Hamburg, germany on NodeRed?

Ryan_Grandy · November 7, 2019, 9:16pm

Did you figure this out? Can the API node be used to set the attributes to the correct objects? I would like to do a very similar this using node red but can’t figure out how to have node red send objects to the attributes of the sensor in the correct format.

Ryan_Grandy · November 19, 2019, 10:43pm

You can get the correct format for this card using the API node and JSON. I got it working recently in a flow I created to monitor device availability and display the last time the device was heard from.