Overview:
This short article gives users a brief guide on creating VIEWS in PostGIS, in addition to referencing additional learning.
What is a VIEW?
In short, A PostgreSQL/PostGIS view is a virtual table created from a SQL query. It doesn't store data itself but presents data from one or more tables as if it were a table. In PostGIS, a view can include spatial data like geometries, allowing you to query and visualize complex geospatial results without duplicating data. It's useful for simplifying access to frequently used queries or combining multiple spatial datasets.
Before you create a VIEW?
You need a SQL editor and database permissions. DBeaver, PGAdmin or the Database manager in QGIS all have SQL editors to create a VIEW. Before creating a VIEW, make sure that you are familiar with SQL editors, have some understanding of WHERE clauses and more importantly you have the correct database permissions to create and view TABLES and VIEWS.
SQL examples
The below gives an example of the structure of a VIEW.
create view schema.table as
select
columns,
geometry_column,
from
schema.table
where
column_x = 'abc';
The below gives an example of a VIEW using a generic bin dataset that contains a wide range of data but cherry picking just a few of the columns, to include just the type of bin, its status, install date and geometry WHERE the bin type is Litter.
create view green_space.litter_bins_view as
select
"type",
status,
date_installed,
geometry
from
green_space.bins
where
type = 'Litter';
Related Articles:
Related Learning :