5

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?

5

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.

  • The question was about using Postgis, not gdal. – John Powell Feb 17 '17 at 12:24
  • 1
    Sure, the title says PostGIS, but the description seems not to be limited to it. He just wants to "output the union of some polygons as a single PNG". He even tells us that he uses GDAL 1.11.5. Also often people ask specific to one tool not knowing there might be easier solutions. – pLumo Feb 17 '17 at 12:45
  • Sure. However, ST_AsPNG is a Postgis function, so keeping it within a query is arguably simpler. It probably depends what you are used to. I find writing SQL a great deal easier than scripting gdal, but I suppose it is a matter of personal taste. Everyone who uses Postgis uses GDAL, whether they know it or not :-) – John Powell Feb 17 '17 at 12:50
  • True. I like your answer too and voted for it. :-) – pLumo Feb 17 '17 at 12:55
  • 1
    OP happy to use GDAL, so that shuts me up :-) – John Powell Feb 17 '17 at 13:15
7

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.

  • This doesn't explain why the OP is getting an error trying to call ST_AsPNG. PostgreSQL is perfectly capable of returning a bytea back to any client. The problem appears to be that ST_AsPNG requires a raster for input; your query won't work because it doesn't convert the geometry to raster first. – jpmc26 Feb 17 '17 at 20:51
  • Using plpythonu also has several downsides I think should be mentioned. The biggest one is that you can only write files on the server's file system with this. If you want this on another machine, you might not have access or it could be a hassle. Additionally, plpythonu isn't included in PostgreSQL by default; I don't think it's even in the standard contrib packages. So to install it, you may need access to and privileges on the server. Then you'd need database privileges to CREATE EXTENSION. If you don't own/manage the machine where PostgreSQL is installed, this could be a real problem. – jpmc26 Feb 17 '17 at 20:59
  • True. Will update answer tomorrow to reflect those points. – John Powell Feb 17 '17 at 22:51
4

Regarding to my comment, this could be the (untested) query:

WITH foo AS (
    SELECT ST_AsRast(ST_Union(geom)) AS rast FROM holdings WHERE town='Macclesfield')

SELECT 
  oid, 
  lowrite(lo_open(oid, 131072), png) As num_bytes
FROM (
        VALUES (lo_create(0),
               ST_AsGDALRaster(((SELECT ST_Union(rast) FROM foo)),'GTiff')
           )) As v(oid,png)

After the query has finished, you will see the number (something else than 176148) of the large object.

Use it to export the result to a PNG.

SELECT lo_export(176148, '/tmp/demo_rast.png')

Also see the ST_AsGDALRaster documentation and have a look at the supported raster formats (ST_GDALDrivers).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.