40

Can you tell me how I can load geodatabase data into PostGIS without having to install Esri apps?

The solution I found online talked about using ArcGIS to convert GDB to SHP and then importing SHP to PostGIS.

Improve this question
2
  • Thank you very much for your answer. FME could be a valid solution, but we consider it as a very expensive solution. We think that a 14-day trial period is insufficient for a migration. Does anybody have experience with other tools, like GDAL scripts or GeoKettle? Regards.
    – SonOfabox
    Jun 19 '12 at 15:26
  • FME Desktop is the best solution for the migration data
    – user3120
    Jun 10 '13 at 17:29
38

I do it all the time for my clients.

Dump your data to FileGDB or Shapefiles then execute the following command:

For ESRI Shapefiles:

ogr2ogr -f "PostgreSQL" PG:"dbname=mydbname user=postgres" myshapefile.shp

For FileGDB:

ogr2ogr -f "PostgreSQL" PG:"dbname=mydbname user=postgres" myFileGDB.gdb

For FileGDB, you need to make sure that the FileGDB driver is installed. You do it with:

ogrinfo --formats

which should show FileGDB somewhere to tell you it is installed.

Improve this answer
5
  • Can you give more details on installing the FileGDB driver for OGR?
    – RyanKDalton
    Jun 19 '12 at 16:52
  • 7
    Easiest way for Windows is trac.osgeo.org/osgeo4w Look for the filegdb package (I am about to update it with bug fixes next week). If you are on Linux or Mac, you can modify the following script I use for building it on the fly (see install_filegdb) gist.github.com/2955440
    – Ragi Yaser Burhum
    Jun 19 '12 at 17:32
  • @RyanDalton and let me know how it goes :)
    – Ragi Yaser Burhum
    Jun 20 '12 at 23:37
  • 1
    What happens to metadata with this process? For example FGDC metadata which holds more than just the coordinate system etc.
    – Oliver Burdekin
    May 28 '16 at 7:09
  • ogr2ogr will not write it by default, but by looking at the code, I see that there is a special "GetLayerDefinition" command that you can execute in the SQL prompt to read this information. ogr2ogr -f "PostgreSQL" PG:"dbname=mydbname user=postgres" SanDiegoData.gdb -sql "GetLayerDefinition Parcels" would dump the ESRI XML metadata to a field in that db
    – Ragi Yaser Burhum
    May 31 '16 at 17:25
7

I haven't tried this so can't verify it works, but the File Geodatabase API may allow you to export the data from file Geodatabase to SHP (and thence PostGIS). The documentation says:

With the API you can... Read and write data
Improve this answer
6
  • Thanks Stephen, useful to know that there's an API but we'll probably try that if there isn't really any tool out there that people have written which does this automagically.
    – Gerson Galang
    Sep 8 '11 at 6:14
  • 6
    If you have the File GEodatabase API, you could convert directly using GDAL as well: trac.osgeo.org/gdal/wiki/FileGDB
    – johanvdw
    Sep 8 '11 at 7:27
  • 2
    Some Caution: the ESRI developed API, and tools based on it, will only work with File GDB (.gdb) created or converted to use in ArcGIS 10 or newer. Support for earlier Personal GDB (.mdb) versions comes from earlier programming efforts and may work depending on the data and the tool.
    – V Stuart Foote
    Sep 8 '11 at 13:12
  • @johanvdw I tried trac.osgeo.org/gdal/wiki/FileGDB a couple of days ago before posting my question above as the instructions on that link didn't work for me as well. I tried the latest gdal 1.8.1 on my Ubuntu 10.04 but that didn't seem to install the File GeoDB plugin into gdal. I tried "--with-fgdb=/usr/local/FileGDB_API" and "--with-fgdb=/usr/local" as their documentation wasn't clear in specifying what I should use in the "with-fgdb" parameter but nothing worked.
    – Gerson Galang
    Sep 9 '11 at 1:14
  • GCG: form that same page: (replace /usr/local/FileGDB_API with whereever you extracted FileGDB_API_<version>.tar.gz)
    – johanvdw
    Sep 9 '11 at 11:19
5

The Esri file geodatabase API only works with file geodatabases created using 10.x - the older versions will not work with GDAL, QGIS or anything else that depends on the open file geodatabase API. The old Personal Geodatabases depend on MS Access and there are readers and code available.

If you have a 10.x file geodatabase you can read layer from FileGDB and load into PostGIS with a single ogr2ogr command (see documentation):

ogr2ogr -overwrite -skipfailures -f "PostgreSQL" PG:"host=myhost user=myuser dbname=mydb password=mypass" "C:\somefolder\BigFileGDB.gdb" "MyFeatureClass"

In order to do this I also had to download VS2010 express edition and build my own GDAL, ogr plugin and 1.3 of the open file geodatabase API.

What I have been unable to do is migrate an older 9.x file geodatabase without using esri software (not available to me atm) - I cannot get past the...

HRESULT=-2147220965, errorText="This release of the GeoDatabase is either invalid or out of date."

...when trying to open the 9.x "gdb" with the open API that only supports 10.x "gdb".

Improve this answer
1
  • The latest versions (mid-2014) of ogr2ogr with OpenFileGDB can at least read older version of ESRI File Geodatabases.
    – c.gutierrez
    Jul 22 '14 at 17:33
2

While I have not tried it yet myself, I did just come across this tutorial (which is shapefile-->PostGIS) that uses the open-source geoETL software called "Spatial Data Integrator". I'm not sure if it supports Esri GDB or not, but it might be worth investigating further to see if you can use this product to make the similar conversion from GDB to PostGIS without using ESRI products.

Other ETL packages that I have heard of are FME from Safe Sofware ($, very popular) and GeoKettle (open-source), as well as many more references on the Spatial ETL wikipedia page.

EDIT: Upon digging a bit further, it looks like SDI does not directly support read of EsriGDB, but can read available OGR file formats.

Improve this answer
2

FWIW work is going on to create a GDB to postgis loader. This depends on latest GDAL trunk which has GDB bindings. Haven't had a chance to compile and try myself but hope to before PostGIS 2.0 is out.

check out - http://trac.osgeo.org/postgis/browser/spike/pramsey/postgis2fgdb

You can probably give Paul a buzz on PostGIS newsgroup to find out where he is with this. http://www.postgis.org/mailman/listinfo/postgis-users

For the Personal GeoDatabase format, GDAL can read that fine and that's what I usually use to export data out of personal geodb.

Improve this answer
0

Answering my own question here that I posted here a while back...

Another option that I found online (in addition to the suggestions above) is to use pgdbf (https://github.com/kstrauser/pgdbf) which dumps the SQL script that you can then use to ingest into postgres manually.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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