Exercises for Chapter 11

In these exercises, we use again the database spatialdata with the two datasets we have used in the chapter. You need to have the PostGIS extension enabled for this database, as explained in the chapter. You can read these datasets and create the corresponding tables using the code from the chapter:

library(RPostgres)
library(tidyverse)
library(sf)

db <- dbConnect(Postgres(),
  dbname = "spatialdata",
  user = "postgres",
  password = "pgpasswd"
)

# dbExecute(db, "DROP EXTENSION postgis")
dbExecute(db, "CREATE EXTENSION postgis")

events <- read.csv(file.path("ch11", "ged.csv"))
dbWriteTable(db, "events", events)
dbExecute(db, "ALTER TABLE events ADD COLUMN geom geometry(point, 4326)")
dbExecute(db, "UPDATE events SET geom = st_setSRID(st_point(longitude, latitude), 4326)")
events <- st_as_sf(events, coords = c("longitude", "latitude"), crs = 4326)

municipalities <- st_read(file.path("ch11", "bosnia.shp"), crs = 4326) %>%
  rename(geom = geometry)
st_write(municipalities, dsn = db, layer = "municipalities")

Exercise 1: Retrieving the Location of the First Event in R/sf

Where did violence start in Bosnia? In this exercise, we use our two datasets to determine in which municipality the first event recorded by the UCDP Geo-referenced Event Dataset occurred. How can you use the joined dataset we created in the chapter for this purpose?

Exercise 2: Retrieving the Location of the First Event in PostGIS

This exercise repeats the task from the previous one, but using PostGIS. Can you solve the task with a single SQL statement?

Exercise 3: Distance Calculation in R/sf

In this exercise, we calculate distances between municipalities. Distances are often used in spatial analysis to model the declining influence of one unit over another - for example, it is typically assumed that state strength declines the further we get away from a country’s capital. To incorporate this into our analysis, we need to amend our municipalities data frame with a new column that contains the distances from the capital of Bosnia, Sarajevo. The sf package has a useful function for this purpose: st_distance(). Using the package documentation, familiarize yourself with this function. What happens if you simply apply this function to the municipalities dataset? To compute the distance from Sarajevo, we use the “Centar” district in our dataset, which is the center district of Sarajevo. From the output of the function call, how can you extract a column with the distances from this district, and append it to the `municipalities’ dataset?

Exercise 4: Distance Calculation in PostGIS

Here, we repeat the distance calculation from Exercise 3 in PostGIS. First, add a new numeric field to the municipalities table in the database. Next, take a look at PostGIS’s distance calculation functions. The standard one, st_distance(), calculates distances in the coordinate system of the respective dataset. Since we have spherical coordinates (latitude, longitude), this would give us incorrect results, which is why we use the st_distanceSphere() function. Similar to its sf counterpart, the function takes as arguments two datasets and computes distances between each pair of features from them. Since we only need distances from Sarajevo, we first need to create a temporary dataset with only one entry: the “Centar” district. For this, it is convenient to use a subquery. In PostgreSQL, you can define these subqueries with the WITH keyword. Can you figure out how to do this from the PostgreSQL documentation? Your WITH query should be a simple SELECT statement that extracts the “Centar” municipality, and it should be used in an UPDATE statement that fills the new column with the distance between each municipality and the “Centar” municipality.