Tom Fosdick

It just occurred to me that SQL’s geometry type can be used like a canvas to visualise nonspatial data in SQL Server – to make graphs etc.

Sure there are other tools, more appropriate tools (like Excel) but you don’t always have them to hand or set up. For instance all I wanted was a quick visualisation of the number of calls one of our customers was taking over time.

declare @result nvarchar(max)=N'select geometry::STGeomFromText(''LINESTRING('

select @result=@result+convert(nvarchar(14),convert(float,dy))+' '+CONVERT(nvarchar(14),ct)+', '
from
(select DATEADD(Day, DATEDIFF(Day, 0, timestamp),0) dy,COUNT(*) ct from dummydata.call
group by DATEADD(Day, DATEDIFF(Day, 0, timestamp),0)) T1 order by dy asc

select @result=substring(@result,1,LEN(@result)-1)+N')'',0)'
exec sp_executesql @result

Which produces, using SQL Server Management Studio’s Spatial visualiser…

This is perfectly good enough to give me a general idea of what the data looks like.
The reason for the shape is that what is shown is test data – one can clearly identify the periods where testing was taking place!

Point data is, of course, easier – one can simply select the points directly out of the data set.

select geometry::STGeomFromText('POINT('+CONVERT(varchar(14),CONVERT(float,dy))+' '+CONVERT(varchar(14),ct)+')',0)
from
(select DATEADD(Day, DATEDIFF(Day, 0, timestamp),0) dy,COUNT(*) ct from dummydata.call
where timestamp>'2011-01-01'
group by DATEADD(Day, DATEDIFF(Day, 0, timestamp),0)) T1 order by dy asc

Or if we want to get really silly, we can visualise the data as the number of calls taken for each day of the week in a nice bar chart…

select geometry::STGeomFromText('POLYGON(('
	+CONVERT(VARCHAR(14),dow)+' 0,'
	+CONVERT(VARCHAR(14),dow)+' '+CONVERT(varchar(14),ct)+','
	+CONVERT(VARCHAR(14),dow+99)+' '+CONVERT(varchar(14),ct)+','
	+CONVERT(VARCHAR(14),dow+99)+' 0,'
	+CONVERT(VARCHAR(14),dow)+' 0))',0),dnam
from (select (DATEPART(dw,timestamp)-1)*100 dow,LEFT(DATENAME(dw,timestamp),3) dnam, COUNT(*) ct from cnc.call 
	group by DATEPART(dw,timestamp),DATENAME(dw,timestamp) 
	) T1 order by T1.dow

Which produces this…

Reading Time: 2 minutesMe: So what sort of order of magnitude are we looking at for pricing?
Exhibitors: Oh well I see you’re British I can put you in contact with our reseller in London…

This is just not good enough for any business that thinks itself truly international. I didn’t ask for a quote I asked for the order of magnitude, something roughly in the area. If a company can’t provide any idea then there’s something very wrong…

1. It implies that the company has no direct presence in the UK, the further implication being that UK is not a core region hence support will be lacking and any UK specific portions of the product will be poorly maintained or just plain poor fullstop.
2. It implies geographical pricing. Today’s business is global, I can and have bought COTS software from other regions. This business model doesn’t work, get over it.
3. When a quick Google of the resellers also fails to reveal pricing one wonders if the product is actually available boxed, or whether it always comes with a cheesy sales drone and a whack of “consultancy” or “project management” attached. Further, there is an implication that without value added services it won’t actually work.
4. Nobody likes talking to cheesy sales drones, not even IT Professionals.

So this is how it should go :-
Me:  So what sort of order of magnitude are we looking at for pricing?
Exhibitor: Err, well,  in the USA we either licence per device at $5pa each or you can buy a server licence for $2000pa. Obviously there will be some regional variation in the UK but they’re the sorts of figures you’ll be looking at. We have a number of partners in the UK, some just ship boxes but if you really want to get the most out of our software I’d recommend talking to blah… blah…

Reading Time: < 1 minuteI used to work for a software house that used the waterfall model. Despite being a strong proponent of continual customer contact I actually hated doing it because all I seemed to be doing was noting issues and reporting them to project management where they disappeared into a mire of beurocracy. I was very rarely able to give customers good news.

Using an Agile methodology however SEED is able to accomodate change. I actually enjoy talking to customers now because we’re able to have open and constructive discussions. This is good for everyone:

• The customer feels lie they’re being listened to – largely because they are
• I and the team feel better because we’re not in a conflict scenario with the customer
• Ultimately the customer gets a system that more reflects their needs, which benefits their business and ours.

Reading Time: 2 minutes

“Oh we didn’t use your code in the end,” said an ex-colleague some time after I’d left a previous employer, “nobody could understand it.” I was a little taken aback because I’d thought the code was particularly clear given the complexity of what I was doing.

I could understand that some less experienced developers might have had trouble with it, but to someone experienced I didn’t think it would be an issue.  This left me feeling distinctly sheepish – should I have written the code in the way I did? Instead of going for ultimate performance could I have simplified it, got enough of a performance gain and achieved a greater degree of maintainability?

I felt sheepish because code that nobody understands is a big development problem for 2 reasons. The first is that at some point someone will need to understand it – that’s going to take time and development time is expensive. The second is that there is a danger that someone who doesn’t understand it (even if they think they do) modifies it and makes a mistake. Often people under pressure to fix bugs have to make guesses and sometimes they’re wrong – it might appear to fix the issue but it may actually unleash a greater demon that’s much, much harder to find.

Sometimes code does need to be complex: the earth for instance is far from round and it’s hard to avoid some pretty hideous maths if you need to work with its geometry. That’s excusable but every effort should be made to lay the code out clearly and comment well. What’s not acceptable is needless complexity through bad design, bad coding style, bad layout, lack of commenting or even in some cases developers showboating.

A good developer is not the one who writes really clever, complex code that nobody else understands. It’s the one who solves complex problems by writing simple, easy to understand code. Maintainability is the key, that’s what business needs to succeed.

Reading Time: 2 minutes

Reading Time: < 1 minuteEver wondered why C# uses such a silly format for its for loop?

I mean why not do it like Pascal?

 
    var loopVar:int;
    for loopVar := 0 to 10 do
    begin
        writeln('something erudite');
    end;

Whereas C, C#, and C++ use constructs like…

    int i;
    for(i = 0; i < 10; i++)
    {
        Console.WriteLine("something erudite");
    }

The reason is not always taught in C / C# /C++ 101 and it’s really quite useful. C’s for loop is split into 3 sections.

1. Initialisation. Here you set up the start conditions for your loop.
2. Comparison. The loop will continue to iterate whilst the condition here evaluates to true.
3. Loop action. An action that is performed every time the loop iterates.

You do not have to initialise an ordinal type, you do not have to use a numeric comparison, you do not have to increment or decrement anything. Today I needed to walk an exception stack, I used a for loop, roughly (but not exactly) like this.

catch(Exception ex)
{
    for (Exception subject = ex; null != subject; subject = subject.InnerException)
    {
        if (subject is ArgumentNullException)
        {
            ///do stuff
        }
    }
}

This is just a simple example, there are all sorts of places where the C style for construct comes in useful – a friendly word of advice though, don’t go nuts. If the operations that you need to perform in order to run the loop don’t neatly fit into the for loop construct, then you should probably do it a different way, else your code is likely to become difficult to read and buggy.

Reading Time: 2 minutes

If you’re a junior engineer and you have a problem you can easily ask someone more senior. But what if you’re the most senior person in the room?

Well if the problem isn’t one of not having the technical expertise but just not being able to find what’s wrong then the answer couldn’t be more simple – ask anyone.

I have a reputation for being able to find problems with other people’s code but the truth is that 90% of the time they actually find the errors themselves. The simple process of having to explain what one was trying to achieve and how he was trying to achieve it often reveals problems with the logic or problems with the code.

In fact it can be better to ask people who, although capable of understanding what you’re doing, are from a different technical area or who are considerably less experienced – the key is that you have to explain what you’re doing to them and the more explaining you have to do, the better.

At my previous company I found that the technical director was a particularly good person to ask. He had been a programmer but wasn’t really used to the way we worked in the modern production environment. He provided a very intelligent but completely different slant on things. OK, so sometimes it was frustrating when he disagreed with a perfectly good way that I was doing something, but he spotted a fair few little mistakes more often and that was very helpful.