Rendering PostGIS Raster graphics with LibreOffice Base Reports

I was excited to learn from Pasha Golub's blog LibreOffice Base 3.5 now comes packaged with native PostgreSQL driver so no separate configuration is required. The connection string syntax follows the old SBC native driver of prior OpenOffice versions we itemized in Using OpenOffice Base with PostgeSQL.

What I really wanted to do with it is experiment with its graphical rendering capabilities. As discussed in PSQL needs a better way of outputting bytea one of the frequently asked questions on the PostGIS list by folks using the new not yet officially released (alpha5 recently released) functionality in PostGIS 2.0 is how to render rasters with common variety tools. I suspected Base was a capable option, but had never tested it to confirm. Since I was installing new LibreOffice 3.5, I thought this might be a good test of its metal.

Connecting to a PostgreSQL database

As said the native PostgreSQL driver in 3.5 just uses the SDBC syntax, so I was able to connect to my database by typing this in for the Database URL:

host=localhost port=5440 dbname=postgis20_sampler

If you want to not be prompted for username and password, for newer odb, seems you have to include in the connection string like so.

host=localhost port=5440 dbname=postgis20_sampler user=postgres password=whatever
as shown in

My port is pretty non-standard since I've got like 20 different versions of PostgreSQL installed on my local pc for development. Your port would most likely be 5432.

Using Ad-Hoc queries that return images in Base

What I really wanted to do was render a randomly complex query that returns images like this one.

   CASE WHEN id = 1 THEN 
    , 200,200,ARRAY['8BUI', '8BUI', '8BUI'], ARRAY[100*,154,118], ARRAY[0,0,0])
    ) As png_img
FROM (VALUES (1,  ST_GeomFromText('POINT(-71.124 42.2395)',4326)  )
     , (2,  ST_GeomFromText('POINT(-71.555 42.4325)',4326) )
      ) AS foo(id, geom) 
      CROSS JOIN generate_series(1,10) As x 
      CROSS JOIN generate_series(1,20) As y

So steps:

  1. Select Reports and in Task choose Create Report in Design View as shown in: Report Switchboard
  2. Pick SQL Command from Data Content tab. If you don't see Data content tab, make sure to click the grey area right below the Page footer in the design view Report design view
  3. Next click the ... next to the Content, which will pop open a designer with tables to pick etc, close the table dialog. Real database programmers don't like being constrained by query designers and want to write real SQL , so go to the view menu and Switch Design View off as shown here turn off design mode. If you are not a real database programmer, just humor me and pretend you are.
  4. Cut and paste your finely crafted SQL masterpiece into the blank window sql window
  5. UPDATE: In LibreOffice 4.0 they introduced an SQL Parser which prevents you from writing some advanced queries such as ones involving CTEs. This particular query is one that confounds the parser and it marks as invalid.
    Turn off the parser by File->Edit->Run SQL command directly run sql command directly
  6. click the run as SQL command directly SQL icon run sql command directly and then the green save icon.
  7. If your SQL is valid, you should see a list of columns from your query like this: field list
  8. Drag the fields on the design screen and fuss with them. After fussing, I got a design view like
  9. Click the execute report which produced an output output that can also be output as pdf or html page.

It's not my best work, but proves the point that yes you can write arbitrarily complex SQL even involving queries that output images and have LibreOffice base render them.