Read a table from an ESRI file geodatabase (.gdb) using R

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.)

Answer 5

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.

Answer 6

I was able to install GDAL through Conda and run readOGR with the following steps.

Programs needed: Rstudio (download available here: https://rstudio.com/products/rstudio/download/) Anaconda3 (download available here: https://www.anaconda.com/products/individual)

You will need to install GDAL in order to run the Rstudio functions to open the .gdb files. One way to install GDAL is through Conda, which I will describe here:

Installing GDAL in Conda:

1. Install Anaconda3 at the linked provided up.
2. Search for Anaconda Navigator in windows search box.
3. Click on Environments on the left hand side of the Anaconda Navigator.
4. Create a new Environment with the + buttom on the bottom left.
5. On the top bar of the environment, click on the drop bown and select "not installed".
6. Search for gdal in the "not installed" packages.
7. Install r-gdalutils, r-rgdal, gdal, and libgdal. (I'm not sure if all of these are actually necessary, I just installed anything with the name gdal). The installation can take a few minutes.

The readGDBlayer function worked for the 2014 .gdb files on Marine Cadastre (where the sample data originated), but was unable to open .gdb files on 2009 - 2013. I am not sure why this is. The readOGR function was able to open the .gdb files from 2009 - 2013

Here is a link to the 2009 AIS data that you can download https://coast.noaa.gov/htdata/CMSP/AISDataHandler/2009/index.html . Does anyone know why readGDBlayer function will not work with this data?