Hi,
I have a flow were I have a msg. come in from MQTT, this msg. is processed and stored in a DB.
How do I delay the second message to wait until the first one processing and stored in the DB, before it gets processed?
Hello. I can't tell what you are doing in that image, even if I squint 
However, I have used the simple Delay function in which two actions happen with one trigger: A camera stream is captured and converted to a .jpg in the 1st action, but the 2nd action is delayed an appropriate time before grabbing that image and processing it.
Perhaps something as simple as that would work for you?
Here the important part is not really what the processing is of the msg. but the fact that the second one needs to be only processed once the first one is done.
Delaying by time is not possible when I process 100s messages.
A delay is a delay is a delay 
By timer or by trigger, each will broaden out your 100s of msgs from real-time.
If a simple delay is not an option then I recall reading how one can trigger an action based on the result of another... Just do a search... I am, cuz now I am curious how what I recall reading might interface with whatever your flow is doing.
1 Like
what about using an RBE node (node-red-node-rbe) to filter some of the messages out if there is no significant value change.
ps. if you have any control over the sent mqtt msgs .. why are you sending so many in the first place ? 
Yes it need to be trigger on the result that the first one were written to the DB
I have a few trackers sending location, so each msg. is unique, so filtering out if a change have occurred is not possible.
There is a message queue node simple-message-queue Use the output from your db write to release the next message.
2 Likes
It often is not as easy as might be thought using the message queue for such a task. The problem is getting it to send the first one, and getting it to restart once the queue is empty. You might find it easier using node-red-contrib-semaphore.
What is happening in the original flow that gets things out of order ?
They are not out of order, it is just that I store some of the msg to the context, recall data from the DB, then use context and DB data to do a calculation and then store data to DB.
So if the next msg arrives before the this process is completed the context data will be incorrect for the calculation.
Can you not store it as another property on the msg (rather than using context) ? So it will be right there when you need it later.
No, they communicate via the Semaphore object that you configure in the nodes.
Some code I have scavenged form a kind member, I have figured out how to make it work with context, but maybe need to have a carful look and see if I can use it differently
[{"id":"60b51f02.82614","type":"mysql","z":"1c27c045.696c9","mydb":"37f92788.1e2638","name":"","x":710,"y":180,"wires":[["8c0f7ed.b72c08","d9663519.ae7568"]]},{"id":"8c0f7ed.b72c08","type":"function","z":"1c27c045.696c9","name":"prepare to/from coords","func":"\nvar from = {\n lat: msg.payload[0].tl_lat,\n lon: msg.payload[0].tl_lat,\n elv: 0,\n}\n\nvar to = {\n lat: flow.get(\"tl_lat\"),\n lon: flow.get(\"tl_lon\"),\n elv: 0,\n}\n\n\nmsg.payload = {\n from: from,\n to: to\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":900,"y":180,"wires":[["3e4f2aa0.301bb6","84c68885.52e1f8"]]},{"id":"3e4f2aa0.301bb6","type":"function","z":"1c27c045.696c9","name":"calculate distance between 2 coords","func":"//Credit: https://javascript.plainenglish.io/calculating-azimuth-distance-and-altitude-from-a-pair-of-gps-locations-36b4325d8ab0\n\nfunction ParseAngle(ang, limit) {\n var angle = parseFloat(ang);\n if (isNaN(angle) || (angle < -limit) || (angle > limit)) {\n node.error(\"Invalid angle value.\", msg);\n return null;\n } else {\n return angle;\n }\n}\n\nfunction ParseElevation(el) {\n var angle = parseFloat(el);\n if (isNaN(angle)) {\n node.error(\"Invalid elevation value.\", msg);\n return null;\n } else {\n return angle;\n }\n}\n\nfunction ParseLocation(pos) {\n var lat = ParseAngle(pos.lat, 90.0);\n var location = null;\n if (lat != null) {\n var lon = ParseAngle(pos.lon, 180.0);\n if (lon != null) {\n var elv = ParseElevation(pos.elv);\n if (elv != null) {\n location = { lat: lat, lon: lon, elv: elv };\n }\n }\n }\n return location;\n}\n\nfunction EarthRadiusInMeters(latitudeRadians) {\n // latitudeRadians is geodetic, i.e. that reported by GPS.\n // http://en.wikipedia.org/wiki/Earth_radius\n var a = 6378137.0; // equatorial radius in meters\n var b = 6356752.3; // polar radius in meters\n var cos = Math.cos(latitudeRadians);\n var sin = Math.sin(latitudeRadians);\n var t1 = a * a * cos;\n var t2 = b * b * sin;\n var t3 = a * cos;\n var t4 = b * sin;\n return Math.sqrt((t1 * t1 + t2 * t2) / (t3 * t3 + t4 * t4));\n}\n\nfunction GeocentricLatitude(lat) {\n // Convert geodetic latitude 'lat' to a geocentric latitude 'clat'.\n // Geodetic latitude is the latitude as given by GPS.\n // Geocentric latitude is the angle measured from center of Earth between a point and the equator.\n // https://en.wikipedia.org/wiki/Latitude#Geocentric_latitude\n var e2 = 0.00669437999014;\n var clat = Math.atan((1.0 - e2) * Math.tan(lat));\n return clat;\n}\n\nfunction LocationToPoint(c) {\n // Convert (lat, lon, elv) to (x, y, z).\n var lat = c.lat * Math.PI / 180.0;\n var lon = c.lon * Math.PI / 180.0;\n var radius = EarthRadiusInMeters(lat);\n var clat = GeocentricLatitude(lat);\n\n var cosLon = Math.cos(lon);\n var sinLon = Math.sin(lon);\n var cosLat = Math.cos(clat);\n var sinLat = Math.sin(clat);\n var x = radius * cosLon * cosLat;\n var y = radius * sinLon * cosLat;\n var z = radius * sinLat;\n\n // We used geocentric latitude to calculate (x,y,z) on the Earth's ellipsoid.\n // Now we use geodetic latitude to calculate normal vector from the surface, to correct for elevation.\n var cosGlat = Math.cos(lat);\n var sinGlat = Math.sin(lat);\n\n var nx = cosGlat * cosLon;\n var ny = cosGlat * sinLon;\n var nz = sinGlat;\n\n x += c.elv * nx;\n y += c.elv * ny;\n z += c.elv * nz;\n\n return { x: x, y: y, z: z, radius: radius, nx: nx, ny: ny, nz: nz };\n}\n\nfunction Distance(ap, bp) {\n var dx = ap.x - bp.x;\n var dy = ap.y - bp.y;\n var dz = ap.z - bp.z;\n return Math.sqrt(dx * dx + dy * dy + dz * dz);\n}\n\nfunction RotateGlobe(b, a, bradius, aradius) {\n // Get modified coordinates of 'b' by rotating the globe so that 'a' is at lat=0, lon=0.\n var br = { lat: b.lat, lon: (b.lon - a.lon), elv: b.elv };\n var brp = LocationToPoint(br);\n\n // Rotate brp cartesian coordinates around the z-axis by a.lon degrees,\n // then around the y-axis by a.lat degrees.\n // Though we are decreasing by a.lat degrees, as seen above the y-axis,\n // this is a positive (counterclockwise) rotation (if B's longitude is east of A's).\n // However, from this point of view the x-axis is pointing left.\n // So we will look the other way making the x-axis pointing right, the z-axis\n // pointing up, and the rotation treated as negative.\n\n var alat = GeocentricLatitude(-a.lat * Math.PI / 180.0);\n var acos = Math.cos(alat);\n var asin = Math.sin(alat);\n\n var bx = (brp.x * acos) - (brp.z * asin);\n var by = brp.y;\n var bz = (brp.x * asin) + (brp.z * acos);\n\n return { x: bx, y: by, z: bz, radius: bradius };\n}\n\nfunction NormalizeVectorDiff(b, a) {\n // Calculate norm(b-a), where norm divides a vector by its length to produce a unit vector.\n var dx = b.x - a.x;\n var dy = b.y - a.y;\n var dz = b.z - a.z;\n var dist2 = dx * dx + dy * dy + dz * dz;\n if (dist2 == 0) {\n return null;\n }\n var dist = Math.sqrt(dist2);\n return { x: (dx / dist), y: (dy / dist), z: (dz / dist), radius: 1.0 };\n}\n\nfunction Calculate(pos1, pos2) {\n \n var result = {\n from: pos1,\n to: pos2,\n };\n\n var a = ParseLocation(pos1);\n if (a != null) {\n var b = ParseLocation(pos2);\n if (b != null) {\n var ap = LocationToPoint(a);\n var bp = LocationToPoint(b);\n var distKm = 0.001 * Distance(ap, bp);\n result.distanceKm = distKm;\n\n // Let's use a trick to calculate azimuth:\n // Rotate the globe so that point A looks like latitude 0, longitude 0.\n // We keep the actual radii calculated based on the oblate geoid,\n // but use angles based on subtraction.\n // Point A will be at x=radius, y=0, z=0.\n // Vector difference B-A will have dz = N/S component, dy = E/W component.\n var br = RotateGlobe(b, a, bp.radius, ap.radius);\n if (br.z * br.z + br.y * br.y > 1.0e-6) {\n var theta = Math.atan2(br.z, br.y) * 180.0 / Math.PI;\n var azimuth = 90.0 - theta;\n if (azimuth < 0.0) {\n azimuth += 360.0;\n }\n if (azimuth > 360.0) {\n azimuth -= 360.0;\n }\n result.azimuth = azimuth;\n }\n\n var bma = NormalizeVectorDiff(bp, ap);\n if (bma != null) {\n // Calculate altitude, which is the angle above the horizon of B as seen from A.\n // Almost always, B will actually be below the horizon, so the altitude will be negative.\n // The dot product of bma and norm = cos(zenith_angle), and zenith_angle = (90 deg) - altitude.\n // So altitude = 90 - acos(dotprod).\n var altitude = 90.0 - (180.0 / Math.PI) * Math.acos(bma.x * ap.nx + bma.y * ap.ny + bma.z * ap.nz);\n result.altitude = altitude;\n }\n }\n }\n return result;\n}\nmsg.payload = Calculate(msg.payload.from, msg.payload.to);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1170,"y":180,"wires":[["b1287392.334b6","25747f9f.e4a8c"]]},{"id":"b1287392.334b6","type":"function","z":"1c27c045.696c9","name":"","func":"let dev_eui = flow.get(\"dev_eui\");\nlet time = flow.get(\"time\");\nlet tl_lat = flow.get(\"tl_lat\");\nlet tl_lon = flow.get(\"tl_lon\");\nlet tl_bat = flow.get(\"tl_bat\");\nlet bytes_34 = flow.get(\"bytes_34\");\nlet gateway_1_rssi = flow.get(\"gateway_1_rssi\");\nlet gateway_1_snr = flow.get(\"gateway_1_snr\");\nlet spreading_factor = flow.get(\"spreading_factor\");\nlet gateway_id = flow.get(\"gateway_id\");\nlet distanceKm = msg.payload.distanceKm;\nflow.set(\"distanceKm\",msg.payload.distanceKm);\n\nmsg.topic = \"INSERT INTO pb_lat_lon(sen_id,tl_timestamp,tl_lat,tl_lon,tl_bat, bytes_34, gateway_1_rssi, gateway_1_snr, spreading_factor, gateway_id,distanceKm) VALUES ((SELECT sen_id FROM udevice_t WHERE dev_id = '\" + dev_eui + \"'),'\" + time + \"',\" + tl_lat + \",\" + tl_lon + \",\" + tl_bat + \",'\" + bytes_34 + \"',\" + gateway_1_rssi + \",\" + gateway_1_snr + \",\" + spreading_factor + \",'\" + gateway_id + \"','\" + distanceKm + \"');\";\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1420,"y":180,"wires":[["c0d054b6.30a7a8","67dedd03.9767c4"]]},{"id":"c0d054b6.30a7a8","type":"mysql","z":"1c27c045.696c9","mydb":"37f92788.1e2638","name":"","x":1590,"y":180,"wires":[["3a0f7ae1.823f66"]]},{"id":"37f92788.1e2638","type":"MySQLdatabase","name":"","host":"dbsoiot.civilwbkm1wy.eu-west-1.rds.amazonaws.com","port":"3306","db":"soiot","tz":"","charset":"UTF8"}]
I'm not seeing where you set those flow variables in there anywhere ? so are they constant ? or in the previous function (not shown)
Sorry, my apologies it is form the previous function, included now.
[{"id":"c0d054b6.30a7a8","type":"mysql","z":"1c27c045.696c9","mydb":"37f92788.1e2638","name":"","x":1590,"y":180,"wires":[["3a0f7ae1.823f66"]]},{"id":"b1287392.334b6","type":"function","z":"1c27c045.696c9","name":"","func":"let dev_eui = flow.get(\"dev_eui\");\nlet time = flow.get(\"time\");\nlet tl_lat = flow.get(\"tl_lat\");\nlet tl_lon = flow.get(\"tl_lon\");\nlet tl_bat = flow.get(\"tl_bat\");\nlet bytes_34 = flow.get(\"bytes_34\");\nlet gateway_1_rssi = flow.get(\"gateway_1_rssi\");\nlet gateway_1_snr = flow.get(\"gateway_1_snr\");\nlet spreading_factor = flow.get(\"spreading_factor\");\nlet gateway_id = flow.get(\"gateway_id\");\nlet distanceKm = msg.payload.distanceKm;\nflow.set(\"distanceKm\",msg.payload.distanceKm);\n\nmsg.topic = \"INSERT INTO pb_lat_lon(sen_id,tl_timestamp,tl_lat,tl_lon,tl_bat, bytes_34, gateway_1_rssi, gateway_1_snr, spreading_factor, gateway_id,distanceKm) VALUES ((SELECT sen_id FROM udevice_t WHERE dev_id = '\" + dev_eui + \"'),'\" + time + \"',\" + tl_lat + \",\" + tl_lon + \",\" + tl_bat + \",'\" + bytes_34 + \"',\" + gateway_1_rssi + \",\" + gateway_1_snr + \",\" + spreading_factor + \",'\" + gateway_id + \"','\" + distanceKm + \"');\";\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1420,"y":180,"wires":[["c0d054b6.30a7a8","67dedd03.9767c4"]]},{"id":"3a0f7ae1.823f66","type":"link out","z":"1c27c045.696c9","name":"","links":["83f5e7c1.ec6008","acd13aa2.aec288"],"x":1715,"y":180,"wires":[]},{"id":"67dedd03.9767c4","type":"debug","z":"1c27c045.696c9","name":"Inser Function","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":1600,"y":80,"wires":[]},{"id":"3e4f2aa0.301bb6","type":"function","z":"1c27c045.696c9","name":"calculate distance between 2 coords","func":"//Credit: https://javascript.plainenglish.io/calculating-azimuth-distance-and-altitude-from-a-pair-of-gps-locations-36b4325d8ab0\n\nfunction ParseAngle(ang, limit) {\n var angle = parseFloat(ang);\n if (isNaN(angle) || (angle < -limit) || (angle > limit)) {\n node.error(\"Invalid angle value.\", msg);\n return null;\n } else {\n return angle;\n }\n}\n\nfunction ParseElevation(el) {\n var angle = parseFloat(el);\n if (isNaN(angle)) {\n node.error(\"Invalid elevation value.\", msg);\n return null;\n } else {\n return angle;\n }\n}\n\nfunction ParseLocation(pos) {\n var lat = ParseAngle(pos.lat, 90.0);\n var location = null;\n if (lat != null) {\n var lon = ParseAngle(pos.lon, 180.0);\n if (lon != null) {\n var elv = ParseElevation(pos.elv);\n if (elv != null) {\n location = { lat: lat, lon: lon, elv: elv };\n }\n }\n }\n return location;\n}\n\nfunction EarthRadiusInMeters(latitudeRadians) {\n // latitudeRadians is geodetic, i.e. that reported by GPS.\n // http://en.wikipedia.org/wiki/Earth_radius\n var a = 6378137.0; // equatorial radius in meters\n var b = 6356752.3; // polar radius in meters\n var cos = Math.cos(latitudeRadians);\n var sin = Math.sin(latitudeRadians);\n var t1 = a * a * cos;\n var t2 = b * b * sin;\n var t3 = a * cos;\n var t4 = b * sin;\n return Math.sqrt((t1 * t1 + t2 * t2) / (t3 * t3 + t4 * t4));\n}\n\nfunction GeocentricLatitude(lat) {\n // Convert geodetic latitude 'lat' to a geocentric latitude 'clat'.\n // Geodetic latitude is the latitude as given by GPS.\n // Geocentric latitude is the angle measured from center of Earth between a point and the equator.\n // https://en.wikipedia.org/wiki/Latitude#Geocentric_latitude\n var e2 = 0.00669437999014;\n var clat = Math.atan((1.0 - e2) * Math.tan(lat));\n return clat;\n}\n\nfunction LocationToPoint(c) {\n // Convert (lat, lon, elv) to (x, y, z).\n var lat = c.lat * Math.PI / 180.0;\n var lon = c.lon * Math.PI / 180.0;\n var radius = EarthRadiusInMeters(lat);\n var clat = GeocentricLatitude(lat);\n\n var cosLon = Math.cos(lon);\n var sinLon = Math.sin(lon);\n var cosLat = Math.cos(clat);\n var sinLat = Math.sin(clat);\n var x = radius * cosLon * cosLat;\n var y = radius * sinLon * cosLat;\n var z = radius * sinLat;\n\n // We used geocentric latitude to calculate (x,y,z) on the Earth's ellipsoid.\n // Now we use geodetic latitude to calculate normal vector from the surface, to correct for elevation.\n var cosGlat = Math.cos(lat);\n var sinGlat = Math.sin(lat);\n\n var nx = cosGlat * cosLon;\n var ny = cosGlat * sinLon;\n var nz = sinGlat;\n\n x += c.elv * nx;\n y += c.elv * ny;\n z += c.elv * nz;\n\n return { x: x, y: y, z: z, radius: radius, nx: nx, ny: ny, nz: nz };\n}\n\nfunction Distance(ap, bp) {\n var dx = ap.x - bp.x;\n var dy = ap.y - bp.y;\n var dz = ap.z - bp.z;\n return Math.sqrt(dx * dx + dy * dy + dz * dz);\n}\n\nfunction RotateGlobe(b, a, bradius, aradius) {\n // Get modified coordinates of 'b' by rotating the globe so that 'a' is at lat=0, lon=0.\n var br = { lat: b.lat, lon: (b.lon - a.lon), elv: b.elv };\n var brp = LocationToPoint(br);\n\n // Rotate brp cartesian coordinates around the z-axis by a.lon degrees,\n // then around the y-axis by a.lat degrees.\n // Though we are decreasing by a.lat degrees, as seen above the y-axis,\n // this is a positive (counterclockwise) rotation (if B's longitude is east of A's).\n // However, from this point of view the x-axis is pointing left.\n // So we will look the other way making the x-axis pointing right, the z-axis\n // pointing up, and the rotation treated as negative.\n\n var alat = GeocentricLatitude(-a.lat * Math.PI / 180.0);\n var acos = Math.cos(alat);\n var asin = Math.sin(alat);\n\n var bx = (brp.x * acos) - (brp.z * asin);\n var by = brp.y;\n var bz = (brp.x * asin) + (brp.z * acos);\n\n return { x: bx, y: by, z: bz, radius: bradius };\n}\n\nfunction NormalizeVectorDiff(b, a) {\n // Calculate norm(b-a), where norm divides a vector by its length to produce a unit vector.\n var dx = b.x - a.x;\n var dy = b.y - a.y;\n var dz = b.z - a.z;\n var dist2 = dx * dx + dy * dy + dz * dz;\n if (dist2 == 0) {\n return null;\n }\n var dist = Math.sqrt(dist2);\n return { x: (dx / dist), y: (dy / dist), z: (dz / dist), radius: 1.0 };\n}\n\nfunction Calculate(pos1, pos2) {\n \n var result = {\n from: pos1,\n to: pos2,\n };\n\n var a = ParseLocation(pos1);\n if (a != null) {\n var b = ParseLocation(pos2);\n if (b != null) {\n var ap = LocationToPoint(a);\n var bp = LocationToPoint(b);\n var distKm = 0.001 * Distance(ap, bp);\n result.distanceKm = distKm;\n\n // Let's use a trick to calculate azimuth:\n // Rotate the globe so that point A looks like latitude 0, longitude 0.\n // We keep the actual radii calculated based on the oblate geoid,\n // but use angles based on subtraction.\n // Point A will be at x=radius, y=0, z=0.\n // Vector difference B-A will have dz = N/S component, dy = E/W component.\n var br = RotateGlobe(b, a, bp.radius, ap.radius);\n if (br.z * br.z + br.y * br.y > 1.0e-6) {\n var theta = Math.atan2(br.z, br.y) * 180.0 / Math.PI;\n var azimuth = 90.0 - theta;\n if (azimuth < 0.0) {\n azimuth += 360.0;\n }\n if (azimuth > 360.0) {\n azimuth -= 360.0;\n }\n result.azimuth = azimuth;\n }\n\n var bma = NormalizeVectorDiff(bp, ap);\n if (bma != null) {\n // Calculate altitude, which is the angle above the horizon of B as seen from A.\n // Almost always, B will actually be below the horizon, so the altitude will be negative.\n // The dot product of bma and norm = cos(zenith_angle), and zenith_angle = (90 deg) - altitude.\n // So altitude = 90 - acos(dotprod).\n var altitude = 90.0 - (180.0 / Math.PI) * Math.acos(bma.x * ap.nx + bma.y * ap.ny + bma.z * ap.nz);\n result.altitude = altitude;\n }\n }\n }\n return result;\n}\nmsg.payload = Calculate(msg.payload.from, msg.payload.to);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1170,"y":180,"wires":[["b1287392.334b6","25747f9f.e4a8c"]]},{"id":"8c0f7ed.b72c08","type":"function","z":"1c27c045.696c9","name":"prepare to/from coords","func":"let lat = flow.get(\"fromLat\");\nlet lon = flow.get(\"fromLon\");\n\nvar from = {\n lat: lat,\n lon: lon,\n elv: 0,\n}\n\nvar to = {\n lat: flow.get(\"tl_lat\"),\n lon: flow.get(\"tl_lon\"),\n elv: 0,\n}\n\n\nmsg.payload = {\n from: from,\n to: to\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":900,"y":180,"wires":[["3e4f2aa0.301bb6","84c68885.52e1f8"]]},{"id":"25747f9f.e4a8c","type":"debug","z":"1c27c045.696c9","name":"Dist Cal","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":1380,"y":80,"wires":[]},{"id":"60b51f02.82614","type":"mysql","z":"1c27c045.696c9","mydb":"37f92788.1e2638","name":"","x":710,"y":180,"wires":[["8c0f7ed.b72c08","d9663519.ae7568"]]},{"id":"84c68885.52e1f8","type":"debug","z":"1c27c045.696c9","name":"To and from set","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":1180,"y":260,"wires":[]},{"id":"b33fa45c.fcdf38","type":"function","z":"1c27c045.696c9","name":"","func":"flow.set(\"dev_eui\",msg.payload.end_device_ids.dev_eui);\nlet dev_eui = msg.payload.end_device_ids.dev_eui;\nflow.set(\"time\",msg.payload.received_at);\nflow.set(\"tl_lat\",msg.payload.uplink_message.decoded_payload.latitude);\nflow.set(\"tl_lon\",msg.payload.uplink_message.decoded_payload.longitude);\nflow.set(\"tl_bat\",msg.payload.uplink_message.decoded_payload.battery);\nflow.set(\"bytes_34\",msg.payload.uplink_message.frm_payload);\nflow.set(\"gateway_1_rssi\",msg.payload.uplink_message.rx_metadata[0].channel_rssi);\nflow.set(\"gateway_1_snr\",msg.payload.uplink_message.rx_metadata[0].snr);\nflow.set(\"spreading_factor\",msg.payload.uplink_message.settings.data_rate.lora.spreading_factor);\nflow.set(\"gateway_id\",msg.payload.uplink_message.rx_metadata[0].gateway_ids.gateway_id);\n\n\nmsg.topic = \"SELECT * FROM soiot.pb_lat_lon WHERE sen_id = (SELECT sen_id FROM udevice_t WHERE dev_id = '\" + dev_eui + \"') order by tl_timestamp DESC limit 1;\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":560,"y":180,"wires":[["60b51f02.82614"]]},{"id":"d9663519.ae7568","type":"debug","z":"1c27c045.696c9","name":"Select Function","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":940,"y":280,"wires":[]},{"id":"7590f955.b0fd48","type":"switch","z":"1c27c045.696c9","name":"","property":"msg.payload.uplink_message.decoded_payload.latitude","propertyType":"msg","rules":[{"t":"nnull"}],"checkall":"true","repair":false,"outputs":1,"x":410,"y":180,"wires":[["b33fa45c.fcdf38"]]},{"id":"4e92a67e.1ce518","type":"mqtt in","z":"1c27c045.696c9","name":"","topic":"v3/+/devices/+/up","qos":"2","datatype":"json","broker":"a2b276d9.819678","x":180,"y":180,"wires":[["a482d75d.1624d8","7590f955.b0fd48","441f25f3.57585c","8cd4c6.37f74b38","613c4479.fbb09c","5328b1c5.bdf71","51d947b4.a86c58"]]},{"id":"249f7816.ef65a8","type":"link in","z":"1c27c045.696c9","name":"","links":["983d8d4d.7d361"],"x":235,"y":80,"wires":[["7590f955.b0fd48"]]},{"id":"a482d75d.1624d8","type":"link out","z":"1c27c045.696c9","name":"abeeway-tracker","links":["26ea1fc0.d363","184ff7c0.0eef98"],"x":375,"y":80,"wires":[]},{"id":"441f25f3.57585c","type":"switch","z":"1c27c045.696c9","name":"","property":"payload.uplink_message.decoded_payload.type","propertyType":"msg","rules":[{"t":"eq","v":"HEARTBEAT","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":410,"y":300,"wires":[["370320cd.c6206"]]},{"id":"8cd4c6.37f74b38","type":"switch","z":"1c27c045.696c9","name":"","property":"payload.uplink_message.decoded_payload.status.sos","propertyType":"msg","rules":[{"t":"true"}],"checkall":"true","repair":false,"outputs":1,"x":410,"y":400,"wires":[["4dd87dce.9e4ec4"]]},{"id":"613c4479.fbb09c","type":"switch","z":"1c27c045.696c9","name":"","property":"payload.uplink_message.decoded_payload.type","propertyType":"msg","rules":[{"t":"eq","v":"SHUTDOWN","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":410,"y":460,"wires":[[]]},{"id":"5328b1c5.bdf71","type":"link out","z":"1c27c045.696c9","name":"","links":["7fa399a.9967b68","32df8f26.81c5"],"x":375,"y":500,"wires":[]},{"id":"51d947b4.a86c58","type":"debug","z":"1c27c045.696c9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":650,"y":680,"wires":[]},{"id":"370320cd.c6206","type":"function","z":"1c27c045.696c9","name":"HEARTBEAT","func":"let dev_eui = msg.payload.end_device_ids.dev_eui;\nlet pb_batt = msg.payload.uplink_message.decoded_payload.battery;\nlet pb_per = msg.payload.uplink_message.decoded_payload.battery;\nlet time = msg.payload.received_at;\n\nmsg.topic = \"INSERT INTO pb_hb_t(pb_id, timestamp, pb_batt, pb_per) VALUES ((SELECT sen_id FROM udevice_t WHERE dev_id = '\" + dev_eui + \"'),'\" + time + \"', \" + pb_batt + \",\" + pb_per + \");\";\ndelete msg.payload.end_device_ids;\ndelete msg.payload.correlation_ids;\ndelete msg.payload.received_at;\ndelete msg.payload.uplink_message;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":550,"y":300,"wires":[["f2ce5d6c.85ecf"]]},{"id":"4dd87dce.9e4ec4","type":"switch","z":"1c27c045.696c9","name":"","property":"msg.payload.uplink_message.decoded_payload.latitude","propertyType":"msg","rules":[{"t":"nnull"}],"checkall":"true","repair":false,"outputs":1,"x":550,"y":400,"wires":[["473d0a18.92f704"]]},{"id":"f2ce5d6c.85ecf","type":"mysql","z":"1c27c045.696c9","mydb":"37f92788.1e2638","name":"","x":710,"y":300,"wires":[[]]},{"id":"473d0a18.92f704","type":"function","z":"1c27c045.696c9","name":"","func":"msg.topic = \"INSERT INTO pb_map (sen_id, state) VALUEs ((SELECT sen_id FROM udevice_t WHERE dev_id = '\" + msg.payload.end_device_ids.dev_eui + \"') ,\" + msg.payload.uplink_message.decoded_payload.status.sos + \") ON DUPLICATE KEY UPDATE state = VALUES(state);\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":700,"y":400,"wires":[["56083f24.a85af"]]},{"id":"56083f24.a85af","type":"mysql","z":"1c27c045.696c9","mydb":"37f92788.1e2638","name":"","x":910,"y":400,"wires":[[]]},{"id":"37f92788.1e2638","type":"MySQLdatabase","name":"","host":"dbsoiot.civilwbkm1wy.eu-west-1.rds.amazonaws.com","port":"3306","db":"soiot","tz":"","charset":"UTF8"},{"id":"a2b276d9.819678","type":"mqtt-broker","name":"abeeway-tracker","broker":"eu1.cloud.thethings.network","port":"8883","tls":"c367e243.42a69","clientid":"","usetls":true,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"c367e243.42a69","type":"tls-config","name":"","cert":"","key":"","ca":"","certname":"","keyname":"","caname":"","servername":"","verifyservercert":true}]
ok - better - still not seeing where flow.fromLat and fromLon are set but no matter
in that function before that first db call - I would also set msg.tl_lat and msg.tl_lon - (and msg.fromLat and msg.fromLon) - - then after rather than flow.get them just use msg.
(or indeed for all those variables you will need to insert later in that second db call)
The function "prepare to/from coords" basically wipe all fields from the msg, I need to figure out how not to do it.
[{"id":"b33fa45c.fcdf38","type":"function","z":"1c27c045.696c9","name":"","func":"flow.set(\"dev_eui\",msg.payload.end_device_ids.dev_eui);\nlet dev_eui = msg.payload.end_device_ids.dev_eui;\nflow.set(\"time\",msg.payload.received_at);\nflow.set(\"tl_lat\",msg.payload.uplink_message.decoded_payload.latitude);\nflow.set(\"tl_lon\",msg.payload.uplink_message.decoded_payload.longitude);\nflow.set(\"tl_bat\",msg.payload.uplink_message.decoded_payload.battery);\nflow.set(\"bytes_34\",msg.payload.uplink_message.frm_payload);\nflow.set(\"gateway_1_rssi\",msg.payload.uplink_message.rx_metadata[0].channel_rssi);\nflow.set(\"gateway_1_snr\",msg.payload.uplink_message.rx_metadata[0].snr);\nflow.set(\"spreading_factor\",msg.payload.uplink_message.settings.data_rate.lora.spreading_factor);\nflow.set(\"gateway_id\",msg.payload.uplink_message.rx_metadata[0].gateway_ids.gateway_id);\n\n\nmsg.topic = \"SELECT * FROM soiot.pb_lat_lon WHERE sen_id = (SELECT sen_id FROM udevice_t WHERE dev_id = '\" + dev_eui + \"') order by tl_timestamp DESC limit 1;\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":560,"y":180,"wires":[["60b51f02.82614"]]},{"id":"60b51f02.82614","type":"mysql","z":"1c27c045.696c9","mydb":"37f92788.1e2638","name":"","x":710,"y":180,"wires":[["8c0f7ed.b72c08","d9663519.ae7568"]]},{"id":"8c0f7ed.b72c08","type":"function","z":"1c27c045.696c9","name":"prepare to/from coords","func":"let lat = flow.get(\"fromLat\");\nlet lon = flow.get(\"fromLon\");\n\nvar from = {\n lat: lat,\n lon: lon,\n elv: 0,\n}\n\nvar to = {\n lat: flow.get(\"tl_lat\"),\n lon: flow.get(\"tl_lon\"),\n elv: 0,\n}\n\n\nmsg.payload = {\n from: from,\n to: to\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":900,"y":180,"wires":[["3e4f2aa0.301bb6","84c68885.52e1f8"]]},{"id":"3e4f2aa0.301bb6","type":"function","z":"1c27c045.696c9","name":"calculate distance between 2 coords","func":"//Credit: https://javascript.plainenglish.io/calculating-azimuth-distance-and-altitude-from-a-pair-of-gps-locations-36b4325d8ab0\n\nfunction ParseAngle(ang, limit) {\n var angle = parseFloat(ang);\n if (isNaN(angle) || (angle < -limit) || (angle > limit)) {\n node.error(\"Invalid angle value.\", msg);\n return null;\n } else {\n return angle;\n }\n}\n\nfunction ParseElevation(el) {\n var angle = parseFloat(el);\n if (isNaN(angle)) {\n node.error(\"Invalid elevation value.\", msg);\n return null;\n } else {\n return angle;\n }\n}\n\nfunction ParseLocation(pos) {\n var lat = ParseAngle(pos.lat, 90.0);\n var location = null;\n if (lat != null) {\n var lon = ParseAngle(pos.lon, 180.0);\n if (lon != null) {\n var elv = ParseElevation(pos.elv);\n if (elv != null) {\n location = { lat: lat, lon: lon, elv: elv };\n }\n }\n }\n return location;\n}\n\nfunction EarthRadiusInMeters(latitudeRadians) {\n // latitudeRadians is geodetic, i.e. that reported by GPS.\n // http://en.wikipedia.org/wiki/Earth_radius\n var a = 6378137.0; // equatorial radius in meters\n var b = 6356752.3; // polar radius in meters\n var cos = Math.cos(latitudeRadians);\n var sin = Math.sin(latitudeRadians);\n var t1 = a * a * cos;\n var t2 = b * b * sin;\n var t3 = a * cos;\n var t4 = b * sin;\n return Math.sqrt((t1 * t1 + t2 * t2) / (t3 * t3 + t4 * t4));\n}\n\nfunction GeocentricLatitude(lat) {\n // Convert geodetic latitude 'lat' to a geocentric latitude 'clat'.\n // Geodetic latitude is the latitude as given by GPS.\n // Geocentric latitude is the angle measured from center of Earth between a point and the equator.\n // https://en.wikipedia.org/wiki/Latitude#Geocentric_latitude\n var e2 = 0.00669437999014;\n var clat = Math.atan((1.0 - e2) * Math.tan(lat));\n return clat;\n}\n\nfunction LocationToPoint(c) {\n // Convert (lat, lon, elv) to (x, y, z).\n var lat = c.lat * Math.PI / 180.0;\n var lon = c.lon * Math.PI / 180.0;\n var radius = EarthRadiusInMeters(lat);\n var clat = GeocentricLatitude(lat);\n\n var cosLon = Math.cos(lon);\n var sinLon = Math.sin(lon);\n var cosLat = Math.cos(clat);\n var sinLat = Math.sin(clat);\n var x = radius * cosLon * cosLat;\n var y = radius * sinLon * cosLat;\n var z = radius * sinLat;\n\n // We used geocentric latitude to calculate (x,y,z) on the Earth's ellipsoid.\n // Now we use geodetic latitude to calculate normal vector from the surface, to correct for elevation.\n var cosGlat = Math.cos(lat);\n var sinGlat = Math.sin(lat);\n\n var nx = cosGlat * cosLon;\n var ny = cosGlat * sinLon;\n var nz = sinGlat;\n\n x += c.elv * nx;\n y += c.elv * ny;\n z += c.elv * nz;\n\n return { x: x, y: y, z: z, radius: radius, nx: nx, ny: ny, nz: nz };\n}\n\nfunction Distance(ap, bp) {\n var dx = ap.x - bp.x;\n var dy = ap.y - bp.y;\n var dz = ap.z - bp.z;\n return Math.sqrt(dx * dx + dy * dy + dz * dz);\n}\n\nfunction RotateGlobe(b, a, bradius, aradius) {\n // Get modified coordinates of 'b' by rotating the globe so that 'a' is at lat=0, lon=0.\n var br = { lat: b.lat, lon: (b.lon - a.lon), elv: b.elv };\n var brp = LocationToPoint(br);\n\n // Rotate brp cartesian coordinates around the z-axis by a.lon degrees,\n // then around the y-axis by a.lat degrees.\n // Though we are decreasing by a.lat degrees, as seen above the y-axis,\n // this is a positive (counterclockwise) rotation (if B's longitude is east of A's).\n // However, from this point of view the x-axis is pointing left.\n // So we will look the other way making the x-axis pointing right, the z-axis\n // pointing up, and the rotation treated as negative.\n\n var alat = GeocentricLatitude(-a.lat * Math.PI / 180.0);\n var acos = Math.cos(alat);\n var asin = Math.sin(alat);\n\n var bx = (brp.x * acos) - (brp.z * asin);\n var by = brp.y;\n var bz = (brp.x * asin) + (brp.z * acos);\n\n return { x: bx, y: by, z: bz, radius: bradius };\n}\n\nfunction NormalizeVectorDiff(b, a) {\n // Calculate norm(b-a), where norm divides a vector by its length to produce a unit vector.\n var dx = b.x - a.x;\n var dy = b.y - a.y;\n var dz = b.z - a.z;\n var dist2 = dx * dx + dy * dy + dz * dz;\n if (dist2 == 0) {\n return null;\n }\n var dist = Math.sqrt(dist2);\n return { x: (dx / dist), y: (dy / dist), z: (dz / dist), radius: 1.0 };\n}\n\nfunction Calculate(pos1, pos2) {\n \n var result = {\n from: pos1,\n to: pos2,\n };\n\n var a = ParseLocation(pos1);\n if (a != null) {\n var b = ParseLocation(pos2);\n if (b != null) {\n var ap = LocationToPoint(a);\n var bp = LocationToPoint(b);\n var distKm = 0.001 * Distance(ap, bp);\n result.distanceKm = distKm;\n\n // Let's use a trick to calculate azimuth:\n // Rotate the globe so that point A looks like latitude 0, longitude 0.\n // We keep the actual radii calculated based on the oblate geoid,\n // but use angles based on subtraction.\n // Point A will be at x=radius, y=0, z=0.\n // Vector difference B-A will have dz = N/S component, dy = E/W component.\n var br = RotateGlobe(b, a, bp.radius, ap.radius);\n if (br.z * br.z + br.y * br.y > 1.0e-6) {\n var theta = Math.atan2(br.z, br.y) * 180.0 / Math.PI;\n var azimuth = 90.0 - theta;\n if (azimuth < 0.0) {\n azimuth += 360.0;\n }\n if (azimuth > 360.0) {\n azimuth -= 360.0;\n }\n result.azimuth = azimuth;\n }\n\n var bma = NormalizeVectorDiff(bp, ap);\n if (bma != null) {\n // Calculate altitude, which is the angle above the horizon of B as seen from A.\n // Almost always, B will actually be below the horizon, so the altitude will be negative.\n // The dot product of bma and norm = cos(zenith_angle), and zenith_angle = (90 deg) - altitude.\n // So altitude = 90 - acos(dotprod).\n var altitude = 90.0 - (180.0 / Math.PI) * Math.acos(bma.x * ap.nx + bma.y * ap.ny + bma.z * ap.nz);\n result.altitude = altitude;\n }\n }\n }\n return result;\n}\nmsg.payload = Calculate(msg.payload.from, msg.payload.to);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1170,"y":180,"wires":[["b1287392.334b6","25747f9f.e4a8c"]]},{"id":"b1287392.334b6","type":"function","z":"1c27c045.696c9","name":"","func":"let dev_eui = flow.get(\"dev_eui\");\nlet time = flow.get(\"time\");\nlet tl_lat = flow.get(\"tl_lat\");\nlet tl_lon = flow.get(\"tl_lon\");\nlet tl_bat = flow.get(\"tl_bat\");\nlet bytes_34 = flow.get(\"bytes_34\");\nlet gateway_1_rssi = flow.get(\"gateway_1_rssi\");\nlet gateway_1_snr = flow.get(\"gateway_1_snr\");\nlet spreading_factor = flow.get(\"spreading_factor\");\nlet gateway_id = flow.get(\"gateway_id\");\nlet distanceKm = msg.payload.distanceKm;\nflow.set(\"distanceKm\",msg.payload.distanceKm);\n\nmsg.topic = \"INSERT INTO pb_lat_lon(sen_id,tl_timestamp,tl_lat,tl_lon,tl_bat, bytes_34, gateway_1_rssi, gateway_1_snr, spreading_factor, gateway_id,distanceKm) VALUES ((SELECT sen_id FROM udevice_t WHERE dev_id = '\" + dev_eui + \"'),'\" + time + \"',\" + tl_lat + \",\" + tl_lon + \",\" + tl_bat + \",'\" + bytes_34 + \"',\" + gateway_1_rssi + \",\" + gateway_1_snr + \",\" + spreading_factor + \",'\" + gateway_id + \"','\" + distanceKm + \"');\";\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1420,"y":180,"wires":[["c0d054b6.30a7a8","67dedd03.9767c4"]]},{"id":"c0d054b6.30a7a8","type":"mysql","z":"1c27c045.696c9","mydb":"37f92788.1e2638","name":"","x":1590,"y":180,"wires":[["3a0f7ae1.823f66"]]},{"id":"37f92788.1e2638","type":"MySQLdatabase","name":"","host":"dbsoiot.civilwbkm1wy.eu-west-1.rds.amazonaws.com","port":"3306","db":"soiot","tz":"","charset":"UTF8"}]
The msg comes in from a MQTT
Well it depends if you want it wiped or not :-)...
If you do then in that first function I was suggesting doing
msg.t1_lat = msg.payload.uplink_message.decoded_payload.latitude
msg.t1_lon = msg.payload.uplink_message.decoded_payload.longitude
then in the prepare node doing
var to = {
lat: msg.tl_lat,
lon: msg.tl_lon,
elv: 0,
}
Or if you didn't want to wipe it you could just save the whole thing eg in the first function
msg.oldPayload = RED.util.cloneMessage(msg.payload);
then properties should be available later as msg.oldPayload.uplink_message.decoded_payload.latitude etc etc
1 Like
Thank you Sir, this work 100%.
