[postgis-users] Spatial query examples

[Jose Gomez-Dans]

Hi,
I am quite inexperienced in terms of SQL, but I am trying to make
progress with PostGIS. what I want to do is to (say) average some
field within a given area. Without going into the average value bit
(oh, and the std deviation would be nice too :D),  my query would look
thus:
select rto.gid, rto.c6_sri, rto.c8_rto, rto.c5_cul, com.the_geom
   from rendimientos.rto200 rto, public.com_agri com
   where com.the_geom&&rto.the_geom and within(com.the_geom,rto.the_geom)='T'  ;

I have Vacuum'ed the databases, but the above query fails to return
any rows. What am I missing? And how could I piggy back the areal
statistics on to the query? Ideally, I would like to have a mean and
std_dev of (say) rto.c8_rto above for all the features that lie within
each feature in com_agri.

The tables are defined as such:
CREATE TABLE com_agri (  gid serial NOT NULL,  area int8,  perimeter
int8,  cod_pv int2,
  cod_com int2,  nom_com varchar(50),  the_geom geometry,
  CONSTRAINT com_agri_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 23030))
WITHOUT OIDS;
CREATE INDEX com_agri_the_geom_gist  ON com_agri  USING gist  (the_geom);

CREATE TABLE rendimientos.rto2001
(
  gid serial NOT NULL,
[......]
  the_geom geometry,
  CONSTRAINT rto2001_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),
  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 23030))
WITHOUT OIDS;
CREATE INDEX rto2001_the_geom_gist  ON rendimientos.rto2001  USING gist
  (the_geom);



Many thanks for your time!
J