I am trying to read a table directly from an ESRI file geodatabase into R. An example data file can be downloaded here. The database contains a point feature class (Zone9_2014_01_Broadcast) and two linked tables (Zone9_2014_01_Vessel and Zone9_2014_01_Voyage). You can read the shapefile in R using readOGR from the rgeos package:

library(rgeos)
library(downloader)

download("https://coast.noaa.gov/htdata/CMSP/AISDataHandler/2014/01/Zone9_2014_01.zip", dest="Zone9_2014_01.zip", mode="wb")
unzip("Zone9_2014_01.zip", exdir = ".")

#  Not Run (loads large point file)
#  broadcast <- readOGR(dsn = "Zone9_2014_01.gdb", layer = "Zone9_2014_01_Broadcast")

The two linked tables also show when you use ogrListLayers or ogrInfo. However, ogrInfo gives a warning:

Warning message: In ogrInfo("Zone9_2014_01.gdb", layer = "Zone9_2014_01_Vessel") : ogrInfo: all features NULL

And if you try to use readOGR on the tables you get an error:

vessel <- readOGR(dsn = "Zone9_2014_01.gdb", layer = "Zone9_2014_01_Vessel")

Error in readOGR(dsn = "Zone9_2014_01.gdb", layer = "Zone9_2014_01_Vessel") : no features found In addition: Warning message: In ogrInfo(dsn = dsn, layer = layer, encoding = encoding, use_iconv = use_iconv, : ogrInfo: all features NULL

Thus, it appears that only geographical features can be read by readOGR. Is there any way to import the tables directly into R or is the only solution to first export them from ArcGIS as *.dbf (or *.txt) files as in this answer?

As an addition, if anyone can provide calls from R to a python script that automates the export of *csv (preferrably) or *.dbf files, that would be an acceptable work-around. The solution just needs to be scalable and automated.

  • 2
    Have you seen the new integration of R and ArcGIS? r-arcgis.github.io maybe something useful for your work. – Alex Tereshenkov Mar 9 '16 at 7:33
  • Thanks for the suggestion...I had seen mention of it at one point, but never looked into it more thoroughly. Perhaps now would be a good time to do that! – Cotton.Rockwood Mar 9 '16 at 16:56
  • @AlexTereshenkov, if you want to write up a short answer for this solution, I will accept it since it is what I was looking for. – Cotton.Rockwood Mar 13 '16 at 19:56
  • 1
    It looks like the R-ArcGIS bridge that @AlexTereshenkov mentioned does have the functionality to read tables directly into R. The integration requires ArcGIS Desktop >10.3.1 (or ArcGIS Pro) and R >3.2. 64-bit R can only be used with the 64-bit background geoprocessing (and only allows use from ArcGIS, not from R) or ArcGIS Pro. Once the bindings are installed, you can use the package arcgisbbinding in R. The function arc.open() will open the table as an arc.dataset-class object. To open directly as a data.table, use function arc.select. – Cotton.Rockwood Mar 13 '16 at 21:46
  • good to know. I've added an answer just to close the thread, but you have figured everything out on your own, so accept but don't upvote :D – Alex Tereshenkov Mar 14 '16 at 7:05
up vote 7 down vote accepted

I'm a little bit late to the party, but this can now be read by sf, with

vessel <- sf::st_read(dsn = "Zone9_2014_01.gdb", layer = "Zone9_2014_01_Vessel")

It returns a warning (no feature geometries present) but also a data.frame with the table. See the thread that started here: https://stat.ethz.ch/pipermail/r-sig-geo/2018-February/026344.html

  • excellent! Thanks Edzer... glad to see this and the evolution of sf!! – Cotton.Rockwood Feb 13 at 20:54
  • strange, I wasn't able to run this on 3 machines: I get an error, not a warning? – Matifou Feb 15 at 22:23
  • you'll need to install the dev version from github, from source, or wait until 0.6-1 release next month – Edzer Pebesma Feb 16 at 12:53
  • oh nice, and yes it works now, thanks!!!! – Matifou Feb 16 at 20:49

I use GDAL 2.0.2 that is "shipped" with FDGB support and without a third party a FGDB driver to investigate that stuff. The test environment is Debian Jessie 64-bit.

In short, it seems, that the "layer" Zone9_2014_01_Vessel contains pure attribute data and the layer Zone9_2014_01_Broadcast contains position data. You can use a workaround within R via a system call and the conversation of the GDB to a shapefile container (last script at the end of the answer).

Here are the investigation steps:

$ mkdir ~/dev.d/gis-se.d/gdb 
$ cd ~/dev.d/gis-se.d/gdb
$ wget https://coast.noaa.gov/htdata/CMSP/AISDataHandler/2014/01/Zone9_2014_01.zip
$ unzip Zone9_2014_01.zip
$ ogrinfo Zone9_2014_01.gdb Zone9_2014_01_Vessel | head -20
Had to open data source read-only.
INFO: Open of `Zone9_2014_01.gdb'
      using driver `OpenFileGDB' successful.

Layer name: Zone9_2014_01_Vessel
Geometry: None <---------------------------- HERE 
Feature Count: 1282
Layer SRS WKT:
(unknown)
FID Column = OID
MMSI: Integer (0.0)
IMO: Integer (0.0)
CallSign: String (255.0)
Name: String (255.0)
VesselType: Integer (0.0)
Length: Integer (0.0)
Width: Integer (0.0)
DimensionComponents: String (255.0)
OGRFeature(Zone9_2014_01_Vessel):1
  MMSI (Integer) = 367603345

As you see the field Geometry is set to None. You can convert the data to a shape file using ogr2ogr and get also only a dbase attribute file:

$ ogr2ogr -f 'ESRI SHAPEFILE' test Zone9_2014_01.gdb Zone9_2014_01_Vessel
$ ls test

Zone9_2014_01_Vessel.dbf

Geometries (positions) can be found in the layer Zone9_2014_01_Broadcast.

$ ogr2ogr -f 'ESRI SHAPEFILE' test Zone9_2014_01.gdb
$ ls test

Zone9_2014_01_Broadcast.dbf  
Zone9_2014_01_Broadcast.shp  
Zone9_2014_01_Broadcast.prj  
Zone9_2014_01_Broadcast.shx  
Zone9_2014_01_Vessel.dbf
Zone9_2014_01_Voyage.dbf

Vessel and Voyage containing no position data according to the AIS messages protocol.

Here the complete workaround in R using a system call for the GDB to shapefile conversation and the package foreign to read the dbf's:

# Load module to get readOGR
require('rgdal');

# Load module to get read.dbf
require('foreign');

# goto the directory with the GDB stuff
setwd('~/dev.d/gis-se.d/gdb')

# Conversation to a shapefile container 
system("ogr2ogr -f 'ESRI SHAPEFILE' test Zone9_2014_01.gdb") 

# read the vessels
vessel <- read.dbf('test/Zone9_2014_01_Vessel.dbf');

# read hte voyage data
voyage <- read.dbf('test/Zone9_2014_01_Voyage.dbf');

# read the geometries in broad cast
broadcast <- readOGR('test/Zone9_2014_01_Broadcast.shp','Zone9_2014_01_Broadcast')

OGR data source with driver: ESRI Shapefile
Source: "test/Zone9_2014_01_Broadcast.shp", layer: "Zone9_2014_01_Broadcast"
with 1639274 features
It has 10 fields

# is vessel OK?    
head(vessel)

MMSI IMO CallSign Name VesselType Length Width   DimensionC
1 367603345  NA     <NA> <NA>         50     20     6     7,13,3,3
2 563000574  NA     <NA> <NA>         70    276    40 188,88,20,20
3 367449580  NA     <NA> <NA>         31     28    10     9,19,5,5
4 367302503  NA     <NA> <NA>         31     20     8     8,12,4,4
5 304003909  NA     <NA> <NA>         71    222    32 174,48,21,11
6 210080027  NA     <NA> <NA>         71    294    32 222,72,22,10

# is voyage OK?
head(voyage)

VoyageID           Destinatio Cargo Draught        ETA  StartTime    EndTime      MMSI
1       12                 KAKE    50      20       <NA> 2014-01-01       <NA> 367603345
2       23             YOKOHAMA    70     125 2014-01-11 2014-01-01 2014-01-30 563000574
3       38         KETCHIKAN AK    31      40 2014-11-12 2014-01-01       <NA> 367449580
4       52 CLARENCE STRAIT LOGS    31      30 2014-09-12 2014-01-01       <NA> 367302503
5       62               JP TYO    71      90 2014-01-13 2014-01-01 2014-01-31 304003909
6       47           VOSTOCHNYY    71     106 2014-01-13 2014-01-01       <NA> 210080027
  • thanks @huckfinn! This is a nice workaround. I have quite a few files (many of which are much larger than the example), so I will give it a shot and see how the conversion to a shapefile affects processing times. I'm also hopeful, there is a direct solution in R, but if no one answers with one, I'll select yours as the answer. – Cotton.Rockwood Mar 9 '16 at 17:01

Not sure if you can do this with readOGR but try

vessel <- readOGR(dsn = "Zone9_2014_01.gdb", layer = "Zone9_2014_01_Vessel", dropNULLGeometries = FALSE)

If that doesn't work try ogr2ogr directly, which can export non-geometries to table. (Maybe try R package gdalUtils to run that, once you've got your process down.)

  • 1
    Unfortunately, readOGR does not have the capability to read gdb tables. – Aaron Mar 9 '16 at 4:39
  • 1
    It probably does, now. – mdsumner Jul 3 '17 at 22:21
  • Still not as of rgdal 1.2-8, gdal 2.0.1 – gregmacfarlane Sep 27 '17 at 14:37
  • It's called OpenFileGDB in ogrDrivers()$name, perhaps you are trying to read a raster? That's still being implemented, either way if you want to find out you could post a question with details about your system and what you've tried. – mdsumner Sep 27 '17 at 21:57

There is a recently released integration between the R and ArcGIS from Esri, called R ArcGIS Tools. It provides integration between R and ArcGIS making it possible to interchangeably access R tools and ArcGIS resources. You should be able to access geodatabase feature classes/tables with this integration.

Sample R tools are available here and sample tools illustrating R usage in geoprocessing scripts are here.

This custom function basically follows the path outlined by @huckfinn but uses the gdalUtils library suggested by @mdsumner.

read_GDB_Layer <- function(dsn, layerName, overwrite = T) {
   conversionDir <- tempdir()

   gdalUtils::ogr2ogr(src_datasource_name = dsn, 
                      dst_datasource_name = conversionDir, 
                      f = "ESRI Shapefile", layer = layerName, 
                      verbose = T, overwrite = overwrite)

   df <- foreign::read.dbf(file.path(conversionDir, paste0(layerName, ".dbf")))

   return(df)
}

Run it like this:

vsl <- read_GDB_Layer(dsn = "Zone9_2014_01.gdb", layerName = "Zone9_2014_01_Vessel")
vyg <- read_GDB_Layer(dsn = "Zone9_2014_01.gdb", layerName = "Zone9_2014_01_Voyage")

If you don't already have gdal installed, you will need to install it to provide access to gdalUtils. You can find binaries and instructions for 'gdal' installation here.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.