How to run custom queries on vector layers and display results?

Question

Is there a way to display the result of a query in QGIS?

For instance :

• To get the total number of inhabitants : SELECT SUM( inhabitants ) FROM countries

• To get the total density : SELECT SUM(inhabitants)/SUM($area) FROM countries

• To get the maximal human density : SELECT MAX( inhabitants/$area) FROM countries

And so on...

It looks it is not possible natively, but is there a plugin ? Or a way to do it in python (using the console) ? If so, is there a way to restrict the query to the current selection ?

I've found out about the plugin "Statist", which almost does the job, but which does not allow custom queries...

---

Related questions:

• A SQL console in QGIS

Answer 1

If your data is in a PostGIS or Spatialite database you can use the DB Manager plugin to run queries like that. You can also use it to generate layers from spatial queries if your query result includes a unique integer field for IDs and a geometry column.

If your data is in shapefiles you can use sqlite's virtual table function. From the command prompt go to the directory your shapefiles are stored in and start the sqlite3 console. Use

SELECT load_extension('libspatialite.so');

if you're on a linux or OS X machine or

SELECT load_extension('libspatialite.dll');

if you're using Windows.

Once spatialite is enabled use

CREATE VIRTUAL TABLE <table_name> USING VirtualShape(<shapefile_name>, 'UTF-8', <srid>);

to make your virtual table. From there you can run any read-only spatial query you like on your shapefile data.

Answer 2

You can run every vaild SQL-statement by setting up a QgsDataSourceURI (with your database connection) and parse the statement in quotes to "aTable" parameter instead of a table name. Notice: geometry-column and primary key have to be set! In your case, try something like:

uri = QgsDataSourceURI()
uri.setConnection('hostname', 'port', 'dbName', 'username', 'password')

sqlQuery = '(SELECT country_id, geom FROM countries)'

uri.setDataSource(aSchema='',
                  aTable=sqlQuery,
                  aGeometryColumn='geom',
                  aSql='',
                  aKeyColumn='country_id')

You can even make JOINS in the SQL-query... If you want to use your aggregate functions, you have to group/aggregate geometry info somehow...