Stardog sandbox covid queries

Tips and Tricks
1

I've been playing around with some queries and data in the stardog sandbox that use some of the user defined functions that I put together and I thought I'd share.

I like working with the CLI and wanted to be able to quickly generate some sort of bar chart. The following is a query a put together based on the query from the sandbox calculating the daily change in covid19 cases in nyc (covid19nyc db).

The query prints an ascii bar chart that colors the bar red if the % change is over 1.5 and green if it's lower. It also includes an emoji smile if it's below and a frown if above.

I've already found a couple of bugs and a few small changes I'd like to make to the functions. I need just one or two more and I think I should be able to easily rotate the chart horizontally instead of vertically. I also think with a little extra logic in the query I could use semi blocks to make the chart smoother.

Query:

prefix string: <http://semantalytics.com/2017/09/ns/stardog/kibble/string/>
prefix emoji: <http://semantalytics.com/2017/09/ns/stardog/kibble/string/emoji/>
prefix console: <http://semantalytics.com/2017/09/ns/stardog/kibble/console/>

select
    ?dailyIncrease
    ?percentageIncrease
    ( if(?percentageIncrease > 1.5, emoji:emojify(":("), emoji:emojify(":)" )) as ?emoji)
    ( string:rightPad(concat(IF(?percentageIncrease > 1.5, console:foregroundRed(), console:foregroundGreen()), string:repeat('\u2589', xsd:integer(roundHalfToEven((?dailyIncrease / ?maxDailyIncrease) * 60))), console:reset()), 70) as ?n)
{
{
    SELECT (MAX(?dailyIncrease) AS ?maxDailyIncrease)
    {
        {
            SELECT ?day (sum(?cases) as ?totalCases) {
                ?report :date ?day ;
                        :cases ?cases
            }
            GROUP BY ?day
        }
        {
            SELECT ?previousDay (sum(?cases) as ?previousTotalCases) {
                ?report :date ?previousDay ;
                        :cases ?cases 
            }
            GROUP BY ?previousDay
        }
        BIND((?totalCases-?previousTotalCases) AS ?dailyIncrease)
        BIND(roundHalfToEven((?dailyIncrease / ?previousTotalCases) * 100, 2) AS ?percentageIncrease)
        FILTER(?day - ?previousDay = "P1D"^^xsd:dayTimeDuration)
    }
}

{
    SELECT ?day ?totalCases ?dailyIncrease ?percentageIncrease 
    {
        {
            SELECT ?day (sum(?cases) as ?totalCases) {
                ?report :date ?day ;
                        :cases ?cases
            }
            GROUP BY ?day
        }
        {
            SELECT ?previousDay (sum(?cases) as ?previousTotalCases) {
                ?report :date ?previousDay ;
                        :cases ?cases 
            }
            GROUP BY ?previousDay
        }
        BIND((?totalCases-?previousTotalCases) AS ?dailyIncrease)
        BIND(roundHalfToEven((?dailyIncrease / ?previousTotalCases) * 100, 2) AS ?percentageIncrease)
        FILTER(?day - ?previousDay = "P1D"^^xsd:dayTimeDuration)
    }
    ORDER BY desc(?day)
}

}

Result:

stardog-covid-query
stardog-covid-query826×2793 360 KB

2

Wow, what a creative use of emoji! I also learned something about using IF statements that I hadn't considered before. Thanks!

From a visual-encoding point of view, you might want to use emoji that have different colors and shapes. It's hard to tell the "good yellow circle face" from the "bad yellow circle face".

3

Thanks. I agree, I'm not sure those particular emojis are a good choice. They show the same information as the colors. They're more there to show what I imagined using emoji functions for. I'm positive there are some clever uses for them. I also learned lately that the different colors you can apply are called Fitzpatrick codes and are based on the Fitzpatrick skin tone scale.

I was going to add some ascii diagramming stuff like box plots.

If you're interested in playing with any of them you can get the jar at GitHub - semantalytics/stardog-extensions: Single repository for all Stardog function extensions . I used to have several repos for this stuff but it was too much to keep track of so I pulled it all into a single repo.