Postgres OnLine Journal (Entries tagged as postgis)

CMake support for PostGIS planned

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 16 Jun 2013 23:28:00 -0400

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.

Continue reading "CMake support for PostGIS planned"

KNN GIST with a Lateral twist: Coming soon to a database near you

nospam@example.com (Leo Hsu and Regina Obe) — Tue, 21 May 2013 20:35:00 -0400

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.

Continue reading "KNN GIST with a Lateral twist: Coming soon to a database near you"

PostGIS 2.1.0 beta2 is out and windows binaries available

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 12 May 2013 18:38:00 -0400

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.

Which PostGIS should you use with PostgreSQL 9.3

nospam@example.com (Leo Hsu and Regina Obe) — Tue, 30 Apr 2013 00:30:00 -0400

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.

Word Play with Spatial SQL

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 21 Apr 2013 20:44:00 -0400

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.

Continue reading "Word Play with Spatial SQL"

Determine which version of PostGIS each database is running

nospam@example.com (Leo Hsu and Regina Obe) — Sat, 13 Apr 2013 18:32:00 -0400

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.

Continue reading "Determine which version of PostGIS each database is running"

pgRouting windows binaries for PostgreSQL 9.2 32-bit and 64-bit

nospam@example.com (Leo Hsu and Regina Obe) — Tue, 09 Apr 2013 00:26:00 -0400

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;

PostGIS In Action 2nd Edition MEAP 3 Update

nospam@example.com (Leo Hsu and Regina Obe) — Fri, 05 Apr 2013 19:06:00 -0400

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.

Continue reading "PostGIS In Action 2nd Edition MEAP 3 Update"

PostGIS 2.0.3 for Windows x32 and x64 installers

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 17 Mar 2013 01:48:00 -0400

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.

Should I install using PostGIS extension

nospam@example.com (Leo Hsu and Regina Obe) — Wed, 06 Mar 2013 16:37:00 -0500

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.

Continue reading "Should I install using PostGIS extension"

PostGIS in Action 2nd Edition reached MEAP

nospam@example.com (Leo Hsu and Regina Obe) — Tue, 22 Jan 2013 16:06:00 -0500

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.

Schema and search_path surprises

nospam@example.com (Leo Hsu and Regina Obe) — Wed, 28 Nov 2012 23:52:00 -0500

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.

Continue reading "Schema and search_path surprises"

Adminer web-based database administration

nospam@example.com (Leo Hsu and Regina Obe) — Mon, 15 Oct 2012 22:51:00 -0400

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.

Continue reading "Adminer web-based database administration"

Waiting for PostGIS 2.1 series and Autobuilds

nospam@example.com (Leo Hsu and Regina Obe) — Thu, 04 Oct 2012 00:58:00 -0400

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.

Creating GeoJSON Feature Collections with JSON and PostGIS functions

nospam@example.com (Leo Hsu and Regina Obe) — Fri, 24 Aug 2012 02:55:00 -0400

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.

Continue reading "Creating GeoJSON Feature Collections with JSON and PostGIS functions"