[postgis-users] Help with SQL

[Nicolas Ribot]

Sorry for the missing group by part:

Query should be:
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)
*group by p.id <http://p.id>;*

Nicolas

On Mon, 9 Mar 2020 at 10:38, Felix Kunde <felix-kunde at gmx.de> wrote:

> Hi Paul,
>
> no need for subqueries here. Do a spatial join between polygon and points
> and start you query with DISTINCT ON (poly.id) poly.id, o.height etc.
>
> DISTINCT ON (poly.id) 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.
> The first column in the ORDER BY must be the "group" of your DISTINCT ON
> and then the height value.
>
> cheers
> Felix
>
>
> *Gesendet:* Montag, 09. März 2020 um 10:21 Uhr
> *Von:* paul.malm at lfv.se
> *An:* postgis-users at lists.osgeo.org
> *Betreff:* [postgis-users] Help with SQL
>
> 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
> _______________________________________________
> 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/e4c5453b/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: noname
Type: image/jpeg
Size: 4612 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200309/e4c5453b/attachment.jpe>