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")'
