Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

share|improve this question
    
No current native way although I have pondered how to add such a feature – Nathan W Dec 31 '12 at 16:31

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.