Wednesday, December 13, 2006

Read data directly from Manifold tables using R

There are (at least) two ways to do this:

1. Via the clipboard

Select the records you want from a table in Manifold, Copy

From R read the data from the clipboard using read.delim the default separator is a tab, so it works already - but we can specify that and other options via arguments.

>d <- read.delim("clipboard")

"d" is now a data frame in R with all the data from the Manifold table. This works for virtual tables of images and surfaces, but it doesn't work for the binary geometry columns (you will get a text summary of those).

2. Using RODBC.

This option requires R with the RODBC package installed, get these from a CRAN mirror via, and a function defined for connecting to a save Manifold project file (see below).


Open a connection to a .map file (I copied this from the function odbcConnectAccess)

>ch <- odbcConnectManifold("C:/temp/")

Submit a query to the connection, and assign the results to variable "d"

>d <- sqlQuery(ch, "SELECT [Longitude (I)] AS lon, [Latitude (I)] AS lat, [Capital] AS name FROM [Countries] WHERE [Area (I)] > 150;")

(We convert the field names to sensible character strings for ease of use later).

"d" is now a data frame with the (centroid) location coordinates and city names of all objects from the "Countries" drawing in ""

My original posting on Georeference, including the function required is here: