We have around 2 million species observation records we want to display to users in an OpenLayers-based front-end. I created a VIEW in our PostgreSQL database that summarizes these records by species, by township (an arbitrary set of 3,000 polygons covering our state, for those not familiar), and indicates the number of records and most recent record, per species, per township. I would like to display these to end users, color-coded in a manner like the one shown in the image. The tabular data needed is simply a township ID, species ID, count of occurrences, and year of most recent occurrence. The spatial data comprises the polygons representing townships.
Since the geometry for the township boundaries are the same in each case, I would prefer to have two materialized views that I serve via GeoServer and join together using OpenLayers:
1) A township view containing only the geometry and township ID, published as WFS
2) A tabular observation record view containing the township ID, species ID, and the summary info (count of records, most recent records).
The reason for going with a materialized view is display speed -- querying to summarize data in this way across 2,000,000 observations for 6,000 species takes a significant amount of time. So, my plan is to materialize a view of this query, and refresh on a weekly basis. But, since it is a materialized view, we'd essentially be storing a (large) duplicate set of township geometry for all 6,000 species.
Instead, I'd hoped that I could have a materialized view of species observation summary data that I could join to the materialized view of township geometry, so that we're not storing or conveying duplicate geometry.
I saw this question, but it doesn't quite answer my question, and I thought it was possible that something has changed in OpenLayers since then that might make this possible.
Is this something that is possible to do, without a significant amount of hacking?