3

I have a SQL-Layer that is defined by the following statement:

select b.id,
       b.description,
       b.is_event,
       geodesic_buffer(b.location_point, 3000)
from bus_stop b
where b.event_id = 7

Is there any way to add a parameter to the SQL, so that it is easier to change, like
where b.event_id = :event_id

2

I cant see a way to do it. I thought maybe QGIS variables would work but using @variablename gives a syntax error.

You could use a table to store the value:
Create a new layer with no geometry and add a single integer column to it, then add a row with the value of the event_id. If the new layer was named events and the column was name event_id then you could change the SQL of the virtual layer to read:

select b.id,
       b.description,
       b.is_event,
       geodesic_buffer(b.location_point, 3000)
from bus_stop b, events e
where b.event_id = e.event_id

Whether changing the value of event_id by editing the row is much easier than the changing the query, I'll leave that for you to decide. But it does open up the possibility of specifying multiple filter values by adding extra rows to the events layer. For example if you had three rows in the events layer with values 1,7 and 10 it would be equivalent to:

select b.id,
       b.description,
       b.is_event,
       geodesic_buffer(b.location_point, 3000)
from bus_stop b
where b.event_id IN (1, 7, 10)
  • Thanks for the answer! What I ended up doing was creating a new table called selected_event with just an id column as you suggested and removing the where statement from the original layer (bus_stop). Then I just added a new virtual layer that selects the features in the bus_stop layer with this statement: select b.* from 'bus_stop' b, selected_event se where b.event_id = se.id. – tuesday Sep 22 '19 at 11:09
1

You can create a view (in Postgres or a virtual layer) without filtering, then in QGIS add the view and include a filter

The view:

select b.id,
       b.description,
       b.is_event,
       geodesic_buffer(b.location_point, 3000)
from bus_stop b;

The filter (right click on the layer, properties, source, provider feature filter, query builder):

event_id = 7
  • I know that's possible, but what I want to do is define the event_id as a variable, so that I can filter multiple layers by it – tuesday Sep 24 '19 at 8:18

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