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.

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?

Many thanks in advance for your help and sharing experience,

John

share|improve this question
    
Are the buffers always the same distance or based on user input? Does you buffer stored procedure operate against data submitted from python or the original table? Also it would be useful to have some idea of what you are trying to achieve. –  Matthew Snape Oct 16 '12 at 12:02
    
Matthew - I am trying to create drivetime polygons. I know something about concave polygons but wanted to try it this way, primarily for better accuracy. The polygons are 200m buffers of MultiLinestrings (that is: roads). I am currently playing with the idea of pre-buffering all of the roads in the database, but I still need to merge them. \n # –  John Steedman Oct 16 '12 at 13:34
    
More generally I'm looking to settle on an architecture that balances quite intensive geo-processing with a responsive web user interface: not as quick as Google of course, but recognizable in terms of today's user expectations! This is for a few power users. –  John Steedman Oct 16 '12 at 13:40
add comment

2 Answers

up vote 3 down vote accepted

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:

enter image description here

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks, David. That sounds like a good policy which I've been drifting towards myself. I'll look into GeoServer for image tiles. I've used python/mapnik in the past for this. –  John Steedman Oct 16 '12 at 16:06
    
The other thing I've just found out is that returning rows via a stored procedure is very, (very, very) slow. –  John Steedman Oct 16 '12 at 16:08
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.