notes (git problem workaround)

[mdsumner]

library(odbc)
library(DBI)
filepath <- "C:\\data\\manifold\\project1.map"
## http://www.georeference.org/forum/t153736.3
## too long wtf
# ;Unicode=True;Ansi=False;OpenGIS=True;DSN=Default
#dstring <- sprintf("DRIVER={Manifold 9.0 Project Driver (*.map)};DBQ=%s;", filepath)

con <- dbConnect(odbc(), "project1", dbms.name = "ManifoldRadian")
dbListTables(con)
dbReadTable(con, "DrawingTable")

## failing now (why?)
#s <- dbSendQuery(con, "SELECT * FROM Drawing Table")
#dbFetch(s)
# mfd_id        Geom Field
# 1      1 blob[140 B]   abc

## ALL of these work
dbGetQuery(con, "SELECT * FROM DrawingTable")
dbGetQuery(con, "SELECT * FROM [DrawingTable]")
dbGetQuery(con, "SELECT * FROM DrawingTable;")
dbGetQuery(con, "SELECT * FROM [DrawingTable];")
dbGetQuery(con, "SELECT * FROM \"DrawingTable\"")

dbGetQuery(con, " SELECT * FROM (SELECT * FROM DrawingTable) FETCH 4")

library(dplyr)
sql_select.ManifoldRadian <-  
  function (con, select, from, where = NULL, group_by = NULL, having = NULL, 
            order_by = NULL, limit = NULL, distinct = FALSE, ...) 
  {
    out <- vector("list", 7)
    names(out) <- c("select", "from", "where", 
                    "group_by", "having", "order_by", "limit")
    out$select <- dbplyr::build_sql("SELECT ", if (distinct) 
      sql("DISTINCT "),  dbplyr::escape(select, collapse = ", ", con = con),
      con = con)

    out$from <- dbplyr:::sql_clause_from(from, con)
    out$where <- dbplyr:::sql_clause_where(where, con)
    out$group_by <- dbplyr:::sql_clause_group_by(group_by, con)
    out$having <- dbplyr:::sql_clause_having(having, con)
    out$order_by <- dbplyr:::sql_clause_order_by(order_by, con)
    if (!is.null(limit) && !identical(limit, 
                                      Inf)) {
      out$limit <- dbplyr::build_sql("FETCH ", as.integer(limit), " ", 
                             con = con)
    }
    dbplyr::escape(unname(purrr::compact(out)), collapse = "\n", 
           parens = FALSE, con = con)
}
#sql_select.ManifoldRadian(con, "a", "DrawingTable")

tbl(con, "DrawingTable")

tbl(con, "mfd_meta") %>% filter(name == "AuthorityLand") %>% pull(Value) %>% cat()

con@quote <- "\""
## doesn't work
#tbl(con, sql("SELECT Field FROM DrawingTable"))

tbl(con, "DrawingTable") %>% 
  #filter(Field == "abc")
  #filter(mfd_id < 5)
  arrange(desc(mfd_id))

## now do some work

tbl(con, "DrawingTable") %>% 
  mutate(isline = GeomIsLine(Geom))

tbl(con, "DrawingTable") %>% 
  mutate(isline = GeomIsLine(Geom))

# SELECT  mfd_id, Field, GeomConvertToLine(Geom) AS line FROM DrawingTable WHERE GeomIsArea(Geom)
tbl(con, "DrawingTable") %>% 
  filter(GeomIsArea(Geom)) %>% 
  transmute(mfd_id, Field, line = GeomWkb(GeomConvertToLine(Geom))) %>% collect() %>% 
  mutate(line = sf::st_as_sfc(line)) %>% 
  sf::st_as_sf()

x <- tbl(con, "list_parcels_hobart") %>% 
  #transmute(a = GeomArea(SHAPE, 0)) %>%
  dplyr::filter(GeomArea(SHAPE, 0) > 20000.0) %>% 
  transmute(g = GeomWKB(GeomUnionAreas(SHAPE)))
plot(collect(x) %>% sf::st_as_sf(), col = "grey")

con@quote <- ""
sq <- 'SELECT GeomUnionAreas(SHAPE1) AS SHAPE2 FROM (SELECT GeomConvertToArea(GeomBounds(SHAPE)) AS SHAPE1 FROM list_parcels_hobart)'
sq <- 'SELECT GeomConvertToArea(GeomBounds(SHAPE)) AS SHAPE1 FROM list_parcels_hobart'
tbl(con, sql(sq))

library(wkutils)
plot(NA, xlim = c(453834, 588782), ylim = c(5176882 , 5256821), asp = 1)
tbls <- c("list_parcels_hobart", "list_parcels_kingborough")
for (i in seq_along(tbls)) {
x <- tbl(con, tbls[i]) %>% 
  transmute(SHAPE1 = GeomWKB(GeomMakeRect(GeomBoundsRect(SHAPE))))  %>% collect() %>% 
  mutate(geom = wk::new_wk_wkb(c(unclass(SHAPE1)))) %>% pull(geom)
plot(x, col = sample(viridis::viridis(1000), length(x), replace = TRUE))
}

%>% 
  transmute(SHAPE2 = GeomUnionAreas(SHAPE1))  %>% show_query()

sq <- 'SELECT GeomUnionAreas(SHAPE1) AS SHAPE2 FROM (SELECT GeomBounds(SHAPE) AS SHAPE1 FROM list_parcels_hobart)'
dbGetQuery(con, sq)

sq <- 'SELECT "SHAPE1", GeomUnionAreas("SHAPE1") AS "SHAPE2" FROM (SELECT GeomBounds("SHAPE") AS "SHAPE1" FROM "list_parcels_hobart")'