Creating VIEWS in PostGIS
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 :
Related Articles
Database Best Practices
This short article is designed to help you better understand how to get the most out of your data in both XMAP and QGIS by applying simple but effective 'best practice' principles to your PostGIS database. The golden rules: Schema names, table names ...
Overview of Views
Summary: This article covers the concept of Hub Views and how they relate to the source table of data. If you are not familiar with Postgres views, then we would recommend having a read of this article, but as part of the Hub implementation we will ...
Adding a PostGIS layer
Overview: This quick how do guide walks you through the process of adding a new PostGIS table into XMAP, create columns and put the new layer into a collection. Steps to creating a new layer: Administration >> Data >> Layers In the Layers section, ...
Database Manager
Overview The Database Manager is made available if your organisation has 1 or more dedicated (private) PostgreSQL databases linked to it. It exposes administration functionality for you to manage your database at a high level without the need to ...
Publishing Layers from a Remote Database
Overview XMAP supports connections to any PostGIS database of version 9.6 or greater. This database can be hosted and managed by us or you. If you have a remote database you can use this to connect to many software applications to improve access to ...