<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi Paul,</div>
<div> </div>
<div>no need for subqueries here. Do a spatial join between polygon and points and start you query with DISTINCT ON (poly.id) poly.id, o.height etc.</div>
<div> </div>
<div>DISTINCT ON (poly.id) will get you one row from the JOIN result per polygon. Which one it picks you can control with the final ORDER BY clause - in your case the height values of points.</div>
<div>The first column in the ORDER BY must be the "group" of your DISTINCT ON and then the height value.</div>
<div> </div>
<div>cheers</div>
<div>Felix</div>
<div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Montag, 09. März 2020 um 10:21 Uhr<br/>
<b>Von:</b> paul.malm@lfv.se<br/>
<b>An:</b> postgis-users@lists.osgeo.org<br/>
<b>Betreff:</b> [postgis-users] Help with SQL</div>
<div name="quoted-content"><!--p.MsoNormal, li.MsoNormal, div.MsoNormal {
margin: 0.0cm;
font-size: 11.0pt;
font-family: Calibri , sans-serif;
}
a:link, span.MsoHyperlink {
color: blue;
text-decoration: underline;
}
a:visited, span.MsoHyperlinkFollowed {
color: purple;
text-decoration: underline;
}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate {
margin: 0.0cm;
font-size: 8.0pt;
font-family: Tahoma , sans-serif;
}
span.E-postmall17 {
font-family: Calibri , sans-serif;
color: windowtext;
}
span.BallongtextChar {
font-family: Tahoma , sans-serif;
}
*.MsoChpDefault {
font-family: Calibri , sans-serif;
}
div.WordSection1 {
page: WordSection1;
}
-->
<div>
<div class="WordSection1">
<p class="MsoNormal"><span>Hi,</span></p>
<p class="MsoNormal"><span>I have 2 tables:</span></p>
<p class="MsoNormal"><span>obstacles (point layer) with a column height_m (INTEGER) and a </span></p>
<p class="MsoNormal"><span>polyobstacles (polygon layer)</span></p>
<p class="MsoNormal"><span>the point layer has obstacles all over the map and the polygon layer has some polygons containing some of the obstacle points.</span></p>
<p class="MsoNormal"><span>Now I would like to select the highest obstacle in each polygon. If there is several points with the same highest height a random obstacle of those highest shall be selected.</span></p>
<p class="MsoNormal"><span>Is there someone that can get me on the track for such a query, I’m losing myself in subqueries?</span></p>
<p class="MsoNormal"><span>Kind regards,</span></p>
<p class="MsoNormal"><span>Paul</span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal"><span style="font-size: 9.0pt;"> </span></p>
<p class="MsoNormal"><span style="font-size: 9.0pt;"> </span><span style="font-size: 9.0pt;"><img alt="2_LFV_engelsk_96" height="107" id="Bild_x0020_1" src="cid:image001.jpg@01D5F5FC.17379520" width="132"/></span></p>
<table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse;">
<tbody>
<tr style="height: 36.85pt;">
<td style="width: 315.0pt;padding: 0.0cm 0.0cm 0.0cm 2.25pt;height: 36.85pt;" valign="top" width="420">
<p class="MsoNormal" style="margin-top: 2.0pt;line-height: 10.0pt;"><span style="font-size: 9.0pt;color: rgb(51,51,51);"> </span></p>
<p class="MsoNormal" style="margin-top: 2.0pt;line-height: 10.0pt;"><b><span style="font-size: 9.0pt;color: rgb(51,51,51);"> Paul Malm</span></b><br/>
<br/>
<span style="font-size: 9.0pt;color: rgb(51,51,51);"> Operations AIM</span></p>
</td>
</tr>
<tr style="height: 24.1pt;">
<td style="width: 315.0pt;padding: 0.0cm 0.0cm 0.0cm 2.25pt;height: 24.1pt;" valign="top" width="420">
<p class="MsoNormal" style="line-height: 9.0pt;"><span style="font-size: 9.0pt;color: rgb(51,51,51);"> Direct +46 (0)8 797 70 23 Mobile: +46 (0)708 601115<br/>
paul.malm@lfv.se </span><br/>
</p>
<p class="MsoNormal" style="line-height: 9.0pt;"><span style="font-size: 9.0pt;color: rgb(51,51,51);"> </span><span style="font-size: 9.0pt;color: rgb(51,51,51);">Mail & Visit: Löjtnantsgatan 25, 115 50 Stockholm, Sweden</span></p>
</td>
</tr>
<tr>
<td style="width: 315.0pt;padding: 0.0cm 0.0cm 0.0cm 2.25pt;" valign="top" width="420">
<p class="MsoNormal" style="margin-top: 2.0pt;line-height: 9.0pt;"><span style="font-size: 9.0pt;color: rgb(51,51,51);"> Unit phone: +46 (0)8 797 70 20 <br/>
</span><span style="font-size: 9.0pt;color: rgb(51,51,51);"><a href="http://www.lfv.se/" target="_blank"><span>www.lfv.se</span></a></span></p>
</td>
</tr>
<tr style="height: 3.7pt;">
<td style="width: 315.0pt;padding: 0.0cm 0.0cm 0.0cm 2.25pt;height: 3.7pt;" valign="top" width="420">
<p class="MsoNormal" style="margin-top: 6.0pt;line-height: 9.0pt;"><span style="font-size: 9.0pt;color: rgb(0,204,0);"> Please consider the enviroment before printing this e-mail message.</span></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span style="font-size: 9.0pt;"> </span></p>
<p class="MsoNormal"><span> </span></p>
</div>
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div>
</div>
</div>
</div>
</div></div></body></html>