May 8, 2009

Leverage the power of PostGIS in QGIS with PgQuery

As many of you may already know I have created a Python plugin for QGIS called PgQuery which gives easy access to powerful PostGIS geoqueries within the QGIS environment. Well it's finally here and you can download it by navigating to the downloads section @

Today I would like to go over how to install and use this tool with the hope that it will become a great help to all those wanting to leverage the power of OSGIS in their organization.

First method: Installing this package really couldn't be any easier. After downloading the archive file unzip it to the user specific Python Plugin directory (C:\Users\
\.qgis\python\plugins). Currently the plug-in doesn't support installation to the main QGIS Python plugins directory.

Second method: You can also install PgQuery by opening QGIS and selecting the Fetch Plugins item under the Plugins menu. In the resulting dialog select the PgQuery for QGIS entry and select the Install/upgrade plugin button.

After doing either one of these methods you can activate the plugin using the QGIS Plugin Manager (PM). It will then show in PM as PgQuery for QGIS (Version 0.1.1. You can just select this plugin and you will see a new entry for PgQuery under plugins as well as an icon on the tool bar.

Running PgQuery
To run the app either select PgQuery for QGIS from the plugins menu or the icon from the tool bar. When the application opens you will see a number of controls on the main form:

  1. An Output Schema pull down to allow you to select the schema of any output tables

  2. An Output Table box which is self explanitory

  3. A Query box which allows you to create queries from scratch, add query components from some of the other controls, or select a query template from the Query Template pull down.

  4. A Query Template pull down as described in 3. When you select a query template it builds the query for you from the pre loaded queries or from a query that you have built and saved to the default location.

  5. A Functions list box which allows you to double click to add functions to the query at your current cursor location.

  6. A Tables tree control that shows a list of all the tables available in the currently loaded PostgreSQL database. If the table is spatial the geometry type will show in the column beside the Table column. You can also double click the tables to add them to the query.

  7. Finaly the Geometry Column pull down that allows you to select the geometry column from any of the currently selected tables.

You will also find a number of menu items at the top.

  1. Under the File menu there are sub menus to allow you to:

    • Open an existing project file that will bring you back to a previous state of SQL query development
    • A Save and Save As menus to save the appropriate project files
    • And, of course, and exit menu to close the application entirely.
  2. You will find a Query menu containing sub menus that give you access to:

    • The Run menu which runs the current query as shown in the query box on the currently active database
    • The Save menu to allow the current query to be saved to a Query Template file for later use.
    • The Clear menu that allows all query statements to be cleared to start over from scratch.
    • And a Check Box that allows the user to select whether or not they want to add the resulting spatial table to be added as a geometry data set to the QGIS map view.

  3. The Database menu has a listing of all available PostgreSQL database connections in QGIS that the user can select to make current.
  4. Finally the Help menu gives access to:
    • Basic Help with PgQuery for QGIS
    • And a basic About PgQuery statement
This application requires that you have PostgreSQL 8.2 or later installed with PostGIS 1.3.2 or later. You can fairly easily build a good application stack using the PostgreSQL Stack Builder that comes with 8.3. This version comes bundled with the most recent release of PostGIS.

You will also need to have PyQt and Python for QGIS installed. All of these can be installed using the online stack builder that comes with the OSGeo4W installation. Make sure to read the INSTALL.txt file that comes bundled with the plug-in.

You will also find a help document in the "docs" folder of the pgquery_plugin directory. this help doc is called help.html which can be opened in any browser and gives you some guidance for using this tool.

Try it out and see how powerful PostGIS can be in your enterprise GIS environment. This enterprise database application has hundreds of spatial functions that allow you to perform myriad geoprocessing and analytical tasks. For more assiatance on PostGIS you can go to and look at the documentation available there.

I hope you find this tool very useful and that it brings enterprise GIS into the reach of ANY organization regardless of size of budget.


A. Maracini said...

Thanks! Looking forward to trying it out!

Edward Mac Gillavry said...

In order to install PgQuery for QGIS 1.1 Pan following the second method you describe, you first go to Repositories tab in the QGIS Python Plugin Installer menu. Simply click on the button labelled Add 3rd party repositories. Coming back to the Plugins tab, you will find the PgQuery plugin listed!

andrewcrice said...

Thank you, excellent tool. This is a huge productivity boost vs. manual view creation methods and fumbling through the postGIS documentation.

For Mac OSX, I had to make several changes to to fix path separation slashes. Maybe there's a more universal way to do this, but I don't really know python. Here's the diff:

HAL:~ arice$ diff /Users/arice/.qgis/python/plugins/pgquery_plugin/ /Users/arice/.qgis/python/plugins/pgquery_plugin/
< pathname = str(pathname) + str("\.qgis//python/plugins\pgquery_plugin\sql")
> pathname = str(pathname) + str("/.qgis/python/plugins/pgquery_plugin/sql")
< filename = pathname + "\\" + fName
> filename = pathname + "/" + fName
< pathname = pathname + "\.qgis//python/plugins\pgquery_plugin\pgfunctions.txt"
> pathname = pathname + "/.qgis/python/plugins/pgquery_plugin/pgfunctions.txt"
< pathname = pathname + "\.qgis//python/plugins\pgquery_plugin\sql"
> pathname = pathname + "/.qgis/python/plugins/pgquery_plugin/sql"
< pathname = pathname + "\.qgis//python/plugins\pgquery_plugin\sql"
> pathname = pathname + "/.qgis/python/plugins/pgquery_plugin/sql"
< pathname = str(pathname) + str("\.qgis//python/plugins\pgquery_plugin\sql")
< filename = pathname + "\\" + text + ".sql"
> pathname = str(pathname) + str("/.qgis/python/plugins/pgquery_plugin/sql")
> filename = pathname + "/" + text + ".sql"
HAL:~ arice$

Anonymous said...

Hello, I got an error message while trying to run the plugin. Any idea ? Thanks !

Traceback (most recent call last): File "/home/user/.qgis//python/plugins/pgquery_plugin/", line 41, in run self.dlg = QueryWindow(True) File "/home/user/.qgis//python/plugins pgquery_plugin/", line 36, in __init__ self.setupUi()
File "/home/user/.qgis//python/plugins/pgquery_plugin/", line 749, in setupUi self.txtQueryString = QPlainTextEdit(self.centralwidget) NameError: global name 'QPlainTextEdit' is not defined
Python :2.5.2 (r252:60911, Jul 22 2009, 15:52:25) [GCC 4.2.4 (Ubuntu 4.2.4-1ubuntu3)]QGIS 1.3.0

Kevin Matin said...

Excellent blog here! There is a good amount of useful information to be seen here! Thanks for sharing your knowledge!
enterprise application development