<div dir="ltr"><div>Sorry for the missing group by part:</div><div><br></div><div>Query should be:</div><div><div>select <a href="http://p.id" target="_blank">p.id</a>, (array_agg(<a href="http://o.id" target="_blank">o.id</a> order by height_m))[1] as heighest_id<br></div><div>from polyobstacles p join obstacles o on st_contains(p.geom, o.geom)</div><div><b>group by <a href="http://p.id">p.id</a>;</b></div><div><br></div><div>Nicolas<br></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, 9 Mar 2020 at 10:38, Felix Kunde <<a href="mailto:felix-kunde@gmx.de">felix-kunde@gmx.de</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div style="font-family:Verdana;font-size:12px"><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 (<a href="http://poly.id" target="_blank">poly.id</a>) <a href="http://poly.id" target="_blank">poly.id</a>, o.height etc.</div>
<div> </div>
<div>DISTINCT ON (<a href="http://poly.id" target="_blank">poly.id</a>) 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 0px 10px 10px;border-left:2px solid rgb(195,217,229)">
<div style="margin:0px 0px 10px"><b>Gesendet:</b> Montag, 09. März 2020 um 10:21 Uhr<br>
<b>Von:</b> <a href="mailto:paul.malm@lfv.se" target="_blank">paul.malm@lfv.se</a><br>
<b>An:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<b>Betreff:</b> [postgis-users] Help with SQL</div>
<div name="quoted-content">
<div>
<div>
<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:9pt"> </span></p>
<p class="MsoNormal"><span style="font-size:9pt"> </span><span style="font-size:9pt"><img alt="2_LFV_engelsk_96" id="gmail-m_-2964747103027720860Bild_x0020_1" src="cid:170bf38b63fc204bfcc1" width="132" height="107"></span></p>
<table style="border-collapse:collapse" cellspacing="0" cellpadding="0" border="0">
<tbody>
<tr style="height:36.85pt">
<td style="width:315pt;padding:0cm 0cm 0cm 2.25pt;height:36.85pt" width="420" valign="top">
<p class="MsoNormal" style="margin-top:2pt;line-height:10pt"><span style="font-size:9pt;color:rgb(51,51,51)"> </span></p>
<p class="MsoNormal" style="margin-top:2pt;line-height:10pt"><b><span style="font-size:9pt;color:rgb(51,51,51)"> Paul Malm</span></b><br>
<br>
<span style="font-size:9pt;color:rgb(51,51,51)"> Operations AIM</span></p>
</td>
</tr>
<tr style="height:24.1pt">
<td style="width:315pt;padding:0cm 0cm 0cm 2.25pt;height:24.1pt" width="420" valign="top">
<p class="MsoNormal" style="line-height:9pt"><span style="font-size:9pt;color:rgb(51,51,51)"> Direct +46 (0)8 797 70 23 Mobile: +46 (0)708 601115<br>
<a href="mailto:paul.malm@lfv.se" target="_blank">paul.malm@lfv.se</a> </span><br>
</p>
<p class="MsoNormal" style="line-height:9pt"><span style="font-size:9pt;color:rgb(51,51,51)"> </span><span style="font-size:9pt;color:rgb(51,51,51)">Mail & Visit: Löjtnantsgatan 25, 115 50 Stockholm, Sweden</span></p>
</td>
</tr>
<tr>
<td style="width:315pt;padding:0cm 0cm 0cm 2.25pt" width="420" valign="top">
<p class="MsoNormal" style="margin-top:2pt;line-height:9pt"><span style="font-size:9pt;color:rgb(51,51,51)"> Unit phone: +46 (0)8 797 70 20 <br>
</span><span style="font-size:9pt;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:315pt;padding:0cm 0cm 0cm 2.25pt;height:3.7pt" width="420" valign="top">
<p class="MsoNormal" style="margin-top:6pt;line-height:9pt"><span style="font-size:9pt;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:9pt"> </span></p>
<p class="MsoNormal"><span> </span></p>
</div>
_______________________________________________ postgis-users mailing list <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <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></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>