In our search engine for renewables, we are running a bunch of PostGIS queries, e.g. to determine the wind speed at a user’s location. In our alpha search engine, we noticed that the PostGIS requests caused the longest query time of all 25 queries.

While experimenting with PostGIS, we discovered a nice time saver: In the alpha version, we translated the CSV data of measurement points (lat, lng, wind speed) into a shapefile with geometry points representing every measurement in the UK. The result looked like below (Southern England with a zoom on the Isle of Wight):

We extracted the data_point for a given location with the raw SQL statement in Django/Python:

We used the raw SQL statement in Django, since it provided the fastest query response. But it was still almost 300ms. Not enough!

So we looked for an alternative solution based on polygon intersect. Instead of looking for the closest data point to the geographic coordinate of the user’s location, we are now searching for the polygon which contains the user’s coordinate. Thanks to the WKT technology, intersect lookups run amazingly fast - in our case in 3ms.

If you have a shapefile with the point geometries (you can use QGIS to convert your CVS measurement data into shapefiles), you can use the QGIS geoprocessing function Voronoi Polygon (Vector | Geometry Tools | Voronoi Polygons) to convert the points into polygons for each measurement point/value. The result is a fancy British spider web:

PostGIS queries based on the polygon intersects can be done as follows:

data_point = WindData.objects.get(geom__intersects = location)

Since we do not have to sort the locations of the data points to find the closest data point anymore, the intersect query runs much faster. The WKT technology allows a hash comparison to determine if a point is part of a polygon or not. This amazing development provides the great speed increase.

The performance comparison in the Django shell says it all:

Nice reduction of almost 99% in the query time.

Before creating a branch, doing a quick fix or messing with the models, we need to make sure our database is the same as the production one.

To do so we have a single bash (or fab) script which does:

This can all be summarized in a fab command which takes into account different OS (linux, mac), different absolute directories, and different naming of files (see fabfile.py in gist)

Ok, so we have one of those (Bfx), Urgent but simple bugfix. 

If our database is out of date we can,

$ fab db_cp_prod2dev

in other words “database copy from production to development” (see gist above).  And because it’s just a quick fix, we’ll stay on the master branch:

$ gb
  dev_some_feature
* master

Note that I will use a few git aliases to save my typing, in particular I have a ~/.bash_aliases with:

So, work, work, work, fix, fix, fix.

$ ga -u
$ gcm "quick fix, the margin in the CSS had gone cookoo, fixed .home-box class"
$ gp

Then send to testing

$ fab update_testing

and similarly send to production when ready

$ fab update_prod

Here’s a graphical overview of the whole thing:

And that’s it for bug-fixing alone.  Next part, developing a small feature in a branch.

These are some wind turbines powering an Antarctic Research Station.  And that’s also about as South as you can get in your quest for renewable energy.  But today we’re writing about migrating your databases South!  Or the following scenario:

You create an übercool insightful Django model.  You put some data on it, make some templates, … looks good!  And then you realize it wasn’t as cool as you thought.  You forgot to add … say the minimum operating temperature for your wind turbines.  Bummer!  Enter South ( intelligent schema and data migration).

 manage.py schemamigration app_name --initial

if you have not done syncdb yet:

 manage.py migrate app_name

if you have already done syncdb:

 manage.py migrate app_name --fake

Then edit your models.py and add all the fields you forgot to add,

Then create the new migration file and finally migrate the database:

 manage.py schemamigration app_name --auto 

 manage.py migrate app_name 

see also [djangopro] for a good explanation.

Also to check the migrations you have done so far simply run:

 manage.py migrate --list