pg_sf_rmd.Rmd

---
title: "PostGIS+sf+Rmarkdown"
output: html_document
---

Use `bash` chunk to execute commands

```{bash message=FALSE, eval=FALSE}
createdb test123 -U postgres
psql -d test123 -U postgres -c "CREATE EXTENSION postgis;"
```

Connect to the database with R, and write some data:

```{r message=FALSE}
library(sf)
library(DBI)
library(RPostgres)
library(mapview)
library(sp)

data(meuse)
pts <- st_as_sf(meuse, coords = c("x", "y"), crs = 28992)

pg <- DBI::dbConnect(RPostgres::Postgres(), host = "localhost", 
                     dbname = "test123", user = "postgres")

st_write(pts, pg, "sf_meuse__", overwrite = TRUE)
```

Write SQL directly in the Rmarkdown document in a `SQL` chunk
(with synatx highlighting):

```{sql connection=pg}
SELECT 
    landuse, 
    cadmium, 
    ST_X(ST_Transform(geometry, 4326)) longitude,
    ST_Y(ST_Transform(geometry, 4326)) latitude 
FROM 
    sf_meuse__;
```

Create a table in the database:

```{sql connection=pg}
DROP TABLE IF EXISTS tmp;
```

```{sql connection=pg}
CREATE TABLE tmp AS
SELECT 
    landuse, 
    cadmium, 
    ST_Buffer(geometry, 100) geometry 
FROM 
    sf_meuse__;
```

Use R to read the table from the database and plot
```{r message=FALSE, warning=FALSE}
st_read(pg, "tmp") %>% 
  mapview()
```

Or write your query directly in the `query` parameter of `st_read`:

```{r}
st_read(pg, 
        query = "SELECT landuse, cadmium, st_buffer(geometry, 100) geometry 
          FROM sf_meuse__;")
```