This is the follow up of this other question of mine, although you don't really need to see it to understand the problem.
I am trying to figure out what would be the best (maybe most usual) way of serving large tables that have both a spatial and a time dimension.
First of all: what I have
I have two tables (not the actual that I have, but you'll get the point):
precipitaion: store precipitation data at daily (
freq = 'daily') and hourly (
freq = 'hourly') frequencies for 7 months so far (I receive regular updates).
Column | Type --------+---------------------- id | varchar date | date start | time end | time freq | varchar prec | float(4) (2,481,069 rows)
areas: store multipolygon areas for all Europe down to municipality resolution.
Column | Type --------+---------------------- id | varchar geom | geometry (162,573 rows)
What I need
I am developing an app that lets the user select a day, or a time in one day and shows in a (OpenLayers) map the areas with the amount of precipitation for that selected time.
I am using GeoServer to serve the data with CQL_FILTERs to pick the selected time (see other sections to understand how the data is built).
What I was doing
I created one table view for daily frequency precipitation and one for hourly frequency precipitation, like so:
create or replace view daily_prec as select * from (select id, "date", "start", "end", sum(prec) as prec, from precipitaion where freq = 'daily' group by "date", "start", "end", id) as prec, areas.geom where prec.id = areas.id;
daily_prec I have 4,634,430 rows, and much more for the
Serving these two views as WMS layers through GeoServer was slow, as all getMap and other requests took more than 1 sec to arrive, so...
...what I am doing now
I created materialized views so that I could create index and spatial index on the tables.
Now, the requests arrive much faster, and I am happy with the overall performance.
The problems are:
- they require a lot of disk space (a single materialized view table can take up 60GB)
- they are very slow to be created (they take about 40 min).
Is there a better approach?
Given that my data needs frequent updates (also the historical data might change as the way it is calculated might vary because we are in a testing phase), and preparing tiles probably is not the way to go (right now at least), would you recommend a different approach for such situation?
Would you recommend using a different type of database and/or service?
I am using PosgreSQL 10 and PostGIS 2.5.