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.
SELECT foo.id, ST_AsPNG(
CASE WHEN id = 1 THEN
, 200,200,ARRAY['8BUI', '8BUI', '8BUI'], ARRAY[100*foo.id,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
GROUP BY foo.id;
- Select Reports and in Task choose Create Report in Design View as shown in:
- 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
- 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 . If you are not
a real database programmer, just humor me and pretend you are.
- Cut and paste your finely crafted SQL masterpiece into the blank window
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
- click the run as SQL command directly SQL icon and then the green save icon.
- If your SQL is valid, you should see a list of columns from your query like this:
- Drag the fields on the design screen and fuss with them. After fussing, I got a design view like
- Click the which produced an 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
Tracked: Feb 14, 02:35