A materialized view is a database object that stores the result of a precomputed query. Unlike regular views, which are virtual and don't store data themselves, materialized views physically store the query result, allowing for faster access to the data. Materialized views are especially useful in scenarios where the underlying data is relatively stable, and the cost of running complex queries in real-time is high.
In this case, we have a geospatial query that we want to optimize with a materialized view.
This example can be found in the United 2023 database, in the data schema.
In the view we build a set of locations. Each location (point) geographically falls into a:
Each point is geospatially joined to the division, district, township, county layer. This way, we can find the fids of each division, district, township, and county of each location.
Here is the sql behind what is in the materialized view: data._list_locations_mv
WITH division1 AS (
SELECT
gis_division1.ogc_fid,
gis_division1.wkb_geometry
FROM data.gis_division1
),
districts AS (
SELECT *
FROM data.gis_division1_districts
),
hucs AS (
SELECT *
FROM data.gis_division1_hucs
),
counties AS (
SELECT *
FROM data.gis_division1_counties
),
townships AS (
SELECT *
FROM data.gis_division1_townships
),
facilities AS (
SELECT
lp.location_ndx,
NULL::character varying AS huc12,
l.location_id,
l.location_name,
l.location_display_label,
lt.location_type_ndx,
l.assoc_parameter_ndx,
l.latitude,
l.longitude,
NULL::text AS huc8,
wb.waterbody_ndx,
l.bmk_reach_ndx,
l.display_order,
NULL::character varying AS huc10,
NULL::character varying(120) AS huc10_name,
NULL::character varying AS huc12_name,
lt.location_type_desc,
st_setsrid(st_point(l.longitude, l.latitude), 4326) AS loc_geometry,
lp.location_ndx_lookup,
l.legend_color,
l.exclude_from_map,
l.assoc_facility_ndx
FROM data.akas_locations_pre lp
JOIN data.list_locations l USING (location_ndx)
LEFT JOIN data._list_location_types lt ON l.location_type_ndx = ANY (lt.location_type_ndx_lookup)
LEFT JOIN data._list_waterbodies wb ON l.waterbody_ndx = ANY (wb.waterbody_ndx_lookup)
WHERE l.assoc_parameter_ndx IS NOT NULL
ORDER BY lp.location_ndx
),
public_facilities AS (
SELECT
b.location_ndx,
b.huc12,
b.location_id,
b.location_name,
b.location_display_label,
b.location_type_ndx,
b.assoc_parameter_ndx,
b.latitude,
b.longitude,
b.huc8,
b.waterbody_ndx,
b.bmk_reach_ndx,
b.display_order,
b.huc10,
b.huc10_name,
b.huc12_name,
b.location_type_desc,
st_setsrid(st_point(b.longitude, b.latitude), 4326) AS loc_geometry,
b.location_ndx_lookup,
b.legend_color,
b.exclude_from_map,
b.assoc_facility_ndx
FROM copublic._list_locations_mv b
JOIN data.list_waterbodies USING (waterbody_ndx)
WHERE list_waterbodies.ui_map_include
), all_facilities AS (
SELECT districts.ogc_fid AS district_ogc_fid,
hucs.ogc_fid AS huc_ogc_fid,
counties.ogc_fid AS county_fid,
townships.ogc_fid AS township_fid,
facilities.location_ndx,
facilities.huc12,
facilities.location_id,
facilities.location_name,
facilities.location_display_label,
facilities.location_type_ndx,
facilities.assoc_parameter_ndx,
facilities.latitude,
facilities.longitude,
facilities.huc8,
facilities.waterbody_ndx,
facilities.bmk_reach_ndx,
facilities.display_order,
facilities.huc10,
facilities.huc10_name,
facilities.huc12_name,
facilities.location_type_desc,
facilities.loc_geometry,
facilities.location_ndx_lookup,
facilities.legend_color,
facilities.exclude_from_map,
facilities.assoc_facility_ndx
FROM facilities
LEFT JOIN districts ON st_intersects(districts.wkb_geometry, facilities.loc_geometry)
LEFT JOIN hucs ON st_intersects(hucs.wkb_geometry, facilities.loc_geometry)
LEFT JOIN counties ON st_intersects(counties.wkb_geometry, facilities.loc_geometry)
LEFT JOIN townships ON st_intersects(townships.wkb_geometry, facilities.loc_geometry)
UNION ALL
SELECT
districts.ogc_fid AS district_ogc_fid,
hucs.ogc_fid AS huc_ogc_fid,
counties.ogc_fid AS county_fid,
townships.ogc_fid AS township_fid,
public_facilities.location_ndx,
public_facilities.huc12,
public_facilities.location_id,
public_facilities.location_name,
public_facilities.location_display_label,
public_facilities.location_type_ndx,
public_facilities.assoc_parameter_ndx,
public_facilities.latitude,
public_facilities.longitude,
public_facilities.huc8,
public_facilities.waterbody_ndx,
public_facilities.bmk_reach_ndx,
public_facilities.display_order,
public_facilities.huc10,
public_facilities.huc10_name,
public_facilities.huc12_name,
public_facilities.location_type_desc,
public_facilities.loc_geometry,
public_facilities.location_ndx_lookup,
public_facilities.legend_color,
public_facilities.exclude_from_map,
public_facilities.assoc_facility_ndx
FROM public_facilities
LEFT JOIN districts ON st_intersects(districts.wkb_geometry, public_facilities.loc_geometry)
LEFT JOIN hucs ON st_intersects(hucs.wkb_geometry, public_facilities.loc_geometry)
LEFT JOIN counties ON st_intersects(counties.wkb_geometry, public_facilities.loc_geometry)
LEFT JOIN townships ON st_intersects(townships.wkb_geometry, public_facilities.loc_geometry)
),
graph_flow_daily AS (
SELECT DISTINCT g1.location_ndx
FROM ui.graph_flow_data_daily g1
),
graph_stage_daily AS (
SELECT DISTINCT g2.location_ndx
FROM ui.graph_stage_data_daily g2
),
graph_water_daily AS (
SELECT DISTINCT g3.location_ndx
FROM ui.graph_waterlevel_data_daily g3
)
SELECT
all_facilities.*,
CASE
WHEN gfd.location_ndx IS NOT NULL THEN true
ELSE false
END AS has_flow_graph,
CASE
WHEN gsd.location_ndx IS NOT NULL THEN true
ELSE false
END AS has_stage_graph,
CASE
WHEN gwd.location_ndx IS NOT NULL THEN true
ELSE false
END AS has_waterlevel_graph
FROM all_facilities
LEFT JOIN graph_flow_daily gfd on gfd.location_ndx = all_facilities.location_ndx
LEFT JOIN graph_stage_daily gsd on gsd.location_ndx = all_facilities.location_ndx
LEFT JOIN graph_water_daily gwd on gwd.location_ndx = all_facilities.location_ndx
The view result in QGIS. Notice that each point has a county, township, huc, and district id through the geospatial join…
The materialized view is fast because it is not doing the geospatial query each time. It is returning a flat, fast result from a hidden table under the hood.
But, when data in the tables that make up the view change, we need to refresh the materialized view to pick up the changes and repopulate. The data.list_locations table is one of the tables in the view that, when it is changed, we want the data._list_locations_mv to refresh, so we use a trigger to cause the refresh of the materialized view.
The trigger function it runs on update, insert, delete on the data.list_locations table is:
data.refresh_list_locations_mv()