[postgis-users] Help with SQL

[paul.malm at lfv.se]

Thanks Felix!

Från: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] För Felix Kunde
Skickat: den 9 mars 2020 10:33
Till: postgis-users at lists.osgeo.org
Kopia: postgis-users at lists.osgeo.org
Ämne: Re: [postgis-users] Help with SQL

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


    [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<http://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/20200310/c8a65739/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 4612 bytes
Desc: image001.jpg
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200310/c8a65739/attachment.jpg>