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.
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.
as shown in
host=localhost port=5440 dbname=postgis20_sampler user=postgres password=whatever
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.
What I really wanted to do was render a randomly complex query that returns images like this one.
SELECT foo.id, ST_AsPNG( ST_AsRaster( CASE WHEN id = 1 THEN ST_Buffer( ST_MakeLine( ST_Translate( ST_Transform(foo.geom,26986),x*random()*500,y*random()*500) ) ,2) ELSE ST_ConcaveHull( ST_MakeLine( ST_Translate( ST_Transform(foo.geom,26986),x*random()*500,y*random()*500) ),0.90) END , 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;
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.