[postgis-users] Spatial query examples

[Brent Wood]

--- Jose Gomez-Dans <jgomezdans at gmail.com> wrote:

> 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'
>  ;

Hi Jose,

There does not seem to be any error in your SQL (at least not that I can spot
right now), which suggests that your geometries in the two tables do not have
any meeting the specified constraint. Try the SQL without the
 "and within(com.the_geom,rto.the_geom)='T'" part to check that there is in
fact some relationship between the geometries, before the extra "within" filter
is applied.

If you still get no records returned, invert the query to return all those
records where com.the_geom&&rto.the_geom = 'F', and you should get a lot more
hits returned. This will check your syntax, but not the underlying data.

The other way to check such things is visually. Open the tables in a desktop
mapping/GIS package (QGIS, OpenJump,uDIG, etc) and see how they relate to each
other on screen. This is a very quick & effective way to identify the cause of
such problems.


> 
> 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 mean is straightforward, select avg(rto.c8.rto) where .... group by ... ;

stddev is a standard statistical function in Postgres, see the second set of
functions described in:
http://www.postgresql.org/docs/current/static/functions-aggregate.html

You might also look at using PL/R if you require more advanced statistics
describing your data (such as medians, etc). This allows SQL functions to
invoke the R stats package on datasets, to support statistical functions in
queries. 

See: http://www.joeconway.com/plr/


Cheers,

  Brent Wood


> 
> 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
>