I like PG VIEWS

Views are perhaps a lesser known ‘thing’ of Postgres. For me (a phrase that I dislike) Views are part and parcel of how I use Postgres on a daily basis. In this post I wanted to share some simple and complex Views and Materialized Views in PostGIS.

If you are new to Views or want to get some additional background, then I would suggest having a read on postgresqltutorial.com

Lets start with a simple view using the OS Open-Data ZoomStack data.

Let me know in the comments if you would like to see a post on the various ways of getting data into Postgres/PostGIS.

First, as always, lets have a look at the raw data before moving forward. Below is what the OS ZoomStack woodland layer looks like in QGIS, and inspecting the attribute table we can see that the data is divided into Local, Regional and National types.

QGIS Categorized symbology for the Woodland layer.

Woodland layers from OS ZoomStack viewed in QGIS.

Lets create a view of the woodland layer, for just the type ‘local’

--raw SQL
create view os_zoom_stack.woodland_local_view as
select 
a.id, 
a.type, 
a.geom  
from  os_zoom_stack.woodland a 
where a.type = 'Local'

How I rock Views?

I always give a view a table name appended by _view. This helps me distigish tables from views when I look at the data in QGIS.

I always use a table alias ((a) in this example) after the table name. If the View needs to be expanded to include data from another table, in the form of a join, then it’s really easy to see what column belongs to what table.

Q.What is the big deal with using a view when I could just load the Woodland layer into QGIS and apply some filters/styling?

A.Simply, this view means less mouse clicks in QGIS. All I need do is drag and drop the view layer from the Browser panel onto the map, that’s it. No filters, and styling can be saved back to the layer in PostGIS. So I only ever need to style once.

Let's make this interesting…

Lets create a view using a Buffer. In the below example a 10k buffer from central London.

create view os_zoom_stack.woodland_local_central_london_10km_view as
SELECT a.id, a.type, a.geom
FROM os_zoom_stack.woodland a
WHERE a.type = 'Local'
AND ST_Within( a.geom, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 
10000)
);

Loading the view into QGIS, results in the following.

This view is perhaps more impressive than the last one, and saves us more mouse clicks in QGIS. Using such a view means that I am not having to run any geoprocessing tools to clip or buffer a layer or even export a dedicated file. #timesaver

Let's make this even more interesting!

In the below, we’ve combines two ZoomStack layers (woodland and buildings) and created an on the fly buffer layer.

create view os_zoom_stack.local_central_london_10km_view as
SELECT a.type,  a.geom
FROM os_zoom_stack.woodland a
WHERE  a.type = 'Local' AND ST_Within(a.geom, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000))
UNION ALL 
SELECT  'buffer' as type, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000) AS geom
UNION ALL 
SELECT 'building' as type,  a.geom
FROM os_zoom_stack.district_buildings a
WHERE ST_Within(a.geom, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000));

When loaded into QGIS and styled based on the column type you get the below result. A single layer, containing three layers! This bit of SQL has saved so much time and effort! No cropping of layers, no clipping, no geoprocessing. Admittedly the buffer could have been created better, as in being smoother, but for the purposes of this post — it will do!

This have been achieved by using the UNION ALL syntax. In general, the UNION ALL operator is used to combine the results of two or more SELECT statements into a single result set, including duplicate rows. You have to be careful though, as each select statement must have the same number of columns and have the same names. In the below SQL we can see that for each select statement we set columns type and geom. In the case of the buffer layer we’ve declared a values of ‘buffer’ for a column alias of type. In the buildings ZoomStack data there is no column or attribute value with/for building, so, like above we’ve created an alias column of type and given it a value of ‘building’

---Woodland Layer
SELECT 
a.type,  
a.geom
FROM os_zoom_stack.woodland a
WHERE  a.type = 'Local' 
AND ST_Within(a.geom, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000))

--Buffer Layer
SELECT  
'buffer' as type, 
ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000) AS geom

--Buildings Layer
SELECT 
'building' as type,  
a.geom
FROM os_zoom_stack.district_buildings a
WHERE ST_Within(a.geom, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000));

Where next?

We could then naturally expand this out so that it contains more layers. But this is where we met the snag of views. They (views) become slower and more PostGIS resource hungry the more data and more functions that you put into them. Views are dynamic, so read the raw, master table everytime they are called. So in the above example, which contains c.34500 features, each time QGIS pans, refreshes etc, then this view is calling two tables, doing a query and buffer against them, and then recreating a buffer.

The upshot is that things get slower. What is the answer? Well in this specific example of using a UNION ALL, that would be a materialized view. Which would look something like:

create materialized view os_zoom_stack.local_central_london_10km_materialsed_view as
SELECT a.type,  a.geom
FROM os_zoom_stack.woodland a
WHERE  a.type = 'Local' AND ST_Within(a.geom, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000))
UNION ALL 
SELECT  'buffer' as type, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000) AS geom
UNION ALL 
SELECT 'building' as type,  a.geom
FROM os_zoom_stack.district_buildings a
WHERE ST_Within(a.geom, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000));

A materialized view effectly takes the data and makes its own standalone table view (of sorts). Its still a view, so relies on the master tables existing but isnt dymanic in the way a view is — this is what makes it faster and less resource intensive. If the master data changed, then the materialized view would not change, where as in a view it would! If the underlying data does change, then to refresh a materialized view we need to run

REFRESH MATERIALIZED VIEW os_zoom_stack.local_central_london_10km_materialsed_view;

While a materalized view, in the case of our UNION ALL examples, is faster than a view, it can be made even faster when you add an index to it. However, the example materalized view don’t include a field that can easily be used to index against, so we need to create one.

First Drop the existing materialized view, then create the materialized view again, but this time we are adding in an id column, and then create the index. Boom! A very fast table.

DROP MATERIALIZED VIEW IF EXISTS os_zoom_stack.local_central_london_10km_materialsed_view;

create materialized view os_zoom_stack.local_central_london_10km_materialsed_view as
SELECT a.id, a.type,  a.geom
FROM os_zoom_stack.woodland a
WHERE  a.type = 'Local' AND ST_Within(a.geom, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000))
UNION ALL 
SELECT  1 as id, 'buffer' as type, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000) AS geom
UNION ALL 
SELECT a.id,'building' as type,  a.geom
FROM os_zoom_stack.district_buildings a
WHERE ST_Within(a.geom, ST_Buffer(ST_SetSRID(ST_Point(532483,180805), 27700), 10000));

CREATE INDEX idx_london_materialized_view ON os_zoom_stack.local_central_london_10km_materialsed_view (id);

I rather enjoyed putting this post together, so if you would like to see more of them, then please do leave a comment below.