NULL values again

Today I learned that you essentially cannot concatenate (concat, ||) nullable columns properly in DB2/SQL.

To be honest, I should have know this of course.

I have a table that records money spent, with a date, text, account and a physical location, e.g. a town, district, ... indicating where the money was spent.

It is nullable because not all transactions need to have a place recorded.

For some statistical analysis I wanted to create one column with text and account and the location simply concatenated into one, like account||' '||text||' '||location.

Turns out, then the location is NULL the whole string will turn NULL

Which reminded me - painfully, after about 30min - that NULL is not a value. You cannot do anything with NULL.

So you have to cast it away to a default value, e.g. with coalesce(location,'dummylocation').

Sigh.

ISO compliant year-week function in DB2

 I've already shown how to create a year-month function in DB2, which - when it comes to date arithmatic - is quite straightforward, because very year (in ISO/Gregorian) calendar starts with the first month.

Some systems argue whether this should have the ordinal 1 or 0, but thats the usual 0/1 issue in programming.

Weeks however, are far more complex, because not every year starts with the begin of a week (whether this is Sunday or Monday in your preference / area).

It might just start with a Thursday... WOW.

So for that ths ISO 8601 standard set a definition on what is to be considered week 1 of a year:

The ISO 8601 definition for week 01 is the week with the first Thursday of the Gregorian year (i.e. of January) in it

Luckily, DB2 has a function for that - WEEK_ISO.

So let's just try that with a 

rtrim(char(year(TS))) || right(digits(week_iso(TS)),2)

Takes the year (need to rtrim it) and adds 2 digits week to it (you might want to insert a "w") between them.

However, this leeds to e.g. 2021-01-03 being in week 53, because week 1 start on 2021-01-04.

the yearweek for 2021-01-03 therefore should be 2020-53 not 2021-53 as the above formular would yield.

Now we need to make sure that if we get a week 53 and its January we return the previous year... Only for January, because some days in December might also be week 53, and we need to keep the year there.

Voila:

create function yearweek_iso(TS timestamp)
returns varchar(6) no external action deterministic 
return    
CASE

     WHEN (week_iso(TS)=53 AND month(TS)=1) Then
              rtrim(char(year (TS)-1)) || right(digits(week_iso(TS)),2)   
     ELSE
              rtrim(char(year(TS))) || right(digits(week_iso(TS)),2)
END

The results now match whatever java.time package might do with week parsing. In order to get the first day of this week back (in Java, where I needed it), you parse as follow:

new DateTimeFormatterBuilder()
            .appendValue(IsoFields.WEEK_BASED_YEAR, 4)
            .appendValue(IsoFields.WEEK_OF_WEEK_BASED_YEAR,2)
            .parseDefaulting(WeekFields.ISO.dayOfWeek(), 1)
            .toFormatter();

How to get a year-month value in DB2

I quickly needed to check the monthly progress of a certain variable over time. Problem was that those bookings/incomes are spread over the month, sometimes only one booking line, sometimes two... So I needed a grouping variable, that the "absolute month", i.e. the usual year-month value,that you know from financial system, e.g. 201709 for this current month.

Here's the easy way to put that into a function on DB2:

create function yearmonth(TS date) returns varchar(6) no external action deterministic return rtrim(char(year(TS))) || right(digits(month(TS)),2)

Then you can easily do a group by yearmonth(date).
(Don't omit the "not external action" and "deterministic" parts, because a) they are true and b) they are needed for grouping)

Of course you don't have to create a function for this, but easier then re-typing it, or creating a view.

(You might want to create the same function for timestamp as well... just so happened that I now only needed it with the date signature).

You're welcome :)

Maps mash up: Underground vs post office

The other day, when I had to post an important letter, I was wondering if there is any post office close to an underground station on my commute, and I could not come up with one. So - me being me after all - I decided to do some in-depth analysis on this. And learn Python along the way.

1.  The Idea

Get all the underground stations and post offices as geo coordinates, and find those closest to each other.

2. Getting the underground data

Easy, the geo data of all the public transport stations (or ony the underground stations) can be found easily, since the are part of the open government data, shared by the municipality of Vienna.

3. Going postal

Getting the post office data is rather challenging, because they are seemingly not considered to be public enough.

Still, this can be done; so first get a list of all the post offices in Vienna from post.at. Best with a little python script (my first!!) to parse it.
Get all those with a ZIP code starting with '1' into a CSV file including their full street address.

Then - thanks for the hint, martin - use the Yahoo! PlaceFinder API to convert those postal addresses to geo coordinates. Get an Appid for Yahoo!, if you don't yet have one.

Important trick here: Don't place everything into the q=... query string, but separate it into postal, city, street, etc. Like this

u = yahooURL+"?appid="+yahooAppidy
u += "&postal="+po.zip
u += "&city="+po.city
u += "&street="+quote_plus(unicodedata.normalize('NFKD', po.addr).encode('ascii','ignore'))
u += "&country=Austria"

From Y! we get some fine XML back and use XPath to access the geo coordinates

doc = ElementTree(file=urlopen(u))
lat = doc.findall('.//Result/latitude')[0].text
lon = doc.findall('.//Result/longitude')[0].text

So now we have the geo coordinates of all the underground stations and all the post offices.

4. Visualize & Verify the data
Let's again use Python to create a simple KML file to load the date acquired so far into Google Maps (or Google Earth). There's a Simple KML python library to do just that. Here's are the maps (post offices, underground stations)

5. Do the Geo Math
This is the tricky part. I decided to put all the data into a (relational) database, and since I have DB2 installed on my system, it was of course DB2. I did not use any geo/spatial extension, but just put the latitude and longitude into proper types. Then I created a user defined function (UDF) to do the geo math. Actually, for this purpose this could have been simpler, since one can disregard all spherical aspects and assume the surface (of the Earth in Vienna) to be flat.
With my newly created haversine function the query then looks like this:

select haversine(p.lat,p.lon, u.lat,u.lon) as distance, p.plz,p.street,p.lat,p.lon,u.station,u.lat,u.lon
from ubahn.post p, ubahn.stationen u
where haversine(p.lat,p.lon, u.lat,u.lon) <1
order by 1 asc
fetch first 20 rows only

I'm only interested in post offices that are maximum 1km (distance<1) from an underground, and I only want the closest 20 of those (first 20 rows only)

6. Create a map
Export those data into a CSV file, and run a pyhton script that creates the KML file for this, with the post office being a point, the distance being a line, and the underground again a point. Map can be found here.

Turns out, there are more pairs than I actually thought.

Geo-Distance vlg Haversine as a DB2 UDF

In a hack I'm currently working on (to be published soon) I needed to calculate the distance between two locations (on the surface of our planet). Admittedly, those are close addresses (all in Vienna), so I could have assumed them all to be on a plane (not an airplane, a flat surface...), but I felt like some real math.

This is a workaround for all of you who do not have spatial extender available or installed.

To calculate the distance of two points on (a perfectly spherical earth) you apply the haversine formula.
This is the representation of it as a DB2 user defined function (UDF)

CREATE FUNCTION HAVERSINE( lat1 decimal(10,8), lon1 decimal(10,8), lat2 decimal(10,8), lon2 decimal(10,8))
RETURNS DECIMAL(15,8)
F1: BEGIN ATOMIC
declare dlat, dlon, a, d double;
set dlat = radians(lat2-lat1);
set dlon = radians(lon2-lon1);
set a = sin(dlat/2) * sin(dlat/2) + cos(radians(lat1)) * cos(radians(lat2)) * sin(dlon/2) * sin(dlon/2);
set d = 2*6367*asin(sqrt(a));
return d;
END

The two points are given in latitude and longitude (in degrees) as lat1/lon1 and lat2/lon2. It returns the distance in kilometers. If you want to change this, replace 6367 (which happens to be the Earth's radius im km in Vienna, for which I needed this) with your radius and units,e.g. 3956 if you want miles in London.
Check it simply at Wolfram Alpha with "earth radius in london".

I guess it is not perfect, but it does work well. I should probably make it DETERMINISTIC (because it is) to improve performance.
Go ahead, play around.

Power of XSLT

Just recently we changed the ERP system in the company I work for. Amongst other (more important) things the structure and format of product quotations has changed significantly.

The good news however was, that we finally had an XML export of those quotes.

It only took my 30 minutes to create an XSLT Template that would not only convert the XML to a really readable form (in HTML) but also add nifty features like totals per product type etc etc.

Quite frankly, it took me about 7 minutes to create the pure formatting XSLT and the remaining 23 minutes to figure out how to do sums in XSLT.

So, the two things I learned from this little exercise:

1. How to format a number using XSLT:

Use the format-number function as in:

<xsl:value-of select="format-number(amount, '#.00')"/>


2. How to add (sum) values (node-sets to be precise)

You have to use the sum function as in:

<xsl:value-of select="format-number(sum(//lineItem[@isSupportLine='true']/price), '#.00')"/>

For me this very line shows the power of XSLT:

It is adding all values of the "price" element of all "lineItems" that have an attribute "isSupportLine='true'".
In other words:  build the sum of the price of all support lines in the quote.

Or in SQL: select sum(price) from lineitems where isSupportLine="true"

Comic: Little Bobby Tables

This is just fantastic:



xkcd - A webcomic of romance, sarcasm, math, and language - By Randall Munroe