Joining Tables with SQL using pyQGIS

One of the features that makes MapInfo’s MapBasic very useful is the ability to join a normal tabular data with a spatial data using plain SQL statements within an application. This is useful because it makes it possible to link numerous attribute tables (census, crime, marketing, etc. data) with just 1 table that contains the spatial information (map data).

But what a lot of people do not know is that this feature is also possible in QGIS with pyQGIS , probably because this is not very well documented (it is not in the pyQGIS cookbook ).

So to illustrate how joining a table that contains attributes with a table that has spatial table, we can start by opening first a PostGIS table and display it in a QGIS map.

The spatial table contains the Japanese city polygons and has the following PostgreSQL information:

- hostname         : localhost
- port number      : 5432
- database         : statistics
- username         : postgres
- password         :
- schema           : public
- table name       : japan_ver52
- spatial column   : the_geom
- unique id column : gid
- QGIS layer name  : Census

In the Python Console:

To access Quantum GIS environment from this console use qgis.utils.iface object (instance of QgisInterface class).

>>> uri = QgsDataSourceURI()
>>> uri.setConnection("localhost","5432","statistics","postgres","")
>>> uri.setDataSource("public","japan_ver52","the_geom","","gid")
>>> vlayer = QgsVectorLayer(uri.uri(),"Census","postgres")
>>> QgsMapLayerRegistry.instance().addMapLayer(vlayer)

Now to link japan_ver52 table with the “2005 Japanese Census” information found in table census2005, a SQL string statement is added that will join the 2 tables via a common attribute column in jcode. Note that the SQL string must be between parentheses "( )", in order for the pyQGIS API to recognise it as a SQL string instead of a table name.

And again in the Python Console:

To access Quantum GIS environment from this console use qgis.utils.iface object (instance of QgisInterface class).

>>> sql = "(select a.gid,a.the_geom,b.population,b.pop_male,b.pop_female,b.household from japan_ver52 a,census2005 b where a.jcode = b.jcode)"
>>> uri = QgsDataSourceURI()
>>> uri.setConnection("localhost","5432","statistics","postgres","")
>>> uri.setDataSource("",sql,"the_geom","","gid")
>>> vlayer = QgsVectorLayer(uri.uri(),"Census","postgres")
>>> QgsMapLayerRegistry.instance().addMapLayer(vlayer)

This will result in a QGIS layer that has the table definition below.

So as seen above, joining tables together using just a SQL statement in pyQGIS is pretty much straightforward. But what makes this quite exciting is that all the PostGIS functions can also be used (ST_BUFFER, ST_DISTANCE, etc.) as well in the SQL statements. This gives numerous possibilities when creating pyQGIS applications.

And ofcourse, this method can also be used with [pgRouting][].