9.3 - 2   

The 2.1.0 release of PostGIS is now available.

The PostGIS development team is proud to release PostGIS 2.1.0. As befits a minor release, the focus is on speed improvements, more features, and bug fixes.

If you are currently using PostGIS 2.0+, you can go the soft upgrade path:

ALTER EXTENSION postgis UPDATE TO "2.1.0";
 

If you are running 2.1.0 of beta or an unreleased version (and the above step fails with error “extension postgis has no update path from version x.x.x to version 2.1.0”) , you need to first copy the file in share\extensions\postgis—2.0.3—2.1.0.sql and change the 2.0.3 to the x.x.x noted in the error you are running. Then follow the above upgrade step again.

Users of 1.5 and below will need to go the hard-upgrade path documented in manual: http://postgis.net/docs/manual-2.1/postgis_installation.html#hard_upgrade

Best served with a bottle of GEOS 3.4.1 and PostgreSQL 9.3beta2 (planned release September).

One of the big changes in PostGIS 2.0 was that the geometry_columns table became a read only view. This change was not without sacrifice. On the plus it meant a table no longer needed to be maintained which was a relief for those who had to constantly ensure the sideline table was up to date. Also on the plus it meant we could base the views on the current user so that if a user connected, they would only see tables they had access to. The sacrifice made was a bit of speed. In most use cases, the speed difference is hardly noticeable and only noticeable if you have a query constantly polling this table. However if you have a database of 15,000 geometry tables it could take as long as 450ms as noted in this ticket.

So just playing with ideas, could the new PostgreSQL 9.3 event triggers and materialized views provide a solution. Keep in mind this is just food for thought. We're not condoning people go out and hack their PostGIS install. We suspect if we implement such a thing in PostGIS core it will change from what we propose here. If you saw our prior article Caching data with materialized views and statement level triggers you know where this is going. We'll add yet another concept to this cocktail and that is what we'll call schema hiding which by that we mean counting on the order of a search_path to hide a named table/view you don't want.

One exciting new feature coming in PostgreSQL 9.3 is materialized views. Materialized views is really a mechanism for caching data of a query. It is especially useful if you have long running queries where the answers change infreqently. There are many things unfortunately that materialized views won't do where you are still better off with regular views.

• They don't refresh themselves automatically. Thus requiring a cron job/pgagent job or a trigger on something to refresh.
• They can't be user dependent or time dependent. For example if you have a view that does something like WHERE user=current_user(), then a materialized view is out of the question.

I've been having issues with PostgreSQL error handling mostly on windows 64-bit using binaries I build with mingw64 and using under VC++ built PostgreSQL. Most of which I don't understand. As many have pointed out these issues MIGHT go away or be easier to debug if PostGIS was built with same tool chain as EDB VC++ distributions. I can't argue with that especially since its never been done.

The good news is changing my build chain from mingw64 GCC 4.5.4 to mingw64 GCC 4.8.0 has eradicated all of these issues, and even ones I had with PL/V8 that happended both in a pure mingw64(w32/w64) as well as PostgreSQL VCC (32/64 bit) environment. It did introduce this minor annoying nat, presumably because my libpq.dll now has a dependency on user32.dll (which just seems wrong). The other nat is that all the binaries I built that have c++ in them (depend on libstd++) now need to be recompiled which means my prior builds of PostGIS will not be compatible with my upcoming 4.8.0 ones. A real pain since for PostGIS/pgRouting I've got like at least 15 of those dependencies and growing. So I've decided to perform this exercise just for PostgreSQL 9.3 -- a new beginning at PostGIS 2.1 and see how it goes before I bother with 9.2, 9.1. These issues really only affect PostgreSQL 64-bit PostGIS users and as a 64-bit user, you may never have even come across them.

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.

Now that PostgreSQL 9.3 beta1 has been released we've started to jump start our experimentation by compiling our favorite extensions. First on the list is PL/V8 js.

This was compiled against 9.3beta1 for 64-bit and 32-bit and plv8 version 1.4.0. We briefly tried with the EDB windows builds which we downloaded from: http://www.enterprisedb.com/products-services-training/pgbindownload and seems to work fine.

• PostgreSQL 9.3 plv8 32-bit download
• PostgreSQL 9.3 plv8 64-bit download

We hope windows users find these useful.

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.

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:

CREATE EXTENSION pgrouting;

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:

http://postgis.net/windows_downloads

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.

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.

So here are the tests we did to experiment with:

In the last article we said you can't have a LEFT JOIN with LATERAL. Turns out we were mistaken and YES indeed you can and when you do it is equivalent or more powerful than SQL Server's OUTER APPLY.

Let us say that in our query we wanted all my zips returned but for ones that had keys where the value is in a certain range, we want those keys returned. we'd do this. The fact we need all even if they have no such keys necessitates us putting the condition in the ON rather than the WHERE.

SELECT zip, (h).key, (h).value As val
 FROM zcta5 AS foo 
   LEFT JOIN LATERAL each(hstore(foo) - 'zip'::text)  As h 
    ON ((h).value BETWEEN '12345' and '14567')
    ORDER BY zip
   limit 5;

Output would be

 zip  |  key   |   val
------+--------+----------
00601 |        |
00602 |        |
00603 |        |
00606 | awater | 12487.00
00610 | hu10   | 12618

One of the improvements coming in PostgreSQL 9.3 is the new LATERAL SQL clause. LATERAL allows you to write more succinct code than you would be able to otherwise and will be a welcome companion to extensions like hstore and PostGIS which both have a plethora of set returning functions. In this article, I'll just demonstrate it's use with hstore and subsequent I'll talk about it's potential use in PostGIS raster,geometry, and topology for exploding subelements.

One thing I was very interested in aside from the succinctness is whether it will be more performant than the older approach. A perfect test case for hstore would be the example we just demonstrated in Unpivoting data in PostgreSQL. For this example, we'll rewrite the hstore view using LATERAL instead of employing a subselect.

One of the great benefits of building a project like PostGIS ontop of PostgreSQL is that when PostgreSQL stands higher, PostGIS becomes that much more powerful without lifting a finger. PostgreSQL development speed has been accelerating which is pretty exhilarating from a dependent project Point of view. Now that PostgreSQL 9.3 is in full-swing we wanted to itemize some things we see coming in this release that will have a great impact on PostGIS use and development. We'd also like to point out items we don't yet see listed that the PostGIS development team has been long waiting for. We'll go over both in this article.