SQL

Databases are a pain in the neck to look after, poorly designed models and processes that don’t remove temporary data can cause a database to grow in size. A database that is allowed to grow large beyond its requirements becomes a burden on the nightly backup, takes longer to restore in the event of a recovery scenario and slows down the development process by preventing developers from testing things out on “live” data. More often than not I have found that the problem lies with log or analytic tables sometimes this information is liberally logged (which it should be) and then totally ignored without a thought for trimming the data on a regular basis. SQL Server Management Studio provides a way of looking at the storage usage of tables individually from the properties context menu item of the table. ...

If you look through the schema of the table we imported in Part 2 there are a number of unused fields and some of the data appears to be missing. Cleaning up the Schema You can go right ahead and remove the fields that start with “Unused” as far as I can tell the full version of Code-Point uses these fields. Remove the nullable attributes from all of the fields, this will prevent us from doing something silly at a later date, and will avoid Object Relational Mappers such as Entity Framework from creating nullable data types. Many of the fields contain codes not data itself but codes that describe other data, so lets prepend code on the end of those fields for the time being. Cleaning up the Data The quality column in Code-Point Open describes the source and reliability of the data, it ranges from the most accurate 10 through to no data 90, when building a system around this data you need to decide at what data is important to your use case. The following query will give you an idea of the quality of the dataset as a whole, I have annotated it based upon the OS Code-Point documentation. ...

All of the data is in different files; SSIS is capable of extracting data from multiple files however for the purposes of this article I am going to stick to the Import Export Wizard. To combine all of the files into one (big) file a quick switch to the command prompt is required: type data\*.csv > .\CodePointOpenCombined.csv Because none of the data files have headers this works fine, if they did have headers some work would be needed to strip those out. Create a new database in SQL Server then follow these steps: ...

In April 2010 the Ordinance Survey released certain parts of their data under special licence which allows for commercial use without cost. All the types of data made available are outside the scope of this post although I hope that the techniques described could be applied to any data set not limited to Ordinance Survey data. In this post I am going to look at Code-Point Open, a list of all UK postcodes with their corresponding spatial positions. ...