I started with a NetCDF .gri and .grd raster file of the UK provided by a colleague. I clipped it in R to be only London, exported and converted to a ASC file, and then imported it to PostGIS using the following commands in R:
library(raster) uk_raster <- raster("AnnMean2011.grd") london_area <- extent(-720000.0,-630000.0,-50000.0,25000) london_raster <- crop(uk_raster, london_area) writeRaster(london_raster, filename="AnnMean2011.asc", format="ascii")
And then on the Ubuntu command line:
raster2pgsql -I -C -s 10001 -t 20x20 AnnMean2011.asc annualmean | psql -d james_traffic
I now have a raster table in PostGIS. The SRID of 10001 is the following by the way, again, provided by a colleague:
INSERT INTO spatial_ref_sys(srid, auth_name, auth_srid, proj4text) VALUES (10001,'CMAQ_Urban',10001,'+proj=lcc +a=6370000 +b=6370000 +lat_1=35 +lat_2=65 +lat_0=52 +lon_0=10 +x_0=000000 +y_0=00000');
In the same database I have a polygon file, SRID 27700, which covers London. I would like to calculate the average value within each polygon, from the raster.
I'm trying something like this, but it's not right:
select polygons.postcode, avg(st_value(joined_data.rast)) from ( select (ST_Intersection(raster.rast, 1, polygons.geom)).* from raster, polygons where ST_Intersects(raster.rast, 1, polygons.geom) ) joined_data group by polygons.postcode
How would I go about this please?
It seems to get the polygon and the raster aligned properly, I have to convert them both to WGS84 I think.