[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.

Apologies for the hassle; it's of course a local SRID of the kind used by our application (in this case +proj=ob_tran +o_proj=longlat +lon_0=-24 +o_lat_p=23.5 +a=6367470.0 +no_defs').
 
> I had to create the ST_PixelAsPoints(rast raster, band integer)
> function which is a derivate of the ST_PixelAsPolygons(rast raster,
> band integer) function available in this page:
> http://trac.osgeo.org/postgis/wiki/WKTRasterUsefulFunctions

Many, many thanks - this is pretty much exactly what I was looking for. :-)

I had hoped that there was a better "native" way of getting the points inside the polygon than to output the points and running an ST_Intersects over them. Hmm - well, at least it is easy enough to optimize ST_PixelAsPoints by constraining it to only return the points within the axis-aligned minimum bounding box of the polygon. Also, I expect simply constructing geom using the corner point and dX/dY would be a bit faster than using ST_Centroid(ST_PixelAsPolygon), so perhaps we can get a solution along these lines running at a reasonable clip.

Thanks again for pointing us at the referenced functions - this gives us something to work with.

Regards,

Michael A.