Tuesday 10 February 2009

Relational Databases - PostgreSQL

Is it just me, or are there a lot more free and open source database systems out there than there were 3 or 4 years ago?

I was following a thread on a Hibernate forum the other day and they mentioned a couple of options that I've used in the past - PostgreSQL and MySQL, a couple I'd heard a little about (e.g. SQLite and Firebird) as well as something called H2.

Of these options, I prefer PostgreSQL. I've been using it on and off for almost a decade, but I don't think that necessarily makes me biased as I could just as easily have been frustrated by it if it wasn't such a flexible and reliable system.

The extensive feature set really saved me some embarassment with a project that had a very tight schedule a few years back.

My team had developed some Java code based around the Geotools APIs to manipulate some GIS data in a shape file. That all appeared to be working fine - until I attempted to deploy it onto our in-house test server which was a Linux system and had the same setup as the server that we intended to host the web-based application on when it went live!

I did some digging around and established that Shapefile manipulation was just for demonstration purposes (so would not have suited our needs anyway), then found that there was also the option of accessing the data via a PostGIS data source. So, I spent my Saturday afternoon building a new PostgreSQL system from source code, with the GIS extensions and the optimal geometry packages available compiled in .... and did a bit of tweaking of the PostGIS access code in Java and had the project right back on schedule for Monday morning's, "So, what did you get up to on the weekend?" coffee break.

I subsequent got the chance to pick up the same set of technologies and have them in place from day 1 in another project, so got to spend some time looking for decent tools for visually verifying the data - uDig was the best nice free system I could find - it connects to PostGIS fairly easily and exists within an Eclipse framework.

No comments:

Post a Comment