I have two PostGIS tables,
cities (city_code and geometry column) and
precip (city_code, date, precip). The
precip table can have more than one entry for each city since I have a precipitarion time series in it.
In order to display municipal precipitation using GeoServer, I though of 2 alternatives:
- Use a parameterized SQL View layer, joining the two tables and setting date as a parameter for the precip data. Something like:
SELECT a.geocodigo, data_ini, chuva, wkb_geometry FROM lm_municipio_a AS a JOIN dado_semana_munic AS b ON a.geocodigo = b.geocodigo WHERE data_ini = '%my_date%'
- Create a SQL View layer that has all the dates, precip and geometries and use WMS Time Dimension to filter the date. The SQL View would be something like
SELECT a.geocodigo as geocodigo, data_ini, chuva, wkb_geometry FROM dado_semana_munic AS a LEFT JOIN lm_municipio_a AS b ON a.geocodigo = b.geocodigo
Right now we are using the second option (SQL View + WMS Time dimension) and Leaflet TimeDimension plugin in order to create some animated precip maps. But I wondered if using a parameterized SQL View layer would be better. I ask this because in the first option, the SQLView is a smaller table with nrows = the number of municipalities. The second option has a larger table, with nrows = the number of observations (I have 20 years of weekly data).