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 ...
أكثر...
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 ...
أكثر...