[postgis-users] Point Statistics from polygons? Do I need plpgsql for this?

[collin]

I have 2 tables: one a polygon grid of 10 meter squares and the other a 
table of points (table definitions below).

I would like to update the grid table with a bunch of statistics from 
the points for each grid square.  However, I am uncertain whether I can 
do this in SQL or if I have to do PL/PGSQL for this.

lreturn, freturn are 2d point geometries. lz and fz are elevation for 
the points. I am using postgresql 8.0beta5, postgis 1.0cvs (10/15), 
proj4.4, geos 2.0.1.

I am hoping to do something like update each row of the grid10m_box1x1 
polygon table with info on the unfiltered_box1x1 points.

UPDATE grid10m_box1x1
SET lzmin = min(unfiltered_box1x1.lz)
WHERE grid10m_box1x1.the_geom && unfiltered_box1x1.lreturn

This obviously doesn't work, since there is no mechanism to use 1 
polygon at a time or group by polygons. Any ideas would be appreciated!

Thanks,
Collin


Table "public.grid10m_box1x1"
    Column    |       Type       |   Modifiers
-------------+------------------+------------------
  gid         | integer          | not null default
  the_geom    | geometry         |		(POLYGON 2D)
  lzmin       | double precision |
  lzmax       | double precision |
  fzmin       | double precision |
  fzmax       | double precision |
  zdiffmin    | double precision |
  zdiffmax    | double precision |
  zdiffavg    | double precision |
  zdiffstddev | double precision |
  pointcount  | integer          |

Table "public.unfiltered_box1x1"
    Column   |       Type       | Modifiers
------------+------------------+-----------
  t          | double precision | not null
  lz         | double precision |
  fz         | double precision |
  zdiff      | double precision |
  li         | integer          |
  fi         | integer          |
  lreturn    | geometry         | not null, GiST (POINT, 2D lwgeom)
  freturn    | geometry         |		(POINT, 2D lwgeom)