PostGIS - query maximum distance between two polygons

Question

In PostGIS, how can you calculate the maximum distance that one polygon is from the nearest point of another polygon?

I have two tables of polygons, 'red' and 'blue'. They can be joined by field district_code. How do you find the max distance from a red polygon to its joined blue polygon. (Distance indicated by dashed black line in image above).

The following query uses ST_MaxDistance, but this gives distance from furthest point to furthest point, not furthest point to nearest point.

select id, district_code, ST_MaxDistance(red.geometry, blue.geometry) AS max_distance
FROM red
INNER JOIN blue ON red.district_code = blue.district_code;

Answer 1

There is no such function, but you can create the functionality yourself (although it can be slow for large datasets) by calculating the distance from every point in the red polygon to the blue polygon. Then get the max(distance) for every red polygon and you should have your answer. As long as your red polygons have not too many vertices, this should still perform reasonably.

Query looks something like:

WITH exploded AS (
  SELECT district_code, (ST_Dumppoints(geometry)).geom FROM red
)
SELECT blue.id, blue.district_code, max(ST_Distance(exploded.geom, blue.geometry) AS max_distance
FROM exploded 
INNER JOIN blue ON exploded.district_code = blue.district_code
GROUP BY blue.id, blue.district_code;

Answer 2

See ST_HausdorffDistance, which calculates a Hausdorff distance to see how far two subsets of a metric space are from each other. It may not meet all of your criteria, but it is another distance tool to try.