# Why ST_Intersects() returns one row more than expected?

I am trying to understand the behaviour of ST_Intersects() in PostGIS.

My goal is to get the mean value of a raster area corresponding to each polygon in a vector layer.

To test my SQL I run initially the script with a vector layer containing only one geometry:

``````CREATE TABLE mean AS
SELECT id, (ST_SummaryStats(ST_Clip(rast,1,geom))).mean
FROM vector, raster
WHERE ST_Intersects(geom,rast)
``````

What returns is a two rows table:

``````1;-146.258943781942
1;-115.587301587302
``````

How it is possible that the same geometry intersects the same raster twice with two different values?

I don't know if it is a coincidence, but the mean value I got doing the same operation with the same two layers with the QGIS Zonal Statistics is -138.788659793814 (the mean between -146.258943781942 and -115.587301587302 is -130... pretty close)

``````CREATE TABLE mean AS
SELECT id, rid, (ST_SummaryStats(ST_Clip(rast,1,geom))).*
FROM vector, raster
WHERE ST_Intersects(geom,rast);
``````

and you will see the rid for each tile in the intersect. You need to summarise the information across the tiles.

You can use ST_SummaryStatsAgg

``````SELECT gid, (ST_SummaryStatsAgg(ST_Clip(rast, geom, true))).*
FROM raster, vector
WHERE ST_Intersects(rast, geom) /*and gid = 1*/
GROUP BY gid;
``````

Most likely there is one record on the very edge which gets included in the result (as is it true it does intersect).

You probably what to use `ST_Within` or `ST_Contains`

http://postgis.net/docs/ST_Within.html

http://postgis.net/docs/ST_Contains.html

• Why would this happen with `ST_SummaryStats()` though? Surely that is being calculated on all of the raster data that intersects?
– djq
Feb 14, 2013 at 14:16
• @Mapperz How is this? My vector layer contains only #1 record, a geometry with unique id "1". The output confirms this since the id "1" is reported in both rows. Do you mean the vector intersects with two records of the raster? Feb 14, 2013 at 14:51
• Perhaps it is returning edge id as a vector would have at least two edges Feb 15, 2013 at 19:30