5

I have a virtual layer in QGIS with a query on a csv. The csv gets updated regulary. I need the max/min attributes in the virtual layer to be updated as well. The virtual layer is used as a title in print composer (displayed as attribute table)

The query looks like SELECT "Events between " as from, min(fromdate) as fromdate, " and " as to, max(todate) as todate from "Events_Happening" WHERE invalid = 0 (Events_Happening is the .csv)

How can I achieve this without recreating the virtual layer by hand every time the csv is updated? (Update on QGIS startup would be enough)

5

I would probably create a macro so that when you open your project, your query can be run immediately. You can create one from the toolbar:

Project > Project Properties > Macros

Then use something like:

from qgis.core import QgsMapLayerRegistry
from qgis.utils import iface

def openProject():
    name = 'virtual_layer'
    query = 'SELECT "Events between " as from, min(fromdate) as fromdate, " and " as to, max(todate) as todate from "Events_Happening" WHERE invalid = 0'
    vLayer = QgsMapLayerRegistry.instance().mapLayersByName(name)[0]
    vLayer.setDataSource("?query={}".format(query), vLayer.name(), "virtual")
    vLayer.triggerRepaint()

def saveProject():
    pass

def closeProject():
    pass

Macro

Make sure to save your project. Now the next time you load your project (you don't need to restart QGIS), it should hopefully update the virtual layer.

5

This method uses a bit of python and the QGIS project openProject() method:

  1. Open blank QGIS and select Project (menu)>Project Properties>Macros tab and enable macro
  2. Under the openProject() method, create a csv virtual layer (see):

    uri = "/some/path/file.csv?delimiter=%s&xField=%s&yField=%s" % (";", "x", "y")

    vlayer = QgsVectorLayer(uri, "layer name you like", "delimitedtext")

  3. Add vector layer to canvas:

    QgsMapLayerRegistry.instance().addMapLayer(vlayer)

  4. Set sql subset expression using setSubsetString() method, see QGIS Filter layer by expression

  5. Finally save project file (you may also need to enable macros within the QGIS Settings>Options section)

Each time you open this project it should re-create the csv virtual layer and apply the sql filter.

  • Pretty much the same train of thought :) – Joseph Jan 9 '18 at 13:49

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.