[postgis-users] Help with SQL

[Nicolas Ribot]

Hi,

Something like accumulating ids of obstacles for each polygon, ordered by
height, and taking the first element of the array:

select p.id, (array_agg(o.id order by height_m))[1] as heighest_id
from polyobstacles p join obstacles o on st_contains(p.geom, o.geom);

Nicolas

On Mon, 9 Mar 2020 at 10:21, <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/a9d3e782/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/a9d3e782/attachment.jpg>