<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt'>Hi. I have a problem that is driving me nuts. I’ve got an out-of-db raster table that contains a time series of images of the same area (one per date), and it is tiled. I’m trying to clip a section of it with a polygon.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>If I union the tiles for a particular date and then clip them, I get the correct answer. I’ll show this in two steps for simplicity:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>#1. Create a unioned raster for a single date<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>CREATE TABLE onedate AS (<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'> SELECT ST_Union(rast) AS rast FROM myrastertable<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'> WHERE image_date = '2010-01-01'<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>#2. Clip the unioned raster (gives the correct answer)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>SELECT ST_Clip(rast, geom,true) AS rast FROM onedate, totalconvexhull<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'> WHERE ST_Intersects(rast, geom)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>…but if I try the same thing with the tiled version of the table, it fails:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>#1. Create a tiled raster for the same date<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>CREATE TABLE onedate_tiled AS (<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'> SELECT * FROM myrastertable WHERE image_date = '2010-01-01'<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>#2. Clip the tiled raster (this fails: it seems to get rid of some of the tiles where there is no overlap with the polygon, but stuff hangs over the edges of the polygon for tiles that cross the boundary of the polygon)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>SELECT ST_Clip(rast, geom, true) AS rast FROM onedate_tiled, totalconvexhull<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>WHERE ST_Intersects(rast, geom)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>I’d love to know what I am missing. Ultimately, I want to clip all of the dates to the same polygon.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>Thanks!<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>John<o:p></o:p></span></p></div></body></html>