Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

Please don't feel like you have to give a review of ALL software's mentioned. You're experience with even one 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 discussion at:

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

share|improve this question
    
There's also Talend's Spatial Data Integrator for use with Talend Open Studio, although I've never used it - just wanted to put it out there. –  Chad Cooper Jan 12 '11 at 3:59
2  
Please refine your need. Do you have a specific workflow, or do you need a multi-purpose ETL ? –  Vincent Apr 29 '12 at 11:14
    
multi-purpose ETL opensource software please! –  Robert Buckley Apr 29 '12 at 19:38
    
I just downloaded the spatialyitics version of ETL tools and am having a hard time just getting it to open. I have used the FME workbench for several projects and am in a situation where I need to use an open source version of the ETL tools. The main issue is that I am not terribly techy and need some help from people who are. Thanks –  Pat Oct 16 at 20:39
    
This does not appear to be an answer to the question so it will be converted to a comment. You may want to consider posting a new question for it. –  PolyGeo Oct 16 at 22:04

9 Answers 9

up vote 10 down vote accepted
+25

I'll talk only about what i've seen in a professional context. A student of mine worked with an enterprise tasked to receive, validate and integrate huge quantities of spatial data, from a well known source (TeleAtlas) into their GIS. She used several workflows using FME, doing very complicated verifications and tranformations on the fly, from a format to another, like feature selection, topology verification, duplicates removing, etc. The workflow was afterwards able to process automatically incoming datasets.

I was on a jury for a viva probation report (sorry, google traduction of "soutenance de rapport de stage"), where the student described another FME workflow like this, but this time to validate the regional datasets sent to the national level for integration to the national risks database. The main difference is that in this last example the dataset were in very diverses file formats, raster and vector, scales, and styles.

Last, i tested Spatial Data Integrator, the open source ETL based on Talend Open Studio. The features were numerous, however less than FME's, but i think the main differences were on the documentation and the user-friendliness of the workflow creation. I was often forced to modifiy the java code source of the workflow components. But it was an earlier version of SDI, and the shortcomings i describe here are somewhat usual with open source projects at their beginning, and we cannont compare on the same level proprietary well honed software and free open source young contenders.

Hope this helps :-)

share|improve this answer

Used Geokettle religiously for a small project high learning curve unless you used to an Eclipse UI... Really powerful as its compiled against GDAL1.10 support all geo types... What I liked was its support for both stored data and data via services... I used it to recreate and sync ArcGIS server datasets on a local postgis instance via ESRI json to GeoJSON... Workflow can build in conditions and validation set it up to query for # of objectid and based on that compile a predefined csv to iterate a post request for 500 features at a time depending on first query was able to consolidate all of the requests into one geojson file, run ogr2ogr to load to postgis even scripted postgis to run vacuum and index with it... Not affiliated just a fan

share|improve this answer
    
Forgot to mention the important detail that you can run it and map a workflow on desktop gui and use pentahos server tools to set it up on Hadoop and have it run as a script or cron job –  user33290 Dec 5 at 0:09

Although my answer is a bit late, it might be of help to others.

QGIS (at least since the current version 2.6) now also has an integrated model builder. Through this toolbox you can access hunderds of algorythms (GDAL, GRASS, SAGA, vectortools, etc). You can also add you own script. I must admit I have not used it extensively, but if I was looking for a model builder I would consider it worthy of investigation.

share|improve this answer

I did a comparison of various tools about a year back that also contains most of the options mentioned in this thread.

As a more direct answer, I use FME a lot due to its versatility. However, when I work with complex data structures such as in CityGML, INSPIRE GML or bigger database models, I use HALE, an open source app developed for ETL and particularly harmonization.

enter image description here

Currently (as of version 2.9.0) it compares to FME (2014 SP1) as follows:

  • HALE has a lower number of formats (HALE: 20, FME 200) and transformers (HALE: 30+, FME: more than 400), but very good support for all XML/GML dialects
  • HALE previews transformation results interactively in a map and in table views, and validates output directly
  • HALE is generally much faster, as local context for each attribute is maintained, saving you a lot of FeatureMergers, for example
  • HALE is Open Source and in production usage since 2010
  • HALE use a declarative mapping UI, which leads to a lower number of required user inputs compared to procedural approaches

Note that I have been on the HALE team for quite a few years.

share|improve this answer

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:

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.

share|improve this answer

If you look at blah238s duplicate link you find more information. I would say that Talend Open Studio and Pentaho GeoKettle is the most prominent open source solutions one can choose. Of these two Talend targets more than just ETL and GeoKettle a little more easy to use as far as I have read.

My municipality are about to give GeoKettle a go as we are having problem using FME to write a GML dialect put together by the swedish association of local authorities and regions (SALAR) and we need this format to deliver geodata to different commercial intrests.

And I believe GeoKettle have support for OGR/GDAL from version 2.0...

share|improve this answer
11  
Hi. Mark Ireland from Safe Software here. I'm obviously going to be as neutral as I can, and not say you should or shouldn't use one product over another. But what I can say is that if you have an XML or GML problem with FME, please email us using xml@safe.com - that address goes straight to our president who is an XML addict and has a standing challenge that there is NO XML/GML problem that he (and FME) cannot beat! So please, whatever route you take, send us an email anyway because we'll want to investigate this format regardless. –  Mark Ireland May 4 '12 at 16:33

FME is probably the best product to use in this space. After that it's GDAL/OGR. Another open source product in this space is geokettle - http://www.spatialytics.org/projects/geokettle/ although I have never used it in anger (being lucky enough to have both the other products mentioned).

If none of those generic options work, you probably want to use a specific conversion tool - what exactly do you need to ETL within your workflow?

share|improve this answer

Most of the simple operations can be carried out by these open source utilities

  • ogr2ogr for vector
  • gdal_translate and gdalwarp for raster

Get FWtools http://fwtools.maptools.org/ and give it a try.

share|improve this answer

I love open-source but FME easily wins out against the opensource ETL's as best I can tell. It's actually quite cheap for maintenance and support too (at least compared to most other corporate solutions we have for things).

If you're looking for translations between formats then OGR may do it (with some piping into GDAL for transformations). Of course, that's command line.

For visual modelling beyond those listed in the "possible duplicate" comment, they're working on a QGIS/SEXTANTE model builder; proof of concept video: https://www.youtube.com/watch?v=LTUu-I2ouqU

(No, I don't work for Safe, I'm just a relatively happy customer).

share|improve this answer
    
Agreed. FWTools (which GDAL/OGR are a part of) is something to try, but for ease of use, number of formats, and overall productivity FME is a lock. I spent over a year cobbling together open-source alternatives and wasn't happy with any, GDAL/OGR being the best. –  Lou Apr 30 '12 at 12:23
    
The QGIS/Sextante ModelBuilder looks really impressive. It looks like it will be a great addition when it is fully hashed out. –  RyanDalton Apr 30 '12 at 17:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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