<html 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;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><a name="_MailOriginalBody"><span style='font-size:11.0pt'>Hi. I am desperate for advice on a very simple problem.<o:p></o:p></span></a></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'><o:p> </o:p></span></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>I’ve got an out-of-db raster table that contains a time-series of images of a large area (one image per date), and it is tiled. It’s about 20,000 rows in all and I’m trying to clip it with a polygon so that I get back a smaller raster.</span> If </span><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>I union the tiles for any particular date and then clip them, I get the answer that I expect. Without the union, the clip doesn’t work as expected, and I don’t understand why. I’ll show this in two steps for simplicity:</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> </span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>#1. Create a unioned raster of 32 tiles to create a single image for a particular date:<o:p></o:p></span></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> CREATE TABLE onedate AS (</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> SELECT ST_Union(rast) AS rast FROM myrastertable</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> WHERE image_date = '2010-01-01'</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> )</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> </span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>#2. Clip the unioned raster with a polygon (gives the correct answer):</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> SELECT ST_Clip(rast, geom,true) AS rast FROM onedate, my_polygon)</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> </span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>…but if I try the same thing without the union, it gives the wrong answer:</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> </span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>#3. Select the 32 tiles for the same date </span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> CREATE TABLE onedate_tiled AS (<o:p></o:p></span></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> SELECT * FROM myrastertable WHERE image_date = '2010-01-01'<o:p></o:p></span></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> </span></span><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>)</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> </span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>#4. 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 lie across the boundary of the polygon)</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> SELECT ST_Clip(rast, geom, true) AS rast FROM onedate_tiled, my_polygon</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> WHERE ST_Intersects(rast, geom)</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> </span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><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.</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> </span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>Thanks!</span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'> </span><o:p></o:p></span></p><p class=MsoNormal><span style='mso-bookmark:_MailOriginalBody'><span style='font-size:11.0pt'>John</span><o:p></o:p></span></p></div></body></html>