README.Rmd

---
output: github_document
---

<!-- README.md is generated from README.Rmd. Please edit that file -->

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)
```

# sfdbi <img src='man/figures/logo.svg' align="right" height="139" />
<!-- badges: start -->
[![Codecov test coverage](https://codecov.io/gh/r-spatial/sfdbi/branch/master/graph/badge.svg)](https://codecov.io/gh/r-spatial/sfdbi?branch=master)
[![Lifecycle: experimental](https://img.shields.io/badge/lifecycle-experimental-orange.svg)](https://www.tidyverse.org/lifecycle/#experimental)
<!-- badges: end -->

## Overview

sfdbi helps you manipulate spatial data stored in a database as if it was
in-memory with standard sf and [dplyr](https://dplyr.tidyverse.org/) functions.
With sfdbi, you can reads and write sf objects to databases and translate sf
operations to `SQL`. We support [`postgis`](https://postgis.net/), but feel free
to open an issue for other backends and show you interest.

## Installation

You can install the development version from [GitHub](https://github.com/) with:

``` r
# install.packages("devtools")
devtools::install_github("r-spatial/sfdbi")
```

sfdbi is not available on CRAN yet.

## Example

This is how you write and read spatial data to a database. Note that sfdbi works best with dplyr.

```{r example}
library(sfdbi)
library(sf)
library(dplyr)

con <- DBI::dbConnect(
  RPostgres::Postgres(), 
  host = "localhost", 
  port = 25432
) %>%
  postgis()

# Create a spatial table
pyramids <- tribble(  
  ~what, ~geom,
  "Giza",        "POINT(31.1342 29.9792)",
  "Khafre",      "POINT(31.130833 29.976111)", 
  "Menkaure",    "POINT(31.128333 29.9725)",
  "Khentkaus I", "POINT(31.135608 29.973406)", 
  "Sphynx",      "POINT(31.137778 29.975278)",
  ) %>% 
  mutate(
    geom = st_as_sfc(geom, crs = 4326)
  )

# Copy spatial data to database
x <- copy_to(con, pyramids)

# Queue operations to the database
y <- x %>% 
  mutate(
    geom = geom %>% 
      st_transform(23035L) %>% 
      st_buffer(10)
  )

# View the query to be executed
y %>% show_query()

# Execute operation in the database and load it in R memory
collect(y)
```