Convert ST_Union polygon to PNG file in PostGIS?

I am working with Postgres 9.6, PostGIS 2.3 and GDAL 1.11.5. I would like to output the union of some polygons as a single PNG (or other image) file, with the polygons in black on a white background. (Not tiles - I just want an image.)

I know how to query the union of the polygons:

SELECT ST_Union(geom) FROM holdings WHERE town='Macclesfield';

But I don't know how to convert this to a PNG file. I've tried:

SELECT ST_AsPNG(ST_Union(geom)) FROM holdings WHERE town='Macclesfield';

But this errors:

ERROR:  function st_aspng(geometry) does not exist
LINE 1: select ST_AsPNG(st_union(geom)) from holdings where town='Ma... 
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I think maybe I need to convert the union into a raster layer first?

Answer

You can use gdal_rasterize to create an image from a vector source:

Create a view of your query in PostGIS:

CREATE VIEW myunion AS SELECT ST_Union(geom) FROM holdings WHERE town='Macclesfield';

Then use gdal_rasterize from the command line:

gdal_rasterize -burn -9999 -tr 0.001 0.001 -l myunion PG:'host=localhost dbname=yourDBname user=usernamehere' holdings.tif

It might work also directly using the -sql statement, but I did not test that:

gdal_rasterize -burn -9999 -tr 0.001 0.001 -l holdings -sql "SELECT ST_Union(geom) FROM holdings WHERE town='Macclesfield'" PG:'host=localhost dbname=yourDBname user=usernamehere' holdings.tif

Someone had a similar problem here, maybe that can help.

If you don't need automation, you can of course use QGIS for your task.