waiting_postgis_22

The PostgreSQL 9.5 FDW api changed a little bit between 9.5beta2 release and 9.5rc1. This means that many FDWs may be broken at the moment. I patched up the OGR_FDW to be compatible with 9.5rc1 and Paul merged this into the ogr_fdw repo.

That said I have not repackaged the Windows PostGIS 2.2.0 9.5 bundle to have this updated library, so as such, if you upgraded to PostgreSQL 9.5rc1, your ogr_fdw will crash your backend if you are using the ones on Stackbuilder. However there are newer versions compiled against 9.5rc1 with the patch on winnie's 9.5 extras folder - http://winnie.postgis.net/download/windows/pg95/buildbot/extras.

We plan to do another release with this patched version (or newer patched version), for PostGIS 2.2.1, which should be out within the next week or so.

I also submitted a patch for ogr_fdw for the new IMPORT FOREIGN SCHEMA support which is available for 9.5+. This patch has not been accepted yet, but I'm hoping it will be in some shape or form before 9.5/PostGIS 2.2.1 release time. How to take advantage of this new feature is briefly described in Import Foreign Schema for ogr fdw for PostgreSQL 9.5. If any one on windows is in a rush to try this new feature, let me know and I'll spin up a binary from my ogr_fdw fork. The IMPORT FOREIGN SCHEMA feature is much more useful than I dreamed it would be for ogr_fdw so I'm very excited.

The PostGIS 2.2.0 bundles for windows for PostgreSQL 9.3-9.4 (both 32/64-bit) and PostgreSQL 9.5beta1 64-bit are now on stackbuilder. I'm working out some compile issues for the PostgreSQL 9.5beta1 32-bit so don't have those ready yet. This includes pgRouting 2.1.0 and inaugural release of PostGIS SFCGAL extension, OGR FDW spatial foreign data wrapper, and pgPointCloud on windows.

I have also updated our popular BostonGIS - Part 1: Getting Started With PostGIS: An almost Idiot's Guide to utilize PostgreSQL 9.5beta1 and PostGIS 2.2. Still on our todo is to update the loader/query example and also the pgRouting tutorial for pgRouting 2.1.0. A ton has changed in pgRouting 2.1.0, so be sure to buy our book pgRouting: A Practical Guide which will have a preview release sale coming soon with first 4-5 draft chapters available and all on final publication all chapters (approximately 10-12).

Check out the PostGIS Bundle 2.2 extensions higlighted in yellow in pgAdmin extensions drop down

To take full advantage of what PostGIS 2.2 has to offer (notable true KNN distance for geometry, geography, and 3D geometries -- not just bounding box like is available for PostgreSQL 9.4 and below), you need to use PostgreSQL 9.5. So start testing out your apps on PostgreSQL 9.5 now so you will be ready when it hits release.

One of the new features coming in PostGIS 2.2 is ST_ClipByBox2D (thanks to Sandro Santilli's recent commits funded by CartoDB ). However to take advantage of it, you are going to need your PostGIS compiled with GEOS 3.5+ (very recent build) which has not been released yet. Windows folks, the PostGIS 2.2 9.3 and 9.4 experimental binaries are built with the latest GEOS 3.5 development branch, so you should be able to test this out with Winnie's experimental builds.

Since the dawn of PostGIS, PostGIS users have needed to mutilate their geometries in often painful and horrific ways. Why is ST_ClipByBox2D function useful, because its a much faster way of mutilating your geometries by a rectangular mold than using ST_Intersection. Why would you want to mutilate your geometries? There are many reasons, but I'll give you one: As your geometry approaches the area of your bounding box and as your bounding box size decreases, the more efficient your spatial index becomes. You can consider this article, Map dicing redux of the article I wrote (eons ago) - Map Dicing and other stuff which describes the same approach with much older technology. Though I will be using more or less the same dataset Massachusetts TOWNSSURVEY_POLYM (its newer so you can't really compare) and I tried to simplify my exercise a bit (not resorting to temp tables and such), my focus in this article will be to compare the speed between the new ST_ClipByBox2D approach and the old ST_Intersection approach. The spoiler for those who don't have the patience for this exercise is that using ST_ClipByBox2D at least on my sample data set on my puny Windows 7 64-bit desktop using PostgreSQL 9.4beta2 64-bit was about 4-5 times faster than using ST_Intersection. There was a downside to this speedup. With the ST_Intersection approach, I had no invalid polygons. In the case of ST_ClipByBox2D, I had one invalid polygon. So as noted in the docs, use with caution. We'd be very interested in hearing other people's experiences with it.

One other benefit that ST_ClipByBox2D has over ST_Intersection which I didn't test out is that although ST_Intersection doesn't work with invalid geometries, ST_ClipByBox2D can.

For these exercises, I'm going to also abuse the PostGIS raster function ST_Tile for geometry use, cause for some strange reason, we have no ST_Tile function for PostGIS geometry. For those who missed the improper use of ST_Tile for raster, refer to Waiting for PostGIS 2.1 - ST_Tile.