postgresql_tutorial1

Basic introduction to PostgreSQL

• Introduction
• Install PostgreSQL
• Postgres and R
• Install and load R libraries

Introduction

This repository provides an intro to PostgreSQL (postgres) database (db).

Install Postgres

• PostgreSQL
• PostgreSQL Download
• PostgreSQL Documentation
• PostGIS

Database connection parameters

The connection details are as follows:

• IP: IP
• Port: 5432
• Data base: georodco_covid19
• user name: username
• password: password

Using the above parameters you can connect to the db using your prefered choice of statitical program (R, Stata, Excel, etc.)

Postgres and R

R is an open-source computational and statistical program. You can download it from here: R-project

• Using R with data from a Postgres db

    # Install libraries (only once)
    install.packages("DBI") # Generic database connector
    #install.packages("RPostgreSQL")
    #install.packages("rpostgis") # optional
  
    # Load library
    library(DBI)
    library(RPostgreSQL)
    library(RPostgis) # optional
  
  
    # create conection to the db
    con <- DBI::dbConnect(drv = "PostgreSQL", user='user', password='password', host='IP', port=5432, dbname='georodco_covid19')
  
    # ask db for table ecu_covid19
    res <- dbSendQuery(con, "SELECT * FROM ecu_covid19")
    cv19 <- dbFetch(res)
  
    # check number of rows and columns (25x15)
    dim(cv19)
  
    # check out first (6) rows in the table
    head(cv19)
  
    # Working with spatial data found in Postgres
    #Method 1: use package sf
    library(sf)
    prov <- st_read(con, layer="provinces")
    library(ggplot2)
    ggplot(prov) +  geom_sf() + theme_bw()
  
    # delete connection object
    dbClearResult(res)
  
    # disconnect from the db
    dbDisconnect(con)
  

Postgres for data management

Relational dbs are flexible, extensible, and scalable. They are very useful for managing data in colloborative projects.