Writing A Covid-19 Visualization App in Ruby on Rails 6, part 1

I couldn’t find the website I wanted so I made one! Here’s a brief overview of what I did to create https://covid.philsmy.com

I was born and raised in Ontario, Canada but now live in Japan. When this pandemic started I wanted to see what was happening back home, but everything I found was simply at the provincial level. I did some poking around and found that Ontario was sharing their Covid-19 data. So I put to the opportunity to do a little more Rails 6 development.

The Data

In this example, I will be getting the data from the official Ontario government data source, found here. The data is available in CSV and GeoJSON. For my purposes, I will be using the CSV version.

The Requirements

I’ll be using Rails 6 and Ruby 2.6.6, so let’s quickly get those installed. I use rbenv on my Mac running Catalina. There are many ways to do this, but basically do something like this if you need to get that install.

rbenv install 2.6.6
ruby -v
gem install rails -v 6.0.2.2

Create The App

Let’s create a new Rails app with our version

rails _6.0.2.2_ new covid-ontario -d postgresql

Create a Model

I want to store all the patient data that I get from the government CSV file, so the first thing I did was create a model for that. I also knew that I didn’t want to show an individual record or delete or edit from the user interface. So I won’t create a controller or views for now.

I created an attribute for the columns in the data, plus I added a country and region column, just in case.

rails g model patient_data external_id:integer episode_date:date age_group:string gender:string acquisition_info:string outcome:string reporter:string reporter_address:string reporter_city:string reporter_postal_code:string reporter_website:string reporter_latitude:float reporter_longitude:float country:string region:string

This will create a patient_datum model and a patient_data table.

The Ontario data comes with an id column that will always represent that case. That is what I will put into the external_id value. But also because I am using PostgreSQL, we can create an index that will later be used in an ‘insert or update’ call.

rails g migration addExternalIdIndexToPatientDatum

Then edit the resulting file to look something like this:

class AddExternalIdIndexToPatientDatum < ActiveRecord::Migration[6.0]
  def change
    add_index :patient_data, [:external_id, :country ,:region], unique: true
  end
end

Store The Data

I’m not so patient (haha!) so let’s get right down to storing this data in our database.

Create the database by running:

rails db:setup

Create a data directory inside your rails app root directory

mkdir data

Pull down the data using wget

wget https://data.ontario.ca/dataset/f4112442-bdc8-45d2-be3c-12efae72fb27/resource/455fd63b-603d-4608-8216-7d8647f43350/download/conposcovidloc.csv

Edit the patient_datum.rb file and add a method that will read that CSV file

Line 8 is the columns we’re getting from the CSV. On line 9 are the columns we will ‘upset’ if we hit a match on the external_id, country, and region index.

I iterate over each record and add the Country and Region to the line of data.

The finally on line 16 I insert the data and tell PostgreSQL to upsert if there is a hit.

Run that and you should end up with all the patient data in your database! That’s the hard part…until we want to display it.

I’ll get down to the displaying of the data in Part 2, and then in Part 3 I’ll look at how I added the Google Mobility Data to show what people were up to!