Quick Tip: Using coalesce to check for NULL or zero

Here is a quick tip that you can use in QGIS expressions, T-SQL, or even PostgresSQL.

Normally if you have a column that you need query on to find all the NULL or zeros you have to do something like this:


Well that isn’t too bad. Sure yeah it’s fine for one column but what if you have three and you need to check them all together


That is pretty long and gets hard to read pretty quick.

To cut this down we can use the coalesce function – T-SQL, PostgresSQL, QGIS Expression. The coalesce function returns the first non-NULL value out of an expression, or list of values. So if you do something like this:

coalesce(NULL, "A", 0)

You will get “A” because the first value is NULL. The function will just evaluate each value/expression until something turns up that isn’t NULL.

Using that logic we can replace the above function with the following:

coalesce(COLA, 0) = 0 AND coalesce(COLB, 0) = 0 AND coalesce(COLC, 0) = 0

To me that is a lot clearer and readable.

QGIS gets Oracle support!

Seems this is a good day for QGIS Oracle users. According this commit made by Jürgen QGIS now has built-in Oracle support. Win!

Native Oracle support can now see QGIS being opened up to a wider user base yet again. A large user base normally means more people willing to sponsor awesome new features and bug fixes. Having seen the growth in the user base from having native MS SQL Server 2008+ support I can imagine what it will be like with Oracle.

The list of formats QGIS can open and edit is getting larger and larger with each release. Is there a future for vendor lock in? I can’t see it.

Standard disclaimer about latest dev build and new features :)

New QGIS PDF and HTML manuals

A quick update from the QGIS documentation team today on the mailing list. The QGIS 1.8 manual is now available in HTML and PDF form.

HTML manual can be found at:

PDF manual at:

This has been part of an ongoing effort from the documentation team since before the 1.8 release to bring our all our documentation into reStructedText rather then LaTeX. Moving to reStructedText allows quicker updates and a larger range of final output formats.

I would like to thank everyone who has been involved in this process as I know what a grueling process updating documentation can be.

Community notice

Just remember you don’t have to be a programmer to contribute to an open source project. If you think you could contribute to the updating of the QGIS documentation please contact the team on the mailing list.

Using a QGIS spatial index to speed up your code

If you need to do any kind of spatial operations in QGIS using Python or C++ you really want them to be as fast a possible in order reduce the amount of time you make the user wait. Lets take the simple scenario of a recent question that was asked on gis.stackexchange; Summing up values of neighboring polygons?.

I went for the SQL approach as I like how quick SQL can express what you need to do, however SQL is not the only way to skin a cat as spatialthoughts has shown in his blog post. Here Ujaval has used Python to find the neighboring polygons of each feature. Running the script on a small dataset yields results in reasonable time however running it on a larger dataset can take a long time.

In order to check if a feature touches another you need to have two features to compare against each other. The simple way to do this is to create two loops where you check each feature against every other feature. Here is a quick code example of just that.

layer = qgis.utils.iface.activeLayer()
# Select all features along with their attributes
allAttrs = layer.pendingAllAttributesList()
# Get all the features to start
allfeatures = {feature.id(): feature for (feature) in layer}

def noindex():
        for feature in allfeatures.values():
                for f in allfeatures.values():
                        touches = f.geometry().touches(feature.geometry())
                        # It doesn't matter if we don't return anything it's just an example

import timeit
print "Without Index: %s seconds " % timeit.timeit(noindex,number=1)

So the above code is pretty simple, just loop each feature and check against every other feature. No worries. No worries at least until you run this on a large dataset then I think you can see the issue here. Running the above code on a layer with around 28000 features takes 1912.41 seconds – that’s 31 minutes. Holy crap!

Note: We put all the features of the layer into a dictionary as it will make lookup quicker in the later index example.

How can we speed up the above code? Lets take a gander at QgsSpatialIndex

QgsSpatialIndex to rule them all

QgsSpatialIndex is a wrapper around the open source SpatailIndex lib and uses a RTree for an index method. If you don’t know what an index is you can think of it like the index in a book – a pointer to a location in the book rather then having to scan every page to find a word.

There isn’t much to using QgsSpatialIndex just insert each QgsFeature and it handles the rest, when we need something out we just use the intersects method to return any features inside an area.

layer = qgis.utils.iface.activeLayer()
# Select all features along with their attributes
allAttrs = layer.pendingAllAttributesList()
# Get all the features to start
allfeatures = {feature.id(): feature for (feature) in layer}

def withindex():
        # Build the spatial index for faster lookup.
        index = QgsSpatialIndex()
        for f in allfeatures.values():

        # Loop each feature in the layer again and get only the features that are going to touch.
        for feature in allfeatures.values():
          # Get the ids of all the features in the index that are within
          # the bounding box of the current feature because these are the ones
          # that will be touching.
          ids = index.intersects(feature.geometry().boundingBox())
          for id in ids:
            f = allfeatures[id]
            if f == feature: continue
            touches = f.geometry().touches(feature.geometry())
            # It doesn't matter if we don't return anything it's just an example

import timeit
print "With Index: %s seconds " % timeit.timeit(withindex,number=1)

Running this code on our 28000 feature layer returns the results in 10 seconds. 31 minutes down to 10 seconds by just using a spatial index. Nice!

So the next time you need to do some spatial operations remember to use the handy QgsSpatialIndex in order to speed up your code. If you don’t want to use QgsSpatialIndex, or need some more flexiblity, you could even use the Python RTree module.

Full code

layer = qgis.utils.iface.activeLayer()

# Select all features along with their attributes
allAttrs = layer.pendingAllAttributesList()
# Get all the features to start
allfeatures = {feature.id(): feature for (feature) in layer}

def noindex():
	for feature in allfeatures.values():
		for f in allfeatures.values():
			touches = f.geometry().touches(feature.geometry())
			# It doesn't matter if we don't return anything it's just an example

def withindex():
	# Build the spatial index for faster lookup.
	index = QgsSpatialIndex()
	map(index.insertFeature, allfeatures.values())

	# Loop each feature in the layer again and get only the features that are going to touch.
	for feature in allfeatures.values():
	  ids = index.intersects(feature.geometry().boundingBox())
	  for id in ids:
	    f = allfeatures[id]
	    touches = f.geometry().touches(feature.geometry())
	    # It doesn't matter if we don't return anything it's just an example

import timeit
print "With Index: %s seconds " % timeit.timeit(withindex,number=1)
print "Without Index: %s seconds " % timeit.timeit(noindex,number=1)

Installing Python setuptools into OSGeo4W Python

The easiest way install Python libraries is to use easy_install and pip.  easy_install and pip are package managers for Python. From the easy_install page:

Easy Install is a python module (easy_install) bundled with setuptools that lets you automatically download, build, install, and manage Python packages.

and from the pip page:

pip is a tool for installing and managing Python packages, such as those found in the Python Package Index. It’s a replacement for easy_install.

To get easy_install you need to install Python setuptools and you are good to go. Sounds easy!  However the setuptools installer assumes that you have the normal standalone Python installed which writes it’s install location to the registry, and when you run the installer it will say that it can’t find Python on the system.  What the!?

If you have installed QGIS, or any other tool from the OSGeo4W install, you will see that OSGeo4W bundles its own version of Python in: C:\OSGeo4W\apps\python27. This is the Python that is used when calling python in the OSGeo4W shell.  It seems someone on the OSGeo wiki has made a bootstrapped installer for setuptools that will install setuptools and easy_install into the  C:\OSGeo4W\apps\python27 folder for you.

Steps to take

  • Download ez_setup.py
  • Run python ez_setup.py in your OSGeo4W shell
  • Done!

To install a package with easy_install just use:

easy_install {packagename}

I wanted to have bottle and flask installed:

easy_install bottle

which gives you something like:

Searching for bottle
Reading http://pypi.python.org/simple/bottle/
Reading http://bottle.paws.de/
Reading http://github.com/defnull/bottle
Reading http://bottlepy.org/
Best match: bottle 0.11.4
Downloading http://pypi.python.org/packages/source/b/bottle/bottle-0.11.4.tar.gz#md5=f767c340de0b7c9581917c48e609479b
Processing bottle-0.11.4.tar.gz
Running bottle-0.11.4\setup.py -q bdist_egg –dist-dir c:\users\woo\appdata\local\temp\easy_install-5b4qq6\bottle-0.11.4\egg-dist-tmp-q2yd68
zip_safe flag not set; analyzing archive contents…
bottle: module references __file__
bottle: module references __path__
Adding bottle 0.11.4 to easy-install.pth file
Installing bottle.py script to C:\OSGeo4W\apps\Python27\Scripts
Installed c:\osgeo4w\apps\python27\lib\site-packages\bottle-0.11.4-py2.7.egg
Processing dependencies for bottle
Finished processing dependencies for bottle

Install pip


Most of the time any Python packages that are needed by your OSGeo4W tools are bundled in the installer and can be downloaded using the OSGeo4W installer, however there have been cases when I wanted to install a non OSGeo4W package into my setup by using easy_install or pip. Like bottle and flask in the example above.

Five new awesomely awesome QGIS features – Round 2

As QGIS is such a fast moving project I have decided to make this a regular blog post in order to highlight some new features added to QGIS. If you haven’t already, don’t forget to check out round one.

Remember that some of these features may still only be new which might change between now and the next official released version. With that out of the way lets get listing.

Atlas integration

If you are a regular user of QGIS Python plugins, and who isn’t, then you would have used the awesome Atlas plugin developed by Vincent Picavet. This great tool can be used to generate mapbooks, or an atlas as some people like to say, using a coverage layer and a print composer. What makes this even more awesome is that it is now built into the print composer.

Atlas composer intergration

The builtin atlas function also gives you the ability to use an expression to do runtime text replacement, including access to all the fields on the coverage layer.  The coverage layer doesn’t even have to be a region layer, it can be a simple point layer, or even a line layer.   You can see the result of me running the atlas generation from the above example here

Big thanks to Oslandia for integrating this great feature, and the companies sponsoring the work.

New Python console

This new addition comes from the great work that Salvatore Larosa has been doing to add a better Python console to QGIS.

The new Python console includes attribute auto complete, syntax highlighting, better copy and paste, uploading to codepad, the ability to run code from a file, etc.  You don’t realise how much difference there is until you go back to using the old one in version 1.8.

New Python console

Tabbed and groups in builtin forms

One of the things I really loved about QGIS, coming from MapInfo, was the builtin forms.  Just having the ability to enter data using controls like combo boxes, calendar widgets, etc makes you one step closer to having better data.   This feature is the exact reason I setup a 67 year old for kerb data collection.

As good as they are the builtin forms have an issue of ending up with as a big scrolling list with lots of fields; also the lack of  the ability to group or put fields on tabs in the UI meant you had to create a custom form.  Well not any more.

There is now a combo box on the Fields tab that allows you to build a generated form but also add tabs and group boxes.  You can even have the same field shown more then once on the form, handy for something like an ID field that you would like to show on each tab.

With this new ability the builtin forms can get me 95% of the way for data entry jobs, the other 5% I just make a custom form – but that is very rare.


Sextante is a great and powerful analytical framework that has been added to the core of QGIS thanks to Victor Olaya.  This is not a feature that I use a lot but this is only due to most of my work being in development and not analysis, however that doesn’t mean that it’s not a really cool feature.

One of the greatest things about the Sextante toolbox is that it allows you to integrate other great open source tools like GRASS, SAGA, R, OTB, etc, right into your QGIS workflow and view the results in the canvas. It even includes a modeller so that you can build a connected diagram of all the bits of your process, even if it crosses between programs.

The toolbox

For me what is even better is that you can use Sextante in your plugins or custom Python code.  Sextante has a Python interface – well the whole thing is written in Python – that you can use to run a Sextante supported algorithm.

import sextante
outputs_0=sextante.runalg("grass:v.crossbones", /file, 0, ,, 1, 2, 3, 4, 1=3.0,2=8.0,5=6.0,8=6.0,11=6.0,14=6.0, None)
outputs_1=sextante.runalg("grass:v.delaunay", outputs_0['output'], True, True, None)
outputs_2=sextante.runalg("grass:v.dissolve", outputs_0['output'], , None)

Victor has created a blog to cover some Sextante recipes at QGIS-SEXTANTE cookbook/. There are also some really cool example of Sextante in use at:

Massive amount of composer additions

This last feature, or rather feature set,  comes from the sponsorship and support of the World BankAustralia-Indonesia Facility for Disaster Reduction, Geoscience Australia and the GFDRR.  Most of this work was done to aid in the development of a new QGIS plugin called inaSAFE, which has also received some great praise

“(InaSAFE) is very beneficial for all of us. It’s a good example of our partnership.”
Dr Susilo Bambang Yudhoyono – President of Indonesia

Some of the improvements include:

  • A new loadFromTemplate method, in the API, that can do text replacement in the QGIS composer template.
  • Better support for EPSG:4326 scale bars
  • Multipage in one composer
  • Automatic overview frame in map frame
  • HTML frame
  • Zebra style grid frame border
  • More control of grid labels. Inside, outside, disabled
  • etc, etc

These are great additions to the QGIS composer and I have already used the overview frame feature along with the new atlas integration  to make some quick nice looking map books. \

A huge thanks to the World BankAustralia-Indonesia Facility for Disaster ReductionGeoscience Australia and the GFDRR, and all the developers included.

You can see some of the output that InaSAFE generates using some of these new features at http://quake.linfiniti.com/

P.S The World Bank also sponsored the new raster Save As.. ability just like we have for vector layers.


FOSS4G-AU in summary

Last Thursday and Friday was our first local Australian FOSS4G event which was hosted at the CSRIO building in Brisbane.  Very big thanks to CSRIO for hosting the event.  The venue was setup perfectly for  hosting an event like this, including dual projectors for presenting, video calls over to Perth, etc.

The first day was done using a un-conference style of event. This is the first time I’ve been to a un-conference and I liked the format a lot.   Once everyone was there on the first morning we collected ideas from people and everyone voted on which ones they would like to see.  After we had picked enough topics Shaun and I made a program for the day and we started.

Topics included:

Me presenting QMap

The second day was a code sprint.  I worked on converting a MapBasic scripts from one of the guys to QGIS, and Jody enlisted the others to help check the headers of the GeoServer project so that it can finally pass OSGeo incubation.

Overall I think it was a very successful event.  I would like to make these a yearly event if we can, provided that we have people to talk, or projects to work on.

More information about up coming OSGeo events in Australia and New Zealand can be found at http://www.meetup.com/osgeo-aust-nz/

Australian FOSS4G is here

So that came around quick.  Seems it is November already.

Tomorrow is the FOSS4G-Au a locally organized un-conference.

How do I register?

If you would still like to come – which you should – you can register here (it’s free!) http://www.meetup.com/osgeo-aust-nz/events/83965312/ but you must RSVP on the meetup page in order to get building access and internet access.

Where is it at? (Directions)

The un-conference is held at The Queensland Centre for Advanced Technologies in Brisbane. Kudos to CSIRO for allowing us to use their facilities.

More details on un-conference?

Jody Garnett has also done a post on the un-conference and what this kind of conference style is like.  You can find more details here: http://how2map.blogspot.com.au/2012/11/foss4g-au-tomorrow.html 

The venue


User defined expression functions for QGIS

Ever since I added expression based labels, including the new expression builder UI, something that I always wanted to add is the ability to define custom user defined functions in Python (or C++) and use them in an expression. The expression engine is used for labels, rule based rendering, layer actions, field calculator, and atlas composer tags.  Thanks to the all the awesome work on the expression engine by Martin all this cool stuff is now possible.

Today I pushed a commit into master that adds the ability to define a function in Python (or C++), register it in the expression engine, then use it anywhere expressions are used.

The good stuff

Lets take a use case from Ujaval Gandhi and his example of counting vertices for each feature.

First we need to import the new qgsfunction decorator function from qgis.utils. The qgsfunction decorator will take a normal Python function, wrap it up in the class used to define a function, and register it in the engine.

So what does an empty function look like:

from qgis.utils import qgsfunction
from qgis.core import QGis

@qgsfunction(0, "Python")
def vertices(values, feature, parent):

@qgsfunction(0, "Python") means we are defining a new vertices function that takes 0 args and lives in the “python” group in the expression builder UI. Any custom function must take (values, feature, parent) as python args. values is a list of QVariants passed into the function, feature is the current QgsFeature, and parent is expression engine node (you use this to raise errors).

Lets stick some more logic in there:

from qgis.utils import qgsfunction
from qgis.core import QGis

@qgsfunction(0, "Python")
def vertices(values, feature, parent):
		Returns the number of vertices for a features geometry
	count = None
	geom = feature.geometry()
	if geom is None: return None
	if geom.type() == QGis.Polygon:
		count = 0
		if geom.isMultipart():
		  polygons = geom.asMultiPolygon()
		  polygons = [ geom.asPolygon() ]
		for polygon in polygons:
		  for ring in polygon:
		    count += len(ring)
	return count

Pretty simple. Get the geometry from the feature, check if it’s a polygon, if it is then count the number of vertices and return that number.

Now that we have that all done we can save it into a file in our .qgis/python folder, lets call it userfunctions.py (note you don’t have to save it here, anywhere that QGIS can find it will do.  Anywhere on PATH)

Lets open QGIS and run import userfunctions.py:

Importing functions from userfunctions.py

Now open the label properties for the layer:

The new function shown in the expression builder

Nice! Notice also that the function doc string is used as the function help. How cool is that.  You can also see the $ sign in front of the function, this is because any functions that take no args are considered special and use the $ sign as a convention, this is all automatic when the function is registered.

And the result is:

The label using the new function

You can even use it in the rule based rendering:

Rule rendering using new function



  • You must unregister a function once you are finished with it using QgsExpression.unregisterFunction(name). This mainly applies to plugins where the user might unload your plugin and the code is no longer available. In the above example we could import userfunctions and never unregister because we plan on using it for the whole session.
  • You can’t override the built-in methods.

FOSS4G-AU un-conference

Another quick announcement for today. The local FOSS4G-AU chapter is holding un-conference and code sprint in Brisbane on the 15 and 16th of November 2012.

Ben Caradoc-Davies made the announcement on the mailing list the other day:

The Free and Open Source Software for Geospatial Australia (FOSS4G-AU)
2012 Unconference and Code Sprint will be held at CSIRO’s Queensland
Centre for Advanced Technologies (QCAT), Pullenvale QLD.
15 November: Unconference
16 November: Code Sprint
This is a participant-organised event: please add your unconference and code sprint topic suggestions to the wiki. Organisers are welcome; please speak up.

See here for details: http://wiki.osgeo.org/wiki/FOSS4G-AU_2012

RSVP essential for building access. Please sign up here:

Email: aust-nz@lists.osgeo.org (sign up here http://lists.osgeo.org/mailman/listinfo/aust-na)

Kind regards,
Ben Caradoc-Davies
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Being part of osgeo-aust-nz and the local Australian open source GIS movement it’s great to see events like this being organized.

I’ll be there, most likely pimping some QGIS stuff.

Hope to see you there.

Just remember that RSVP is essential to get into the building.