Hi Tim,
Thanks for pointing me in the right direction.
I just discovered that the "update SQL layer..." (in the layer's context
menu) does the trick in a non-automated but convenient way.
In that specific dialog I see both the layer's filter definition (which
is a client-side filter) as well as the layer's SQL definition (which is
a server-side filter).
Thus I can also easily copy (in fact: move) the filter's definition to
the SQL definition.
Lesson learned: I shoudl have used the "Update SQL layer..." instead of
the "Filter..." from the very beginning of my project ;-)
Which brings me to a few observations regarding the user-interface:
- Both "Update SQL layer..." and "Filter..." have an almost similar
function, but are visually very much separated in the layer context menu
(even in a different visual group in this menu)
- "Update SQL layer..." seems only accessible through the layer context
menu, whereas "Filter..." is accessible through both the context menu
and the regular (main) menu (and through the Ctrl-F shortcut)
- The icon for the "Update SQL layer..." menu-entry is the same as the
icon for the DBManager, which is a bit confusing.
Combining them in one general query/filter function would be an
improvement, I guess.
Kind regards,
Gert-Jan
tim dunlevie schreef op 29-04-2022 4:35:
Hi Gert-Jan,
yes - this is done via:
QGIS....Database menu dropdown --> DB Manager --> select your PostGIS
database --> database dropdown, SQL window --> either script your
'view' here, or select the SQL query builder dialogue --> select
'Create View'
is this what you wanted?
Tim
On Thu, Apr 28, 2022 at 9:16 PM gisnederland via Qgis-user
<qgis-user@lists.osgeo.org> wrote:
Hi Tim and Raymond,
Perhaps I'd explain a little more:
The QGis database tool (as Tim suggests) converts my QGis layer
"myLayer" (= a postgis table "myTable" with a QGis filter
("myFilter"
applied to it) to a new postgis *table*
I was hoping to find an (automated!) way to convert the layer to a
new
postgis *view*, something like:
create or replace view "myView"
as select "myFilter"
from "myTable"
Kind regards,
Gert-Jan
tim dunlevie via Qgis-user schreef op 28-04-2022 14:48:
You can also use the QGIS database tool from the menu...to get to
the
SQL query gui...construct your query through that - add table, add
a
condition (where blah = blah)...then you have the ability to
create a
view in the postgis database.....rather than writing sql query
using
pgadmin etc...
On Thu, 28 Apr 2022, 8:38 pm Raymond Nijssen via Qgis-user,
<qgis-user@lists.osgeo.org> wrote:
Hi Gert-Jan,
Not sure how exactly you are filtering your data, but to create
views
you can run these queries on your PostgreSQL database using the
tool
you
like (for example DB-manager in QGIS, or DBeaver or PGAdmin).
In case this is an SQL query filter:
CREATE VIEW blabla1 AS
[your sql select query goes here]
In case it's a 'provider object filter' (in the Source tab of
your
layer
properties):
CREATE VIEW view_name AS
SELECT * FROM table_name WHERE
[your filter goes here]
Hope this helps,
Raymond
On 28-04-2022 14:09, gisnederland via Qgis-user wrote:
Hi all,
I've got a QGis (3.20) project with (amongst others) 15 layers
that all
point to 1 postgis table, but all have a different filter
applied
(in
QGis).
I'm looking for a convenient way to convert these
layers-with-filter to
views in PostGIS, in which the SQL "select"-statement matches
the
QGis
filter expression.
Any way to do this in QGis, or with other tooling?
Kind regards,
Gert-Jan
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
