How to use PostGIS to handle complex geoprocessing workflows?

Question

Our organization is considering moving our geoprocessing workflow to PostGIS. We are currently using ArcGIS, with a plethora of custom Python tools used in ModelBuilder. We're moving most of our data into PostGIS to be consumed by a variety of apps, and now we're asking if it also makes sense to perform the data processing there as well.

We process data to be compatible with our software. A customer purchases our software, gives us their data, and we process it to be optimized for use in our software. This requires us to build a variety of tools to handle varying qualities of input data. We can't expect to receive data in a particular format or schema, so we build tools to map input fields to output fields, parse single fields into multiple fields, merge multiple datasets, etc. We also perform spatial joins, intersections, trim whitespace and concatenate fields, and many other common operations. PostGIS appears to be perfectly capable of performing all of our processing needs.

For those of you who use PostGIS to do your data processing, do you have any advice for organization, tools to use, etc.?

• do you use it in conjunction with QGIS python processing?
• are people using a Python ORM for non-web processing? I've been leaning towards using GeoDjango since it has a Python ORM for PostGIS. Our initial test of using PostGIS to process data has many large SQL text blocks in Python code and we are thinking that the GeoDjango ORM may help with creating more manageable and readable code. There's also the GeoAlchemy ORM that interacts similarly with PostGIS, and doesn't appear to be as web-specific as Django is.

I haven't heard of people using PostGIS to do geoprocessing as much as I see people using QGIS or ArcGIS, so I want to know if it is a comparable alternative.

Answer 1

I really like using PostGIS for geoprocessing purposes.

My two main resons is:

1) It is often very much faster to do complex tasks in the database because you get the help of the query planner to do things in the right order.

2) Just save the sql lines you used in a textfile and you have a very good documentation of what you have done.

My workflow, if the tasks involve a lot of "steps" use to be something like:
1- Build parts of the query or all of it depending of the nature of the task
2- Test the query on a small part of the dataset to see how it performs
3- Do some tweaking if necessary
4- Run the query on the whole dataset
5- Save the lines in a text file with some notes.
All this is often about as fast as starting up ArcGIS and wait for a license from the license server.

Answer 2

We use PostGIS and some kind of Python programming environment for a number production geoprocessing web services we've developed; no complaints!

GeoDjango is a great choice if you're working mostly (or exclusively) with features for a web application. It doesn't support PostGIS Raster or PostGIS 2.0's raster data type. It does come natively with the latest version of Django, now. You can make up for a lack of raster support and overall robustness by using custom, raw SQL queries in Django.

For more robust geoprocessing applications, and particularly if you're looking to use an object-relational model, try GeoAlchemy2. The original GeoAlchemy library, which extends SQLAlchemy, provides support for feature data; GeoAlchemy2 extends it by providing (limited) support for the new raster data type in PostGIS 2.0.

And then, there's always the Python bindings for GDAL and OGR!

Answer 3

Though possible, it's hard to imagine that you would want to do much geoprocessing inside of a database engine or a web framework. I recommend you look at the underlying code libraries -- geos, proj.4, and gdal. There are Python bindings or libraries for all three. Another option to look into is the Sextante geoprocess plugin for QGIS, as it allows model/workflow building.

Some other thoughts:

Don't rule out the use of PostGIS. It provides good storage and server capabilities, and exposes some geos and proj.4 functionality though SQL. It also plays nice with the other tools mentioned: Django, QGIS, and Python.

Besides possible use of the aforementioned Sextante plugin, QGIS is good for visualization, has some tools for working with postgres, and also includes a Python console.

If you're looking for ORM and want a web front end, Django will do it. If you don't mind a less-than-sexy interface, the admin pages will give you a CRUD interface with relatively little effort -- even geometry editing if you use GeoDjango.

Answer 4

Take a look at ETL, specifically, FME for spatial operations (or the open source GeoKettle).

I really like using FME, as it creates a visual workflow, and you can separate out the logic for spatial operations, joins, merges... everything, and you can work with non-database formats, and different databases... You can do a lot, and easy, and fast. If you have experience wtih model builder, you'll pick it up quick, plus there is lots of documentation online.

The only disadvantage of FME is that it costs money. But I think it's worth it.

An alternative to using FME is probably GDAL and OGR along with perhaps Python to tie it together. Or, as you say, doing it all in PostgreSQL. I think an ETL has a strong role in spatial data wrangling, and it does a lot that you can't do just in your database.

I haven't used it, but GeoServer provides an implementation of WPS, I haven't used this, but others may comment on how this could be useful to you?

I can't comment on using GeoDjango, but I thought it was more a CMS, like a front-end for viewing data.