<HTML dir=ltr><HEAD><TITLE>[postgis-users] Spatial query examples</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3086" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText42472 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Jose,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>I think your within statement is backward. If you want all rto200 within com_agri, then it should be </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV></DIV>
<DIV dir=ltr><FONT size=2>within(rto.the_geom, com.the_geom)</FONT></DIV>
<P>Try the following statement and see if that gives you want you want</P>
<P><FONT size=2>select avg(rto.c8_rto) as avgrto, stddev(rto.c8_rto) as stddevrto </FONT></P>
<P><FONT size=2>from rendimientos.rto200 rto, public.com_agri com<BR> where com.the_geom && rto.the_geom and within(rto.the_geom, com.the_geom) ;</FONT></P>
<P><FONT size=2></FONT> </P>
<P><FONT size=2>Hope that helps,</FONT></P>
<P><FONT size=2>Regina</FONT></P>
<DIV dir=ltr><BR></DIV>
<DIV dir=ltr>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr><FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Jose Gomez-Dans<BR><B>Sent:</B> Tue 5/15/2007 8:42 AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> [postgis-users] Spatial query examples<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Hi,<BR>I am quite inexperienced in terms of SQL, but I am trying to make<BR>progress with PostGIS. what I want to do is to (say) average some<BR>field within a given area. Without going into the average value bit<BR>(oh, and the std deviation would be nice too :D), my query would look<BR>thus:<BR>select rto.gid, rto.c6_sri, rto.c8_rto, rto.c5_cul, com.the_geom<BR> from rendimientos.rto200 rto, public.com_agri com<BR> where com.the_geom&&rto.the_geom and within(com.the_geom,rto.the_geom)='T' ;<BR><BR>I have Vacuum'ed the databases, but the above query fails to return<BR>any rows. What am I missing? And how could I piggy back the areal<BR>statistics on to the query? Ideally, I would like to have a mean and<BR>std_dev of (say) rto.c8_rto above for all the features that lie within<BR>each feature in com_agri.<BR><BR>The tables are defined as such:<BR>CREATE TABLE com_agri ( gid serial NOT NULL, area int8, perimeter<BR>int8, cod_pv int2,<BR> cod_com int2, nom_com varchar(50), the_geom geometry,<BR> CONSTRAINT com_agri_pkey PRIMARY KEY (gid),<BR> CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),<BR> CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =<BR>'MULTIPOLYGON'::text OR the_geom IS NULL),<BR> CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 23030))<BR>WITHOUT OIDS;<BR>CREATE INDEX com_agri_the_geom_gist ON com_agri USING gist (the_geom);<BR><BR>CREATE TABLE rendimientos.rto2001<BR>(<BR> gid serial NOT NULL,<BR>[......]<BR> the_geom geometry,<BR> CONSTRAINT rto2001_pkey PRIMARY KEY (gid),<BR> CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),<BR> CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =<BR>'MULTIPOLYGON'::text OR the_geom IS NULL),<BR> CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 23030))<BR>WITHOUT OIDS;<BR>CREATE INDEX rto2001_the_geom_gist ON rendimientos.rto2001 USING gist<BR> (the_geom);<BR><BR><BR><BR>Many thanks for your time!<BR>J<BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>