Friday, February 22. 2013

Just recently saw Michael Paguier's article on the recent commit of postgres_fdw so I was excited to try this out for myself. Questions we wanted to answer/experience were:

Can I connect against different versions of PostgreSQL? Yes to test I tried connecting from my PostgreSQL 9.3 dev instance to my 9.2 instance on another box.

Can I use a foreign type like -- you guessed it PostGIS if we have same version of PostGIS installed on both databases? Yes. I didn't try with different versions so not sure if that would work especially since the structure changed a bit between 1.5 and 2.0. I suspect 2.0 and 2.1 would work fine and might be a good way to cheat run 2.1 on 9.3 but creating derivatives of my 2.0 data with functions only available in 2.1.

Will it use my spatial index? No or at least we couldn't figure out how and I don't see how it is possible, but would be nice if it could be

Can I have a table defined with dynamic SQL? Nope or at least the docs don't mention it. This is a feature I really liked about the ODBC_FDW
because often times I want the table to be filtered especially if I need my filter to use an expensive index like a spatial one or utilize functions only available on the foreign server. If that were part of the definition of the foreign table, then it could in theory process that part on the foreign server. So this makes postgres_fdw not a good replacement for dblink in many cases.

Thursday, February 14. 2013

A while back I mentioned to Bborie (aka dustymugs) and Sandro (aka strk): We're missing ability to label our images with text.
Picture this: What if someone has got a road or a parcel of land and they want to stamp
the parcel id or the road name on it and they don't want to have to depend on some piece of mapping software. Many report writers and databases can talk to each other directly
such as we demonstrated in Rendering PostGIS raster graphics with LibreOffice
and we've got raster functionality in the database already. Can we cut out the middleman?
Some ideas came to mind. One we could embed a font lib into PostGIS thus having the ultimate bloatware minus the built-in coffee maker.
strk screeched. He's still recovering from my constant demands of having him upgrade his PostgreSQL version. Okay fair enough.

Bborie suggested why don't you import your letters as rasters and then vectorize them. So that's exactly what we are going to demonstrate in this article
and in doing so we will combine some of the new features coming in PostGIS 2.1 we've been talking about in Waiting for PostGIS 2.1 series.

Warning this article will have a hint of Rube Goldbergishness in it.
If you are easily offended by seeing stupid tricks done with SQL, stop reading now.
We are going to take a natural vector product and rasterize it just so
we can vectorize it again so we can then rasterize it again. Don't think about it too much. It may trigger activity in parts of your brain you didn't know were there
thus resulting in stabbing pains similar to what you experience by quickly guplping down a handful of Wasabi peas.

So here are the steps for creating your own font set you can
overlay on your geometries and rasters.

See if you can spot the use of window functions and CTEs in these examples.

Friday, February 08. 2013

PostgreSQL allows you to customize statement logging in numerous ways. You can globally set the level of logging you want at both the postgresql.conf (that will affect all databases) or at the database level using the various log_statement* variables. Most of these
are documented in runtime config logging.

For this brief article, we'll talk about the log_line_prefix variable. By default this variable is very minimalistic and just prefixes the log lines with the date time of the statement. If you have just one database (like the way Oracle runs) this is not a big deal,
but if you are like us and run several databases on one PostgreSQL instance, it would be nice to as part of the prefix include the database name.