[postgis-users] Help with SQL

[Martin Davis]

A slight correction to the SQL (JOINs need an ON condition, which in this
case is simply TRUE)

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
  ) AS obs_max ON true;

On Mon, Mar 9, 2020 at 9:28 AM Martin Davis <mtnclimb at gmail.com> wrote:

> 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/c1b174ad/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/c1b174ad/attachment.jpg>