Find the two postcodes furthest apart by district

user_4539008 0
Viewed 59

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

postcode lat long district
AB1 0AA 57.101474 -2.242851 Aberdeen City
AB1 0AB 57.102554 -2.246308 Aberdeen City
AB1 0AD 57.100556 -2.248342 Aberdeen City
AB1 0AR 57.091357 -2.224831 Aberdeenshire
AB1 0AS 57.083838 -2.234437 Aberdeenshire
AB1 0AT 57.089299 -2.239768 Aberdeenshire

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.

postgis amazon-rds amazon-web-services postgresql sql
2 Answers

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;
user_62199790

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;
user_123699100
Related