Postgres OnLine Journal     

One of the things that really frustrated me about the KNN GIST distance box box centroid operators that came in PostgreSQL 9.1 and PostGIS 2.0 was the fact that one of the elements needed to be constant to take advantage of the index. In PostGIS speak, this meant you couldn't put it in the FROM clause and could only enjoy it in one of two ways.

PostGIS 2.1.0 beta2 is out. Details on what's new in it are in official news release: http://postgis.net/2013/05/11/postgis-2-1-0beta2. This is the first version of PostGIS to work with PostgreSQL 9.3, so if you are planning to experiment with PostgreSQL 9.3 coming out soon, use this one. Also check out the documentation in new ePUB offering format if you have an ereader and let us know how it looks. It seems to vary alot depending on what ePub reader used.

For windows users, we've got binary builds available compiled against PostgreSQL 9.3beta1 (and also available for 9.2 9x32,64) and 9.0,9.1 (x64). Details on windows PostGIS downloads page: http://postgis.net/windows_downloads. It does not yet have the new Advanced 3D offering (provided by SFCGAL https://github.com/Oslandia/SFCGAL), but we hope to have that compiled and packaged with the binaries before release time.

PostgreSQL 9.3 will be coming out in beta soon and with that, some who want to experiment with both PostGIS and PostgreSQL 9.3 have asked if they can use PostGIS 2.0. The answer is NO. A lot of major changes happened in PostgreSQL 9.3 that required us to patch up upcoming PostGIS 2.1. These changes were not backported to 2.0 and I personally do not plan to back-port them unless lightning strikes me and I escape unscathed, a big wad of cash falls from the sky, or for some reason we can't make the 2.1 cut before 9.3 comes out. So if you are planning to experiment with PostgreSQL 9.3, PLEASE use PostGIS 2.1 development branch. I will try to make sure we release 2.1 before PostgreSQL 9.3 comes out even if I have to resort to hitting some people over the head with a rubber bat :).

If ever in doubt what versions of PostGIS works with what versions of PostgreSQL /GEOS / GDAL, please refer to the matrix that we try to keep up to date. http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS.

Now some people might say "Isn't it cruel not to support PostGIS 2.0 for 9.3", and my answer is "it's crueler to". The reason is simple. We have limited bandwidth for testing permutations of things and the more permutations of things we support, the dirtier our code base becomes making it harder to maintain and also the less time we can devote to properly testing each permutation. I'd rather say we don't support something than to do a half-hearted job of supporting all. On a slightly different, but also pragmatic note, package maintainers (except for windows maintainers :)) generally only carry one version of PostGIS per version of PostgreSQL, and I'd rather users getting from packages see our best foot than a two year old aging foot.

Note: that going from PostGIS 2.0 to 2.1 is a soft upgrade so you can install 2.1 on your existing PostgreSQL 9.2 without dump restore and then you should be able to pg_upgrade over to 9.3 if your database is too big to dump restore.

In Happy Valentine PostGIS we demonstrated how to use PostGIS raster to decipher letters from a raster, vectorize them and then reuse this vectorized letters to form new words. Admittedly the letters were a bit grainy since they were vectorizations of low res rasters and I didn't bother smoothing them. Bruce Rindahl offered a script to SVG to PostGIS geometry and using Batik to convert a font file to SVG format and gave me a hi-res converted kankin fontset. I still haven't figured out how his script works.

Bborie Park thought that was all too complicated and thought (as I have always) that we need an ST_GeomFromSVG function for PostGIS of which he is on a mission to create when he's less busy. He also suggested I wrap my letter writer function as an extension. Taking all these ideas, I formulated an extension you install with

CREATE EXTENSION postgis_letters;

postgis_letters (http://www.bostongis.com/postgisstuff/postgis_letters_extension.zip) is an sql / data extension containing mostly data, but as the name suggests relying on PostGIS. The data are geometry vectors of the kankin font. I plan to add in more free fonts later once I figure out how to use Bruce's script or Bborie comes up with a better way and also more positioning logic and handling of spaces. So its a little rough at the moment. The purpose of the extension is so I can write words on my images in reports e.g. state names or overlay labels on geometry features like roads and land. Using the power of both geometry/raster you can have a fully functioning report image writer that would return a fully formed image for use in LibreOffice (or for my ASP.NET web apps Active Reports.NET). This wouldn't rely on any mapping server to draw images (just pure PostGIS/PostgreSQL). Hopefully with new and improved binary features coming in PSQL for (looks like 9.4), outputting these raster images from psql will also be trivial. While on my mission to do something useful, I got distracted by something more entertaining: describing spatial processes with words. Here it goes.

One of the features of PostGIS (pain to some however you look at it), is that PostGIS library file is versioned by minor version. The library will have for example a postgis-2.0 or postgis-1.5.dll / .so to denote the version. Each version of PostGIS can be compiled to run on usually about 3 or 4 versions of PostgreSQL.

Since PostGIS is not part of PostgreSQL proper and has to be installed separately, it is possible to run a different version of PostGIS in each database of a cluster. While this is a great feature for PostGIS developers and also great for users who want to keep their old legacy PostGIS apps, while testing or creating new apps with the PostGIS 2.0 or experiment with 2.1 development series, it does pose some obvious challenges.

For example you can't simply just upgrade your cluster to a new version of PostgreSQL. You need to make sure the new cluster has the various versions of PostGIS compiled and available. One step to that end is figuring out exactly what version of PostGIS each database in your cluster is running. Here is a quick psql script I wrote up to help with that.

We've got experimental pgRouting windows binaries available for windows PostgreSQL 9.2 32-bit and 64-bit for pgRouting 1.0.7 development branch. More details on our Boston GIS blog page.

The final versions we plan to release with upcoming PostGIS 2.1 PostgreSQL 9.2 on stackbuilder as part of the PostGIS install. Barring no difficulties we'll also have experimental binaries for PostgreSQL 9.3 releases once 9.3 reaches beta.

This version and upcoming pgRouting versions support the PostgreSQL extension model, so if you have postgis already installed, its just an additonal simple step:

The 3rd MEAP update of PostGIS In Action, 2nd Edition will be going out very shortly to Early Action purchasers. Keep your eyes peeled. Lots of errata corrections in previous chapters and appendix, and one very VERY new chapter on Raster functions which took a ton of time to write, so hopefully it will be well received. Our progress on the chapters is listed on PostGIS In Action 2nd Edition Chapters and all the ones marked as completed you will find in the MEAP. The ones with paperclips have downloadable code and data which you can click on the paperclip to download.

Regarding Raster, the Raster Function chapter is just merely the tip. You'll see a lot more raster usage in upcoming Relating two or more spatial objects and Raster Processing chapter which we are still fleshing out.

We are immensely grateful to all the early action subscribers who have posted errata or general comments about what can be clarified or examples that don't work. General comments about what specific kinds of examples you'd like to see are also welcome. Your opinions really influence what we write and make for a better book.

We have all the setup executables for PostgreSQL 8.4-9.2 32-bit and PostgreSQL 9.1-9.2 x64-bit for PostGIS 2.0.3. We'll have the 9.0x64-bit ones up shortly but do have the binaries.

We are planning to release these to stackbuilder sometime next week, but would like some feedback first with any issues people run into.

One person already reported a serious bug in our original packaging which we fixed. If you already installed 2.0.3 the x64-bit version or any of the 9.2 versions (both x32 and x64) that was not marked as -2.zip, -2.exe, you'll want to reinstall. The 8.4-9.1 32-bits were not affected so just have a -1.

Packages can be downloaded from:
http://download.osgeo.org/postgis/windows

General details on what is included:

As always if you are installing for PostgreSQL x64 make sure to pick a zip or installer with 64 in the name. The unmarked or marked with 32 are just for PostgreSQL 32-bit.

Should I install using PostGIS extension? is an FAQ that comes up quite a bit in PostGIS circles and unfortunately askers get mixed answers. In How to upgrade your database to PostGIS 2.0 we covered WHY you should use extensions. In this article I'll use my Lincoln-Douglas debate skills to argue why you shouldn't. Keep in mind that I put a great deal of effort in fitting PostGIS extensions into the existing PostGIS build structure and I eat my own dogfood, so I might be a little biased and a poor defender of the counter argument.

Just a heads up, the Second Edition of PostGIS In Action has officially reached MEAP stage meaning you can buy now and get draft chapters as we write them and get final copy when released. Have first drafts of 5 chapters so far. And many more coming shortly.

It is currently the Manning Deal of the Day

January 22, 2013
PostGIS in Action
Second Edition
Get half off the MEAP eBook
or MEAP pBook
Enter pgislaunchau in the Promotional Code box when you check out.
http://www.manning.com/obe2 With purchase you also get the E-Book copy of the first edition.

This was an article we were meaning to write and got reminded by Szymon Guz latest PostgreSQL search_path behavior. The PostgreSQL search_path variable allows you to control what order schemas are searched and which schemas do not require schema qualification to use tables/views/functions in the schema. The pg_catalog schema is one that never requires schema qualification even if you don't add it to your schema search_path. Schemas are searched in the order they are listed and when creating new objects (e.g. tables, views, function, types) that are not schema qualified, they are always created in the first schema of the search path. Any objects not in a schema listed in the search_path must be schema qualified.

We were recently looking for an open source light-weight database web administration tool that would be preferably database cross-platform and would work on either ASP.NET or PHP. Adminer fit the bill. Adminer is written in PHP. It supports, out of the box PostgreSQL, MySQL, SQLite2 and SQLite3, and even SQL Server and Oracle. This covers most of the databases we commonly use. Here we'll go over some other features we liked about it that are rare in other web database administration tools.

In the spirit of Depesz, Waiting for PostgreSQL 9.3 series, we've started our own little PostGIS series on our Boston GIS blog called Waiting for PostGIS 2.1 that showcases all the new cool features coming in PostGIS 2.1 as they get committed to the code base. Check it out.

If you are on windows, the PostGIS 2.1 window binary builds get built whenever there is a change in PostGIS 2.1 code base by Winnie our windows PostGIS build bot, for PostgreSQL 9.0-9.2 (64-bit) and 9.2 (32-bit), so you can't use the I can't compile excuse if you are on windows, not to test out the new changes :). You can download these from Windows Experimental Builds. We are hoping to do the same for Debian Squeeze (6) soon. The main reason we built our bot Debbie on Debian (which makes the source tarballs), is because Debian is a very popular deployment platform for PostGIS, that is currently underserved so we have many Debian users frustrated at not having a readily available PostGIS 2.0 release for example.

If you do a lot of web-based GIS applications, a common desire is to allow a user to draw out an area on the map and then do searches against that area and return back a FeatureCollection where each feature is composed of a geometry and attributes about that feature. In the past the format was GML or KML, but the world seems to be moving to prefer JSON/GeoJSON. Normally you'd throw a mapping server that talks Web Feature Service , do more or less with a webscripting glue, or use a Webservice such as CartoDb that lets you pass along raw SQL.

In this article we'll demonstrate how to build GeoJSON feature collections that can be consumed by web mapping apps. using the built in JSON functions in PostgreSQL 9.2 and some PostGIS hugging. Even if you don't use PostGIS, we hope you'll come away with some techniques for working with PostgreSQL extended types and also how to morph relational data into JSON buckets.

There has been a lot of talk lately about schemaless models touted by NoSQL groups and how PostgreSQL fits into this New world order. Is PostgreSQL Object-Relational? Is it Multi-Model. We tend to think of PostgreSQL as type liberal and it's liberalness gets more liberal with each new release. PostgreSQL is fundamentally relational, but has little bias about what data types define each column of related tables. One of PostgreSQL great strengths is the ease with which different types can coexist in the same table and the flexible index plumbing and plan optimizer it provides that allows each type, regardless of how wild, to take full advantage of various index strategies and custom index bindings. Our 3 favorite custom non-built-in types we use in our workflow are PostGIS (of course), LTree (Hierarchical Type), and HStore (Key-Value type). In some cases, we may use all 3 in the same database and sometimes the same table - where we use PostGIS for spatial location, LTree for logical location, and Hstore just to keep track of random facts about an object that are easier to access than having a separate related table and are too random to warrant devoting a separate column for each. Sometimes we are guilty of using xml as well when we haven't figured out what schema model best fits a piece of data and hstore is too flat of a type to work. The advent of JSON in PostgreSQL 9.2 does provide for a nested schema-less model similar to what the XML type offers, but more JavaScript friendly. I personally see JSON as more of a useful transport type than one I'd build my business around or a type you'd use when you haven't figured out what if any structure is most suitable for your data. When you have no clue what structure a piece of data should be stored, you should let the data tell you what structure it wants to be stored in and only then will you discover by storing it in a somewhat liberal fashion how best to retrofit in a more structural self-descriptive manner. Schemas are great because they are self-describing, but they are not great when your data does not want to sit in a self-described bucket. You may find in the end that some data is just wild and refuses to stay between the lines and then by all means stuff it in xml or json or create a whole new type suit it feels comfortable in.