1

I have two tables with point geo data, one (sfc_obs) with weather stations (names), one (smos_grid) with satellite pixels (dggs), and I'm trying to find which dgg is closest to each name using ST_Distance(). I have no trouble getting the distances and sorting, but I'd like to get just the closest match for each name. I think I need to use a GROUP BY OR DISTINCT clause, but everything I've tried has resulted in errors (e.g. GROUP BY name -> "column smos_grid.dgg must appear in the GROUP BY clause...") or resulted in the whole table (below) spit back at me. I have a feeling that one of the related questions may have the answer, but I haven't been able yet to successfully adapt any of them to my situation. I want the distinct pair of name and dgg with the minimum distance. I could (and probably will, in the meantime) use another language to parse the current output, but I'd like to know what the real solution is.

Base query: SELECT sfc_obs.name AS name,smos_grid.dgg AS dgg, ST_Distance(sfc_obs.geom_utm, smos_grid.geom_utm) AS distance FROM sfc_obs,smos_grid ORDER BY name,distance;

Result:

      name       |  dgg   |     distance     
-----------------+--------+------------------
 ALGONA          | 196466 | 7204.37098751625
 ALGONA          | 196465 | 8842.89089934474
 ALGONA          | 196978 |  11010.118792918
 ALGONA          | 195953 | 14495.8081360579
 ALGONA          | 196979 |  16646.802911716
 ALGONA          | 195952 | 20472.9039241652
 ALGONA          | 196977 |  21241.154673217
 ALGONA          | 195954 | 21880.6543096356
 ALGONA          | 196467 | 22540.4472393787  
 ...
 ALGONA          | 212884 | 457182.275288169
 ALGONA          | 213907 | 463002.906164119
 ALGONA          | 213396 | 463254.000442818
 ALGONA          | 213908 | 469680.703686122
 ALGONA          | 213397 | 470914.550849778
 ALGONA          | 213909 | 476963.651054924
 Ames            | 202105 | 3626.03192856493
 Ames            | 201592 | 10236.1588500566
 Ames            | 201593 | 13579.2271071596
 Ames            | 202104 | 15430.7774139333
 Ames            | 202106 |  16638.577453568
 Ames            | 202618 | 17402.7790010433
 Ames            | 202617 | 18506.8761326297
 ...

Desired result:

      name       |  dgg   |     distance     
 -----------------+--------+------------------
 ALGONA          | 196466 | 7204.37098751625
 Ames            | 202105 | 3626.03192856493
 ...

1 Answer 1

1

Looks like I just needed to look for some better terms (mainly "nearest neighbor"). I found this site which led me to a solution: http://geeohspatial.blogspot.com/2013/05/k-nearest-neighbor-search-in-postgis.html

Query:

SELECT sfc_obs.name, (SELECT smos_grid.dgg FROM smos_grid ORDER BY sfc_obs.geom_utm <-> smos_grid.geom_utm LIMIT 1), ST_Distance(sfc_obs.geom_utm, (SELECT smos_grid.geom_utm FROM smos_grid ORDER BY sfc_obs.geom_utm <-> smos_grid.geom_utm LIMIT 1)) AS distance FROM sfc_obs ORDER BY name

Result:

      name       |  dgg   |     distance     
-----------------+--------+------------------
 ALGONA          | 196466 | 7204.37098751625
 Ames            | 202105 | 3626.03192856493
 ANKENY          | 203130 | 6122.19132070171
 ATLANTIC        | 202609 | 3634.92253035928
 AUDUBON         | 201585 | 2456.33907602079
 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.