check if some crucial operations work within postgis

[ghost]

check if you can run the following ops using postgis functionality:

1. select ( lat, lon, var(t, lat, lon) ) of tiles that intersect with some rectangle + time t is fixed (dragging on leaflet/D3 map)
2. select ( lat, lon, var(t, lat, lon) ) with (lat, lon) in some region + time t is fixed (on a region- not tile-basis as in 1)
3. compute average of var(t, lat, lon) over (lat,lon) within some polygon + time t is fixed (spatial average at some time) aka Zonal Statistics
4. compute average of var(t, lat, lon) over t in [t_0, t_1] + (lat, lon) within some polygon (temporal average within some region) aka Map Algebra needed here

write down the SQL queries for these ops.

also note any additional netcdf metadata you need to make this ops possible / make them more user-friendly (by e.g. abstracting out the band number and use year/week, etc instead)

[ghost]

1. SELECT val, ST_AsText(geom) FROM (SELECT (ST_PixelAsCentroids(rast, 1)).* FROM netcdf_data WHERE ST_Intersects(rast, ST_Polygon(ST_GeomFromText('LINESTRING(-91 -11, -90 -11, -90 -10, -91 -10, -91 -11)'), 4326))) foo;
2. select ST_Clip(rast, 1, ST_Polygon(ST_GeomFromText('LINESTRING(-91 -11, -90 -11, -90 -10, -91 -10, -91 -11)'), 4326), TRUE) from netcdf_data;
3. select ST_SummaryStats(ST_Union(ST_Clip(rast, 4, ST_Polygon(ST_GeomFromText('LINESTRING(-180 -90, 180 -90, 180 90, -180 90, -180 -90)'), 4326), true))) from netcdf_data;
4. select ST_MapAlgebra(ARRAY[ROW(rast, 1), ROW(rast, 2), ROW(rast, 3), ROW(rast, 4)]::rastbandarg[], 'st_stddev4ma(double precision[], int[], text[])'::regprocedure) from (select ST_Union(ST_Clip(rast, ARRAY[1,2, 3], ST_Polygon(ST_GeomFromText('LINESTRING(-180 -90, 180 -90, 180 90, -180 90, -180 -90)'), 4326))) as rast from netcdf_data) as foo;

the last one seems to have a bug because for mean + stddev it rounds down to integer instead of keeping decimal precision, sent a mail to postgis user list about that.

also, we need more tables :

• table to hold counties, states, countries, general regions as geoms to intersect them with rasters in netcdf_data (for spatial aggregation)
• table to hold just the time variables of all netcdfs with a mapping from date => band number in order to hide the band number from the user
• since each rast field in netcdf_data is for a single netcdf variable within a specific netcdf we need an ID there that let's us associate a rast to a (netcdf_ID, variable_ID). that is why i suggest we have a table for (proper) netcdf variables with an ID column that can be joined with the ID column on netcdf_data, let's call this table netcdfs_vars. now this table itself can be joined with netcdf_meta to associate a variable to its netcdf file. so to sum up: we'd have 3 tables here : netcdf_meta (currently there) , netcdfs_vars(ID of var, ID of netcdf it belongs to), netcdf_data (currently there but would contain an ID column which is the ID of the variable the rast field has data for). so from rast we can look at its ID which brings us to the right variable in netcdfs_vars where there is another ID which brings us to the right netcdf file in netcdf_meta, so we can associate a rast to a variable and that variable in turn to a netcdf.

overall this is looking very good and we are able to run spatial / temporal selection & aggregation with the functionality postgis already provides by doing the above queries. @njmattes @ricardobarroslourenco would be glad for feedback here to tell me how i can make the above queries even more convenient for you in part. for developing the frontend, most importantly if these queries output something you can work with conveniently within python (we are going to run these queries using psycopg2 directly).

[ghost]

queries work and are written down in #23 with new schema for grid_data though that has a time column + only stores 1 timeframe in a rast.