# 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 '13 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? –  Francesco Feb 14 '13 at 14:51
Perhaps it is returning edge id as a vector would have at least two edges –  lewis Feb 15 '13 at 19:30