33

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:

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.

locked by PolyGeo♦ Feb 5 '18 at 0:24

This question's answers are a collaborative effort: if you see something that can be improved, just edit the answer to improve it! No additional answers can be added here

Read more about locked posts here.

10 Answers 10

16
+25

This question has been converted to Community Wiki and wiki locked because it is an example of a question that seeks a list of answers and appears to be popular enough to protect it from closure. It should be treated as a special case and should not be viewed as the type of question that is encouraged on this, or any Stack Exchange site, but if you wish to contribute more content to it then feel free to do so by editing this answer.

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.

23

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.

10

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

  • video link is broken. Can you fix it? – GeoStoneMarten Feb 7 at 12:49
6

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.

6

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.

  • How do you think it stacks today vs FME? Especially for web and geoRSS feeds of data? – Dr.YSG Aug 17 '18 at 16:56
  • @Dr.YSG the maintenance of hale was taken over by wetransform GmbH in 2015, so there is a now a dedicated team behind its development. There has been substantial development in the past four years. Changes are documented in the GitHub release notes. It supports processing of JSON/GeoJSON data. GeoRSS is a simple XML format that re-uses some parts of GML, so it is fully supported as well. To automatically process GeoRSS feeds, you would want to look into hale-cli (the command line interface and other APIs). – tr_xsdi Aug 19 '18 at 8:05
5

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 is about to give GeoKettle a go 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.

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

4

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.

3

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

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.

3

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.

1

[WARNING: A gratuitous promotion of a product I am involved with]

We have been working on a tool to do ETL. It is similar in flavor to FME but designed for simpler tasks and to require less expertise to use. Some details can be found at https://www.geoactive.it just look for information on Data Dragon. This application uses GDAL/OGR in the back end and we are pushing any additions we make back to GDAL/OGR.

Commercial use requires it to be purchased but we do have academic licenses available for free so if you want to use it for study ect message me and I will provide you with some more details.

This is in it early release stage so there are still some minor bugs ect so please be gentle with us.

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