NEW FEATURE: "Distance Column added"
So it is now possible to select a country, and the flow will then calculate the distance for all countries to the selected country based on the latitude/longitude formulas described at https://www.movable-type.co.uk/scripts/latlong.html
This makes it possible to sort the list based on proximity to a selected country.
The flow:
[{"id":"3739886a.1825b8","type":"tab","label":"COVID19 - country comparison","disabled":false,"info":""},{"id":"52a875d9.1a5e1c","type":"ui_base","theme":{"name":"theme-light","lightTheme":{"default":"#0094CE","baseColor":"#0094CE","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":true,"reset":false},"darkTheme":{"default":"#097479","baseColor":"#097479","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":false},"customTheme":{"name":"Untitled Theme 1","default":"#4B7930","baseColor":"#4B7930","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"},"themeState":{"base-color":{"default":"#0094CE","value":"#0094CE","edited":false},"page-titlebar-backgroundColor":{"value":"#0094CE","edited":false},"page-backgroundColor":{"value":"#fafafa","edited":false},"page-sidebar-backgroundColor":{"value":"#ffffff","edited":false},"group-textColor":{"value":"#1bbfff","edited":false},"group-borderColor":{"value":"#ffffff","edited":false},"group-backgroundColor":{"value":"#ffffff","edited":false},"widget-textColor":{"value":"#111111","edited":false},"widget-backgroundColor":{"value":"#0094ce","edited":false},"widget-borderColor":{"value":"#ffffff","edited":false},"base-font":{"value":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"}},"angularTheme":{"primary":"indigo","accents":"blue","warn":"red","background":"grey"}},"site":{"name":"Node-RED Dashboard","hideToolbar":"false","allowSwipe":"false","lockMenu":"false","allowTempTheme":"true","dateFormat":"DD/MM/YYYY","sizes":{"sx":48,"sy":48,"gx":6,"gy":6,"cx":6,"cy":6,"px":0,"py":0}}},{"id":"78853c89.625cc4","type":"ui_tab","z":"","name":"COVID19 - country overview","icon":"dashboard","order":1,"disabled":false,"hidden":false},{"id":"984606ac.155a48","type":"ui_group","z":"","name":"table","tab":"78853c89.625cc4","order":2,"disp":false,"width":"28","collapse":false},{"id":"50d8c10e.e27b4","type":"ui_group","z":"","name":"commands","tab":"78853c89.625cc4","order":1,"disp":false,"width":"3","collapse":false},{"id":"48cc765f.3e0de8","type":"http request","z":"3739886a.1825b8","name":"","method":"GET","ret":"txt","paytoqs":false,"url":"https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv","tls":"","persist":false,"proxy":"","authType":"","x":250,"y":80,"wires":[["d78d0310.f64ee","b1fdd2fb.c9e67"]]},{"id":"d78d0310.f64ee","type":"debug","z":"3739886a.1825b8","name":"http response","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":580,"y":80,"wires":[]},{"id":"b1fdd2fb.c9e67","type":"csv","z":"3739886a.1825b8","name":"","sep":",","hdrin":false,"hdrout":true,"multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"x":230,"y":140,"wires":[["79857f5e.29c53","d78c600d.35a92"]]},{"id":"79857f5e.29c53","type":"debug","z":"3739886a.1825b8","name":"csv2array","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":560,"y":140,"wires":[]},{"id":"d78c600d.35a92","type":"change","z":"3739886a.1825b8","name":"calculate reproduction rates,...","rules":[{"t":"set","p":"payload","pt":"msg","to":"/* payload[0]~>$sift(function($v,$k){ $k~>/col/}) */\t(\t $header := payload[0]~>$each(function($v,$k) { { \"key\" : $k, \"val\" : $v} } );\t {\t \"header\" : $header,\t \"last_date_retrieved\" : $header[-1].val,\t \"last_col_min_8\" : $header[-8],\t \"location\" : payload[col2 != \"Country/Region\"].(\t $new_cases_current := ($lookup($,$header[-1].key)-$lookup($,$header[-8].key))/7;\t $new_cases_1day_ago := ($lookup($,$header[-2].key)-$lookup($,$header[-9].key))/7; \t $new_cases_8days_ago := ($lookup($,$header[-9].key)-$lookup($,$header[-16].key))/7; \t $new_cases_1week_ago := ($lookup($,$header[-8].key)-$lookup($,$header[-15].key))/7;\t $new_cases_2week_ago := ($lookup($,$header[-15].key)-$lookup($,$header[-22].key))/7;\t $new_cases_3week_ago := ($lookup($,$header[-22].key)-$lookup($,$header[-29].key))/7; \t $new_cases_4week_ago := ($lookup($,$header[-29].key)-$lookup($,$header[-36].key))/7; \t {\t \"province_state\" : col1,\t \"country_region\" : col2,\t \"latitude\" : col3,\t \"longitude\" : col4,\t \"distance\" : -1,\t \"total_conf_cases\": $lookup($,$header[-1].key),\t \"new_cases_current\" : $new_cases_current,\t \"new_cases_1week_ago\" : $new_cases_1week_ago,\t \"reproduction_number\" : $new_cases_1week_ago = 0 \t ? ($new_cases_current = 0 ? 0 : 9999)\t : ($new_cases_current/$new_cases_1week_ago)~>$round(2),\t \"reproduction_number_1day_ago\" : $new_cases_8days_ago = 0 \t ? ($new_cases_8days_ago = 0 ? 0 : 9999)\t : ($new_cases_1day_ago/$new_cases_8days_ago)~>$round(2),\t \"reproduction_number_1week_ago\" : $new_cases_2week_ago = 0 \t ? ($new_cases_1week_ago = 0 ? 0 : 9999)\t : ($new_cases_1week_ago/$new_cases_2week_ago)~>$round(2),\t \"reproduction_number_2week_ago\" : $new_cases_3week_ago = 0 \t ? ($new_cases_2week_ago = 0 ? 0 : 9999)\t : ($new_cases_2week_ago/$new_cases_3week_ago)~>$round(2), \t \"reproduction_number_3week_ago\" : $new_cases_4week_ago = 0 \t ? ($new_cases_3week_ago = 0 ? 0 : 9999)\t : ($new_cases_3week_ago/$new_cases_4week_ago)~>$round(2)\t }\t )\t }\t \t)","tot":"jsonata"},{"t":"set","p":"last_date_retrieved","pt":"msg","to":"payload.last_date_retrieved","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":200,"wires":[["9124a122.24074","4e81e87d.b3abf8"]]},{"id":"9124a122.24074","type":"debug","z":"3739886a.1825b8","name":"with reproduction rates,...","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":610,"y":200,"wires":[]},{"id":"4e81e87d.b3abf8","type":"change","z":"3739886a.1825b8","name":"select only countries","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.location[$not($exists(province_state))]","tot":"jsonata"},{"t":"set","p":"options","pt":"msg","to":"payload.country_region","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":260,"wires":[["4406d2a6.88b6ac","7f27f9ea.a5ea88","631625f5.c0ae7c"]]},{"id":"4406d2a6.88b6ac","type":"debug","z":"3739886a.1825b8","name":"only countries","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":580,"y":240,"wires":[]},{"id":"e9092edc.7f8d3","type":"ui_table","z":"3739886a.1825b8","group":"984606ac.155a48","name":"","order":0,"width":"28","height":"15","columns":[{"field":"country_region","title":"location","width":"14px","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"distance","title":"distance","width":"5px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"new_cases_per_million","title":"cases/day/milion","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number","title":"RN (Repr. Nr)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_1day_ago","title":"RN (-1 day)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_1week_ago","title":"RN (-1week)","width":"7px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_2week_ago","title":"RN (-2week)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"html_reproduction_number_3week_ago","title":"RN (-3week)","width":"8px","align":"right","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"total_cases_per_million","title":"tot. cases/million","width":"12px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"population_in_million","title":"popul. [million]","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"total_conf_cases","title":"tot. cases","width":"10px","align":"right","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":750,"y":620,"wires":[]},{"id":"7f27f9ea.a5ea88","type":"change","z":"3739886a.1825b8","name":"add population, ...","rules":[{"t":"set","p":"countries","pt":"msg","to":"[{\"name\":\"Burundi\",\"population\":11890.781},{\"name\":\"Comoros\",\"population\":869.595},{\"name\":\"Djibouti\",\"population\":988.002},{\"name\":\"Eritrea\",\"population\":3546.427},{\"name\":\"Ethiopia\",\"population\":114963.583},{\"name\":\"Kenya\",\"population\":53771.3},{\"name\":\"Madagascar\",\"population\":27691.019},{\"name\":\"Malawi\",\"population\":19129.955},{\"name\":\"Mauritius\",\"population\":1271.767},{\"name\":\"Mayotte\",\"population\":272.813},{\"name\":\"Mozambique\",\"population\":31255.435},{\"name\":\"Réunion\",\"population\":895.308},{\"name\":\"Rwanda\",\"population\":12952.209},{\"name\":\"Seychelles\",\"population\":98.34},{\"name\":\"Somalia\",\"population\":15893.219},{\"name\":\"South Sudan\",\"population\":11193.729},{\"name\":\"Uganda\",\"population\":45741},{\"name\":\"Tanzania\",\"population\":59734.213},{\"name\":\"Zambia\",\"population\":18383.956},{\"name\":\"Zimbabwe\",\"population\":14862.927},{\"name\":\"Angola\",\"population\":32866.268},{\"name\":\"Cameroon\",\"population\":26545.864},{\"name\":\"Central African Republic\",\"population\":4829.764},{\"name\":\"Chad\",\"population\":16425.859},{\"name\":\"Congo (Brazzaville)\",\"population\":5518.092},{\"name\":\"Congo (Kinshasa)\",\"population\":89561.404},{\"name\":\"Equatorial Guinea\",\"population\":1402.985},{\"name\":\"Gabon\",\"population\":2225.728},{\"name\":\"Sao Tome and Principe\",\"population\":219.161},{\"name\":\"Botswana\",\"population\":2351.625},{\"name\":\"Eswatini\",\"population\":1160.164},{\"name\":\"Lesotho\",\"population\":2142.252},{\"name\":\"Namibia\",\"population\":2540.916},{\"name\":\"South Africa\",\"population\":59308.69},{\"name\":\"Benin\",\"population\":12123.198},{\"name\":\"Burkina Faso\",\"population\":20903.278},{\"name\":\"Cabo Verde\",\"population\":555.988},{\"name\":\"Cote d'Ivoire\",\"population\":26378.275},{\"name\":\"Gambia\",\"population\":2416.664},{\"name\":\"Ghana\",\"population\":31072.945},{\"name\":\"Guinea\",\"population\":13132.792},{\"name\":\"Guinea-Bissau\",\"population\":1967.998},{\"name\":\"Liberia\",\"population\":5057.677},{\"name\":\"Mali\",\"population\":20250.834},{\"name\":\"Mauritania\",\"population\":4649.66},{\"name\":\"Niger\",\"population\":24206.636},{\"name\":\"Nigeria\",\"population\":206139.587},{\"name\":\"Saint Helena\",\"population\":6.071},{\"name\":\"Senegal\",\"population\":16743.93},{\"name\":\"Sierra Leone\",\"population\":7976.985},{\"name\":\"Togo\",\"population\":8278.737},{\"name\":\"Algeria\",\"population\":43851.043},{\"name\":\"Egypt\",\"population\":102334.403},{\"name\":\"Libya\",\"population\":6871.287},{\"name\":\"Morocco\",\"population\":36910.558},{\"name\":\"Sudan\",\"population\":43849.269},{\"name\":\"Tunisia\",\"population\":11818.618},{\"name\":\"Western Sahara\",\"population\":597.33},{\"name\":\"Armenia\",\"population\":2963.234},{\"name\":\"Azerbaijan\",\"population\":10139.175},{\"name\":\"Bahrain\",\"population\":1701.583},{\"name\":\"Cyprus\",\"population\":1207.361},{\"name\":\"Georgia\",\"population\":3989.175},{\"name\":\"Iraq\",\"population\":40222.503},{\"name\":\"Israel\",\"population\":8655.541},{\"name\":\"Jordan\",\"population\":10203.14},{\"name\":\"Kuwait\",\"population\":4270.563},{\"name\":\"Lebanon\",\"population\":6825.442},{\"name\":\"Oman\",\"population\":5106.622},{\"name\":\"Qatar\",\"population\":2881.06},{\"name\":\"Saudi Arabia\",\"population\":34813.867},{\"name\":\"State of Palestine\",\"population\":5101.416},{\"name\":\"Syria\",\"population\":17500.657},{\"name\":\"Turkey\",\"population\":84339.067},{\"name\":\"United Arab Emirates\",\"population\":9890.4},{\"name\":\"Yemen\",\"population\":29825.968},{\"name\":\"Kazakhstan\",\"population\":18776.707},{\"name\":\"Kyrgyzstan\",\"population\":6524.191},{\"name\":\"Tajikistan\",\"population\":9537.642},{\"name\":\"Turkmenistan\",\"population\":6031.187},{\"name\":\"Uzbekistan\",\"population\":33469.199},{\"name\":\"Afghanistan\",\"population\":38928.341},{\"name\":\"Bangladesh\",\"population\":164689.383},{\"name\":\"Bhutan\",\"population\":771.612},{\"name\":\"India\",\"population\":1380004.385},{\"name\":\"Iran\",\"population\":83992.953},{\"name\":\"Maldives\",\"population\":540.542},{\"name\":\"Nepal\",\"population\":29136.808},{\"name\":\"Pakistan\",\"population\":220892.331},{\"name\":\"Sri Lanka\",\"population\":21413.25},{\"name\":\"China\",\"population\":1439323.774},{\"name\":\"China, Hong Kong SAR\",\"population\":7496.988},{\"name\":\"China, Macao SAR\",\"population\":649.342},{\"name\":\"Taiwan*\",\"population\":23816.775},{\"name\":\"Dem. People's Republic of Korea\",\"population\":25778.815},{\"name\":\"Japan\",\"population\":126476.458},{\"name\":\"Mongolia\",\"population\":3278.292},{\"name\":\"Korea, South\",\"population\":51269.183},{\"name\":\"Brunei\",\"population\":437.483},{\"name\":\"Cambodia\",\"population\":16718.971},{\"name\":\"Indonesia\",\"population\":273523.621},{\"name\":\"Laos\",\"population\":7275.556},{\"name\":\"Malaysia\",\"population\":32365.998},{\"name\":\"Myanmar\",\"population\":54409.794},{\"name\":\"Philippines\",\"population\":109581.085},{\"name\":\"Singapore\",\"population\":5850.343},{\"name\":\"Thailand\",\"population\":69799.978},{\"name\":\"Timor-Leste\",\"population\":1318.442},{\"name\":\"Vietnam\",\"population\":97338.583},{\"name\":\"Anguilla\",\"population\":15.002},{\"name\":\"Antigua and Barbuda\",\"population\":97.928},{\"name\":\"Aruba\",\"population\":106.766},{\"name\":\"Bahamas\",\"population\":393.248},{\"name\":\"Barbados\",\"population\":287.371},{\"name\":\"Bonaire, Sint Eustatius and Saba\",\"population\":26.221},{\"name\":\"British Virgin Islands\",\"population\":30.237},{\"name\":\"Cayman Islands\",\"population\":65.72},{\"name\":\"Cuba\",\"population\":11326.616},{\"name\":\"Curaçao\",\"population\":164.1},{\"name\":\"Dominica\",\"population\":71.991},{\"name\":\"Dominican Republic\",\"population\":10847.904},{\"name\":\"Grenada\",\"population\":112.519},{\"name\":\"Guadeloupe\",\"population\":400.127},{\"name\":\"Haiti\",\"population\":11402.533},{\"name\":\"Jamaica\",\"population\":2961.161},{\"name\":\"Martinique\",\"population\":375.265},{\"name\":\"Montserrat\",\"population\":4.999},{\"name\":\"Puerto Rico\",\"population\":2860.84},{\"name\":\"Saint Barthélem\",\"population\":9.885},{\"name\":\"Saint Kitts and Nevis\",\"population\":53.192},{\"name\":\"Saint Lucia\",\"population\":183.629},{\"name\":\"Saint Martin (French part)\",\"population\":38.659},{\"name\":\"Saint Vincent and the Grenadines\",\"population\":110.947},{\"name\":\"Sint Maarten (Dutch part)\",\"population\":42.882},{\"name\":\"Trinidad and Tobago\",\"population\":1399.491},{\"name\":\"Turks and Caicos Islands\",\"population\":38.718},{\"name\":\"United States Virgin Islands\",\"population\":104.423},{\"name\":\"Belize\",\"population\":397.621},{\"name\":\"Costa Rica\",\"population\":5094.114},{\"name\":\"El Salvador\",\"population\":6486.201},{\"name\":\"Guatemala\",\"population\":17915.567},{\"name\":\"Honduras\",\"population\":9904.608},{\"name\":\"Mexico\",\"population\":128932.753},{\"name\":\"Nicaragua\",\"population\":6624.554},{\"name\":\"Panama\",\"population\":4314.768},{\"name\":\"Argentina\",\"population\":45195.777},{\"name\":\"Bolivia\",\"population\":11673.029},{\"name\":\"Brazil\",\"population\":212559.409},{\"name\":\"Chile\",\"population\":19116.209},{\"name\":\"Colombia\",\"population\":50882.884},{\"name\":\"Ecuador\",\"population\":17643.06},{\"name\":\"Falkland Islands (Malvinas)\",\"population\":3.483},{\"name\":\"French Guiana\",\"population\":298.682},{\"name\":\"Guyana\",\"population\":786.559},{\"name\":\"Paraguay\",\"population\":7132.53},{\"name\":\"Peru\",\"population\":32971.846},{\"name\":\"Suriname\",\"population\":586.634},{\"name\":\"Uruguay\",\"population\":3473.727},{\"name\":\"Venezuela\",\"population\":28435.943},{\"name\":\"Australia\",\"population\":25499.881},{\"name\":\"New Zealand\",\"population\":4822.233},{\"name\":\"Fiji\",\"population\":896.444},{\"name\":\"New Caledonia\",\"population\":285.491},{\"name\":\"Papua New Guinea\",\"population\":8947.027},{\"name\":\"Solomon Islands\",\"population\":686.878},{\"name\":\"Vanuatu\",\"population\":307.15},{\"name\":\"Guam\",\"population\":168.783},{\"name\":\"Kiribati\",\"population\":119.446},{\"name\":\"Marshall Islands\",\"population\":59.194},{\"name\":\"Micronesia (Fed. States of)\",\"population\":115.021},{\"name\":\"Nauru\",\"population\":10.834},{\"name\":\"Northern Mariana Islands\",\"population\":57.557},{\"name\":\"Palau\",\"population\":18.092},{\"name\":\"American Samoa\",\"population\":55.197},{\"name\":\"Cook Islands\",\"population\":17.564},{\"name\":\"French Polynesia\",\"population\":280.904},{\"name\":\"Niue\",\"population\":1.618},{\"name\":\"Samoa\",\"population\":198.41},{\"name\":\"Tokelau\",\"population\":1.35},{\"name\":\"Tonga\",\"population\":105.697},{\"name\":\"Tuvalu\",\"population\":11.792},{\"name\":\"Wallis and Futuna Islands\",\"population\":11.246},{\"name\":\"Belarus\",\"population\":9449.321},{\"name\":\"Bulgaria\",\"population\":6948.445},{\"name\":\"Czechia\",\"population\":10708.982},{\"name\":\"Hungary\",\"population\":9660.35},{\"name\":\"Poland\",\"population\":37846.605},{\"name\":\"Moldova\",\"population\":4033.963},{\"name\":\"Romania\",\"population\":19237.682},{\"name\":\"Russia\",\"population\":145934.46},{\"name\":\"Slovakia\",\"population\":5459.643},{\"name\":\"Ukraine\",\"population\":43733.759},{\"name\":\"Channel Islands\",\"population\":173.859},{\"name\":\"Denmark\",\"population\":5792.203},{\"name\":\"Estonia\",\"population\":1326.539},{\"name\":\"Faroe Islands\",\"population\":48.865},{\"name\":\"Finland\",\"population\":5540.718},{\"name\":\"Iceland\",\"population\":341.25},{\"name\":\"Ireland\",\"population\":4937.796},{\"name\":\"Isle of Man\",\"population\":85.032},{\"name\":\"Latvia\",\"population\":1886.202},{\"name\":\"Lithuania\",\"population\":2722.291},{\"name\":\"Norway\",\"population\":5421.242},{\"name\":\"Sweden\",\"population\":10099.27},{\"name\":\"United Kingdom\",\"population\":67886.004},{\"name\":\"Albania\",\"population\":2877.8},{\"name\":\"Andorra\",\"population\":77.265},{\"name\":\"Bosnia and Herzegovina\",\"population\":3280.815},{\"name\":\"Croatia\",\"population\":4105.268},{\"name\":\"Gibraltar\",\"population\":33.691},{\"name\":\"Greece\",\"population\":10423.056},{\"name\":\"Holy See\",\"population\":0.809},{\"name\":\"Italy\",\"population\":60461.828},{\"name\":\"Malta\",\"population\":441.539},{\"name\":\"Montenegro\",\"population\":628.062},{\"name\":\"North Macedonia\",\"population\":2083.38},{\"name\":\"Portugal\",\"population\":10196.707},{\"name\":\"San Marino\",\"population\":33.938},{\"name\":\"Serbia\",\"population\":8737.37},{\"name\":\"Slovenia\",\"population\":2078.932},{\"name\":\"Spain\",\"population\":46754.783},{\"name\":\"Austria\",\"population\":9006.4},{\"name\":\"Belgium\",\"population\":11589.616},{\"name\":\"France\",\"population\":65273.512},{\"name\":\"Germany\",\"population\":83783.945},{\"name\":\"Liechtenstein\",\"population\":38.137},{\"name\":\"Luxembourg\",\"population\":625.976},{\"name\":\"Monaco\",\"population\":39.244},{\"name\":\"Netherlands\",\"population\":17134.873},{\"name\":\"Switzerland\",\"population\":8654.618},{\"name\":\"Bermuda\",\"population\":62.273},{\"name\":\"Canada\",\"population\":37742.157},{\"name\":\"Greenland\",\"population\":56.772},{\"name\":\"Saint Pierre and Miquelon\",\"population\":5.795},{\"name\":\"US\",\"population\":331002.647}]","tot":"json"},{"t":"set","p":"payload","pt":"msg","to":"payload~> | $ | (\t $cur_country := $.country_region;\t $population := $$.countries[name = $cur_country ].population;\t {\t \"population_in_million\" : $population/1000~>$round(2),\t \"new_cases_per_million\" : new_cases_current*1000/$population ~>$round(2),\t \"total_cases_per_million\" : total_conf_cases*1000/$population ~>$round(0)\t } ) |\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":270,"y":340,"wires":[["8637a5d1.94f4c8","b58129cd.f46a38"]]},{"id":"8637a5d1.94f4c8","type":"debug","z":"3739886a.1825b8","name":"population,... added","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":600,"y":340,"wires":[]},{"id":"737a23b5.a584ec","type":"ui_button","z":"3739886a.1825b8","name":"","group":"50d8c10e.e27b4","order":1,"width":0,"height":0,"passthru":false,"label":"retrieve","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":170,"y":20,"wires":[["48cc765f.3e0de8","bd53b32d.740eb"]]},{"id":"e3654952.086598","type":"ui_text","z":"3739886a.1825b8","group":"50d8c10e.e27b4","order":2,"width":0,"height":0,"name":"","label":"","format":"{{msg.payload}}","layout":"row-spread","x":1050,"y":100,"wires":[]},{"id":"bd53b32d.740eb","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"retrieving data ...","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":20,"wires":[["e3654952.086598"]]},{"id":"4ccd263e.f616c8","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"ui_table","pt":"flow","to":"payload","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"\"date \" & last_date_retrieved & \" retrieved\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":620,"y":460,"wires":[["e3654952.086598"]]},{"id":"4a582d99.bd4984","type":"ui_toast","z":"3739886a.1825b8","position":"dialog","displayTime":"20","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"","raw":true,"topic":"","name":"","x":490,"y":700,"wires":[[]]},{"id":"f721729f.198f","type":"ui_button","z":"3739886a.1825b8","name":"","group":"50d8c10e.e27b4","order":4,"width":0,"height":0,"passthru":false,"label":"info","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":80,"y":700,"wires":[["be40b73d.9caab8"]]},{"id":"be40b73d.9caab8","type":"template","z":"3739886a.1825b8","name":"","field":"payload","fieldType":"msg","format":"html","syntax":"plain","template":"<head>\n<style>\ntable {\n font-family: arial, sans-serif;\n border-collapse: collapse;\n width: 100%;\n}\n\ntd, th {\n border: 1px solid #dddddd;\n text-align: left;\n padding: 8px;\n}\n\ntr:nth-child(even) {\n background-color: #dddddd;\n}\n</style>\n</head>\n<body>\n<table>\n <tr>\n <th>column</th>\n <th>field</th>\n <th>description</th>\n </tr>\n <tr>\n <td>1</td>\n <td>location</td>\n <td>country / state</td>\n </tr>\n <tr>\n <td>2</td>\n <td>distance</td>\n <td>distance in km from the country you have selected in the dropdown menu.<br>\n -1 if no country is selected.\n </td>\n </tr>\n <tr>\n <td>3</td>\n <td>cases/day/million</td>\n <td>average number of confirmed cases per day (over the last 7 days) per million persons.<br>\n we have taken an average over the last 7 days to filter out any weekend effects.\n </td>\n </tr>\n <tr>\n <td>4</td>\n <td>RN (Repr. Nr)</td>\n <td>Reproduction number which is defined as <br><i>(conf_cases_last_week / conf_cases_week_before)</i><br>\n where <i>conf_cases_last_week</i> is the total number of new confirmed cases registered last week.\n A week is chosen to filter out any weekend effects.<br>\n A RN of 9999 indicates that previous week no cases were reported while this week at least one case has been reported.\n </td>\n </tr>\n <tr>\n <td>5</td>\n <td>RN (-1 day)</td>\n <td>same as previous column but then calculated for the previous day. \n So tomorrow this column shows the same value you can see today in previous column.\n </td>\n </tr>\n <tr>\n <td>6</td>\n <td>RN (-1week)</td>\n <td>same as 3rd column but then calculated for the previous week. \n So a week later this column will show the same value as you can see today in 3rd column.\n </td>\n </tr>\n <tr>\n <td>7</td>\n <td>RN (-2week)</td>\n <td>same as 3rd column but then calculated for 2 weeks ago. \n So 2 weeks later this column will show the same value as you can see today in 3rd column.\n </td>\n </tr>\n <tr>\n <td>8</td>\n <td>RN (-3week)</td>\n <td>same as 3rd column but then calculated for 3 weeks ago. \n </td>\n </tr>\n <tr>\n <td>9</td>\n <td>tot. cases/million</td>\n <td>total confirmed cases (at date retrieved) per million. So this includes all confirmed cases.\n </td>\n </tr>\n <tr>\n <td>10</td>\n <td>popul. [million]</td>\n <td>total country population expressed in million.\n </td>\n </tr>\n <tr>\n <td>11</td>\n <td>tot. cases</td>\n <td>total reported confirmed cases.\n </td>\n </tr>\n</table>\n</body>\n","output":"str","x":260,"y":700,"wires":[["4a582d99.bd4984"]]},{"id":"b58129cd.f46a38","type":"change","z":"3739886a.1825b8","name":"html with background color","rules":[{"t":"set","p":"payload","pt":"msg","to":"( /* 3 color gradient green (0,255,0) - yellow (255, 255, 0) - red (255,0,0) \t note that class tag is added as a hack to assure that sorting is workign properly\t */\t $rep_nr_with_style_fn := function($rep_nr){ \t \"<div class='J\" & $substring($string($rep_nr/1000),2) &\t \"' style='background-color: rgb(\" &\t ( $rep_nr < 0.6 ? \"0,255,0\"\t : $rep_nr <= 1.0 ? (\t $fraction255 := (($rep_nr - 0.6)*255/0.4)~>$round(0);\t $fraction255 & \",\" & \t ((255 - ( $fraction255*(255-255)/255))~>$round(0)) & \",0\";\t )\t : $rep_nr <= 2.0 ? (\t $fraction255 := (($rep_nr - 1)*255)~>$round(0);\t \"255,\" & (255 - $fraction255) & \",0\";\t )\t : \"255,0,0\") & \t \")'>\" & $rep_nr & \"</div>\" \t };\t payload ~> | $ | {\t \"html_reproduction_number\" : $rep_nr_with_style_fn(reproduction_number),\t \"html_reproduction_number_1day_ago\" : $rep_nr_with_style_fn(reproduction_number_1day_ago),\t \"html_reproduction_number_1week_ago\" : $rep_nr_with_style_fn(reproduction_number_1week_ago), \t \"html_reproduction_number_2week_ago\" : $rep_nr_with_style_fn(reproduction_number_2week_ago),\t \"html_reproduction_number_3week_ago\" : $rep_nr_with_style_fn(reproduction_number_3week_ago)\t } |\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":400,"wires":[["e9092edc.7f8d3","4ccd263e.f616c8","94e3c9a.18b3d38"]]},{"id":"94e3c9a.18b3d38","type":"debug","z":"3739886a.1825b8","name":"with background color","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":600,"y":400,"wires":[]},{"id":"631625f5.c0ae7c","type":"ui_dropdown","z":"3739886a.1825b8","name":"","label":"country","tooltip":"","place":"Select option","group":"50d8c10e.e27b4","order":3,"width":0,"height":0,"passthru":false,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":580,"y":280,"wires":[["b82fe468.2f9e68","e3654952.086598"]]},{"id":"b82fe468.2f9e68","type":"change","z":"3739886a.1825b8","name":"","rules":[{"t":"set","p":"country","pt":"flow","to":"{\t \"name\" : payload,\t \"latitude\" : $flowContext(\"ui_table\")[country_region = $$.payload].latitude,\t \"longitude\" : $flowContext(\"ui_table\")[country_region = $$.payload].longitude\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":280,"wires":[["188cf083.a11a5f"]]},{"id":"188cf083.a11a5f","type":"change","z":"3739886a.1825b8","name":"msg.payload = flow.ui_table","rules":[{"t":"set","p":"payload","pt":"msg","to":"ui_table","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":940,"y":340,"wires":[["f4d0f6f8.2e6178"]]},{"id":"f4d0f6f8.2e6178","type":"split","z":"3739886a.1825b8","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":890,"y":400,"wires":[["eb8e08a0.6168a8"]]},{"id":"eb8e08a0.6168a8","type":"function","z":"3739886a.1825b8","name":"calculate distance","func":"var lat1 = flow.get(\"country\").latitude;\nvar lon1 = flow.get(\"country\").longitude;\nvar lat2 = msg.payload.latitude;\nvar lon2 = msg.payload.longitude;\n\n/* calculate distance as specified in \n https://www.movable-type.co.uk/scripts/latlong.html */\n \nconst R = 6371e3; // metres\nconst φ1 = lat1 * Math.PI/180; // φ, λ in radians\nconst φ2 = lat2 * Math.PI/180;\nconst Δφ = (lat2-lat1) * Math.PI/180;\nconst Δλ = (lon2-lon1) * Math.PI/180;\n\nconst a = Math.sin(Δφ/2) * Math.sin(Δφ/2) +\n Math.cos(φ1) * Math.cos(φ2) *\n Math.sin(Δλ/2) * Math.sin(Δλ/2);\nconst c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));\n\nconst d = R * c; // in metres\n\nmsg.payload.distance = Math.round(d / 1000) ;\n\nreturn msg;","outputs":1,"noerr":0,"x":930,"y":440,"wires":[["e9fe4c33.8355","61e0af74.21009"]]},{"id":"e9fe4c33.8355","type":"join","z":"3739886a.1825b8","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":890,"y":480,"wires":[["e9092edc.7f8d3"]]},{"id":"61e0af74.21009","type":"debug","z":"3739886a.1825b8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1150,"y":440,"wires":[]}]
Here below a chart where "United Kingdom" is selected and we have sorted according increasing distance:
