The old measurements of length, area, volume and weight in the UK and the States refuse to die. They are known as the Imperial and US customary measurement systems respectively. Not only are they a natural way of dealing with approximate measurements, and tied to human dimensions, but they are no longer difficult to calculate. The metric system has, of course, the advantage of being easy to calculate in your head, but even your phone has the computing power to deal with adding the old dimensions. The addition or subtraction of areas, volumes or distances isn’t such a problem any more, just as we no longer find that talking with far-away people is impossible. Technology makes the metric system less compelling.
This thought came to me when I was asked by a friend to calculate the relative area of woodland in the 1840s. The data was from a transcription of the Tithe records. In those days, fields and woodland was measured in Acres, Roods and perches. In fact, it isn’t just the old records and documents that have the old measurements but even today the old measurements are still used. (a hectare is roughly 2.47 acres). I never thought that this would happen to me, but here I was staring at a database containing an enormous number of fields, including their size, farm, occupier, owner, type and so on. The size was in Acres, roods and perches. It had to be aggregated. We needed to know the relative areas of pasture, meadow and woodland for each farm. We wanted to know the size of the farms and holdings and the variance in the amount of woodland. The way that the area of each field was expressed had to be turned into a decimal number so it could be used in calculations and aggregations. The result had to be expressed in the old way.
Here are some of the field sizes, along with the acreage expressed as a decimal, which is a useful ‘lingua franca’.
To achieve this, I created a simple function. It could have been simpler but the field areas were originally recorded by hand, which introduced a certain degree of irregularity; so it had to cope.
CREATE OR ALTER FUNCTION dbo.CalculateAcreage (@Acreage VARCHAR(50))
/**
Summary: >
This scalar function calculates the acreage as a float, based on the
measure of acres, roods and perches. it is used to calculate the total
screage of a farm based on 1847 tithe returns
Author: Phil Factor
Date: 23/04/2020
Examples:
- Select dbo.CalculateAcreage ('2 acres 3 roods 9 perches')
- Select dbo.CalculateAcreage ('1 acre')
- Select dbo.CalculateAcreage ('3 acres, 1 rood, and 30 perches')
- Select dbo.CalculateAcreage ('3 acres,1 rood, and30perches')
- Select dbo.CalculateAcreage ('3')
Returns: >
nothing
**/
RETURNS Numeric(19,6)
AS
BEGIN
DECLARE @ii INT, @iimax INT;
DECLARE @Acres Numeric(19,6);
DECLARE @Quantity Numeric(19,6);
SELECT @ii = 1, @iimax = Len(@Acreage), @Acres = 0, @Quantity = 0;
DECLARE @STATE VARCHAR(10) = 'number';
WHILE (@ii <= @iimax)
BEGIN --start by assuming it is a number
IF Substring(@Acreage, @ii, 100) LIKE 'acre%'
SELECT @Acres = @Acres + @Quantity, @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 4;
IF Substring(@Acreage, @ii, 100) LIKE 'rood%'
SELECT @Acres = @Acres + (@Quantity / 4), @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 4;
IF Substring(@Acreage, @ii, 100) LIKE 'perch%'
SELECT @Acres = @Acres + (@Quantity / 160), @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 5;
IF @STATE = 'number' AND Substring(@Acreage, @ii, 1) = ' '
SELECT @STATE = 'seeking';
IF @STATE = 'seeking' AND Substring(@Acreage, @ii, 1) LIKE '[1234567890]%'
SELECT @STATE = 'number';
IF (@STATE = 'number')
SELECT @Quantity =(@Quantity * 10) + Convert(INT, Substring(@Acreage, @ii, 1));
SELECT @ii = @ii + 1;
END;
RETURN @Acres;
END;
GO
Some transcriptions use ‘ac.’ Instead of acres, ‘ro.’ Instead of roods and so on. The function is easily altered to cope with this. You also have the problem of malformed records. These will usually be zero, so if you filter out records with a reported zero acrage, you can correct the entry.
Now having done this, it is nice to go from acres as a decimal number to acres, roods and perches. This is, of course, trivial until you get to the details. This is basically the way that lists are done in English, where the list is comma-delimited until you get to the final pair, at which point you signal the end of the list by using the word ‘and’ between the final two elements.
Eg: Dopey, Doc, Bashful, Happy, Grumpy, Sleepy and Sneezy: 1 yard, 1 foot and 2 inches.
GO
CREATE OR ALTER FUNCTION dbo.WrittenVersionOfAcres (@Quantity Numeric(19,6))
/**
Summary: >
This converts a number of acres expressed as a decimal into a string
listing its acres, roods and perches
Author: Phil Factor
Date: 04/05/2020
Examples:
- Select dbo.WrittenVersionOfAcres(dbo.CalculateAcreage ('2 acres'))
- Select dbo.WrittenVersionOfAcres(3.437500)
- Select dbo.CalculateAcreage(dbo.WrittenVersionOfAcres(3.437500))
- Select dbo.CalculateAcreage(dbo.WrittenVersionOfAcres(dbo.CalculateAcreage ('2 acres')))
Returns: >
nothing
**/
RETURNS NVARCHAR(100)
AS
begin
RETURN
(select
CASE
when CHARINDEX(' ,',backwards)>0
THEN REVERSE(STUFF(backwards,CHARINDEX(' ,',backwards),2,' dna '))
ELSE REVERSE(backwards)
end
FROM
(SELECT
REVERSE(
STUFF(
CASE WHEN acres>0 THEN ', '+ CONVERT(varchar(5),acres)+ ' acre'
+ CASE WHEN acres>1 THEN 's' ELSE '' END ELSE '' END+
CASE WHEN roods>0 THEN ', '+ CONVERT(varchar(5),roods)+ ' rood'
+ CASE WHEN acres>1 THEN 's' ELSE '' END ELSE ''END+
CASE WHEN perches>0 THEN ', '+ CONVERT(varchar(5),perches)+ ' perch'
+ CASE WHEN acres>1 THEN 'es' ELSE '' END ELSE ''END
,1,2,'')
)
FROM (SELECT CONVERT(int,floor(@Quantity)) AS acres,
Convert(int,floor((@Quantity % 1)*4)) AS roods,
Convert(int,floor(((@Quantity % 1)*4)%1*40))AS perches
)f
)g(backwards))
end
go
Why would we want to do this? Simple: firstly, we can use this for the aggregated results. Secondly, we can run a lot of tests and make sure that it is possible to cycle a whole lot of random areas expressed as decimal area values to and fro between the two functions until you hit an error where the original value isn’t the same as the generated value. I did this with thousands of records of acreage as recorded from the old tithe records, checking also that a result of zero was legitimate.
These two functions solved my problem and allowed me to do the calculations easily.
It occurred to me, of course, that this same sort of function could deal with other type of measurement. Here we can calculate distances
CREATE OR ALTER FUNCTION dbo.CalculateMiles (@distance VARCHAR(50))
/**
Summary: >
This scalar function calculates the distance in miles as a float, based on the
measure of leagues, miles, furlongs, chains, yards, feet and inches.
Author: Phil Factor
Date: 23/04/2020
Examples:
- Select dbo.CalculateMiles('10 leagues and 2 miles')
- Select dbo.CalculateMiles('1760 yards')
- Select dbo.CalculateMiles('2 feet and 6 inches')
Returns: >
mileage expressed as Numeric(28,18)
**/
RETURNS Numeric(28,18)
AS
BEGIN
DECLARE @ii INT, @iimax INT;
DECLARE @miles Numeric(28,18);
DECLARE @Quantity Numeric(28,18);
SELECT @ii = 1, @iimax = Len(@Distance), @miles = 0, @Quantity = 0;
DECLARE @STATE VARCHAR(10) = 'number';
WHILE (@ii <= @iimax)
BEGIN --start by assuming it is a number
IF Substring(@Distance, @ii, 100) LIKE 'league%'
SELECT @miles = @miles + (@Quantity*3), @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 6;
IF Substring(@Distance, @ii, 100) LIKE 'mile%'
SELECT @miles = @miles + @Quantity, @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 4;
IF Substring(@Distance, @ii, 100) LIKE 'furlong%'
SELECT @miles = @miles + (@Quantity / 8), @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 7;
IF Substring(@Distance, @ii, 100) LIKE 'chain%'
SELECT @miles = @miles + (@Quantity / 80), @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 5;
IF Substring(@Distance, @ii, 100) LIKE 'yard%'
SELECT @miles = @miles + (@Quantity / 1760), @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 4;
IF Substring(@Distance, @ii, 100) LIKE 'feet%'
SELECT @miles = @miles + (@Quantity / (1760*3)), @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 4;
IF Substring(@Distance, @ii, 100) LIKE 'foot%'
SELECT @miles = @miles + (@Quantity / (1760*3)), @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 4;
IF Substring(@Distance, @ii, 100) LIKE 'inch%'
SELECT @miles = @miles + (@Quantity / (1760*3*12)), @Quantity = 0,
@STATE = 'seeking', @ii = @ii + 4;
IF @STATE = 'number' AND Substring(@Distance, @ii, 1) = ' '
SELECT @STATE = 'seeking';
IF @STATE = 'seeking' AND Substring(@Distance, @ii, 1) LIKE '[1234567890]%'
SELECT @STATE = 'number';
IF (@STATE = 'number')
SELECT @Quantity =(@Quantity * 10) + Convert(INT, Substring(@Distance, @ii, 1));
SELECT @ii = @ii + 1;
END;
RETURN @miles;
END;
go
Once more, we can translate back from a decimal to a string. Naturally, it does all the conversions for you so that you can use the functions for calculating your timber cutting-lists. Well, certainly in the UK, the conformance to the metric system is skin-deep at best; the real work is done in old measurements.
go
CREATE OR ALTER FUNCTION dbo.WrittenVersionOfMiles (@Quantity Numeric(28,18))
/**
Summary: >
This converts a number of miles expressed as a decimal into a string
listing its miles, furlongs,chains, yards feet and inches
Author: Phil Factor
Date: 04/05/2020
Examples:
- select dbo.WrittenVersionOfMiles(dbo.CalculateMiles ('2 feet 6 inches'))
- select dbo.WrittenVersionOfMiles(3.437500)
- select dbo.CalculateMiles(dbo.WrittenVersionOfMiles(3.437500))
- select dbo.CalculateMiles(dbo.WrittenVersionOfMiles(dbo.CalculateMiles ('2 miles')))
Returns: >
nothing
**/
RETURNS NVARCHAR(100)
AS
begin
RETURN
(select
CASE
when CHARINDEX(' ,',backwards)>0
THEN REVERSE(STUFF(backwards,CHARINDEX(' ,',backwards),2,' dna '))
ELSE REVERSE(backwards)
end
FROM
(SELECT
REVERSE(
STUFF(
CASE WHEN miles>0 THEN ', '+ CONVERT(varchar(5),miles)+ ' mile'
+ CASE WHEN miles>1 THEN 's' ELSE '' END ELSE '' END+
CASE WHEN furlongs>0 THEN ', '+ CONVERT(varchar(5),furlongs)+ ' furlong'
+ CASE WHEN furlongs>1 THEN 's' ELSE '' END ELSE ''END+
CASE WHEN chains>0 THEN ', '+ CONVERT(varchar(5),chains)+ ' chain'
+ CASE WHEN chains>1 THEN 's' ELSE '' END ELSE ''END+
CASE WHEN yards>0 THEN ', '+ CONVERT(varchar(5),Yards)+ ' yard'
+ CASE WHEN yards>1 THEN 's' ELSE '' END ELSE '' END+
CASE WHEN feet>0 THEN ', '+ CONVERT(varchar(5),feet)
+ CASE WHEN feet>1 THEN ' feet' ELSE ' foot' END ELSE ''END+
CASE WHEN inches>0 THEN ', '+ CONVERT(varchar(5),inches)+ ' inch'
+ CASE WHEN inches>1 THEN 'es' ELSE '' END ELSE ''END ,1,2,'')
)
FROM (SELECT CONVERT(int,floor((@Quantity))) AS miles,
floor((@Quantity % 1)*8) AS furlongs,
floor((@Quantity % 1)*8%1*10) AS chains,
floor(round(((@Quantity % 1)*8)%1*10,5)%1*22)AS yards,
floor(round((((@Quantity % 1)*8)%1*10)%1*22,5)%1*3) AS feet,
floor(round(((((@Quantity % 1)*8)%1*10)%1*22)%1*3,5)%1*12) AS inches
)f
)g(backwards))
END
GO
You can test this out….
SELECT dbo.WrittenVersionOfMiles
(dbo.CalculateMiles ('5 yards and 12 inches ')
+dbo.CalculateMiles ('1 feet and 36 inches ')--6 yards and 2 feet
+dbo.CalculateMiles ('4 feet and 15 inches ')--8 yards, 1 foot and 3 inches
+dbo.CalculateMiles ('5 feet and 3 inches ')--10 yards and 6 inches
+dbo.CalculateMiles ('12 feet and 6 inches '))
….or …
SELECT dbo.WrittenVersionOfMiles(Sum(dbo.CalculateMiles(TheValues))) FROM
(VALUES ('5 yards and 12 inches '),
('1 feet and 36 inches '),
('4 feet and 15 inches '),
('5 feet and 3 inches '),
('12 feet and 6 inches '))f(TheValues)
The old measurements of weight, area, distance and volume were never legislated out of existence in favour of the metric system of revolutionary France. They merely went underground. The technology can nowadays cope with any awkwardness. I was able to come up with reports based on the meticulous measurements of our forebears, and these were measurements that they could and did perform in their heads. It was very satisfactory, though, to be able to get these figures. I therefore relax in my acres of garden, raising a pint as toast to a stubborn breed of people who don’t give an inch in the face of the bureaucratic rush to the metric system.
The post Tackling Imperial and US Customary Measurements in Databases appeared first on Simple Talk.
from Simple Talk https://ift.tt/35DhJ7G
via
No comments:
Post a Comment