How to do a spatial join in PostGIS that returns on the minimum value of point intersecting polygon?

Question

I have a polygon table (buffer) and a point table (dem_points), and I want to do a spatial join on them. I need to join the point with the minimum elevation (dn) to its spatially corresponding polygon in PostGIS.

In the above example I want my output to be the polygon with osm_id of'1234' joined with the point where dn=4200. I have this query but something is off:

SELECT osm_id, gid, MIN(dn) 
FROM (
      SELECT a.id AS osm_id, b.id AS gid, b.dn AS dn
      FROM 
         buffer AS a, dem_points AS b
      WHERE ST_Within(buffer.geom, dem_points.geom)
     )
GROUP BY (osm_id, gid);`  

With this query I receive this error:

ERROR:  subquery in FROM must have an alias
LINE 2:      FROM (
              ^
HINT:  For example, FROM (SELECT ...) [AS] foo.
ERROR: subquery in FROM must have an alias
SQL state: 42601
Hint: For example, FROM (SELECT ...) [AS] foo.
Character: 40  

How do I need to structure my query to spatially join the point with the minimum elevation to the polygon it falls within?

Answer 1

First use a window function to get the ordered rank of the dem_points. In a second step filter the dem_point with the lowest dn by the rank.

SELECT osm_id, gid, dn
FROM   (
         SELECT b.osm_id, p.gid, p.dn,
                row_number() OVER (PARTITION BY osm_id order by dn) as rank
         FROM   buffer b, dem_points p
         WHERE  ST_Within(p.geom, b.geom) 
       ) joined
WHERE  rank = 1

Answer 2

You need to group the points per buffer geom, then find the min(dn) for each buffer geom, and finally join it back to the original to get the point gid. Try something like this (using WITH syntax to make it slightly easier to follow):

WITH foo AS (
  SELECT b.osm_id, p.gid, p.dn
  FROM buffer b, dem_points p
  WHERE ST_Within(p.geom, b.geom)
), bar AS (
  SELECT osm_id, min(dn) as dn FROM foo
  GROUP BY osm_id
)
SELECT bar.osm_id, foo.gid, bar.dn 
FROM bar JOIN foo ON foo.osm_id=bar.osm_id AND foo.dn=bar.dn

Note that another way to do this is to use PostgreSQL's HAVING clause but I think that's more difficult to follow.

Answer 3

I have found an alternative way to solve this question. Following this thread: https://geonet.esri.com/thread/16118 . By performing a Many to One Join where the points are the target features and the polygons are the join features. Then running a definition query against the new layer like this:

dn in (select min(dn) from dn group by osm_id)

This will now only display those points with the minimum "dn" value. All this needs to be performed within a personal geodatabase.