Opening personal geodatabase in QGIS

Question

I am trying to use an ArcGIS (10.2) personal database MDB as a container for data to pass to a QGIS user. I formerly used QGIS 1.8 for Mac OSX, and after some fiddling around, I was able to open a personal geodatabase and load one or more features from it.

I just upgraded to QGIS 2.0 and upon trying to check my MDB test file before sending to my colleague, I am unable to load the geodatabase. In fact, personal geodatabase as a file type no longer appears in the available types QGIS is prepared to open in the File Type drop down box in the load vector data operation.

Is there some setting I need to tweak or an additional plugin I need to install in order to regain this functionality?

Answer 1

Try 32-bit version - on Windows it works.

Answer 2

It doesn't look like the Java-based MDB driver is included in the most recent GDAL-Complete framework. Please file a request with kyngchaos.com, though understand there may be issues with creating a one-size-fits-all package installer (see JAVA note below). IMHO, the Java-based MDB driver is the only reliable means of reading MDB files for Mac OS X, as of now, since the PGeo and related Geomedia drivers fail to function anymore via ODBC.

I have a patch for compiling-in the Java-based MDB driver for Mac that was applied to the GDAL 2.0 branch, but not to the upcoming 1.10.2. There is also a homebrew pull request. Nether of those will help a current Kyngchaos.com install to support MDB, though.

In the meantime, if you don't mind trying it yourself, you can attempt to build a shared plugin for GDAL 1.10, install it, and see if it works with your data source:

1. Ensure you have XCode and/or its command line tools installed.

2. Download the GDAL source (MUST match same GDAL version as Kyngchaos.com's) and un-archive to folder, then enter and configure the build for basic defines for your OS (no other options necessary):

   $ cd /path/to/gdal-1.10.1
   $ ./configure
   

3. Build shared plugin:

   # make directory for build output
   $ mkdir ogr_plugins
   
   # compile shared plugin (multi-line, single command)
   $ g++ -Wall -g ogr/ogrsf_frmts/mdb/ogr*.c* -shared -o ogr_plugins/ogr_MDB.dylib \
     -Iport -Igcore -Iogr -Iogr/ogrsf_frmts -Iogr/ogrsf_frmts/mdb \
     -I/System/Library/Frameworks/JavaVM.framework/Headers \
     -framework GDAL -framework JavaVM
   
   # fix lib id
   $ install_name_tool -id ogr_MDB.dylib ogr_plugins/ogr_MDB.dylib
   
   # copy plugin to known GDAL_DRIVER_PATH (sudo needed)
   $ sudo cp -f ogr_plugins/ogr_MDB.dylib /Library/Application\ Support/GDAL/1.10/PlugIns/
   

4. Install necessary *.jar support files (or the driver will not work at all). See the MDB driver page, under How to run the MDB driver (on Linux) for details. Ignore step #1 and for step #3 substitute /Library/Java/Extensions for lib/ext. I used the noted downloads from the related mdb-sqlite project on that page, browse source here.

5. Verify plugin:

   $ cd /Library/Application\ Support/GDAL/1.10/PlugIns
   $ otool -L ogr_MDB.dylib
   ogr_MDB.dylib:
       ogr_MDB.dylib (...)
       /Library/Frameworks/GDAL.framework/Versions/1.10/GDAL (...)
       /System/Library/Frameworks/JavaVM.framework/Versions/A/JavaVM (...)
       /usr/lib/libstdc++.6.dylib (...)
       /usr/lib/libSystem.B.dylib (...)
   
   $ ogrinfo --formats | grep 'MDB'
   -> "MDB" (readonly)
   

6. Launch QGIS and verify plugin loading in PyQGIS console:

   from osgeo import ogr
   sorted([ogr.GetDriver(drv).name for drv in range(0,ogr.GetDriverCount())])
   

7. You should be able to load a MDB data source via Add Vector Layer > File > Browse, or try drag/drop of *.mdb file. Note, *.mdb files do not yet show up in the browser panel.

If you have MDB driver support in GDAL, but for whatever reason QGIS does not load the file, or because the loading in QGIS is very slow, try using the ogr2ogr utility to convert the layers to a more useable format.

IMPORTANT NOTE ABOUT JAVA: The above plugin build command assumes an Apple-supported install of Java 1.6.x. However, since Apple has dropped support for native Java installs, and now (since 10.8) recommends users download and install the latest Java from Oracle, adjust the commands to accommodate an Oracle install:

• (supplement to #1 above) Update Java Home path link. If Oracle Java has been installed, this is not updated and may point to old Apple-supported Home (i.e. now a broken symbolic link).

  $ cd /Library/Java
  $ sudo rm Home
  $ sudo ln -s $(/usr/libexec/java_home) .
  

  Note: if you update your Java version, you will probably have to update this symbolic link as well, and maybe recompile the shared plugin, if you want to use the new Java version.

• (replaces #3 above) Build shared plugin:

  # make directory for build output
  $ mkdir ogr_plugins
  
  # compile shared plugin (multi-line, single command)
  $ g++ -Wall -g ogr/ogrsf_frmts/mdb/ogr*.c* -shared -o ogr_plugins/ogr_MDB.dylib \
    -Iport -Igcore -Iogr -Iogr/ogrsf_frmts -Iogr/ogrsf_frmts/mdb \
    -I/Library/Java/Home/include -I/Library/Java/Home/include/darwin \
    -L/Library/Java/Home/jre/lib/server -ljvm -framework GDAL
  
  # fix lib id and Java lib path
  $ install_name_tool -id ogr_MDB.dylib ogr_plugins/ogr_MDB.dylib
  $ install_name_tool -change @rpath/libjvm.dylib \
    /Library/Java/Home/jre/lib/server/libjvm.dylib ogr_plugins/ogr_MDB.dylib
  
  # copy plugin to known GDAL_DRIVER_PATH (sudo needed)
  $ sudo cp -f ogr_plugins/ogr_MDB.dylib /Library/Application\ Support/GDAL/1.10/PlugIns/
  

Please post if you have success with this shared plugin method for reading MDB files.

Answer 3

I think the answer is simply not using personal geodatabase. Even Esri dislikes the format, why not just give them shapefiles, kml, gml, geojson...anything but mdb...

Just to back up my Esri dislikes personal geodatabase claim...

http://downloads2.esri.com/support/TechArticles/ArcGIS10and101Deprecation_Plan.pdf

ArcGIS Server 10.1 will no longer support the Microsoft Access based Personal Geodatabase. With the migration of ArcGIS Server to be 64bit and because of the lack of scalability of Personal Geodatabases, we have removed support for Personal Geodatabases from ArcGIS server 10.1. [Page 7 of 8, bullet #4]

Answer 4

This is certainly a non-programmer's answer, but as an end user I have to do what I have to do.

Simply, I installed Lisboa 1.8 and use it to pull out shapefiles from ESRI personal geodatabases. Afterwards, I access them through QGIS Dufour 2.0.

Not a very elegant solution, but it works.