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.

enter image description here

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?

  • What is off, exactly? – John Powell Oct 29 '15 at 15:20
  • Not sure, but I get this error: ERROR: subquery in FROM must have an alias LINE 2: FROM ( ^ HINT: For example, FROM (SELECT ...) [AS] foo. ********** Error ********** ERROR: subquery in FROM must have an alias SQL state: 42601 Hint: For example, FROM (SELECT ...) [AS] foo. Character: 40 – cbunn Oct 29 '15 at 15:21
  • you need to put an as alias_name after your inner query, ie, SELECT osm_id, gid, MIN(dn) FROM (.......) AS g GROUP BY (osm_id, gid); – John Powell Oct 29 '15 at 15:27
  • 1
    It is always a good idea to put any errors you are encountering in the question :-) – John Powell Oct 29 '15 at 15:28
  • Now I get this error: ERROR: column a.id does not exist LINE 3: SELECT a.id AS osm_id, b.id AS gid, b.dn AS dn ^ ********** Error ********** ERROR: column a.id does not exist SQL state: 42703 Character: 59 – cbunn Oct 29 '15 at 15:31

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
  • This query does not return any data, just the table header names of osm_id and dn. But they are not populated. – cbunn Nov 3 '15 at 23:21
  • The parameters in ST_Within() were in the wrong order. I edited the answer accordingly. – yxcv Nov 4 '15 at 5:58

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.

  • This query returns the error: ERROR: column foo.pid does not exist LINE 10: FROM bar JOIN foo ON foo.pid=bar.pid AND foo.dn=bar.dn ^ ********** Error ********** ERROR: column foo.pid does not exist SQL state: 42703 Character: 236 – cbunn Nov 3 '15 at 23:22
  • I corrected the field name to osm_id – mtn.biker Nov 4 '15 at 3:14
  • The query runs now, but it returns duplicate records. So instead of returning the osm_id with the lowest 'dn' value it returns an osm_id record for each 'dn' value contained within it. – cbunn Nov 4 '15 at 18:05
up vote 0 down vote accepted

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.

  • i thought you trying to get the min elevation? you are using the max function in the subquery? – ziggy Sep 28 '16 at 16:45
  • oh you're right i have edited my formula to show this. – cbunn Oct 4 '16 at 16:11

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.