I am writing a data intensive web application that is delivered through apache. My question is about how to best arrange processing given that there are multiple options.
The database contains about 3 million rows and the prototype currently runs as follows:
User clicks on a point on the OpenLayers window
The coordinate is sent as an AJAX request through to a python function on the server
Currently my application is stateless
Python's psycopg2 is used to call a pgsql stored procedure and a largish set of WKT values (and a data field) are returned back to the python module
The data field is used to categorize the WKT records in python as follows: all WKT values are categorized into one of 5 groups. About 1% of the WKT values are actually modified.
The five sets/groups of WKT are buffered to create five distinct polygons. I currently call a stored procedure in the database to do this. This in turn just uses ST_BUFFER. (I've considered using Shapely but am not sure there will be a performance advantage since the GEOS library is used in either case...)
Finally the 5 WKT text values are wrapped up in a JSON string and sent back to OpenLayers for rendering as five layers.
I am finding that the bottlenecks are the initial spatial search and the final buffering stage.
I guess the Question is:
Is there a better way to arrange things? For example, should ALL of the data processing be done in PostgreSQL (e.g. with cursors) and is would this be a good thing in terms of maintenance and performance? Would it be better to use a tile server to avoid passing long WKT strings over to the web client? How would you address it?