[postgis-users] Raster Points in Polygon (was Re: PostGIS Raster for Met Data)

[Michael Akinde]

Hi,

----- Original Message -----
> > I had to modify the coordinates of your raster and your polygon
> > because the 900917 SRID do not exist by default in spatial_ref_sys,
> > but this example returns the x and y coordinates (a well as the
> > point geometry and the value) of every pixels intersecting a
> > polygon.
> 
> If I understand this right, SRID 900917 is a custom coordinate system
> for the (i,j) pixel coordinates of the image? Wouldn't this mean that
> you have to have a different SRID for every tile (raster) of the
> image, since each tile starts with (1,1)?

No. We deal with meteorological data rather than data images, and apart from topography data (which can be pretty massive), none of our raw data is tiled.

A raster for us typically represents one meteorological parameter (e.g., temperature, daily precipitation, etc.) for a given time and data source. A typical database of the kind we are  working on now would hold such data for several different parameters over lengthy time periods (30-50 years * daily values * # parameters * # data sources) for about 3-5 TB of data. For a limited system we might have only one raster definition, but a typical database would probably have a handful or so.

A query using polygons as described above would, e.g., be to extract all the data points within a region (a county, for instance) and aggregate the results (typically avg, min or max) for each day or month over a thirty year period. The result is outputted in all sorts of interesting graphs interactively, so we need to do this in an effective manner. Not necessarily with sub-second response times (our users do understand that they are asking for a lot of data), but still snappily enough that people don't need to go out to lunch while waiting for their data. :-)

We already do simple polygons with our existing WDB system, but:
- The algorithm is imprecise (just something simple that was hacked together at need).
- It doesn't handle more complex polygons.
- Isn't fast enough (not optimized at all).

Thus our current interest in PostGIS raster.

Regards,

Michael Akinde