editor note - 4   

Just read that Microsoft has formed a new foundation called CodePlex foundation, presumably to spinoff their Code plex site and allow it to stand separately from Microsoft. The mission appears to be to allow an easier avenue for developers working for proprietary software companies to contribute to open source projects.

Monty has some details about this on his blog The CodePlex Foundation: Why is Microsoft founding it?.

The line up of people they have on their advisory board (including Monty) and board of directors is interesting CodePlex About. I'm particularly happy that Miguel De Icaza is on the board since he is one of my favorite people and I believe shares my pragmatic ideals on the synergy between open source and non-open source software. I wonder what it takes to get on this board. It would be really nice if someone in the PostgreSQL community were on this board just to ensure the needs of the PostgreSQL community (especially our growing number of windows users) is well represented.

As to the argument of Monty's that software for sell is dying, not sure I quite agree though haven't given it much thought. Certainly I would like to think that service for sell is rising since that's the business we are in and enjoy most. One thing I believe is that software is getting more complicated and people expect more. With that said even as a company that sells software, you would be foolish not to try to leverage on the open source software out there that fits nicely into your codebase. You just won't be able to compete even with the sole proprietor next door who is with it.

As David Page already noted, Leo and I are taking over responsibility of building PostGIS windows one-click installers/stack builder from Mark Cave-Ayland. The PostGIS 1.4 windows packaging was a little late in coming this time since it was our first and also some things changed in the PostGIS packaging for 1.4. Even so we made some mistakes such as statically compiling libproj in with the postgis-1.4.dll and forgetting some new images in the packaged html help, which we will fix in 1.4.1 release.

Mark will still be providing a supporting role and helping out when we screw up or helping us if we run into compile issues as we go along so he's not going away; he will be a great safety net. When Mark started his role a long time ago, he was as many would like to say "Very entrenched in the dark side," and over the years, he has seen the light. As a result, these moments of catching issues in the PostGIS release cycle that effect windows users such as troubleshooting the memory bug in the loader files that affected Windows Vista users and testing on various Windows OS, has fallen on us, because well we have access to all windows os.

It also became painful for Mark to walk in the shadow of darkness once he had seen the light. Luckily we are still windows addicts so this having to constantly test on Windows and building for Windows is something we would naturally do anyway and yes as shocking as it sounds we do run some production PostgreSQL apps on windows and it works pretty well, thank you very much. We don't expect this to change any time soon.

As part of this change, we hope to provide more interim windows builds of PostGIS so windows users can experiment with future releases before they come out. Yes compiling on windows is a tad bit more difficult than on Linux. These PostGIS windows experimental builds can be found http://postgis.net/windows_downloads

Main changes in PostGIS

• The PostGIS steering committee has agreed to be good and not be adding new functions between micro releases of PostGIS as we have done in the past and as we've been smacked around for. As part of that change, from PostGIS 1.4 moving forward each micro version will overwrite the previous micro version in the MS Windows registry. E.g. 1.4.1 will overwrite 1.4.0 so no need to uninstall the old and reinstall to get rid of registry junk. Just install on top of your existing 1.4.
• As of PostGIS 1.4 it is possible to run different versions of PostGIS in different databases on teh same PostgreSQL server install since the .so/.dll from minor to minor have unique names (naming is postgis-1.4.so (postgis-1.4.dll), postgis-1.5.so etc). This is mostly useful for testing and comparing different versions of PostGIS before you officially upgrade and if you have several different spatial apps using different databases, you don't risk breaking them all at once.
• PostGIS is now an official incubation project of OSGEO. Things are still being drafted. But I guess that means our PostGIS defacto steering committee composed of Kevin, Paul, Mark, and myself is now more or less official.

I am very excited to report that we have finally released PostGIS 1.4. We are still preparing the windows binaries UPDATE: Windows binaries are now available and installers which will become available in the coming week for PostgreSQL 8.2,8.3, and 8.4. Below are the details excerpted from Paul Ramsey's postgis news announcement. We also recently came back from OSCON 2009 where we gave a talk on Tips and Tricks for writing PostGIS spatial queries. In that talk we showcased some of the new features of PostGIS 1.4, as well as demonstrating how the new Windowing and Common Table Expressions introduced in PostgreSQL 8.4 simplifies and provides more options for writing PostGIS spatial queries. We'll be making the slides and data available shortly.

RefCardz DZone PostgreSQL Essentials -- stay tuned

On another exciting note, not only are we working on our upcoming Manning book PostGIS in Action, but we have contracted with DZone RefCardZ to do a PostgreSQL Essentials. Recall we had discussed this a while back that how come there is one for MySQL, but none for PostgreSQL and that someone should write one up for PostgreSQL, preferrably someone who is writing a PostgreSQL related book. So I guess that someone would be us.

We are currently finalizing our first draft of this. Sadly we are a little behind on schedule, but hope to make the time up in the coming month. We'll provide more details on sponsorship and availability as the story unfolds. You can expect to see the general essential stuff like, backup, restore, the growing family of PostgeSQL PL/Languages and examples of them, basic architecture, common SQL constructs. In addition we will show case some of the new PostgreSQL 8.4 enhancements.

PostgreSQL 8.4 has come out, and while I am a bit disappointed that PostGIS 1.4 has not come out for fear that we've missed a bit of the PostgreSQL 8.4 momentum, I am happy that we are nearing closer and just maybe we'll have it out by end next week. We now have a PostGIS 1.4RC1 http://postgis.refractions.net/download/postgis-1.4.0rc1.tar.gz tar ball as well as experimental binary builds of this for windows user's running PostgreSQL 8.3 http://postgis.refractions.net/download/windows/pg83/experimental/postgis/ or PostgreSQL 8.4 http://postgis.refractions.net/download/windows/pg84/experimental/postgis/. Please give both a try.

Working in the Cathedral Really?

As Paul duly noted in his blog entry Working in the Cathedral the model for PostGIS development is morphing, but I wouldn't call this morphing process one that is entirely toward the Cathedral model. Unlike the perceived Cathedral model, I would like to think we will have more frequent releases and beta releases, perhaps parallel experimental builds and most importantly, more fun. The main idea being making it much easier for mere mortals and fake mortals to taste test the cookies in the oven while they are cooking. By fake I mean unit tests, build bots, and computer generated people where the fear of destruction is removed. I feel this is the similar model PostgreSQL goes by or is trying to achieve.

This month is jam packed with a lot of PostGIS news.

PostGIS 1.3.6 is out

PostGIS 1.3.6 has been released. It is mostly a bug fix relase and is the first PostGIS that can be compiled under PostgreSQL 8.4 beta. Details can be found at PostGIS 1.3.6 release notes. We don't have Windows binaries ready yet, but expect to see that in the next week or so.

We are writing a PostGIS Book

Leo and I have been writing a PostGIS in Action book for the past couple of months, and now that it is finally listed on the Manning website, we can talk about it. We are working on our chapter 4 right now. If you are interested in learning PostGIS, check it out. The first chapter is free and with the Manning Early Access Program (MEAP), you can purchase the book now and have great influence on the direction of the book.

The book starts off hopefully with a gentle introduction to OpenGIS Consortium (OGC) spatial databases and concepts in general and PostgreSQL/PostGIS in particular. As we move further into the book, we cover more advanced ground. We plan to cover some of the new PostgreSQL 8.4 features in conjunction with PostGIS, writing stored functions to solve spatial problems and some of the other new exciting stuff and ancillary tools for PostGIS such as PgRouting, Tiger Geocoder, and WKT Raster.

Given all that ground, I suspect our estimate of 325 pages, may be a little low when all is said and done. It is funny that when we started out, we thought to ourselves -- "How can anyone fill up 325 pages." Turns out very easily especially once you start throwing in diagrams and pictures to demonstrate a point. Diagrams are kind of important to have when describing GIS and geometry concepts. So far its been fun and has forced us to sit down and walk thru all the things we took for granted and thought we understood but didn't. You realize just how little you understand when you try to explain something to someone else who really doesn't understand. So perhaps the process of explaining is the greatest of all learning experiences.

The database industry is getting way too action packed for my blood. First we had Sun buying MySQL, then Oracle buying Sun (thus inheriting MySQL and Java) (recall they already owned InnoDb (the MySQL main transactional engine) and they also by the way own BerkelyDB which is the database engine underlying Subversion repository and they also own all those CRMS and ERPs (Peoplesoft and Seibel), and now we have IBM integrating EnterpriseDb into their DB so that it can look like Oracle Db. What next? Perhaps Microsoft will join the party to integrate EnterpriseDb into their SQL Server offering so SQL Server can look like Oracle and better yet a SQL Server for Linux/Unix to complete the circle.

Today was a very eventful day for PostgreSQL. We'll cover these changes in a bit.

Massive Forking of PostgreSQL project

First in PostgreSQL Announcements - David Fetter announces massive forking of the PostgreSQL project in several factions. We now have the following -- so take your pick:

1. Shizzle: High-performance and Feature-Free
2. MaryMary: Compiled with libhaltingproblem
3. Narcona: Painless installation and setup
4. OurThing: Lots of sources, based in Sicily
5. XPostgres: Everybody who's ever worked on Postgres code, back to UC Berkeley and Illustra.
6. Moon/PostgreSQL: Corporate support, as long as it lasts.

I feel this may be good for the community because it is hard to satisfy all these factions in one project. Now perhaps the newsgroups will be a bit calmer.

PgAdmin has come to an end -- make way for OpenPgAdmin

Dave Page announced today that the PgAdmin team received an offer they couldn't refuse from a very big software company yet to be announced. So they are closing PgAdmin and you will soon be able to purchase the services and support contracts from this new company.

Not to worry, Devrim Gunduz, has forked PgAdmin to form OpenPgAdmin. You can check out the site here http://openpgadmin.info.

I must say as much as we are saddened to see the PgAdmin group leave for more fun escapades, We are happy we finally have an administration tool that has the word Open in its name. If it starts with Open, its got to be open. Now all we need is an OpenPost PostgreSQL fork to go with it. OpenPost I think will be easier to pronounce than PostgreSQL and Postgres and it has Open in its name.

So to add to the list of PostgreSQL project forks, I would like to see another project fork

OpenPost: Its free, open source, fast, feature-rich, easy to use and best of all you can pronounce it and its open.

The OSGEO Toronto Sprint

The OSGEO C-Camp Toronto Sprint was fun, although Leo and I couldn't stay for the whole event. I've never seen people close bugs so quickly. Paul Ramsey and Mark Cave-Ayland were on a marathon run in the PostGIS ring. Olivier Courtin was also following not too far behind with SVG bug fixes and so forth. We also discussed the possiblity of having ST_GeomFromGML, ST_GeomFromGeoJSON, ST_GeomFromKml and so forth and what that would entail. It was great to meet Pierre Racine of WKT Raster fame in person and chat with Mateusz and Sandro Santilli via IRC. Frank Warmerdam, the GDAL god came to our table to provide his big two cents about how WKT Raster meta data should be stored, dealing with large RASTERS and other things I didn't understand.

Mark Leslie in Australia did his part too, though he wasn't present -- he would come into IRC when others had fallen asleep. Such is the way with timezones. He has been working on beefing up the curved support in PostGIS. The FOSS 4G 2009 conference will be in Sydney, Australia.

It was nice to be able to put a face to these people I've talked via newsgroups. It was also strange since most of the clients and many of the people we work with we have never met, so the idea of meeting in person has become a very foreign concept for us.

PostGIS 1.3.5 urgent upgrade if you are running 1.3.4

We were forced to release a 1.3.5 PostGIS upgrade as a result of a bug we accidentally introduced in 1.3.4 during our code cleanup. We apologize for any inconvenience this may have caused people. This bug affects the use of MULTILINESTRINGS and rears its ugly head by giving errors such as invalid circular line string when calling ST_Multi or another odd error when doing a Force collection on a MULTILINESTRING. This hits mapserver users using these geometry types the hardest.

More details of the issue can be gleaned from Paul Ramsey's blog. Warning: PostGIS 1.3.4 + Mapserver

PostGIS 1.3.4 is finally out the door. This version has:

1. Support for 7.3, 7.4, 8.0, 8.1, 8.2, 8.3, 8.4 beta
2. GEOS 2.2.3, GEOS 3.0.3, GEOS 3.1 beta
3. Numerous bug fixes and speed improvements
4. Slightly better documentation
5. addition of function comments to help guide new users while in the psql or pgAdmin environment
6. One new function ST_AsGeoJSON to support javascript apis such as OpenLayers.

Some advanced GEOS functions such as ST_SimplifyPreserveTopology and ST_CoveredBy (both released in 1.3.3) will not be installed unless you are running GEOS 3.0 or above.

MySQL is turning out to be one big soap opera as far as I can tell and as Bruce Momjian has mentioned.

Lets go over some of the interesting episodes of this saga:

1. First Sun buys MySQL
2. Falcon storage engine creator Jim Starkey leaves MySQL/Sun
3. Brian Aker heads Drizzle which is a fork of MySQL that hopes to be a stream-lined implementation of MySQL that leaves out all that nonsense we don't need such as views and stored procs and targeting it self for running on the cloud, optimizing for massive concurrency, and ease of install. I presume Brian still works for MySQL/Sun though.
4. Michael "Monty" Widenius Leaves MySQL and Sun to help with this Drizzle thing, but will still be working on Maria according to Brian (see comment below).
5. And now Jay Pipes leaves as well to work on Drizzle. He will still be a Sun staff engineer on Drizzle, but not the MySQL community leader.

I'm not sure what all these things say about the stability of the MySQL core. I mean should I stick with MySQL 5 or run for the Drizzle, but I think I'll stick with PostgreSQL where ever I can. PostgreSQL may not be quite as interesting from a soap opera perspective, but it seems a tad bit more dependable and I really like my views and stored functions.

One thing I'm really looking forward to have in the upcoming PostgreSQL 8.4 is the introduction of the WITH RECURSIVE feature that IBM DB2 and SQL Server 2005 already have. Oracle has it too but in a non-standard CONNECT BY so is much less portable. This is a feature that is perhaps more important to us for the kind of work we do than the much complained about lack of windowing functions.

I was recently taking a snoop at IBM DB2 newsletter. Why I read magazines and newsletters on databases I don't even use I guess is to see what I'm missing out on and to sound remotely educated on the topic when I run into one of those people. I also have a general fascination with magazines. In it their latest newsletter they had examples of doing Fibonacci and Graphs with Common Table Expressions (CTEs).

Robert Mala's Fibonacci CTE
Robert Mala's Graph CTE

Compare the above to David Fetter's Fibonacci Memoizing example he posted in our comments way back when.

I'd be interested in seeing what solutions David and others come out with using new features of 8.4. We can see a before 8.4 and after 8.4 recipe.

As a slightly off-topic side note - of all the Database magazines I have read - Oracle Magazine is the absolute worst. SQL Server Magazine and IBM DB2 are pretty decent. The real problem is that Oracle's magazine is not even a database magazine. Its a mishmash of every Oracle offering known to man squashed into a compendium that can satisfy no one. You would think that Oracle as big as their database is would have a magazine dedicated to just that. Perhaps there is another magazine besides Oracle Magazine, but haven't found it so I would be interested to know if I missed something.

As many people who know us know we sit on several camps especially when it comes to databases. The camps we sit on are growing rather than shrinking. While we do have our favorites, we understand that peoples needs and comfort levels are different from ours and we try to take that into consideration when making recommendations to people. The only thing that is generally true about the clientele we consult for is that they fit one of the following features:

• Very minimal bureaucratic structure - this generally rules out most fortune 500 companies and shall we say smaller companies who are too bureaucratic for their own good
• Dot com startups/Niche product developers who are looking to keep costs down to a minimum without too much fuss and are trying to produce a product to change the world
• Small companies who have a relatively low IT budget, but are predominantly windows-based
• Mid-sized companies predominantly windows-based or departments with decent IT staff, who are looking for something their staff can easily maintain rather than simply keeping licensing costs down

It has come up as a topic of discussion, now that SQL Server 2008 is coming out soon and with its new fangled geodetic spatial support, how does this change things?

The short answer is - not much except to increase awareness of spatial databases and to give us more business. As part of our due diligence work we have put together a comparison of the 3 databases spatial functionality - Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6 to compliment our Cross Compare of SQL Server, MySQL, and PostgreSQL

There has been a lot of talk lately about PostgreSQL and what MySQL can learn from the PostgreSQL clan. We would like to look at the reverse of that. This article is a bit of a complement to Joshua Drake's What MySQL (and really, Sun) can learn from PostgreSQL.

First of all a lot of staunch advocates of PostgreSQL wonder what exactly is it that MySQLers see in that beast of a database or as Martin Mickos likes to call it The Ferrari of databases?

PostGIS 1.3.3 is out

PostGIS 1.3.3 has been released and is already in the PostgreSQL RPM and soon will be in Yum repository. PostgreSQL 8.3 users who are using PostGIS are encouraged to upgrade because this release contains a fix for a major bug that affected spatial aggregates in 8.3. The windows Application Stack Builder version with the update should be out within this or next week.

Shp2PgSQL Loader now can load plain DBFs

We've been working on the 2007 Topologically Integrated Geographic Encoding and Referencing (Tiger) data recently. For those who are familiar with the US Census current Tiger format, this is the first version to be released in Environmental Systems Research Institute (ESRI) Shape file format. We ran into one small problem. The Tiger data includes related data with no geometry. These come as plain old DBase (DBF) files. Prior versions of Shp2PgSQL could not deal with DBF files with no corresponding Shape (SHP) geometry files, but the version packaged with 1.3.3 can.

I would like to thank Paul Ramsey for checking over my DBF-only patch and squeezing it into this release. Now that I have gotten my hands dirty again with C code, I almost feel like a real programmer. As a side note, even if you don't use PostGIS, this should come in handy for loading any DBF file into PostgreSQL.

New PostgreSQL Yum Repository

We recently had the pleasure of trying out the new PostgreSQL YUM repository for Fedora/RedHat Enterprise/Cent OS distros that is maintained by Devrim GÜNDÜZ. It made the process of installation on Redhat Enterprise Linux a lot simpler.

In this issue we shall provide step by step instructions on using it geared toward the non-Red Hat Linux/CentOS indoctrinated folk (AKA the misguided Microsoft Windows people). The reason we feel this is necessary is that a lot of people develop on Windows and then deploy on Linux. The Linux experience can be somewhat intimidating, so we hope to make this an easier process by assuming you know little if anything about Linux. So stay tuned for that article.