Monday, 14 December 2009

SWIFT Gazetteer

We were commissioned on Friday to look into matching the SWIFT gazetteer (which is one of the databases used by social / wellbeing services) against the Local Land and Property Gazetteer. I was given a file that contained just over 110,000 address rows from SWIFT and started doing some of the investigation work today.

Happily about 80% of the SWIFT addresses had a UPRN (Unique Property Reference Number) next to them and I was able to match these pretty easily to the LLPG UPRNs; all of the values that were on the Knowsley LLPG file looked right apart from some postcode discrepanicies. Some of the UPRNs in SWIFT are out of borough (mainly Liverpool / Sefton addresses) and I'll need to get a copy of the Merseyside - wide gazetteer and repeat the process.

This leaves about 10,000 rows (ish) that need to be matched 'by hand' and I'll continue working on the routines this week. Doing work like this is pretty tedious but it's important to be thorough and to have a clear step - by - step process so that the work you do is transparent. We've done data matching exercises in the past so now I can use all that experience and write a clear, concise document explaining what I have done, what the results have been etc. (it's almost like writing up a science project at school). Someone else could easily follow my work and recreate what I have done, or use it as a basis for matching a different data set.

SWIFT doesn't follow the BS7666 format for organising address and there is data randomly strewn across multiple columns - you can see the house number in as many as five of the columns. So I'll need to do some work which standardises all the data into just a few columns and then try to match on these. The important thing is being able to explain why each row didn't match and this could quickly become labour - intensive. I'll post some more updates this week.

Wednesday, 25 November 2009

PLINGS and QlikView

Spent some more time working on the PLINGS app today. Tidied up the 'Create New User' form and improved the way it worked. This is probably the last application I will ever write in ASP.NET; having seen what DOJO can do I find the whole thing slow and unresponsive.

Also expanded the eClaims dashboard for QlikView with the help of one of our potential customers. The functionality that you can create very quickly was amazing and it has received a very positive response.

QlikView Reporting

Stumbled across an article in Time Magazine about a company in the US that makes a product called QlikView. It's a really, really good development suite for building data dashboards. It allows you to import tables of data and it automatically finds relationships between data. So if you load data about mileage claims (I've used this to put a demo together) you can quickly show the sum of all the unauthorised claims for a specific department in September 2009 by clicking a few controls. There are no complicated SQL scripts, no VB to change the data - it takes no time at all to get something up and running. There's also a full suite of controls - graphs, charts, dials, lists to make everything look neat and funky.

Did I mention it was fast! I can achieve in two hours something that would have taken me days of boring development time. Plus I know that I can easily train this out to other people on the team.

The sales team is coming in to see us on the 7th December and they have offered some free time to use some of our data and put together a polished demo. Hopefull we can convince customers that this is the way forward in terms of reporting / data dashboards.

Monday, 23 November 2009

GIS Dashboard Replication with STRATUS RIA tools / DOJO (8)

This morning I had a look at the different 'sub layers' available to me. Each map is built from a base layer (of which we have two, an OS map that shows streets, houses etc. as coloured polygons and a satellite map which shows cars, trees, phone boxes etc.). You can also add extra information on top of these such as plotting the location of libraries, one stop shops, bridges, bus stops and ward boundaries.

This is pretty useful because I now have a map of the borough with all the ward boundaries and the user can click inside one of the boundaries and see the stats for that ward. Now I need to figure out how to plot the geographic location of individual instances that occur in the ward which shouldn't be too difficult because the API has a method for doing that sort of thing.

GIS Dashboard Replication with STRATUS RIA tools / DOJO (7)

I have now (sadly) gotten into the habit of writing this a day late. I spent Friday morning doing some more work which involved getting the application to dynamically output a table of data and a line chart that summarised the data on every click of the mouse. The charting element is included in the DOJO library and took a little bit of figuring out while the table is just building and injecting a string of HTML into a DIV on the web page which is easy enough to do.

Friday, 20 November 2009

GIS Dashboard Replication with STRATUS RIA tools / DOJO (6)

Another successful afternoon (yesterday, I'm writing this a day late). Now I have the web application displaying both east / north / ward info when the user clicks as well as displaying enforcement statistics for the specific ward. Had a bit of a crisis over the mouse click - for some reason the app was only displaying the stats info on the second click - but I fudged it in the end by nesting a function within a function on the OnMapClick event. It works so I guess that's the main thing - I have a suspicion that the problem was being caused by the search results that were coming back. I use two different search functions and both were returning an object called 'results'; need to investigate this further.

Today I need to work on the following:
  • Replace the standard layer with a layer that shows the ward boundaries
  • Add a date filter that lets the user decide how much data they want to see
  • Figure out how to display graphs of data using the DOJO framework

Thursday, 19 November 2009

GIS Dashboard Replication with STRATUS RIA tools / DOJO (5)

Wow! Lot's of progress today. Finally got my head around the fact that the API will allow you to search a specific MapInfo table for data that relates to that specific point. So if I want to find which ward I'm clicking on, for example, I can just use the search at point method and connect it to the wards table and it will give me the name of the ward. Simple!

I have a demo working now where a mouseclick will give the easting, northing and ward name of the point I have clicked on the map. I now have to figure out how to pull enforcement statistics back for the ward I have clicked on; just need to get my functions in the right order.

Tuesday, 17 November 2009

GIS Dashboard Replication with STRATUS RIA tools / DOJO (4)

Another pretty successful afternoon. Three of us have worked together to figure out how to pull back and display event information within a specific radius of a point on the map. We also discovered that the map was configured wrong based on the PB examples (their bounds are set for London, not Knowsley).

There doesn't appear to be an onMapMouseOver event (or something similar) to fire an event when the mouse is moved. I phoned the support team and they said that they tried to get it to work but couldn't. That's a bit of a shame.

Now I need to start planning how to actually put the dashboard together and also if there's a way to filter data that gets pulled back from the Geostore table. I also need to work out how to show events for a specific area partnership. Lots to do but much further on than I thought I would be after just two days.

GIS Dashboard Replication with STRATUS RIA tools / DOJO (3)

Success! After yesterday's frustrations at not being able to download the right files a colleague who knows a lot more about setting up Apache websites than I do suggested recreating the Stratus web server locally and pointing Aptana at the local version instead. So we did the following:
  • Installed Apache Tomcat 5.5.23
  • Copied the contents of the Stratus webapp (that's the root for Tomcat much like inetpub\wwwroot is the root for IIS) into the local webapp folder
  • Created a new project in Aptana and pointed this to the ria content on the local machine
  • Moved all my test files across and hit compile

After a little bit of messing with the filepaths for the .js and .css resources we were good to go. The map displayed and I was able to move around, zoom in / out and all that good stuff.

Now I've started working on getting onclick events from the map, and also using the features tables to do a 'Find My Nearest' search for libraries and one stop shops. This is giving me a little headache - I can't get any x / y co-ords to work other than the sample ones provided by PB - but I'll get there.

A good morning's work.

Monday, 16 November 2009

GIS Dashboard Replication with STRATUS RIA tools / DOJO (2)

Bit of a frustrating afternoon. I've been building up the structure of the application and making sure that all the necessary .js files are available (there are a lot). If you dump a load and then refresh Aptana it takes a few minutes to update itself which is irritating (I guess it's compiling all the content or something).

Now I have got to the stage where it looks like FireFox is downloading a load of content from the server that the API sits on but it's trying to get hold of one particular file in a folder that doesn't exist (ria/widgets./i18n.js) anywhere on the server. I wonder if this is a bug? The wierd thing is that the examples work fine so maybe it's something I'm doing wrong.

More tomorrow.

GIS Dashboard Replication with STRATUS RIA tools / DOJO (1)

Just finished 1/2 day #1. Spent the morning setting up the development environment and running through some basic DOJO tutorials. Achieved the following:
  • Downloaded and installed Apanta Web onto Windows XP ( This is a free development tool that comes with a DOJO plugin that makes life easier.
  • Downloaded and installed FireFox ( I am reliably informed that you have to pay for a debugger that works with IE; the FireFox one is (of course) free.
  • Set up a new project in Apanta. If you create a default web project then it asks you if you want to add the reference to DOJO. Learned how to add files, set the start page, that sort of thing.
  • Worked through a series of DOJO tutorials at that show off some of the cool stuff you can do pretty easily. They all eventually compiled and worked.

There was a bit of post - install jiggery pokery for Apanta; you have to download and install the DOJO plug in, set the proxy properly so that it can connect to the web (for some reason it didn't like the new proxy we have installed) and set up a debug environment that defaults to FF and not IE. But after a mornings' work I'm good to go.

GIS Dashboard Replication with STRATUS RIA tools / DOJO

On Friday I was asked to drop everything and investigate how we can replicate an existing GIS dashboard using the STRATUS RIA tools ( and the DOJO toolkit ( DOJO is a JavaScript library that let's you do extremely cool things. If I stick to my plan then I'm going to record my experiences here and there should be an update for every 1/2 day that I spend working on this project.

Monday, 5 October 2009

PLINGS Development

Started working on the app on Thursday. Have done some learning about the ASP.NET AJAX controls that come bundled with ASP.NET 3.5. There's a few new things around using the ScriptManager and an UpdatePanel but once you get your head around these it's OK.

Today I started looking at the 'Create Activities' function. One of the requirements for this is to allow a user to choose a recurrence type from a menu (i.e. daily, weekly) or create their own custom recurrence type (for example fortnightly every Thursday). I think to implement this I'm going to store a list of recurrence types in a database table together with information on the recurrence and then offer this list to the user on the Create New Activity screen. One of the options will also be to create a new Recurrence type and if they choose this they will get redirected to another form once the activity has been created. This form will allow them to build the custom recurrence type and link it back to the Activity they just created.

Friday, 23 January 2009

"Proper" software design - a totally liberating experience

I am into week three of a module on web application design as part of my MSc. This has largely focused on the pros and cons of XML as a way of exchanging data but as part of my research into topics I have read a few articles on the best way to design Web Services - small but extremely powerful software components that live on a web server and allow interaction with data. They can easily be consumed by other applications because of the international language of XML and the concept of the 'mash-up' (websites and portals that amalgamate data from multiple sources) is possible because of Web Services.

There's a really good article written by Close (2005) that discusses contract - first web service design. What this means is that design starts with the schema; the data that the web service will take in and send out is decided upon first before any coding begins. If, like me, you have worked in an organisation where the needs of the business typically have to be bent around how an application has been implemented because the implementation was led by IT then you will understand how important starting with the data is.

Last week I started working on the design for an upgrade of an existing system that allows the online approval of mileage and expense claims. The current system works but it works really badly; maintenance is a nightmare and the user experience would be rated poor (to put it mildly). It's also incredibly slow. The mistake I made there was to treat the 'tinkering' with code as the design phase. I had a few ideas, hashed them together and unleashed it. As more and more people came to rely on the service it ground to a halt.

Fortunately I have learned a few things since then. I made the decision to write a web service that would interact with the database and this would provide the foundation for a web front end to be built upon. Equally I could expect someone to be able to plug an InfoPath form into the web service and build on top of it too. I had been to a couple of meetings with the business owner of the service (in this case the internal audit division) and we had agreed what the weaknesses were and what improvements they wanted making. Based on this two page document I sat with a blank piece of paper in front of me and asked the question "what operations do I need to perform on this data?"

I made the mistake of starting with the methods for the web service, and a couple of days into the design process I realised I needed to design the underlying operations first. What I wanted was two levels; the top level would be the web service methods that could be called by an application and return data based on inputs. The second level would be the components that the web service methods would be constructed from. I figured that if I put this bottom layer in place first it would be easy to put the web service layer together from the component 'blocks'.

It's incredible how liberating this was. I could essentially make my mistakes 'cheaply'; there was no investment in code that I would have to rewrite because I screwed up my thinking. At every point I was able to ask questions like: "what should be the input", "what should be the output", "how should an error be handled".

I studied software engineering at university and I have been developing applications for five years in my current post. But I have never taken this approach before. Part of this is down to immaturity, part is because I never really saw the need. But I understand the need now. Doing this creates a clear audit trail of paperwork and documentation about exactly how the service works and how the technology knits together. It's easy to create clear project tasks from the design documentation and I'm confident that I can hand it to a colleague and get them to do the building work.


Close, S. (2005) 'Contract-First Web Services: 6 Reasons to Start with WSDL and Schema' [Online] SYS-CON MEDIA.
Available from (Accessed 23rd January 2009)