<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=us-ascii"><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:"Times New Roman",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
{mso-style-priority:99;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.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;}
/* List Definitions */
@list l0
{mso-list-id:1703246276;
mso-list-type:hybrid;
mso-list-template-ids:-568562036 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></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;font-family:"Calibri",sans-serif;color:#1F497D'>You don’t need to use ST_GeomFromText if it’s already a geometry, if you need to set the SRID to 4326 then use ST_SetSRID(geom, 4326), but I don’t think you need that either.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Couple of other things I see as issues with this query<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>1)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>ST_Buffer is missing an amount to buffer by, if your coordinates are in 4326 then your units would be in degrees so should be changed to something like <o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>ST_Buffer(geom, 0.00001) <o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>2)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>ST_Buffer(… FROM ch04.newpoint) should not be like that. I’m assuming your ch04.newpoint is the table of the points you want to intersect with<o:p></o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So should be more like:<o:p></o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FROM ch04.dumprastval AS r INNER JOIN ch04.newpoint AS p ON ST_Intersects(r.rast, p.geom)<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>3)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Gathering from the name ch04.dumprastval, it sounds like you may have already run ST_DumpAsPolygons to convert your raster to a geometry? In which case you shouldn’t have a rast column at all.<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>4)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>While not required, it helps to understand what is going on if you prefix the columns with the table each is coming from. For example in #2 I was guessing. If you tables are kinda long, it helps to alias them such as<o:p></o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>ch04.newpoint AS p<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Once you have a revised query it helps to post the error you are having since it’s hard to test your query without data.<o:p></o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina <o:p></o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Amy O'Keefe<br><b>Sent:</b> Friday, November 6, 2020 7:50 AM<br><b>To:</b> 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br><b>Subject:</b> [postgis-users] raster intersecting geometry<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Hi all,<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>could anyone help me with the below query?<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>I'm trying to find the geometries from a table (ch04.newpoint) which intersect the raster (ch04.dumprastval).<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>However I can't seem to get it to work!<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Thanks.<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>SELECT <o:p></o:p></span></p><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>CAST((gval).val As integer) AS val,<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>ST_Union((gval).geom) As geom<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>FROM (<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>SELECT ST_Intersection(<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>ST_Clip(rast,ST_Envelope(buf.geom)),<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>1,<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>buf.geom<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>) As gval<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>FROM ch04.dumprastval<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>INNER JOIN (<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>SELECT ST_Buffer(<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>ST_GeomFromText(geom,4326) FROM ch04.newpoint<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>ON ST_Intersects(rast,geom)<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>) As foo<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>GROUP BY (gval).val<o:p></o:p></span></p></div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>ORDER BY (gval).val;<o:p></o:p></span></p></div><p class=MsoNormal><span style='font-size:7.5pt;font-family:"Calibri",sans-serif'><br><br>This email and any attachments are confidential and intended solely for the use of the addressee and may contain information which is covered by legal, professional or other privilege. If you have received this email in error please notify the system manager at <a href="mailto:postmaster@ulster.ac.uk">postmaster@ulster.ac.uk</a> and delete this email immediately. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Ulster University. <br>The University's computer systems may be monitored and communications carried out on them may be recorded to secure the effective operation of the system and for other lawful purposes. Ulster University does not guarantee that this email or any attachments are free from viruses or 100% secure. Unless expressly stated in the body of a separate attachment, the text of email is not intended to form a binding contract. Correspondence to and from the University may be subject to requests for disclosure by 3rd parties under relevant legislation. <br><b>The Ulster University was founded by Royal Charter in 1984 and is registered with company number RC000726 and VAT registered number GB672390524.The primary contact address for Ulster University in Northern Ireland is Cromore Road, Coleraine, Co. Londonderry BT52 1SA</b> </span><o:p></o:p></p></div></div></body></html>