PostGIS - query maximum distance between two polygons

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;

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;
• wouldn't you have too explode (use st_dumppoints) on the blue polygon also? – ziggy Apr 6 '17 at 14:36

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.

• I think the Wikipedia description of "it is the greatest of all distances from a point in one set to the closest point in the other set" is the most clear. Notably, the algorithm computes distances only between pairs of points, not points and segments, although a approximation of point-segment distance can be obtained by providing the optional densifyFrac argument to ST_HausdorffDistance. I would be curious to see how this performs relative to @tilt 's solution which is exact. – dbaston Oct 19 '16 at 12:29
• [ST_HausdorffDistance] (postgis.net/docs/ST_HausdorffDistance.html) does not allow you to specify which table to measure from and to, so return the greatest distance from any point on blue to the nearest point on red, if that is greater than the greatest distance from any point on red to the nearest point on blue. – Chris Roberts Oct 19 '16 at 21:40