mdsumner/adrian

Connecting R and Radian

adrian's Issues

errors from Manifold SQL

invalid object reference

Table name doesn't exist

SELECT * FROM mf_meta
Invalid object reference.

unknown name

Column doesn't exist

SELECT unicorn FROM mfd_meta
SELECT * FROM mfd_meta ORDER BY unicorn

'unicorn': Unknown name.

wrappers

plot

• convert to WKB, then collect, wk -> wkutils, plot

convert to sf

• convert to WKB, then collect, sf::st_as_sf (via wk might be faster)

extent

• convert to bounds only, return vector

projection

• get layer projstring
• get layer wkt

errors are heinous

I don't understand why these errors are so outrageous

library(odbc)
library(DBI)
dbConnect(odbc(), "project1")
con <- dbConnect(odbc(), "project1")
dbReadTable(con, "Drawing Table")
Error in new_result(connection@ptr, statement, immediate) : 
  nanodbc/nanodbc.cpp:1374: 42000: Invalid object reference. 

it's just the wrong table name

dbReadTable(con, "DrawingTable")
  mfd_id        Geom Field
1      1 blob[140 B]   abc
2      2  blob[76 B]   def
3      3  blob[28 B]     g
4      4  blob[28 B]     h
5      5  blob[28 B]     i
6      6  blob[28 B]     j
7      7  blob[28 B]     k
8      8  blob[28 B]     l

direct to project file

This thread shows a way http://www.georeference.org/forum/t153736.3

We'd like to flesh out examples of accessing projection files directly, without configuring the ODBC interface.

This code relies on

• installed version of Manifold 9.0 (not just the free viewer)
• an existing project file at "C:\temp\test.map" or set that to suit

I've tried connection strings that use dev builds of Manifold (as listed in the ODBC interface), but the string is too long (?) and gives an error from the odbc package.

library(DBI)
library(odbc)
filepath <- "C:\\temp\\test.map"

template <- "DRIVER={Manifold 9.0 Project Driver (*.map)};DBQ=%s;Unicode=True;Ansi=False;OpenGIS=True;DSN=Default;"
dstring <- sprintf(template, filepath)

con <- dbConnect(odbc::odbc(), .connection_string = dstring)
dbListTables(con)

## need an existing table name  (mfd_meta is always present)
dbReadTable(con, "mfd_meta")

dbGetQuery(con, "SELECT * FROM mfd_meta")

To use dbplyr tbl_lazy needs a bit more groundwork (not yet pushed to this repo): https://twitter.com/mdsumner/status/1318017630981156864/photo/1

notes (git problem workaround)

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

TheLIST ortho SQL

-- $manifold$
--
-- Auto-generated
-- New Data Source
--

CREATE DATASOURCE [OrthoTas] (
  PROPERTY 'Source' '{ "Source": "https:\\/\\/services.thelist.tas.gov.au\\/arcgis\\/services\\/Basemaps\\/Orthophoto\\/MapServer\\/WMSServer", "SourceAgent": "Mozilla\\/5.0 Manifold\\/9.0", "SourceCache": true, "SourceCacheTemp": true, "SourceCoordSystemPreferred": "EPSG:28355", "SourceUuid": "8ee66e00-c08e-4fb0-abdc-329c3859dee0" }',
  PROPERTY 'Type' 'wms'
);