Introduction | Adding or updating content | Table description | Exporting data
The OilSandsDatabase is an online application written mainly in PHP and some JavaScript including jQuery. Its purpose is to collect all available sources reporting and prognosing Oil Sands Production in Canada in one place, making it easier to compare and evaluate each data set.
In case you are viewing this document embedded in the website, the whole code can be found at Github.
This code has been built by a self-learned programmer. The code seldomly follows good standards and there are some severe design-flaws regarding flexibility. The code resembles a learning process. If rebuilt from scratch, OilSandsDatabase would look different. If you intend on contributing or changing things, let me know! I'll be happy to rebuild any part that needs more transparency.
Good tools to use are (in order of importance)
Generally, the steps to add a new source of data to the database are as follows:
- Find a table or graph that you want to include in the database.
- On the webbpage, go to Sources->Add Source.
- Table or graph?
If it's a table: Copy-and-paste the table into the textfield. If it's a graph: Convert the graph to data using WebPlotDigitizer and add the data into the textfield.
- Open a multiple tab opener
- Add the links from the upper part of this file
- Click on submit while pressing the ctrl-key
- Now a large collection of excel-files should be downloaded to your computer. The tabs that are still open will probably show errors. In this case the excel file could not be downloaded. Check manually for these files.
- Convert all files into csv-format using a converter.
- Merge these files into one single file by opening your console, navigating to your folder using
cd "path\to\folder", and then enteringcopy *.csv collected.txt - Make sure all projects mentioned at the end of this file are included
- Due to the high risk of lines appearing twice during the process, remove all duplicate lines
- Copy and paste
- Open OilSandsReview
- Create 14-days-trial account if needed
- Open the statistics page
- Choose Production by Project
- Download all pages as
htmlinto one folder - Open your console, navigate to the folder using
cd "path\to\folder", and merge all files into one usingcopy *.html collected.txt - When editing the source, use the button htmlToCsv to easily convert to a csv-table.
- Be careful: The months have other units than the years! Create an extra column called Unit and manually add
Thousand Cubic metres per month(if applicable) where monthly values are given. Where units are omitted, the standard unit of the source (in this case Thousand Cubic metres per year) is assumed.
The tables behind the scenes are stored in a MySQL database. Within the database, all tables are prepended by a osdb_, so that the table Sources actually is called osdb_sources.
The tables within the database are:
- buttons
- compilations
- data
- errors
- errors_to_calculate
- gallery
- logs
- projects
- ranking
- sources
- synonyms
- tags
- working
This is the most basic table. Here all values that are important for a certain Source are entered as well as the data itself.
The data can be in any shape and will initially be stored as a text. By using the buttons, the data should be shaped into a format that better suits the insertion into the database.
This table contains the code to all the buttons that appear on the Add sources to database page
Contains the name for every compilation of data
Good tools to analyze the data more are (in order of importance)
- R with RStudio
- Packages ggplot2
- TableauPublic
Comma-seperated-values (csv) files of collections of time series can be created on the page Compilations and Ranking. On Compilations the time series can be composed individually, whereas Ranking provides links to prepared collections of time series.
Using the links to create csv files will automatically download the file to your local pc or prompting you to do so. When done downloading, copy all csv files into a seperate folder of your choice.
Download the plotter file for R, place it in the same folders as the csv files and start your local R installation or RStudio.
In R, change the working directory to the folder where you placed the csv files using the command setwd("path/to/directory"). Use citations and your own path, of course.
Now you can "convert" every csv file into plotted graphs by using the single command source("plotter.r"), which will execute the file plotter.r.
- Loads and/or installs the packages needed (make sure you have an internet connection the first time you run the script).
- Looks for csv files in the same directory.
- Creates a folder "figures" (if csv files exist)
- For every csv file, the script creates several plots with varying ranges of time