silicate_MonetDB.R

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

#install.packages("MonetDBLite")
library(DBI)
dbdir <- "~/db"
con <- dbConnect(MonetDBLite::MonetDBLite(), dbdir)


library(sf)

## Linking to GEOS 3.6.2, GDAL 2.2.3, proj.4 4.9.3

nc <- read_sf(system.file("shape/nc.shp", package="sf"))
library(silicate)
## load up the database
x <- PATH(nc)
l <- purrr::map(names(x), ~copy_to(con, x[[.x]], name = .x))

## Identifier(s) "AREA", "PERIMETER", "CNTY_", "CNTY_ID", "NAME", "FIPS", "FIPSNO", "CRESS_ID", "BIR74", "SID74", "NWBIR74", "BIR79", "SID79", "NWBIR79" contain uppercase or reserved SQL characters and need(s) to be quoted in queries.

names(l) <- names(x)
## this is a filter, then Reduce on semi_join throughout the DB
## run a cascaded query on the objects table and collect the data
newd <- silicate:::semi_cascade0(l, PERIMETER > 2, tables = names(l)) %>% 
  purrr::map(collect, n = Inf)

## Joining, by = "object_"

## Joining, by = "path_"

## Joining, by = "vertex_"

## Warning: Translator is missing window variants of the following aggregate functions:
## * median

## Warning: Translator is missing window variants of the following aggregate functions:
## * median

## Warning: Translator is missing window variants of the following aggregate functions:
## * median

## Warning: Translator is missing window variants of the following aggregate functions:
## * median

## Warning: Translator is missing window variants of the following aggregate functions:
## * median

## Warning: Translator is missing window variants of the following aggregate functions:
## * median

## Warning: Translator is missing window variants of the following aggregate functions:
## * median

## Warning: Translator is missing window variants of the following aggregate functions:
## * median

## rebadge as silicate
class(newd) <- class(x)
plot(newd)