<div dir="ltr">A slight correction to the SQL (JOINs need an ON condition, which in this case is simply TRUE)<div><br></div><div>SELECT <a href="http://poly.id">poly.id</a>, obs_max.*<br>FROM polyobstacle poly <br>JOIN LATERAL (SELECT * FROM obstacles o<br> WHERE ST_Contains(poly.geom, o.geom) <br> ORDER BY height_m LIMIT 1<br> ) AS obs_max ON true;<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Mar 9, 2020 at 9:28 AM Martin Davis <<a href="mailto:mtnclimb@gmail.com">mtnclimb@gmail.com</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 dir="ltr">This is a good case for using a LATERAL JOIN:<div><br></div><div>SELECT <a href="http://poly.id" target="_blank">poly.id</a>, obs_max.*</div><div>FROM polyobstacle poly </div><div>JOIN LATERAL (SELECT * FROM obstacles o</div><div> WHERE ST_Contains(poly.geom, o.geom) </div><div> ORDER BY height_m LIMIT 1</div><div> ) obs_max</div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Mar 9, 2020 at 2:21 AM <<a href="mailto:paul.malm@lfv.se" target="_blank">paul.malm@lfv.se</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 lang="SV">
<div>
<p class="MsoNormal"><span lang="EN-US">Hi,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I have 2 tables:<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">obstacles (point layer) with a column height_m (INTEGER) and a
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">polyobstacles (polygon layer)<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">the point layer has obstacles all over the map and the polygon layer has some polygons containing some of the obstacle points.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">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.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Is there someone that can get me on the track for such a query, I’m losing myself in subqueries?<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Kind regards,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Paul<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:9pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:9pt">
</span><span style="font-size:9pt"><img width="132" height="107" id="gmail-m_2031318179932632170gmail-m_-8375580493621889790Bild_x0020_1" src="cid:170c01859184ce8e91" alt="2_LFV_engelsk_96"><u></u><u></u></span></p>
<table border="0" cellspacing="0" cellpadding="0" style="border-collapse:collapse">
<tbody>
<tr style="height:36.85pt">
<td width="420" valign="top" style="width:315pt;padding:0cm 0cm 0cm 2.25pt;height:36.85pt">
<p class="MsoNormal" style="margin-top:2pt;line-height:10pt">
<span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"><u></u> <u></u></span></p>
<p class="MsoNormal" style="margin-top:2pt;line-height:10pt">
<b><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"> Paul Malm</span></b><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"><br>
<br>
Operations AIM</span><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"><u></u><u></u></span></p>
</td>
</tr>
<tr style="height:24.1pt">
<td width="420" valign="top" style="width:315pt;padding:0cm 0cm 0cm 2.25pt;height:24.1pt">
<p class="MsoNormal" style="line-height:9pt"><span lang="EN-US" 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> <br>
<br>
<u></u><u></u></span></p>
<p class="MsoNormal" style="line-height:9pt"><span lang="EN-US" 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><span style="font-size:9pt;color:rgb(51,51,51)"><u></u><u></u></span></p>
</td>
</tr>
<tr>
<td width="420" valign="top" style="width:315pt;padding:0cm 0cm 0cm 2.25pt">
<p class="MsoNormal" style="margin-top:2pt;line-height:9pt">
<span lang="EN-US" 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 lang="EN-US">www.lfv.se</span></a></span><span lang="EN-US" style="font-size:9pt;color:rgb(51,51,51)"><u></u><u></u></span></p>
</td>
</tr>
<tr style="height:3.7pt">
<td width="420" valign="top" style="width:315pt;padding:0cm 0cm 0cm 2.25pt;height:3.7pt">
<p class="MsoNormal" style="margin-top:6pt;line-height:9pt">
<span lang="EN-US" style="font-size:9pt;color:rgb(0,204,0)"> Please consider the enviroment before printing this e-mail message.</span><span lang="EN-US" style="font-size:9pt;color:rgb(0,204,0)"><u></u><u></u></span></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span lang="EN-US" style="font-size:9pt"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
</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>
</blockquote></div>