I wish to be able to query PostGIS and Spatialite tables and add vectors from individual rows whose attributes meet criteria as multiple layers under a group or sub group instead of a single layer for a table or query.

The aim is to be able to list rows of a table under a group as 'individual layers' (representing 'individual rows') where each layer can be be turned on and off but also to which normal functionality in the layer panel applies when right clicking each layer - for example : 1. zoom to layer (ie to the extent of an individual vector) 2. copy or duplicate the layer 3. rename layer 4. set layer scale visibility 5. each individual layer can be exported

  • Perfect! thanks so much for that! That just what I am after. Have a great Easter Weekend. Cheers David
    – dawrightau
    Apr 12 '20 at 9:44

There is a DB Manager standard plug-in in QGIS to make the trick.

  1. Check in the Manage and Install Plugins... dialogue if DB Manager is turned on.
  2. Make a connection to your PostGIS/Spatialite database in the Data Source Manager (probably you have already it if you can add full table layers)
  3. From the Database menu select DB Manager
  4. In the DB Manager dialogue on the left open database in providers tree enter image description here
  5. Select SQL Window (the second icon in the toolbar)
  6. Enter your SQL query in the upper part and press Execute
  7. Click in the Load as new layer checkbox
  8. Set the unique values column (Primary key) and geometry column and press Load button
  9. You'll get a QueryLayer with the SQL selected features in your QGIS project

Here is an example on a PostGIS database (it is the same for SpatiaLite): enter image description here


and the result enter image description here

I added symbology (Categorized) to the layer for each unique id, then you can turn them on/off individually even without using DB Manager. enter image description here

  • Hi Zoltan, For clarification I edited / expanded my original question. While the approach of classifying the symbology does enable individual vectors / features to be turned on and off, it doesn't actually add the 'individual rows' as 'individual layers'. The approach you advised allows only 2 functions - visibility of each feature to be toggled on / off and editing of each symbol. It does not allow full layer control (see edited question points 1 to 5). Is there a way to query / add each individual table row as an individual layer over which full layer functionality can be applied?
    – dawrightau
    Apr 15 '20 at 1:32
  • I suppose to have hundreds of layers instead of one is not convenient. About your requirements: 1) you can zoom to selection if you selected the feature (view menu & tool) 2) nop 3) rename symbology group instead 4) you can turn on/off thematic groups also 5) after selection export selected features. AFAIK there is no direct function in QGIS for your requirements. 1) you can write a pgplsql function to generate a view for each row in your table (but what if the row is deleted? you need another trigger function to delete view, too) 2) you can write Python to generate filters to layer.
    – Zoltan
    Apr 15 '20 at 6:20
  • Zoltan thanks for clarifying "AFAIK" there is no direct QGIS function. I was trying to keep my question simple but may need to post a different question. The heart of my issue is datasets exported from an ESRI MDB file to a KML file. Each folder contains multiple individual polygons (ie hundreds). There are multiple attributes and values for each polygon. I have tried several approaches (including KML Tools plugin) to bring into QGIS / PostGis and access each polygon as a layer so table attributes are accessible but they are embedded in an html table in the description field. Any thoughts?
    – dawrightau
    Apr 15 '20 at 8:25
  • Can you show a sample? I don't understand what is that html table.
    – Zoltan
    Apr 15 '20 at 12:29
  • Hi Zoltan The html (ie <tr><td>) table is embedded in the kml schema for each polygon. I finally created a workflow to extract ~60 attributes from each html table into a shapefile. I could then group like polygons and export to several shapefiles. This allowed more sense of the hundreds of polygons, especially once layer order of different groups could be applied and the attribute table used to search and zoom. So I don't think I can achieve what I asked as functionality AFAIK doesn't exist. Ideally functionality would allow loading table rows as layers as with styled / categorised features.
    – dawrightau
    Apr 16 '20 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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