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.
Group Bynor for a
Sum, as all fields are either in the
group byor in the filtering
whereclause. That being said, having the proper indexes and eventually tuning Postgres configuration are likely the solution.
sum(prec) as preccolumn in the query would have required a GROUP BY statement). Anyway, thanks for confirming the solution, I appreciate your help.