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)

QGIS Map view

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.

QGIS Table view

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][].


Comments