Best design for Open Source Python / PostGIS prototype

Question

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.

I have at my disposal OpenLayers/JQuery/Javascript, PostGIS/Postgresql (with pgsql), python/psycopg2, php.

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?

Answer 1

Buffering bottleneck

When using ST_Buffer you can reduce the complexity of the resulting shape by adding a lower num_seg_quarter_circle option. This should reduce the amount of processing when buffering, and in subsequent operations.

From the PostGIS documentation:

Generally in PostGIS you will get better performance if you run queries against existing properly indexed tables. This gives you easy access to several optimisations (such as clustering). Consider processing the 1% that changes separately, and merging the two at the end.

Answer 2

Not thinking at all about architecture, for all Web mapping applications, you want to do as much of the processing ahead of time. This means that if you can, buffers should be pre-computed, all of your data should be in the output SRS, etc. Obviously, some data and calculations need to be dynamic.

I suggest that beyond Python, you look at MapServer and Geoserver to do the calculations and produce the output. Both of them could produce image tiles or GeoJSON output. Both applications can use PostGIS as a back end.