[postgis-users] Help with SQL

[paul.malm at lfv.se]

Thanks Martin!

Från: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] För Martin Davis
Skickat: den 9 mars 2020 18:39
Till: PostGIS Users Discussion
Ämne: Re: [postgis-users] Help with SQL

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

SELECT poly.id<http://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<mailto:mtnclimb at gmail.com>> wrote:
This is a good case for using a LATERAL JOIN:

SELECT poly.id<http://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<mailto: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


    [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<mailto: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<mailto: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/8b3ed4b7/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/8b3ed4b7/attachment.jpg>