I have a table in PostgreSQL that contains postcode data as below

I would like to find out the two postcodes by district that are furthest apart. I know I can use PostGIS similar to the following to calculate the distance between two sets of lat long:

st_distancesphere(st_makepoint(lat1, long1), st_makepoint(lat2, long2))

But how would I use a window function to do this across all combinations of postcodes within a district group?

I'm using PostgreSQL 12.7 on AWS RDS.

CodePudding user response：

You can use a triangle join to get the pairs

select * 
from (
    select t1.district, t1.postcode postcode1, t2.postcode postcode2
        , row_number() over(partition by t1.district order by st_distancesphere(st_makepoint(t1.lat, t1.long), st_makepoint(t2.lat, t2.long)) desc) rn
    from mytable t1
    join mytable t2 on t1.postcode > t2.postcode and t1.district = t2.district
) t
where rn = 1;

CodePudding user response：

Postgres' DISTINCT ON is another option that avoids window functions entirely:

WITH pairs AS (
  SELECT
    loc.postcode,
    loc.district,
    xx.postcode AS other_postcode,
    st_distancesphere(st_makepoint(loc.lat,loc.long),st_makepoint(xx.lat,xx.long)) 
                AS distance
  FROM locations loc
  LEFT JOIN locations xx
    ON loc.district = xx.district
    AND loc.postcode < xx.postcode  
         -- guarantees we compare each pair once and not twice
)

SELECT DISTINCT ON (p.district)
    p.postcode,
    p.district,
    p.other_postcode,
    p.distance
FROM pairs p
ORDER BY p.district, p.distance DESC;