Re: [Qgis-user] create PostGIS view from QGis layer
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: QGISDatabase 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 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, 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
Re: [Qgis-user] create PostGIS view from QGis layer
Hi Gert-Jan, yes - this is done via: QGISDatabase 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, > > 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
Re: [Qgis-user] create PostGIS view from QGis layer
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, 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
Re: [Qgis-user] create PostGIS view from QGis layer
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
Re: [Qgis-user] create PostGIS view from QGis layer
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