3

I have many grids in shapefile format. Adding alphanumeric labels to them can be done through using a virtual layer SQL query as found in this thread: Creating GRID with customized labels in QGIS, which works well with some small tweaks for me.

I would like to script creating these automatically, which will save me a lot of time, but I am running into problems. Here is my code:

from qgis.core import (QgsProject, QgsLayoutExporter, QgsApplication)

#get a list of all the layer names
root = QgsProject.instance().layerTreeRoot()
mygroup = root.findGroup("Site Locations")
layer_list= mygroup.findLayers()
layer_list = [layer.name() for layer in mygroup.children()]

#list the layers
layers = QgsProject.instance().mapLayers()

#looping code making a virtual layer for each layer
i = 0
for layer in layers:
    site = layer_list[i]
    vlayer = QgsVectorLayer("?query= select *, char(r+64) || ROW_NUMBER() OVER(PARTITION BY r) as newID from (SELECT *,DENSE_RANK() OVER (ORDER BY round( ST_minY(geometry)/0.000002) desc) as r FROM layer ORDER BY round(ST_minY(geometry)/0.000002)  DESC, round( ST_minX(geometry)/0.000002)  ASC)", site+"_vl", "virtual")
    
    QgsProject.instance().addMapLayer(vlayer)
    i = i + 1

This code will create virtual layers, which are all named properly - but each of the files is empty and missing their source. This is the original source URI that is shown when clicking to repair the source:

OVER(PARTITION BY r) as newID from (SELECT *,DENSE_RANK() OVER (ORDER
BY round( ST_minY(geometry)/0.000002) desc) as r FROM layer ORDER BY
round(ST_minY(geometry)/0.000002)  DESC, round(
ST_minX(geometry)/0.000002)  ASC)```

I assume that the code is using the query instead of the shapefile as the source of the layer - how do I go about setting the proper source layer while running the query?

1 Answer 1

3

You are not listing layer names, and you dont update the SQL query for each row so it is trying to query a layer named layer.

Try inserting the correct layer name using an f string for each iteration:

...
layer = [layer.name() for layer in  QgsProject.instance().mapLayers().values()]
    
for layer in layers:
    site = layer_list[i]
    vlayer = QgsVectorLayer(f"?query= select *, char(r+64) || ROW_NUMBER() OVER(PARTITION BY r) as newID from (SELECT *,DENSE_RANK() OVER (ORDER BY round( ST_minY(geometry)/0.000002) desc) as r FROM {layer} ORDER BY round(ST_minY(geometry)/0.000002)  DESC, round( ST_minX(geometry)/0.000002)  ASC)", site+"_vl", "virtual")
    ...
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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