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.

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.

share|improve this question
1  
Is all of your process "backend"? I am not a Django or GeoDjango user, but I think of those products for developing websites only (and more trouble than they are worth, IMHO). Why not just a bunch of shell or python scripts run at the (Unix of course) command line or periodically via "cron"? (Less clickey-clickey is always better in my mind.) You would probably want to organize these systematically, at least by incoming data stream. Also, Postgis can probably slice and dice your data without QGIS -- what specific types of operations do you have in mind? –  forkandwait Aug 7 '13 at 15:49
1  
Yes, our processing is backend. However, we will eventually have an OpenLayers web map for customers to view and edit their data. We might use Django for the app's user and admin accounts. If so, I thought that might be another reason to look into GeoDjango for processing, even though Django was built primarily for websites. This Large Scale Processing with Django presentation suggests that Django is not just for web sites: slideshare.net/dibau_naum_h/large-scale-processing-with-django –  Tanner Aug 7 '13 at 23:52
1  
For backend work, I would use PostGIS, a little ogr2ogr, a scripting language (Python, Ruby, Tcl, whatever), and a unix command line. I would avoid trying to mix Django into that except to keep your database as compatible as possible with it. Then later, put a front end on it if you need it. My rule is: less clickey = more productive (though GIS analysts feel more comfortable with clickey-clickey crap... I mean "intuitive interfaces"). –  forkandwait Aug 8 '13 at 16:03
    
Regarding the slideshare -- that looks crazily complicated, and maybe appropriate if you are taxing your processing power trying to keep up, but otherwise nightmarish to manage. –  forkandwait Aug 8 '13 at 16:10
1  
A couple of generic etl questions that you may find helpful: "Spatial ETL comparisons" and "Is there any safe fme alternatives?" –  RyanDalton Aug 9 '13 at 6:37
add comment

4 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
1  
While I would agree that one would not use a web framework to do geoprocessing, I disagree strongly that one would not use PostGIS (or another database engine) to do geoprocessing. We need specifics to move forward in the discussion, but I do a huge amount of geometry slicing/ dicing and point-in-poly analysis using PostGIS and SQL. –  forkandwait Aug 7 '13 at 20:11
2  
@forkandwait Oh, I agree with you on PostGIS. However, I got the impression that they use a number of small scripts that they may chain up differently for each project. My goal was to get them to investigate the underlying libraries so they could choose whichever environment worked best. –  Scro Aug 7 '13 at 21:09
add comment

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!

share|improve this answer
    
YMMV, but I find object-relational libraries don't really add anything to plain old SQL. As I said in another comment, it would be most interesting to hear specifics. –  forkandwait Aug 7 '13 at 20:15
2  
I can describe a case study: a web service for generating raster inputs for a post-fire erosion model. Basically, a number of rasters need to be subset and added to one another. I selected GeoAlchemy2 (GA2) to interface with PostGIS, where the data are stored. Using GA2, I can create compact, re-useable PostGIS queries. One query creates a "burned land cover" product (a reclass of a land cover, subset). This product is needed on its own for some modeling, but is also added to another raster, a soils layer, to produce a third output product. GA2 lets me mix, match and serialize in Python. –  Arthur Aug 13 '13 at 13:30
add comment

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.

share|improve this answer
add comment

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.