[postgis-users] Spatial query examples

[Obe, Regina]

Jose,
 
I think your within statement is backward.  If you want all rto200 within com_agri, then it should be 
 
within(rto.the_geom, com.the_geom)

Try the following statement and see if that gives you want you want

select avg(rto.c8_rto) as avgrto, stddev(rto.c8_rto) as stddevrto   

from rendimientos.rto200 rto, public.com_agri com
   where com.the_geom && rto.the_geom and within(rto.the_geom, com.the_geom)  ;

 

Hope that helps,

Regina


________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Jose Gomez-Dans
Sent: Tue 5/15/2007 8:42 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Spatial query examples



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




-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070515/c5b47611/attachment.html>