[Qgis-user] create PostGIS view from QGis layer

[gisnederland]

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 at 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 at 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 at 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 at 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 at 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 at lists.osgeo.org
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user