Subscribe to this thread
Home - General / All posts - Manifold 9 ODBC
sga30 post(s)
#11-Sep-20 12:53

Hi all,

I'm trying to connect to a Manifold9 .map project from R. I'm using the RODBC package, but any other working option is welcome.

I have defined a DSN file in the ODBC datasources. In R I ran following commands

#########

library(RODBC)

conr2<-RODBC::odbcDriverConnect("DSN=test") #This works

sqlTables(conr2) #This works

sqlQuery(conr, "select * into test from (select * from geocoded)") #This works

sqlQuery(conr2, "select * from geocoded;") #This fails

#########

To summarize, I can successfully connect to the database, list all tables, send a query that writes a table. But when I send a query that retrieves rows, I get following error messages

"HYC00 0 Requested functionality is not implemented."

"[RODBC] ERROR: SQLRowCount failed"

I have tried both with Manifold 9 (stable release) and Edge and both do not work. However, if I run the same code with a Manifold 8 file and the corresponding M8 driver, it does work.

Might this come from a problem in the ODBC driver?

Many thanks in advance for your answers

mdsumner


4,260 post(s)
#12-Sep-20 00:17

Can you also try with these args in odbcDriverConnect? 'rows_at_time = 1, believeNRows = FALSE'

Another option is with the {odbc} package? I tried this stuff a while back - hoping to try again soon.

Yet another option is to try `sf::read_sf()` with a syntax like this with dsnname, layername, and geometry column name (which you'll get from Manifold). This is totally separate from R RODBC/odbc and goes via the GDAL ODBC driver (all built-in to the sf package). Again I haven't tried in a quite a while.

# R

 read_sf("ODBC:test,geocoded(geometry)""geocoded")}


https://github.com/mdsumner

sga30 post(s)
#15-Sep-20 07:03

Hi mdsumner

Many thanks for your answer.

The additional arguments in odbcDriverConnect did not change the problem. The error message remained the same.

However, I did manage to successfully read a table using the odbc package. Thanks for the suggestion! The following code did work:

##########

dstring <- "DRIVER={Manifold 9.0 Project Driver (*.map)};DBQ=E:\test.map;Unicode=True;Ansi=False;OpenGIS=True;DSN=Default;"

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

dbListTables(con)

dbReadTable(con, "geocoded")

dbGetQuery(con, "SELECT * FROM geocoded")

###########

Regarding the geometry column, it returns something like "blob[413 B]", and I'm not sure how to use it. However, if I transform the geometry to WKT using GeomWkt in the manifold query, I get a usable output. Anyway, I'm not particularly interested in the geometry column in R. I just want to retrieve the data in R to be able to draw some plots/graphs, but I'll stick to manifold for the GIS stuff.

Out of curiosity, I also tried the sf::read_sf. It works, but I get an error with the geometry column

In CPL_read_ogr(dsn, layer, query, as.character(options), ... : GDAL Error 1: GetNextRawFeature(): Corrupt data

Which then return an empty geometrycollection.

As I said, it was just out of curiosity, since I'm not particularly interested in the geometry column.

Thanks again for your answer, it really helped!

mdsumner


4,260 post(s)
#15-Sep-20 13:05

Oh cool!

Use sf::st_as_sfc() on the geometry column, to create the sfc column-type. Then sf::st_as_sf() will work.

Can you run a query that ignores any geometry column? If you don't want it then it can be ignored.

Possibly you'll have to cast it to WKB in the query (but maybe the OpenGIS option does that already). That may also fix the GDAL error from read_sf, but I didn't expect that to work at all so I'm not sure (you saw that GDAL error from code like the following ?

#R 

read_sf(dstring)

There is more options now with the 'wk' package, but at some level this becomes an R topic so I won't go into that until I try this out myself properly. Now that this works it means we can set up a proper lazy tbl, so that no data is read (a few rows from the top of a query) until calling dplyr::collect() - which will be v nice.


https://github.com/mdsumner

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.