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.
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.
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.
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).
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.
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.
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.
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.
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.
QGIS (at least since the current version 2.6) now also has an integrated model builder. Through this toolbox you can access hundreds of algorithms (GDAL, GRASS, SAGA, vector tools, 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.
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.
[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 etc.
