In my quest to understand spatial GIS functionality, I have come to the ultimate goal: evaluation the actual database products themselves.

PostgreSQL / PostGIS

PostGIS is a variant of PostgreSQL with spatial extensions. The main reason for maintaining the GIS feature set outside of PostgreSQL proper seems to be licensing: the spatial extensions are LGPL GPL licensed.

PostGIS is widely recognized as the most mature and feature-rich GIS implementation for SQL databases (and perhaps any database), matched only by the costly Spatial extension for the Oracle Enterprise database. (See comparisons in the links below.)

While the underlying index should be opaque to the user of a DBMS with spatial features, the API used to define spatial types and operate on them is of course more visible. The relevant standard in this space is often referred to as "OpenGIS", however the Open Geospatial Consortium in fact defines a long list of standards. The standard relevant to SQL databases is known more precisely as "OpenGIS Implementation Specification for Geographic information - Simple feature access - Part 2: SQL option" aka "Simple feature access".

It is not meaningful to recite the standard at length in my blog, my focus is instead on actual implementations that I will blog about later. The following points are however worth noting:

I work for a company that is the leading supplier of automotive maps, and wants to be the leading supplier of online maps. So it was only a matter of time that I needed to learn more about how spatial extensions work in different open source databases. Let's start from the beginning, understanding various spatial data structures that are used in implementations...

Links are provided to Wikipedia articles - which are both comprehensive, yet easy to understand - for those who want to get a deeper understanding of each structure. All Wikipedia articles on spatial indexes are listed here: https://en.wikipedia.org/wiki/Spatial_index#Spatial_index

B-tree

Flavio of MongoDB fame complained in a tweet about wanting a date format in JSON. Currently you can only express strings and numbers as the primitive data types in JSON, as well the composite forms of a list: [] and an object: {}. The latter is essentially just a subhierarchy in the object/array that is your full JSON document.

i wonder why there's no "STANDARD" way to (de)serialize times / date times into / from json! They might not be "primitive" but COME ON!!

I found a very interesting blog post today: Open Source IaaS Community Analysis. It is a statistical analysis into forum/mailing-list traffic of the 4 major private cloud open source projects: OpenStack, OpenNebula, Eucalyptus and Cloudstack. While I have never met or read anything from the author, qyjohn, it seems we actually worked at Sun at the same time :-)

For a casual follower - like me - of these four cloud projects, the post is interesting in many ways. But for anyone interested in open source business models it is very interesting indeed. Readers of this blog will remember my research from 2010: How to grow your open source project 10x and revenues 5x. The research showed that 9 out of 9 Xtra Large projects are all governed through foundations, whereas the best performing open source codebases owned by a single vendor have developer communities that are roughly 10x smaller. Based on this observation I made this recommendation:

My talk for MySQL Connect has been accepted. This is the MySQL specific 2 day conference just before the big Oracle OpenWorld and JavaOne. It is kind of a new conference by Oracle. They've had something called MySQL Sunday at this spot before, but this year it is a bit bigger and with a bigger spin on it. I'm glad it is on a weekend because otherwise I wouldn't be able to attend.

I will give the popular Evaluating MySQL High Availability Alternatives talk that I have been refining in two Percona Live conferences already. With MySQL 5.6 in the oven now, there's something new again to talk about.

Sheeri was also in Hämeenlinna for the OUGF Harmony conference a few weeks ago, and of course she had her video camera with her. My talk "Synchronous multi-master clusters with MySQL: an introduction to Galera" is now posted on Youtube. (You may also want to watch the slides on slideshare while listening.)

I think this is a really good talk if you are interested in Galera. First we cover how to get a Galera cluster running, including the most important options and status variables to pay attention to. I cover both the internal Galera architecture and how to use load balancers and clustering frameworks, and explain how galera handles node crashes and network partitioning.

In the series of shell one liners, today I wanted to stalk someone, so I came up with this one liner to show all diffs ever created by a specific user in a give bzr repository:

bzr log -n0 | grep -B 1 henrik | grep revno | grep -v merge | \
awk -F'revno: ' '{print $2}' | while read revno; do echo; \
echo '#####################################################################'; \
echo revno: $revno; bzr diff -c$revno; done 

bzr log -n0 prints out the full log history of your repo. It may look something like this:

    ------------------------------------------------------------
    revno: 2441.2.16 [merge]
    committer: Henrik Ingo 
    branch nick: drizzle-json_server-keyvalue