[postgis-users] Spatial query examples

[Jose Gomez-Dans]

Regina and Brent,

On 5/15/07, Brent Wood <pcreso at pcreso.com> wrote:
> 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.

Spot on. I did a simple test with some points and polygons, and the
problem was the fact that the within statement was the other way
around, as both suggested. The icing on the cake, and a sample recipe
to produce spatial aggregates, with views to  to plot nice maps or
tables was (note the group by statement):
CREATE TABLE stats AS
SELECT c.id AS id, AVG(rto.c8_rto) AS avgvals, STDDEV(rto.c8_rt8) AS
stdvals,c.the_geom AS the_geom
FROM rto2000 r , com_agri c
WHERE c.the_geom && r.the_geom AND CONTAINS(c.the_geom, r.the_geom)='T'
GROUP BY c.id,c.the_geom ;

The resulting table can be nicely exported as a shapefile, loaded into
QGIS, Kosmo or somesuch...

Thank you !
Jose