[postgis-users] PostGIS Raster for Met Data

[Pierre Racine]

> In our case, we very rarely deal with databases that contain grids of just one
> SRID. Just for the basic weather forecast, we have to deal with three: a detailed
> high resolution grid covering Northern Europe, a slightly lower resolution grid
> for Europe, and a global grid (the weather forecast is then picked from
> whichever of the three data sets is "best"). For stuff like our ocean forecasts, we
> have about a dozen different grids (the Norwegian coastline poses some
> interesting issues for weather forecasters).

For sure doing overlay gis operations like st_intersection is not possible when the srid of the two geometries is different. In this case you would have to reproject (ST_Transform) the geometry so it fit the srid of right grid. But if your application is able to decide which grid it must query then it should also be able to decide which tale it must query and hence putting all your grids in different table should not be a problem.

> > > Does anyone have any experience with generating sub-tables to handle
> > > this problem? I'm particularly interested in performance, of course.
> > I don't.
> 
> Due to the number of grids I am somewhat leery of setting up (and tearing
> down) multiple tables on an operational database (data grids change over time).
> Keeping it in one table would presumably be better, assuming it is practicable.
> 
> > > - Is there an easy (and efficient) way to run a query to get all of
> > > the coordinate  points (x,y index) from within a raster that are
> >> contained in a polygon? That
> > > would be quite interesting for us if we still need to build our own
> > > solution on top of the existing functionality for performance
> > > purposes. Haven't looked at the source code yet.
> >
> > You mean the raster values at those points? The points are contained
> > in a polygon, the rasters are contained in the polygon or you speak
> > about the vertexes of a polygon?
> 
> No, the former is handled quite nicely by ST_INTERSECT, if I've understood
> correctly. I was thinking of a function that returns the x, y offsets; i.e., 0 0 for
> the origin point, 1 0 for the next point along the x axis, and so on. Since we have
> our value data stored in files outside the database, the x,y offsets allow us to
> retrieve data points very rapidly. This is how we have it implemented in WDB
> today, but with our own very limited function for identifying those offsets.

You can easily get the x,y upperleft corner of a pixel with the Raster2WorldcoordX(0,0) and Raster2WorldcoordY(1,0) functions. Is that what you need? 

But normally, if you store your rasters in the database, you don't have to do that anymore to get the raster value associated with a point. You can just do:

SELECT ST_Value(rast, ST_MakePoint(x,y))
FROM yourgridtable
WHERE ST_Intersects(rast, ST_MakePoint(x,y))

Pierre