editor note     

Two exciting things happened this past month.

• Our article on upcoming PostGIS 2.0 recently came out in GeoInformatics Magazine December 2011 Issue 8. Check it out here. Starts on page 30.
• We just got notice that our book proposal has been accepted and this time it's not about PostGIS.

In our article The Pure Relational database is dead there were a lot of misunderstandings as a result of our poor choice of words. People thought we were bashing the relational model because in their mind that was what pure meant. I got hit with a lot of poetic insults. I still can't think of an alternative word to use for what I meant. Simple doesn't really do it as even relational databases with just standard types were far from simple when you consider the planner and all the other stuff going on under the hood to protect you from the underlying storage structure. What I was trying to say is that in the beginning most relational databases just supported a standard set of types which you could not expand on and most people when they think relational today still think just that. That type of relational database is in my book dead or almost dead.

How did this all start. Well whenever we use something like PostgreSQL to store anything complex -- take your pick: geometry data, tree like structures which we use ltree for, full-text query constructs, and Yes XML we get bashed by some know-it-all who has a very narrow view of what a relational database should be doing and suggesting we use a NoSQL database, a graph engine or a full text engine or normalize our data more. I have also learned XML is a dirty word to many people. I mistakenly thought XML was a complex type people could relate to, but turns out they can relate to it so well that it brings up tragic memories I can only equate to Post Traumatic Stress Disorder suffered by war veterans or (early or wrong) technology adopters. That was not my intent either. XML was just merely an example. I will not say you should use XML in your tables, but I will also not say you should stay clear of it as many people wanted me to say. I will say its use is rare, but it has its place. It has its place just as any other complex type and it has its own special needs for navigation, indexing etc. which many relational databases handle fine enough.

A lot of redditers took offense at our article XPathing XML data with PostgreSQL with the general consensus, if you are going to be stuffing XML in a relational database where will you stop? That is not what relational databases are designed for. We had comitted a sacrilegious sin and worsed yet encouraging bad habits by forcing people to think more about different options they have for storing data in a relational database and god forbid demonstrating querying such columns with xml specific functions. What were we thinking? How dare we try to query XML data with SQL? Perhaps we were thinking like this guy or this guy, both equally misguided spatial relational database folk. Of course we stepped one foot further by actually defining a column as xml and dare storing data in it for later consumption rather than just an intermediary step.

If I want to store documents, that are navigateable I should be using a document database like MongoDb, CouchDB etc designed for that kind of stuff. If I've got graphs I should be using a graph database. This got me thinking that the "Pure Relational Database" is dead, and I'm surprised most people don't seem to realize it.

So while "Relational databases" have changed over the last 25 years, most people's notions of them have not kept up with the pace of its change.

First let me define what I mean by Pure. A pure relational database is one with standard meat and potato types like text, dates, numbers well suited for counting money and computing how close the world is to total bankruptcy which you store as fields in a row of a table and that you then define foreign keys / constraints / primary keys on to relate them to other tables. You reconstitute your real world objects by stitching these tables together with joins etc and return sets using where conditions, summarize by using group bys or other mathy like constructs. Don't get me wrong; these are very beautiful things because they allow for easy slicing of dimensions and not having to think about all the dimensions that make up an object all at once. In fact it was so beautiful that some people thought, "wow that's cool, but it would be even cooler if I could store more complex objects in those columns with their own specific needs for querying." and so was born the object relational database as some people refer to them that thought relational but also understood that different types had their own unique needs for querying, storage, indexing etc.

Nowadays most, if not all, relational like databases have standardized on some variant of SQL. In essence though, the pure relational database doesn't allow you to define new types or have exotic types such as arrays, xml, graphs, geometries, rasters, sparse matrices etc. Much less thinking involved and less likely you will shoot yourself in the foot by dumping a bunch of xml in a field and trying to do something with it. When it is used to store more complex things such as spreadsheets and other user documents, these are stored as blobs and just retrieved. Even such use is frowned upon.

Well most relational databases I can think of nowadays have richer types: e.g. PostgreSQL, Oracle and Firebird all support arrays as a column type. Some even allow you to define custom types and functions to support your custom types e.g. PostgreSQL (I could go on forever), Oracle has rich user defined type support too, and SQL Server 2005+ with each version getting better and better for user defined custom types and introducing more exotic types and support infrastructure. Even MySQL/Drizzle (mostly in the form of different storage engines). Even my favorite light-weight SQLite under the hood has some tricks that aren't what I would call relational. E.g. Spatialite/RasterLite has a whole geometry type library built on SQLite with functions you can call from SQL and I'm sure there are lots of middleware tools you don't know about using the SQLite and Firebird engine for more than relational tasks (e.g. HTML5 anyone/ CAD anyone).

FOSS 4G 2011 Videos. My resident PostGIS developer strk says he can't see them because blip.tv is using some sort of proprietary video swf format. I can't really tell what he is talking about. Does anyone know if fosslic videos are available in other formats like ogg or a Gnash swf viewer compatible format?

We mentioned in prior article Our FOSS4G 2011 the new Stuff and provided the slides in that article. Now we have the video to go with it.

• Paul's standard State of PostGIS He ran out of time so our's is more like a continuation.
• PostGIS 2.0 the new Stuff (our presentation)
• PostGIS Raster 2.0 - store, manipulate and analyze raster Pierre Racine

It is with sadness that I learned of the passing of Dennis Ritchie - inventor of C and who made much of Unix, other operating systems, and many software (including Postgres) possible. More details at Remembering Dennis Ritchie: Software Pioneer and Dennis Ritchie, in Memoriam.

Dennis Ritchie co-authored the book, The C Programming Language, a classic, which many of my peers grew up with. It was one of the textbooks at MIT for Civil Engineering 1.00 when we were attending when the course was essentially an introduction to programming with C.

The harsh irony is that when Steve Jobs passed away I was probably the only one around me who felt no remorse and hoped the curve fanaticism Jobs fueled would die with him. When Dennis Ritchie passed away I was probably one of the few around me who knew who he was and appreciated the great contributions he made to the computer industry.

This is about improvements to GIST indexes that I hope to see in PostgreSQL 9.2. One is a patch for possible inclusion in PostgreSQL 9.2 called SP-GiST, Space-Partitioned GiST created by Teodor Sigaev and Oleg Bartunov whose basic technique is described in SP-GiST: An Extensible Database Index for Supporting Space Partitioning Trees. For those who don't know Teodor and Oleg, they are the great fellows that brought us many other GiST and GIN goodnesses that many specialty PostgreSQL extensions enjoy -- e.g. PostGIS, trigrams, ltree, pgsphere, hstore, full-text search to name a few.

Another is a recent one just committed by Alexander Korotkov which I just recently found out about on New node splitting algorithm for GIST and admit I don't know enough about to judge. I have to admit to being very clueless when it comes to the innards of index implementations so don't ask me any technical details. It's one of those short-comings among the trillion others I have that I have learned to accept will probably never change.

What the SP-GIST patch will provide in terms of performance and speed was outlined in PGCon 2011: SP-GiST - a new indexing infrastructure for PostgreSQL Space-Partitioning trees in PostgreSQL.

What it provides specifically for PostGIS is summarized in Paul's call for action noted below. As a passionate user of PostGIS ,ltree, tsearch, and hstore, I'm pretty excited about these patches and other GIST and general index enhancements and there potential use in GIST dependent extensions. I'm hoping to see these spring to life in PostgreSQL 9.2 and think it will help to further push the envelope of where PostgreSQL can go as a defacto platform for cutting-edge technology and scientific research. I think one of PostgreSQL's greatest strength is its extensible index API.

Paul's PostGIS newsgroup note about seeking funding for faster GIST indexes , work done so far on SP-GIST and call for further action is rebroadcast in it's entirety here.

Thanks to the sponsorship of Michigan Technological University, we now
have 50% of the work complete. There is a working patch at the
commitfest https://commitfest.postgresql.org/action/patch_view?id=631
which provides quad-tree and kd-tree indexes.

However, there is a problem: unless the patch is reviewed and goes
through more QA/QC, it'll never get into PostgreSQL proper. In case
you think I am kidding: we had a patch for KNN searching ready for the
9.0 release, but it wasn't reviewed in time, so we had to wait all the
way through the 9.1 cycle to get it.

I am looking for sponsors in the $5K to $10K range to complete this
work. If you use PostgreSQL in your business, this is a chance to add
a basic capability that may help you in all kinds of ways you don't
expect. We're talking about faster geospatial indexes here, but this
facility will also radically speed any partitioned space. (For
example, the suffix-tree, which can search through URLs incredibly
fast. Another example, you can use a suffix tree to very efficiently
index geohash strings. Interesting.)

If you think there's a possibility, please contact me and I will send
you a prospectus you can take to your manager. Let's make this happen
folks!

Paul

The Free and Open Source Software for Geospatial (FOSS4G) is schedule for September 12-16, 2011 in Denver, CO. PostGIS is going to be making a big showing at this event. Paul Ramsey's popular Introduction to PostGIS workshop is already sold out. Check out the schedule of other PostGIS related talks FOSS4G 2011 PostGIS related talks.

We'll be presenting on Friday PostGIS 2.0, the new stuff and showcasing some of the new features in upcoming PostGIS 2.0. In fact Friday seems to be a day jam packed with PostGIS talks back to back in the Windows room. We probably won't even have to leave the room to get our fill of PostGIS.

I'm particularly looking forward to Steven Singer's PostGIS replication talk and Jim Mlodgenski's Scaling PostGIS Queries with Stado since these are becoming critical areas as we take on larger and more complex work.

Since others have shared their PostgreSQL Conference notes on Planet PostgreSQL: Josh Berkus, Blake Crosby, Selena Deckelman, Dmitri Fontaine, Bruce Momjian, Gabrielle Roth, Andreas Scherbaum, and Greg Smith, I thought I'd do my civic duty and add Paul Ramsey's notes to the mix. His are on his corporate OpenGeo blog which is carried by Planet Geospatial and Planet OSGeo but not by Planet PostgreSQL. One thing I admire about Paul is how easily he lets himself be changed by his environment. Sometimes you have to be a little careful what you say to him since he sometimes takes your comments a little too much to heart and changes a little bit more than you had intended. Anyrate here are his notes: PgCon Notes #1, PgCon Notes #2, PgCon Notes #3.

This past week our PostGIS Project Steering Committee has gotten a wee bit bigger with the addition of Sandro Santilli and Chris Hodgson. So now we are 5 people strong. Though we have drastically different opinions on things, I think we all have the best interest of PostGIS users in mind such that the difference creates a healthy compromise in perspectives.

Our PostGIS development team has gotten a new addition as well. We have Bborie Park hailing from UC Davis Center for Vectorborne Diseases helping out on the raster front. You might have seen him on the PostgreSQL news groups asking questions. He is currently working on raster image export functions, so that you can do things like ST_AsPNG(rast,...) right from the database. In addition he is also working on raster statistics functions like histograms, mean, minmax, stddev, reclass functions.

Bborie, if you get some of this in for 2.0, I promise to help document these new functions and to write an ASP.NET and PHP application/tutorial that flaunts some of them.

We just got our complimentary author hard-copies of PostGIS in Action today. Those who ordered directly from Manning should be getting there's shortly too if they haven't already. Amazon and other distributors should start shipping soon as well.

We'll be saving some copies for door prizes at the next event we present at.

I am happy to report, that the final proof of the PostGIS in Action E-Book got released today and the printed version is scheduled for release Aprill 11th, 2011 and should be available on Amazon and other locations around then. The other e-Reader formats will come after that. You can buy from here or download the two free chapters, if you haven't already.

Each hard-copy purchase comes with a free E-Book version. There is a coupon in the back of the book when you get it to get the E-Book versions.

Yes, I know it's been a really really long time. On the bright side, we produced twice as much content as we had set out to do and that was with keeping things as concise as we could get away with, still managing to cover more than we set out to cover, and stripping out as many unnecessary words as we could muster. So 520 pages and almost 2 years later, this is where we are.

A good chunk of the additional bulk of the book was the appendices which are about 150 pages total and focus strictly on PostgreSQL and SQL. After many comments from early reviewers, we thought it unfair not to have a good chunk of PostgreSQL and just general relational database content to familiarize programmers and GIS folks with the RDBMS that PostGIS lives in. Most GIS folk unfortunately have the hardest time with getting up to speed with SQL and just standard RDBMS management.

Two free chapters and accompanying code for all chapters

The two free chapters we selectively picked because we thought they would be most beneficial to newcomers and people new to relational databases. So the free chapters are:

• Chapter 1: What is a spatial database? Which provides a fast paced history of PostGIS, PostgreSQL, Spatial Databases and moves into an even faster journey into converting flat file restaurant locations to spatial point geometries, loading in an ESRI shapefile of roads. Then shows you how to write standard spatial queries and render the results.
• Appendix C: SQL Primer -- goes through querying information_schemas, the common points of writing SELECT, INSERT, UPDATE, DELETE SQL statements and the finer points of using aggregate functions, Windowing constructs and common table expressions as well as a brief overview of how PostgreSQL stacks up with other relational databases (SQL Server, Oracle, IBM DB2, MySQL, Firebird) in SQL features.
• All the chapter code and accompanying data. It's a bit hefty at 57 MB.

So even if you don't buy our book, we hope you find the free chapters useful.

You can get a more detailed listing of all the chapters from the PostGIS in Action book site.

We'd like to thank all those who supported us through this long and unpredictable journey. Hopefully we'll have several more, though hopefully a bit less nerve-racking than this first one.

PostGIS Mini Conference in Paris, France

There will be a PostGIS 1 Day mini conference in Paris June 23rd organized by Oslandia and Dalibo. For Details: PostGIS mini conference English Details and PostGIS mini conference French Details. Speaker submissions are due April 22nd. Main focus will be upcoming PostGIS 2.0 which gathering from newsgroup a lot of people are already bouncing around.

PGCon 2011 - Paul will make a show

Paul Ramsey (OpenGeo) will be speaking at PGCon 2011. PostGIS knows where you are? and everyone will know where Paul is too.

We are on the final stretch of our book writing adventure. All the chapters are done and more or less finalized. We are now going over the proofs of the chapters making last minute corrections before print. Hopefully we'll see the printed version before end February. It's been a long 1.5+ years. I was really hoping we'd be published before Leo's 40th, but his 40th came and went. Though looks like we'll make it before mine with 5 - 6 months to spare. On the bright side, I guess if we write a book again, we'll know what to expect.

I really love the Manning code annotation style. Here are some snapshots of some from PostGIS in Action. We have just the black and white prints of some of the chapters so we can make sure the printed figures will look okay. The e-book version will be in color, but sadly the printed will be in black and white.

In February, we'll be speaking in 2011 North Carolina Geographic Information Systems Conference, Raleigh, NC USA and visiting a long-time friend from our college days:

• PostGIS 2.0 Raster and 3D Support Enhancements (GIS Goes 3D Special Track) Friday February 18th 8:30 - 10:00 AM
• Cross Comparison of Spatially Enabled Databases: PostGIS, SQL Server and JAvaSPAtial (JASPA) (GeoJenga How to Stack your Apps Track) - Friday February 18th 10:30 - 12:00 PM

I've always enjoyed dismantling things. Deconstruction was a good way of analyzing how things were built by cataloging all the ways I could dismantle or destroy them. I experimented with mechanical systems, electrical circuitry, chemicals and biological systems sometimes coming close to bodily harm. In later years I decided to play it safe and just stick with programming and computer simulation as a convenient channel to enjoy my destructive pursuits. Now getting to the point of this article.

In later articles, I'll start to demonstrate the use of PL/R, the procedural language for PostgreSQL that allows you to program functions in the statistical language and Environment R. To make these examples more useful, I'll be analyzing data generated from PostGIS tests I've been working on for stress testing the upcoming PostGIS 2.0. PostGIS 2.0 is a major and probably the most exciting release for us. Paul Ramsey did a summary talk recently of Past, Present, Future of PostGIS at State of PostGIS FOSS4G Japan http://www.ustream.tv/recorded/10667125 which provides a brief glimpse of what's in store in 2.0.

The PostGIS development team is proud to announce the availability of PostGIS 1.5.2. Further details are on the postgis.org website PostGIS 1.5.2 release.

Leo and I are still working on the windows builds. As stated before the 32-bit builds will be out first. We are still preparing our 64-bit test environment on our new 64-bit laptop.

For those who are on 64-bit windows. Sami has some PostGIS 64-bit binaries for PostgreSQL 9.0 windows available on his blog. Though he just has the binaries available so you will need to use the other files from the 32-bit install.

To answer Sami's question, since he has asked it more than once: I really can't understand why PostGIS developers still want to compile the whole stuff using msys/mingw and that kind of stuff. We have Visual C++ (yes, the compiler is available for free), everything compiles with it and you don't have to whine about how hard it is to compile stuff for Windows because it's not GNU.
There are 3 reasons:

• Believe it or not -- the PostGIS development crew is relatively small and most work on Unix or MacOSX which do compile under GNU. Each builds there own regression tests. We need to be able to test consistently on all platforms which means we need a devlopment environment that all regression tests will work on without too much fuss. As much of a pain as we whine about with mSys -- its the most like what everyone else uses and mimicks the environment most consistently.
• Supporting VC means supporting yet another set of make and configure files and yuck project files. I don't even think express can deal with solution files. etc. GEOS does it and it was a pain for them. I know because a while back I would point out all the issues I was having compiling under VC++ (not to mention I don't need VC++) -- cause I'm a webdeveloper -- so don't have it normally installed). It took Mat some time to revise packaged scripts to even get PostGIS to compile under VS. There are people that bicker, but no one steps up to the plate wanting to support VS/VC++.
• Leo and I are predominantly web developers and database professionals; frankly in my ideal world everything would be interpreted or Just in time compiled (JIT) by the server. MingW / VS slash anything that needs compiling is just a big pain however I look at it and they are of equal pain to me. I got out of desktop development so I wouldn't need to deal with compiling stuff.

Okay we have whined enough. There are talks in the PostGIS and GEOS group of switching to CMake and to have a process that builds said make / project files so that we can more easily support GNU and VS without hopefully not adding too much extra work on anyone's plate. We will see how that goes. Will we compile the 64-bit version under Msys64 or VS -- we would like to do both and compare the 2. :)