#postgresql 9

[renooble-blog]

Reduce your PostgreSQL/PostGIS query time by 99%

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.

[renooble-blog]

git workflow - part 2

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

which does,

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.