PostGIS – Converting ST_Union Polygon to PNG File in PostGIS

pngpostgis

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?

Best Answer

The key to this is to recognize that ST_AsPNG returns a bytea, ie a byte array, representing a png. So, you need to somehow convert those bytes to a file on the file system. There are a couple of ways of doing this as shown in the docs.

The oid approach, as outlined by @Stefan, works, but requires psql and is a bit obscure, in my opinion.

The other approach, is to use Pythonu (ie, a Python stored procecure) to convert the bytea to an actual png file.

CREATE OR REPLACE FUNCTION write_file (param_bytes bytea, param_filepath text)
  RETURNS text AS $$
    f = open(param_filepath, 'wb+')
    f.write(param_bytes)
    return param_filepath
$$ LANGUAGE plpythonu;

After which you can do:

WITH png (bytes) AS
    (SELECT ST_AsPNG(ST_Union(geom)) 
       FROM holdings 
       WHERE town = 'Macclesfield')
SELECT write_file(bytes, path_to_png) FROM png;

which is very clean.

As a note of warning, the Python stored procedure langauge is called plpythonu -- the final u stands for untrusted. As it states in the docs:

PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted variant plpython might become available in the future if a secure execution mechanism is developed in Python. The writer of a function in untrusted PL/Python must take care that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator. Only superusers can create functions in untrusted languages such as plpythonu

so you will want to make sure you use it in an isolated environment, if you go this route.

EDIT: As has been pointed out by @jpmc26, the plpythonu approach will only work server-side, and you will need to install plpythonu (which is not in the standard package), both of which means that you will need to have admin/super user privileges on the server, both of which are reasonable observations. I would argue, however, that if you don't have access to the server, once you have understood that ST_AsPNG returns a byte array, it is would be reasonably straight forward to write something client-side to consume the return value -- which could even be plain vanilla python, via psycopg2 (or anything other programming language that can call a Postgres function and write the resulting byte stream to disk locally).

For anyone wanting to go this route, in Ubuntu/Debian, you need to install the plpythonu extension, and then create the extension, which can be done with (change for your version).

sudo apt install postgresql-plpython-9.6

followed by 

CREATE LANGUAGE plpythonu;

either within psql, pgadmin, or from a shell prompt of some description.

Related Question