Tuesday, May 03, 2022

House price for England and Wales March 2022

I'm currently uploading the latest house price data to my website. I really need to improve the performance of the import, it now takes days to complete, but it should be complete some time soon. 

There's a definite trend appearing in the data since last summer, sales are falling and prices have peaked. Annual house price inflation is still positive but it looks like it will go negative very shortly, unless the government intervene again. It's also noticeable that the price drops are mostly being driven by the price of flats, although I have no insight into why that would be the case.

Wednesday, April 13, 2022

Converting .ashx handlers to .NET 5/6

I've been trying to figure out how to convert my site to .NET 5 or 6 for a long while. The pages are written in PHP (because that was the only thing available to me on my web host at the time) but there are many .ashx handlers that were added when I originally decided to move to ASP.NET but never got round to completing the job. But the .NET Framework isn't going to be improved or added to in the future so moving to .NET 5/6 needs to happen at some point. It's not a simple process, the differences are fairly major, but I think I've finally figured out a path to making the move.

My main concern was that I wanted to have a common code base so I can reuse code and continue to work on my current site whilst I set up the updated one, rather than rewriting the whole thing from scratch. A rewrite would certainly be simpler but the switch over would almost certainly be a disaster.

The first step was to work on assemblies used by the site. The Portability Analyzer helped here in figuring out how portable my code was (although it's not generally too helpful in explaining what to do with APIs that are not available in .NET 5). Fortunately it wasn't too difficult to move .NET Coords to .NET Standard 2, meaning it could be used from old and new .NET. I used the .NET Upgrade Assistant to convert to the latest SDK project format. Luckily there were cross platform versions of the third party assemblies I use.

One other thing I did was rip out Entity Framework, which I've never been able to get along with and move from the Oracle MySql ADO.NET connector to MySqlConnector. Like everything from Oracle on Windows, their MySql connector is a complete dumpster fire.

Once that was done, I decided the next step was to move all my .ashx handler code into a new assembly. So every .ashx file now looks something like

<%@ WebHandler Language="C#" Class="DoogalCode.AdministrativeAreasCSV" %>

I configured that assembly to target .NET 4.8 and .NET 5, since there would have to be differences between them. At this point, the .NET 4.8 assembly compiled fine (although with many warnings about using things from System.Web even though that's perfectly fine in .NET 4.8!) and the .NET 5 assembly failed with a gazillion errors. 

The next step was to build the .NET 5 assembly without errors. I decided the simplest way to do that was to build .NET 5 only versions of all the things the compiler was complaining about. This meant things like HttpContext, HttpRequest, HttpResponse and IHttpHandler. The properties and methods didn't do anything except throw an exception, I didn't want the code to run, just compile. I'd figure out the details of  how to implement those methods as I went through each handler and got them working in .NET 5.

Once I'd done that, I needed a .NET web site. Since my front end is written in PHP, I decided to take a look at PeachPie, which claims to be a PHP compiler for .NET. And I am mightily impressed. I created a new PeachPie project, copied my PHP files across and it just worked. I had my website running in .NET 5 in a few minutes. Admittedly I don't do anything very complicated in my PHP, so I can't be sure it's perfect but it's certainly perfect for my needs.

But I still had a bunch of .ashx handlers to get working in .NET 5. I added a method to my website startup class that let me use my dummy IHttpHandler and HttpContext classes to call into my handlers

private static void MapHandler<THandler>(IApplicationBuilder app, string path) where THandler : IHttpHandler, new()


  app.Map(path, (app2) =>


    app2.Run(async context =>


      var handler = new THandler();

      await Task.Run(() =>


        handler.ProcessRequest(new DoogalCode.HttpContext(context));





Then for each handler I mapped URL paths to the handler via this method, like so

MapHandler<CountiesCSV>(app, "/CountiesCSV");

Now I could call my handlers and start to fix up the methods in my dummy classes. Which is where I am currently, going through each handler fixing issues as I find them, whilst still being able to work on my live website. Once that is done and has gone live, I may try to figure out how to finally move my PHP code to proper .NET

Monday, March 28, 2022

House price data for England and Wales February 2022

I've uploaded the latest Land Registry data to my website. I've also added a "Sale Type" column to the data which shows if the sale is a non-standard sale. These include transfers under a power of sale/repossessions, buy-to-lets, transfers to non-private individuals and sales where the property type is classed as ‘Other’. The aggregated data now ignores non-standard sales, although it has not had a major effect.

Monday, February 28, 2022

Distance to the sea for all postcodes

I've added the approximate distance to the sea for every postcode. This is available on the individual postcode pages and as part of the CSV downloads

Sunday, February 20, 2022

Distance to a line

Probably only of use to me, but I've added a page that lets you find the shortest distance between a postcode and a straight line

Distance to a line (doogal.co.uk)

Saturday, February 19, 2022

UK postcode data update

I've uploaded the latest ONS postcode data to my website. The data has passed my sanity tests, but let me know if you spot anything amiss. The CSV downloads now include a UPRNs column, which is a comma separated list of the UPRNs in the postcode

Friday, January 28, 2022

.NET Coords in .NET Core

.NET Coords is now a .NET Standard 2.0 project, meaning it can be used in .NET Framework 4.8 and .NET Core projects

Saturday, January 22, 2022

UPRN data

I've uploaded UPRN (Unique Property Reference Number) data for the UK to my website. They are only currently available as a link from viewing a particular postcode (an example) but do let me know if it would be useful to provide them in some other way.

Unfortunately the freely available data doesn't include the thing that would be make this data exceedingly useful, the address. I doubt that is likely to happen since there are many companies whose business is based on selling that data

One other major problem is the open data includes old UPRNs but no indication that the UPRN is no longer active, hopefully this will be addressed in a future release.

There's more information on this data here which covers other issues with it.

Saturday, January 01, 2022

C# code to generate a segment FIT file

Someone asked about getting hold of the code to generate FIT files from Strava segments. It's not possible to provide standalone code since it's has dependencies on the rest of my website and a Strava library, but here's what I use which may give people an idea of how to use the rather confusing Garmin FIT API

using System;
using System.Collections.Generic;
using System.IO;
using System.Web;
using Dynastream.Fit;
using Newtonsoft.Json.Linq;
using Strava.Common;
using Strava.Segments;
using Strava.Streams;
public static class FitGenerator { private static void AddLeaderEntry(SegmentPointMesg newRecord, int goal, float currentDistance, float totalDistance, byte goalIndex) { if (goal != 0) { var currentGoalTime = (float)Math.Floor(currentDistance / totalDistance * goal); newRecord.SetLeaderTime(goalIndex, currentGoalTime); } } private static SegmentLeaderboardEntryMesg GetLeaderboard(int goal, byte goalIndex, SegmentLeaderboardType type, string name) { var goalLeaderboard = new SegmentLeaderboardEntryMesg(); if (goal != 0) { goalLeaderboard.SetMessageIndex(goalIndex); goalLeaderboard.SetSegmentTime(goal); goalLeaderboard.SetType(type); if (!string.IsNullOrEmpty(name)) goalLeaderboard.SetName(name); } return goalLeaderboard; } private static int LatOrLngToSemicircle(double latOrLng) { return (int)(latOrLng * (Math.Pow(2, 31) / 180)); } private static bool HasPr(List<SegmentStream> pr, Segment seg) { if (pr != null) return true; if (seg.AthleteSegmentStats != null && seg.AthleteSegmentStats.PrElapsedTime != null) return true; return false; } public static byte[] Generate(Segment seg, int goal, string goalName, int rival, string rivalName, int challenger, string challengerName) { List<SegmentStream> altitude = null; try { StravaBaseHandler.TryStravaRequest((client) => { altitude = client.Streams.GetSegmentStream(seg.Id.ToString(), SegmentStreamType.Altitude | SegmentStreamType.LatLng); }); } catch (Exception ex) { throw new Exception("Failed to get stream for segment " + seg.Id, ex); } // get all the streams we are interested in SegmentStream locationStream = null; SegmentStream altitudeStream = null; SegmentStream distanceStream = null; locationStream = SegmentStream.GetStream(altitude, StreamType.LatLng); altitudeStream = SegmentStream.GetStream(altitude, StreamType.Altitude); distanceStream = SegmentStream.GetStream(altitude, StreamType.Distance); // grab user's best time List<SegmentStream> pr = null; var athlete = HttpContext.Current.Request.Cookies["athlete"]; if (athlete != null) { try { pr = StravaBaseHandler.GetSegmentPr(seg.Id.ToString(), athlete.Value); } catch { // do nothing } } // Generate some FIT messages // Every FIT file MUST contain a 'File ID' message as the first message var fileIdMesg = new FileIdMesg(); var records = new List<SegmentPointMesg>(); fileIdMesg.SetType(Dynastream.Fit.File.Segment); fileIdMesg.SetManufacturer(Manufacturer.Strava); // Types defined in the profile are available fileIdMesg.SetProduct(65534); fileIdMesg.SetTimeCreated(new Dynastream.Fit.DateTime(System.DateTime.Now)); fileIdMesg.SetSerialNumber(1); fileIdMesg.SetNumber(1); var fileCreator = new FileCreatorMesg(); fileCreator.SetHardwareVersion(0); fileCreator.SetSoftwareVersion(0); // segment ID var segmentId = new SegmentIdMesg(); segmentId.SetName(seg.Name); segmentId.SetEnabled(Bool.True); if (seg.ActivityType == "Run") segmentId.SetSport(Sport.Running); else segmentId.SetSport(Sport.Cycling); byte prIndex = 0; byte goalIndex = 1; byte rivalIndex = 2; byte challengerIndex = 3; byte komIndex = 4; byte qomIndex = 5; if (!HasPr(pr, seg)) { goalIndex--; rivalIndex--; challengerIndex--; komIndex--; qomIndex--; } if (goal == 0) { rivalIndex--; challengerIndex--; komIndex--; qomIndex--; } if (rival == 0) { challengerIndex--; komIndex--; qomIndex--; } if (challenger == 0) { komIndex--; qomIndex--; } if (seg.KOM() == 0) { qomIndex--; } segmentId.SetSelectionType(SegmentSelectionType.Starred); segmentId.SetUuid(Guid.NewGuid().ToByteArray()); if (HasPr(pr, seg)) segmentId.SetDefaultRaceLeader(prIndex); if (seg.Map.Polyline == "") throw new Exception("The segment has no map data (which is somewhat unexpected), " + "I can't generate the FIT file"); var points = PolylineDecoder.Decode(seg.Map.Polyline); // figure out SW and NE double swLat = 1000; double swLong = 1000; double neLat = -1000; double neLong = -1000; foreach (var pt in points) { swLat = Math.Min(swLat, pt.Latitude); swLong = Math.Min(swLong, pt.Longitude); neLat = Math.Max(neLat, pt.Latitude); neLong = Math.Max(neLong, pt.Longitude); } // segment info var lap = new SegmentLapMesg(); lap.SetUuid(Guid.NewGuid().ToByteArray()); lap.SetTotalDistance(seg.Distance); lap.SetTotalAscent((ushort)seg.TotalElevationGain); lap.SetSwcLat(LatOrLngToSemicircle(swLat)); lap.SetSwcLong(LatOrLngToSemicircle(swLong)); lap.SetNecLat(LatOrLngToSemicircle(neLat)); lap.SetNecLong(LatOrLngToSemicircle(neLong)); lap.SetMessageIndex(1); lap.SetStartPositionLat(LatOrLngToSemicircle(points[0].Latitude)); lap.SetStartPositionLong(LatOrLngToSemicircle(points[0].Longitude)); lap.SetEndPositionLat(LatOrLngToSemicircle(points[points.Count - 1].Latitude)); lap.SetEndPositionLong(LatOrLngToSemicircle(points[points.Count - 1].Longitude)); // goal entry var goalLeaderboard = GetLeaderboard(goal, goalIndex, SegmentLeaderboardType.Goal, goalName); var rivalLeaderboard = GetLeaderboard(rival, rivalIndex, SegmentLeaderboardType.Rival, rivalName); var challengerLeaderboard = GetLeaderboard(challenger, challengerIndex, SegmentLeaderboardType.Challenger, challengerName); var komLeaderboard = GetLeaderboard(seg.KOM(), komIndex, SegmentLeaderboardType.Kom, ""); var qomLeaderboard = GetLeaderboard(seg.QOM(), qomIndex, SegmentLeaderboardType.Qom, ""); SegmentStream prDistanceStream = null; SegmentStream prTimeStream = null; if (pr != null) { prDistanceStream = SegmentStream.GetStream(pr, StreamType.Distance); prTimeStream = SegmentStream.GetStream(pr, StreamType.Time); } SegmentLeaderboardEntryMesg prLeaderboard = null; if (HasPr(pr, seg)) { prLeaderboard = new SegmentLeaderboardEntryMesg(); prLeaderboard.SetMessageIndex(prIndex); var prTime = seg.AthleteSegmentStats.PrElapsedTime; prLeaderboard.SetSegmentTime(prTime); prLeaderboard.SetType(SegmentLeaderboardType.Pr); } for (var i = 0; i < locationStream.Data.Count; i++) { var newRecord = new SegmentPointMesg(); var latLng = locationStream.Data[i]; if (latLng != null) { var intLat = LatOrLngToSemicircle(Convert.ToDouble(((JArray)(latLng)).First)); newRecord.SetPositionLat(intLat); var intLng = LatOrLngToSemicircle(Convert.ToDouble(((JArray)(latLng)).Last)); newRecord.SetPositionLong(intLng); } if (altitudeStream != null) newRecord.SetAltitude(Convert.ToSingle(altitudeStream.Data[i])); var currentDistance = Convert.ToSingle(distanceStream.Data[i]); newRecord.SetDistance(currentDistance); newRecord.SetMessageIndex((ushort)i); if (HasPr(pr, seg)) { if (pr != null) { // add PR time // find index of the PR item that is at or beyond current distance var pos = prDistanceStream.Data.Count - 1; for (var j = 0; j < prDistanceStream.Data.Count; j++) { if (Convert.ToSingle(prDistanceStream.Data[j]) - Convert.ToSingle(prDistanceStream.Data[0]) >= currentDistance) { pos = j; break; } } // find time at that distance if (pos > 0) { var prStartDistance = Convert.ToSingle(prDistanceStream.Data[pos - 1]) - Convert.ToSingle(prDistanceStream.Data[0]); var prEndDistance = Convert.ToSingle(prDistanceStream.Data[pos]) - Convert.ToSingle(prDistanceStream.Data[0]); var fraction = (currentDistance - prStartDistance) / (prEndDistance - prStartDistance); var prTime = Convert.ToSingle(prTimeStream.Data[pos - 1]) + fraction * (Convert.ToSingle(prTimeStream.Data[pos]) - Convert.ToSingle(prTimeStream.Data[pos - 1])); newRecord.SetLeaderTime(prIndex, (float)Math.Floor(prTime - Convert.ToSingle(prTimeStream.Data[0]))); } else { newRecord.SetLeaderTime(prIndex, 0); } } else { AddLeaderEntry(newRecord, seg.AthleteSegmentStats.PrElapsedTime.Value, currentDistance, seg.Distance, prIndex); } } // add goal time AddLeaderEntry(newRecord, goal, currentDistance, seg.Distance, goalIndex); AddLeaderEntry(newRecord, rival, currentDistance, seg.Distance, rivalIndex); AddLeaderEntry(newRecord, challenger, currentDistance, seg.Distance, challengerIndex); AddLeaderEntry(newRecord, seg.KOM(), currentDistance, seg.Distance, komIndex); AddLeaderEntry(newRecord, seg.QOM(), currentDistance, seg.Distance, qomIndex); records.Add(newRecord); } // Create file encode object var encodeDemo = new Encode(ProtocolVersion.V20); using (var fitDest = new MemoryStream()) { // Write our header encodeDemo.Open(fitDest); // Encode each message, a definition message is automatically generated and output if necessary encodeDemo.Write(fileIdMesg); encodeDemo.Write(fileCreator); encodeDemo.Write(segmentId); if (prLeaderboard != null) encodeDemo.Write(prLeaderboard); if (goal != 0) encodeDemo.Write(goalLeaderboard); if (rival != 0) encodeDemo.Write(rivalLeaderboard); if (challenger != 0) encodeDemo.Write(challengerLeaderboard); if (seg.KOM() != 0) encodeDemo.Write(komLeaderboard); if (seg.QOM() != 0) encodeDemo.Write(qomLeaderboard); encodeDemo.Write(lap); encodeDemo.Write(records); encodeDemo.Close(); fitDest.Flush(); fitDest.Position = 0; return fitDest.ToArray(); } } }