[postgis-users] gridded data

[Pierre Racine]

> I want to use daily weather variables that are in a grid for USA.  I
> would want store the daily variables ( a new grid each day) so I can
> have a database that will allow the client to send a point lat/lon and
> then return a calculation based on a few days worth of two or three
> variables.
> 
> Do I store each day (or variable) as a new table?  How does a grid get
> stored in postgres?  Is this a job for PostGIS Raster (though I do not
> want to produce images, just queries at a point)?

Hi Steve,

PostGIS with the raster extension is just the right tool to do that. I would recommend to store each weather variable in a separate table.

To tile (with the raster2pgsal.py -k option) or not to tile your rasters depends on their size. Generally it is better to tile them.

Assuming your rasters are in a geographic coordinate system, you can then query the temperature for a lat/long like this (if they are tiled):

SELECT ST_Value(rast, ST_MakePoint(lat, long) as temp
FROM yourrastertable
WHERE ST_Intersects(rast, ST_MakePoint(lat, long))

Just omit the WHERE clause if you don't tile them when loading.

Try also:

SELECT ST_Value(rast, ST_MakePoint(lat, long) as temp
FROM yourrastertable
WHERE ST_DWithin(rast, ST_MakePoint(lat, long), 0)

Which might be faster...

Please refer to the PostGIS Raster tutorials:

http://trac.osgeo.org/postgis/wiki/WKTRaster

Pierre