[postgis-users] Help with SQL

[Martin Davis]

This is a good case for using a LATERAL JOIN:

SELECT poly.id, obs_max.*
FROM polyobstacle poly
JOIN LATERAL (SELECT * FROM obstacles o
  WHERE ST_Contains(poly.geom, o.geom)
 ORDER BY height_m LIMIT 1
  ) obs_max



On Mon, Mar 9, 2020 at 2:21 AM <paul.malm at lfv.se> wrote:

> Hi,
>
> I have 2 tables:
>
> obstacles (point layer) with a column height_m (INTEGER) and a
>
> polyobstacles (polygon layer)
>
> the point layer has obstacles all over the map and the polygon layer has
> some polygons containing some of the obstacle points.
>
> 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.
>
> Is there someone that can get me on the track for such a query, I’m losing
> myself in subqueries?
>
> Kind regards,
>
> Paul
>
>
>
>
>
>     [image: 2_LFV_engelsk_96]
>
>
>
> *   Paul Malm*
>
>    Operations AIM
>
>    Direct  +46 (0)8 797 70 23  Mobile: +46 (0)708 601115
>    paul.malm at lfv.se
>
>    Mail & Visit: Löjtnantsgatan 25, 115 50 Stockholm, Sweden
>
>    Unit phone: +46 (0)8 797 70 20
>    www.lfv.se
>
>    Please consider the enviroment before printing this e-mail message.
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200309/8a61b3e8/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 4612 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200309/8a61b3e8/attachment.jpg>