When I wrangled the Current Reservoir Levels data set, I left all the values in one column and I didn’t feel comfortable with it. I realize after playing around a little more that while it is possible to subset the column and select only the data I want, it’s less than ideal to do this constantly....
Now that I’ve familiarized myself with the R ecosystem, including RStudio, blogdown, querying databases, and basic data wrangling and visualization techniques, I’d like to start with some work of substance. I want to start with work that is small in scope to pace myself. One of the factors that discourages people from tech work is the disconnect between the headlines and the reality of what needs to be done once work starts....
After successfully querying my pi-gres database with SQL commands, retrieving data, and saving it locally, it’s time to make the data presentable. This is easier said than done: while it’s possible to scrape something together that works well enough, I have several other considerations: Portability and reusability: as much fun as I have taking deep dives into the documentation (and I only say that half sarcastically), I want to write code that I can use in other projects without substantially rebuilding it every time....
Now I have successfully connected to my pi-gres database, but I still haven’t developed a smooth way of querying the database directly. The database connection is created as an object in memory, and something about this object does not play well when R Markdown documents (.Rmd extension) are processed by blogdown. R Markdown and packages like blogdown are designed to make R code more portable and presentable. It makes sense that formats designed for sharing would not work seamlessly with a complex entity like a database connection, since that’s beyond their scope....
Fortunately, the basic settings we input when installing postgres worked without issue in pgAdmin4. Troubleshooting configuration files, sockets, ports, identity files, and the other minutiae of modern network security is a very easy way to burn a lot of time. Connecting to pgAdmin4 ssh into the Pi from a terminal and ensure that PermitTunnel = yes in the file /etc/ssh/sshd_config Install and open pgAdmin4, then create a server connection: The server name is a label for inside pgAdmin4, so we can set it to just about anything....