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

[strk at refractions.net]

On Tue, Dec 07, 2004 at 09:30:14AM -0800, collin wrote:
> 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!

Try with a subselect:

UPDATE grid10m_box1x1
SET lzmin = (
	SELECT min(lz) from unfiltered_box1x1 WHERE lreturn && the_geom
);

--strk;

> 
> 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)
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users