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.

enter image description here

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?

  • Why aren't you joining the data in the MV and serving it all up? I'm not understanding why you need 2 separate services... Feb 16, 2017 at 22:46
  • There are 6,000 species, times 2,000 townships, so 18,000,000 polygons that would be stored in the materialized view (but only 3,000 unique polygons). I tried joining the materialized township table to the materialized observation data view in a non-materialized view (so the duplicative geometry isn't actually stored), but it is pretty slow this way. Feb 16, 2017 at 23:07
  • 1
    what about building another view that completes what you want OpenLayers to do? that is, combining the pre-built geometry with the pre-built table and serving that up? Feb 16, 2017 at 23:08
  • That is what I was describing in my final sentence in the previous comment. I joined the two mat views together in a standard view, and then tried querying it. It is slower this way compared to having the geometry denormalized in the observation mat view, but it might still be workable. I probably won't know until I've got all the data loaded in. At this point I'm just testing on a handful of species. Feb 16, 2017 at 23:16


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.