[postgis-users] Strategies for rapid application development using PostGIS

[William Furnass]

I wish to use PostGIS/PostgreSQL for data mining exercise (MSc
project) rather than developing fully-fledged GIS app, the goal being
to determine the causes of water quality issues in a water
distribution network.  I need to:
 - filter and pre-process several datasets (each containing ~10^5
records) describing water quality issue events and possible cause
events
 - associate the events in different cause and effect datasets with
edges/nodes in my distribution network model to produce an ‘integrated
network model’
 - traverse the network model, giving consideration to the network
hydraulics, in an attempt to associate one or more possible causes
with each water quality issue.

I believe I can achieve most of this using ‘standard’ SQL, PostGIS
functions and possibly some recursive PL/pgSQL functions to traverse
my network structures and then visually verify that the region of
influence determined for each water quality event is sane using
Quantum GIS.  However, I would very much like to automate aspects of
the dataset processing, integrated model construction and model
analysis to allow me to more easily perform sensitivity analysis on
certain parameters, to reduce the amount of ugly adhoc snippets of
(say) Python that I need to write and to perhaps allow the analysis
parameters to be set and the results viewed from a QGIS plugin.

I’ve considered baking lots of SQL into pyodbc cursor.execute(sql)
calls but think that such an approach could get rather messy.  I’ve
also taken a look at the SQLAlchemy/GeoAlchemy ORM framework, which
initially seems to be rather too heavy for my needs and would require
me to spend much time finding ways to rewrite complex queries
involving many joins and much nesting.  I should also mention that I
have little time to devise an analysis tool and generate some results
and that I haven’t done much programming (OOP or otherwise) for a
number of years.

How do other list subscribers manage data analysis projects (as
opposed to full-on GIS application development projects) using
PostGIS?  How far into writing ORM code or integrating an existing ORM
framework have people gone when undertaking similar tasks?