Success !! Eat my code PostgreSQL...

Quick little blog post to celebrate spending almost all of today working out how to keep a leading zero when you extract the DAY from a date field in PostgreSQL. You see normally you can do something like this...

SELECT EXTRACT(DAY FROM date) FROM yourtable

Which is fine. It will give you a value between 1 and 31. However I wanted the values below 10, to be returned as 01, 02, 03 etc. Alot of messing around has finally led to me finding the 'lpad' function. What that does is tell your query how many characters the result should be, and then pad it with whatever you want. So if I tell the query my result should be 10 characters long, with padding X, then when it grabs the value '1' it actually turns it into 'XXXXXXXXX1' (note the 9 X's). So using this, I told it that the result should be two characters long, and that it should pad with '0's. End result below.... awesome...especially pleased as I figured it out all by myself... haha.

UPDATE camdencrimes2
SET camdengps_link = (EXTRACT(YEAR FROM date_of_incident) || '' || EXTRACT(MONTH FROM date_of_incident) || '' || (SELECT lpad (cast((EXTRACT(DAY from date_of_incident)) as varchar), 2, '0')) || '' || incident_id);
SELECT DISTINCT(camdengps_link) FROM camdencrimes2

EDIT: Have since found out that there is a MUCH easier way to do this using the to_char function and defining the data output as 'DDMMYYYY'. I thought something like this would exist but couldn't find it until someone told me.

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.