PostgreSQL thoughts...

PostgreSQL, and the spatial extension to it PostGIS, are open-source database alternatives to the like of Oracle and MySQL. And they're pretty good. I completed a module earlier this term ran by the great Dr Claire Ellul (though she was a bit tough on marking my assignment!) during which we were introduced to the basics of creating, updating and querying tables - and then linking the spatial aspects of those tables up to QGIS - which is another open-source software, this time as an alternative to the like of ArcGIS which is the mainstream GIS software. Here's a random map I made in QGIS having linked it up to a PostgreSQL database I made, to break up my waffle... ;-)

So anyway, having had a good mess around with PostgreSQL and it's interaction with QGIS, I decided to make use of it's power while completing my dissertation. You see I've got two datasets for my dissertation, the first is a set of GPS tracks with around 600,000 points, and the second a set of incident locations numbering around 4000 rows. In excel. So if I'm going to do anything serious with them, I'm going to need a way of playing around with the data, and give I don't have enough money for an Oracle license or similar PostgreSQL seemed the way to go. It's been pretty good, but there are some little tweaks, cheats and interesting behaviours I've noticed - some of which have caught me out and really slowed me down. These include...

1. If you try to create a column of type 'Geometry' using right-click in the GUI, then it doesn't create it properly. You think it has. But it hasn't. You have to use the command 'CreateGeometryColumn' instead.
2. If you install generic ODBC drivers into Windows, you can reverse engineer* database diagrams using Microsoft Visio. Really excellent. *As in make a diagram of your already existing database.
3. When you use a command such as "SELECT table_name INTO TABLE new_table_name" to move data around, the new table that you've created inherits the columns and types of data from the old table, but not the contraints and primary keys etc that were in your previous table. This has been a real doozy. So instead I have now started making the new table first, using the INHERITS function, and then using the INSERT INTO funtion with the SELECT function to move the data over. EDIT: (I've now discovered this isn't quite true. Or rather it is.. but it links the two tables permanently. Which isn't what I was looking for. So I'm actually still at a loss).
4. If in doubt, communicate with other people! I've joined the postgis-users@postgis.refractions.net mailing list, and have been helped with a few more complicated queries by other postgis users. I've also helped a few out myself which felt nice!
5. That concatanating non-text fields in a query is odd. I've been getting by, but I don't fully understand it yet. For example the below queries... according to the documentation the second one should work... but it doesn't... however the first one does.
   
   update incidents
   set key='' || date_of_incident || '' || hour_of_incident
   But this doesn't....
   
   update incidents
   set key= date_of_incident || hour_of_incident

So anyway... that's my thoughts and moans etc on PostgreSQL at the moment. It's really good basically. But I do worry that Oracle is the mainstream and that the skills I'm acquiring in PostgreSQL, whilst useful and fairly transferable, would be better spent learning Oracle.

PS: I've typed this blog post while a query is running. Which has been running for about 15 minutes now. I really don't think it should take that long. I should perhaps stop it. But what if it's right and is just taking a long time? Grr.