[GIS] Seeking options for Spatial ETL (Extract, Transform, Load)

Question

This question's answers are a community effort. Edit existing answers to improve this post. It is not currently accepting new answers or interactions.

I am interested in the pros and cons of various spatial ETL (extract, transform, load) tools. If you have used the items listed here (or add your own), I seek your opinions and experiences. In particular I would like to see usability comparisons of:

• Safe Software FME (proprietary)
• ArcGIS Data Interoperability Extension (proprietary, subset of FME features)
• Talend Spatial extension (previously Spatial Data Integrator) (open source)
• GeoKettle (open source)
• ogr2ogr (I don't know that it is truly ETL, but more of a file translation toolset)
• Any additional lot listed

There is no need to give a review of ALL software mentioned. If you are experienced with even one then that will be very beneficial in making a decision about which direction to go.

Example: I am looking to create a schema conversion function that will allow me to select the input layer, create a translation, and output to a new, pre-defined schema. Optimally, after creating the translation script, I would like to have an interactive form where I can "map" fields in my input layer to the output layer (ie- The output layer will have a field called "Address", what is it called in the input layer?)

Some were mentioned in the Q&A at What tools are available for uploading gis data to a database?

And here are a couple of related articles that I found.

• http://fuzzytolerance.info/code/open-source-etl-tools/
• http://geobiwiki.spagoworld.org/xwiki/bin/view/Main/Talend+GeoKettle
• http://slashgeo.org/2011/07/25/Open-Source-ETL-Tools-GeoKettle-20-and-Spatial-Data-Integrator-420-Released

Answer 1

For a recent project working with several GBs of spatial data, I started the data loading / reprojections with FME. It worked well, but there is a learning curve.

By the end of the project I was using Python scripts to automate the reamining processes. FME can be scripted, but if you have the Python basics why complicate things further? Python gives you complete flexibility and with each import script written your Python skills are improving.

I found the following Python packages invaluable when working with data transformations:

• PyProj
• GeoPy
• Shapely
• xlrd for importing data from Excel spreadsheets
• pyobdc to connect to databases
• SQLAlchemy to run SQL statements and work with databases

If you have a developer / programming background I'd recommend using Python, if you prefer working with a GUI (which can also generate nice images for documentation) I'd recommend FME.