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?