mdsumner / adrian Goto Github PK
View Code? Open in Web Editor NEWConnecting R and Radian
Connecting R and Radian
Table name doesn't exist
SELECT * FROM mf_meta
Invalid object reference.
Column doesn't exist
SELECT unicorn FROM mfd_meta
SELECT * FROM mfd_meta ORDER BY unicorn
'unicorn': Unknown name.
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
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
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
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")'
-- $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'
);
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.