Jerome Lefebvre

UFL Connector example, Covid-19 data

Blog Post created by Jerome Lefebvre on Mar 10, 2020

Note: The format of the data source that I am using for the INI file below changes quite a bit day to day. Either names of countries or relationships between countries and regions. Thus the INI file no longer works after the 10th of this month... if anybody wants to update it, please post a newer version below in the comments.


As with many I assume, I anxiously look at how Covid-19 is spreading around the world.

Other than limiting my person risk, there is little I can do that is constructive. But, as a guy with PI, I can at least play with numbers as a way to deal with that anxiety. 


I first want to write up how to get some data into PI as there might be some UFL tricks to be learned by doing so.

The data I am using is the data underlying this dashboard: Operations Dashboard for ArcGIS  and can get be found on GitHub GitHub - CSSEGISandData/COVID-19: Novel Coronavirus (COVID-19) Cases, provided by JHU CSSE 


The main thing I want to be able to visualize is when will the spread will slow down and not focus on the sadder aspects of this. Thus, I will only be looking at confirmed cases, which we can grab from this file:… 


The location data

Bringing up the data in Excel for a closer look, we can see that the first two columns gives various granularity to the areas affect. Either City -> State -> Country as in the US, or Provinces -> Country for China or Simply country as in the case of South Korea. For countries we also see that there are two cases, either the province is listed with the country name as in Taiwan, or nothing is listed in the province as in Japan. UFL can certainly handle that with a few IF statements.


The CSV data itself is: 

Zhejiang,Mainland China
,South Korea
"King County, WA",US

Thus, we need to be careful about using commas to delight fields. I won't go over all details but, the interesting steps are

1. How to detect the line starts with a double quote. To do so, I use the following IF statements:

If (["\"(*)"] is Null) then

In other words, if grabbing the entry that starts with a double quote returns null, then I am not starting with a double quote.

2. Once I deal with the somewhat messy two first columns, I don't want to keep being reminded that there might be an extra comma to avoid, thus I re-assign the __MESSAGE variable

When I am not dealing with an extra comma:

__MESSAGE = ["*,*,(*)"]

And when I have one:

__MESSAGE = ["\"*\",*,(*)"]


Reassigning __MESSAGE means that the Latitude and Longitude columns are very easy to get:

NumValue = ["(*),*"]
StaticAttributes = Add("Latitude", NumValue)
NumValue = ["*,(*),*"]
StaticAttributes = Add("Longitude", NumValue)

I'm using a Number field to cast the values to a Float.


Timestamps in the header

Looking in the image above we see that the timestamps are in the header. The UFL StoreEvents as the following definition: 

StoreEvents( TagNames, ElementAttributes, TimeStamp(s), Values[, Statuses, Questionables] )

While you can submit either one timestamp for all values or one timestamp per value. You can not submit only one Tag for all Values. Which means, we will have to add the same tag name for each column in a row that we have:

FOREACH (CsvGetItem(["*,*,(*)"], ",")) DO
  IntValue = __ITEM
  Values = Add(IntValue)
  TagNames = Add(TagName)
  AttributeNames = Add("Confirmed")


This allows us to store all values contain in a single row with one call to StoreEvents.


With UFL's ability to create an AF hierarchy, we can get the following structure:

And with countries with more layers, we can have the following:


With just this structure and no additional work, we can get going and visualize things in Esri using our integrator. And having the historical data into PI, we can go back in time and see how things have changed.



Doing a bit of AF work, we can how the disease is growing in individual regions once it gets a foothold in the country itself.