In r-gris/dplyrodbc:

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "README-", 
  fig.height = 9,
  fig.width = 9
)
library(raster)

dplyrodbc

Here we illustrate connecting to a Manifold project via ODBC. First with direct RODBC connection and conversion to Spatial types, then via dplyr wrapping for the SQL backend.

Explanation for Manifold people

R makes this all a lot easier from my perspective. I'm writing wrappers for the dplyr package so it can use Manifold as a "SQL backend".

In practical terms

library(dplyr)  ## extension code
map <- connectManifold("C:/path/to/project.map")
## connect table which drives Manifold SQL
table <- tbl(map, "Drawing Table")

## issue some dplyr-verbs that are converted to Manifold SQL and evaluated natively
table %>% group_by(FactorColumn) %>% summarize(count = n(), avgTemp = mean(Temp))

There is no limit to the standard-SQL that can be carried out through this wrapping, and we can also issue actual Manifold-SQL commands so we have all the spatial goodness at our R fingertips. It's the best of both worlds for R-users and Manifold lifers IMO.

I've made a lot of progress (and most of the credit belongs to the dplyr developers). I'm hoping it will provide a straightforward basis to finally bring to maturity this R/Manifold connection.

Minimal test for RODBC

This code shows the tradtional way to read data from a Manifold project using RODBC.

library(RODBC)
library(dplyrodbc)
mapfile <- system.file("extdata", "Countries.map", package = "dplyrodbc")
con <- odbcConnectManifold(mapfile)
tab <- "Countries"
cnames <- names(sqlQuery(con, sprintf("SELECT * FROM [%s] WHERE 0 = 1", tab)))
## Drop all Intrinsic columns (we cast one to GeomWKB)
cnames <- cnames[-grep("\\(I\\)", cnames)]
## some columns explode the app, presumably "Geom (I)" because of the size of the binary blob
dat <- sqlQuery(con, sprintf("SELECT CoordSysToWKT(CCoordSys([Geom (I)])) AS [CoordSys], CGeomWKB([Geom (I)]) as [GeomWKB], %s FROM [%s]", 
                           paste(dplyrodbc:::sql_surroundquote(cnames, "[", "]"), collapse = ", "), tab), stringsAsFactors = FALSE)

close(con)

The data.frame x has all the attributes, the Coordinate System (in OGC WKT format), and the Geometry as a binary blob (in OGC WKB format).

We don't want to look at the binary blob, so convert it to sp Spatial Geometry and discard. We can't deal with the WKT without awful kludges, so we skip it for now.

library(rgdal)
geom <- try(wkb::readWKB(dat$GeomWKB))
if (!inherits(geom, "try-error")) {
  dat$GeomWKB <- NULL

  ## save full handling for another day (mixed CRS?)
  wkt <- unique(dat$CoordSys)
  #proj4 <- dplyrodbc:::prj_to_epsg(wkt[1])
  ## after rgdal 1.1-4.zip
  proj4 <- rgdal::showP4(wkt)
  dat$CoordSys <- NULL
  proj4string(geom) <-  proj4
  dwg <- sp::SpatialPolygonsDataFrame(geom, dat)
  rm(dat, geom, proj4)
}

plot(dwg)
text(coordinates(dwg), lab = dwg$FIPS, cex = 0.6)

library(raster)
print(dwg)

Connecting to the ODBC SQL engine via dplyr

We need some fixes to RODBCDBI.

##devtools::install_github("mdsumner/RODBCDBI",   ref = "mike")
library(RODBCDBI)

Methods for our specific classes extending DBI for ODBC.

library(dplyrodbc)  ## move this to manifoldr when it's working
library(dplyr)

And Manifold.

## not clear how to do this yet
## we need cases for odbcConnectWhatever
#' @export
setMethod(
  "dbConnect", 
  "ODBCDriver",
  function(drv, dsn, user = NULL, password = NULL, manifold = FALSE, ...){
    uid <- if(is.null(user)) "" else user
    pwd <- if(is.null(password)) "" else password
    if (manifold) {
      connection <- odbcConnectManifold(dsn) 
    } else {
      connection <- odbcConnect(dsn, uid, pwd, ...)
    }
    new("ODBCConnection", odbc=connection)
  }
)

## gah, got to get the namespace figured out
fs <- list.files("R", pattern = ".r$", full.names = TRUE)
for (i in fs) source(i)


manifold <-src_manifold(mapfile)
## woah, not cool  (doesn't happen now?)
# ct <- tbl(manifold, "Countries Table")
#  Show Traceback
#  
#  Rerun with Debug
#  Error in odbcQuery(channel, query, rows_at_time) : 
#   'Calloc' could not allocate memory (2147483648 of 1 bytes) 


ct <- tbl(manifold, "Countries Table")


## group by number of Branches, make a count and summarize the Area
ct %>% group_by(`Branches (I)`) %>% summarize(n = n(), totalarea = sum(`Area (I)`))


## that doesn't make sense for a Longitude/Latitude drawing so reproject the Geom first
#WKTToCoordSys(wkt)
#Project(Geom, cs)

## not sure how to escape WKT CRS
#ct %>% mutate(target = wkt)


#a <- collect(ct)

# a <- collect(ct %>% group_by(`Branches (I)`) %>% select(`Longitude (I)`, `Latitude (I)` ))
# Error in .valueClassTest(ans, "data.frame", "fetch") : 
#   invalid value from generic function ‘fetch’, class “character”, expected “data.frame”
#