editor note - 3   

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. :)

Over the past two weeks, the PostGIS development team has been working hard to get out PostGIS 1.5.2 in time for the PostgreSQL 9.0 release. This release contains fixes allowing PostGIS to compile against 9.0. Due to an unfortunate turn of events, we missed the cut by a couple of days and are currently experiencing technical difficulties with the postgis.org website. These should be resolved soon and barring no further difficulties, we should have the final PostGIS 1.5.2 ready late this week.

On the plus side, we do have a PostGIS 1.5.2 rc1 available for download from our PostGIS Wiki Release Candidate Downloads section. Please feel free to test these out so that we have a smooth release.

Paul's related post is here

Here are the details of what is fixed:

 - This is a bug fix release, addressing issues that have been
   filed since the 1.5.1 release.

 - Bug Fixes
   - Loader: fix handling of empty (0-verticed) geometries in shapefiles.
     (Sandro Santilli)
   - #536, Geography ST_Intersects, ST_Covers, ST_CoveredBy and
     Geometry ST_Equals not using spatial index (Regina Obe, Nicklas Aven)
   - #573, Improvement to ST_Contains geography
   - Loader: Add support for command-q shutdown in Mac GTK build (Paul Ramsey)
   - #393, Loader: Add temporary patch for large DBF files 
     (Maxime Guillaud, Paul Ramsey)  
   - #507, Fix wrong OGC URN in GeoJSON and GML output (Olivier Courtin)
   - spatial_ref_sys.sql Add datum conversion for projection SRID 3021
     (Paul Ramsey)
   - Geography - remove crash for case when all geographies are out of
     the estimate (Paul Ramsey)
   - #469, Fix for array_aggregation error (Greg Stark, Paul Ramsey)
   - #532, Temporary geography tables showing up in other user sessions
     (Paul Ramsey)
   - #562, ST_Dwithin errors for large geographies (Paul Ramsey)
   - #513, shape loading GUI tries to make spatial index when loading DBF only
     mode (Paul Ramsey)
   - #527, shape loading GUI should always append log messages 
     (Mark Cave-Ayland)
   - #504 shp2pgsql should rename xmin/xmax fields (Sandro Santilli)
   - #458 postgis_comments being installed in contrib instead of 
     version folder (Mark Cave-Ayland)
   - #474 Analyzing a table with geography column crashes server
     (Paul Ramsey)
   - #581 LWGEOM-expand produces inconsistent results
     (Mark Cave-Ayland)
   - #471 DocBook dtd errors (Olivier Courtin)
   - Fix further build issues against PostgreSQL 9.0 
     (Mark Cave-Ayland)
   - #572 Password whitespace for Shape File to PostGIS 
     Import not supported (Mark Cave-Ayland)
   - #603 shp2pgsql: "-w" produces invalid WKT for MULTI* objects.
     (Mark Cave-Ayland)
 - Enhancement
   - #513 Add dbf filter to shp2pgsql-gui and allow uploading dbf only
     (Paul Ramsey)

We should have windows binaries available a short time after release. Unfortunately we do not have the 64-bit windows build ready yet, so you still have to use the 32-bit version of PostgreSQL 9.0 if you need PostGIS on windows.

PostGIS, SQL Server 2008 R2, Oracle 11G R2

We just completed our compare of the spatial functionality of PostgreSQL 8.4/PostGIS 1.5, SQL Server 2008 R2, Oracle 11G R2 (both its built-in Locator and Spatial add-on). Most of the compare is focused on what can be gleaned from the manual of each product.

In summary, all products have changed a bit since their prior versions. The core changes:

• PostGIS 1.5 has geodetic support now in the form of geography as well as some beefed up functions and additional distance functions like ST_ClosestPoint, ST_MaxDistance, ST_ShortestLine/LongestLine
• SQL Server 2008 R2 basic spatial support hasn't changed much when compared to SQL Server 2008, but there is a lot more integration going on integrating Spatial into reporting services, Share Point and just integration in general with SQL Server 2008 R2 and the Office 2010 stack.
• Oracle 11G R2 - has finally offered an uninstall script for Locator folks who do not care to break the law by accidentally using functions only licensed in Oracle spatial, but innocently exposed in Oracle Locator. If all that were not great enough, you are now allowed to legally do a centroid if you are using Oracle Locator. Doing unions, intersections, and differences is still a legal no no for Oracle Locator folks. Oracle now provides Affine transform functions, which have long been provided by PostGIS and have been available via the MPL licensed CLR Spatial package of SQL Server 2008.

I still haven't figured out where this R2 convention started. I thought it was just a Microsoft thing, but I see Oracle follows the same convention as well.

This month we we will be giving two mini-tutorials at PgCon East 2010 on Saturday, March 27th. The topic of the talks will be, you guessed it, PostGIS. We have changed our Beyond talk to PostGIS: Adding spatial support to PostgreSQL to a beginner focus instead of an intermediate focus. Topic content will be more or less the same but focused more on people new to spatial database analysis. Our web applications talk will cater more to the web developer trying to integrate PostGIS in their web applications.

Marcus Rouhani of the Federal Aviation Administation will also be talking about the Airport GIS project and migration from Oracle to PostgreSQL.

On a somewhat related note, we also hope to be finished with all the chapters of our upcoming book this month. We just completed the first draft of our Chapter 10: PostgreSQL Add-ons and ancillary tools. After some back and forth with our editor, this will be up on MEAP, available for read and comments for early book buyers. Still two more chapters to finish after that before we get to the polishing of the text, images, layout and final print version.

Our publisher Manning is running a 50% off sale this Friday (tomorrow or is it today) on any MEAP book and they have a lot of interesting ones in the pipeline (including ours).

Waiting for PostGIS 2.0

The OSGEO just completed a recent coding sprint in New York. The New York sprint was a meeting of the minds of OSGEO people from various projects -- PostGIS, Mapserver, Geoserver, OpenLayers, GDAL, and some others were represented. Sadly we were not able to attend this one. A summary of the sprint with a PostGIS bent can be found on Olivier Courtin's New York sprint summary (Original French Version) and Olivier Courtin's New York sprint summary (Google English translation) and Paul's New York sprint summary.

This was a truly exciting year for us and the PostgreSQL project and perhaps a bit depressing for MySQL.

The following events happened:

• PostgreSQL 8.4 was released which had blow away features like Common Table Expressions (CTE) , Recursive CTEs, and Windowing Functions. This meant we could finally get some of our hard-core Oracle and SQL server friends really excited about PostgreSQL.
• This is the first year we got out of our shy mode and actually presented at conferences. We presented at PGCon 2009 and OSCON 2009.
• The PostGIS project steering committee was formed with Regina as one of the founding members
• We started writing our PostGIS in Action due out sometime in 2010. Sadly we are a bit behind schedule, but on the bright side, you can buy the book now and it will probably be a bit heftier than the 325 pages we had planned. To celebrate our upcoming book, we have launched our book promo site PostGIS in Action: The Book where the adventure begins. There you will find source code downloads, data, presentations as we put each together. You will also see a brief description of chapters , our progress with each chapter, what you can expect from each chapter, and related links to the chapter content. We are currently at what we hope is our last quarter sprint.
• We wrote a DZone cheatsheet which was confronted with mixed emotions.
• 2009 was also the year Oracle threatened to buy Sun and engulf MySQL in the process. Interestingly this was predictable in someone's wildest dreams. Is this the end of Open source databases as we know it? Only time will tell.

Plans for 2010

What are our plans for 2010?

• Get PostGIS 1.5 out the door some time in January 2010
• We hope in 2010 to present at at least one PGCon conference and hopefully make FOSS4G 2010 in September 2010. Our book better be written by then.
• Finish our book and hopefully soon.
• Increase the adoption of PostgreSQL and PostGIS significantly. To paraphrase our favorite Larry's famous words our strategy is to Get big very fast.
• Get PostGIS 2.0 out the door sometime in late 2010.

What will happen to the database industry in 2010

I usually try to keep my mouth shut on these topics. I must say that I have noticed a bit of animosity from some PostgreSQL people toward the whole MySQL/Oracle affair, comments like He lives by the sword, he should die by the sword. Other interesting conjectures as to what this means for Open Source databases, Is Monty right that the apparent rape of MySQL by Oracle is only bad and will cause countless pain and suffering for many. All I can say is "What..ever".

Some argue that Monty's fight is all about money and some don't that he is earnestly trying to save the world from Oracle. To me its a fight about a man who has spent half his life nurturing this child MySQL named after his own son. Of course he has quite a bit of emotional attachment to it, as many in the PostgreSQL clan have an equal attachment to PostgreSQL and we have an equal non-economic (as well as economic) attachment to PostGIS and PostgreSQL. Equally so I'm sure Larry Ellison has perhaps a stronger attachment to the namesake Oracle database named after a CIA project he spent more than half his life nurturing.

So in short the motives on all side are clear and irrelevant to all except said people with said motives. In the end, what is relevant is what is relevant.

This week is a busy week for events. While PostgreSQL is having its PostgreSQL West conference in Seattle, the biggest Open Source GIS conference of the year is happening in Sydney, Australia FOSS4G 2009. Sadly given our schedule and the distances of the commutes, we couldn't make either conference.

Mateusz Loskot pointed out that the video is out for Paul Ramsey's FOSS4G 2009 Keynote speech on Beyond Nerds Bearing Gifts: The Future of the Open Source Economy. I think its a very important distinction Paul makes between selling software and selling a product, that a lot of people miss when trying to evaluate the solvency of open source software.

For those who don't know Paul, he's one of the co-founders of the PostGIS project and Refractions Research.