Response to Case Study - Request 2
Updated: Nov 2, 2022
There are a few things needed before we can start showing the data in dashboards. Firstly I am going to modify the resorts table adding in columns for Latitude and Longitude and a geoLocation field of data type Geography.
ALTER TABLE tblResort ADD Latitude FLOAT;
ALTER TABLE tblResort ADD Longitude FLOAT;
ALTER TABLE tblResort ADD geoLocation GEOGRAPHY;
Next I need to populate the postcodes and Lat/Long. columns for the resorts. As there is no actual destination in those towns I do not have a postcode so am using the Latitude and Longitude for the central part of these resorts as found online.
UPDATE tblResort SET Latitude = '54.27966', Longitude = '-0.40443' WHERE ID = 1;
UPDATE tblResort SET Latitude = '54.59947', Longitude = '-3.13256' WHERE ID = 2;
UPDATE tblResort SET Latitude = '55.60652', Longitude = '-1.71704' WHERE ID = 3;
UPDATE tblResort SET Latitude = '55.94973', Longitude = '-3.19333' WHERE ID = 4;
UPDATE tblResort SET Latitude = '52.61265', Longitude = '1.72408' WHERE ID = 5;
I have found online a postcode csv which contains Lat/Long. data for most UK postcodes. Having downloaded this into a new table called tblPostCodes I need to do some tidy up by removing unwanted columns and rename the ones I keep.
ALTER TABLE tblPostCodes DROP COLUMN [column2];
ALTER TABLE tblPostCodes DROP COLUMN [column3];
ALTER TABLE tblPostCodes DROP COLUMN [column4];
ALTER TABLE tblPostCodes DROP COLUMN [column5];
ALTER TABLE tblPostCodes DROP COLUMN [column6];
ALTER TABLE tblPostCodes DROP COLUMN [column7];
ALTER TABLE tblPostCodes DROP COLUMN [column10];
ALTER TABLE tblPostCodes DROP COLUMN [column11];
ALTER TABLE tblPostCodes DROP COLUMN [column12];
ALTER TABLE tblPostCodes DROP COLUMN [column13];
ALTER TABLE tblPostCodes DROP COLUMN [column14];
ALTER TABLE tblPostCodes DROP COLUMN [column15];
-- Rename columns to meaningful names
EXECUTE sp_rename @objname = 'tblPostcodes.Column1', @newname = 'PostCode', @objtype = 'COLUMN';
EXECUTE sp_rename @objname = 'tblPostcodes.Column8', @newname = 'Latitude', @objtype = 'COLUMN';
EXECUTE sp_rename @objname = 'tblPostcodes.Column9', @newname = 'Longitude', @objtype = 'COLUMN';
EXECUTE sp_rename @objname = 'tblPostcodes.Column16', @newname = 'Outcode', @objtype = 'COLUMN';
EXECUTE sp_rename @objname = 'tblPostcodes.Column17', @newname = 'Incode', @objtype = 'COLUMN';
Next I need to add Longitude, Latitude and geoLocation columns to the Customers table as well.
ALTER TABLE tblCustomers ADD Latitude FLOAT;
ALTER TABLE tblCustomers ADD Longitude FLOAT;
ALTER TABLE tblCustomers ADD geoLocation GEOGRAPHY;
Using a join between customer table and postcode table via postcode I can now populate Longitude and Latitude columns in the customer table as follows.
UPDATE C SET C.Latitude = PC.Latitude, C.Longitude = PC.Longitude FROM tblCustomers C INNER JOIN tblPostCodes PC ON PC.PostCode = C.PostCode;
I can now populate the geoLocation fields in the customers table and the reports table using the spatial data functions available in SQL Server, using their Lat/Long.
UPDATE tblCustomers SET geoLocation = geography::Point(Latitude, Longitude , 4326) WHERE Longitude IS NOT NULL AND Latitude IS NOT NULL;
UPDATE tblResort SET geoLocation = geography::Point(Latitude, Longitude , 4326);
An example of the data in the geoLocation column for the resort row in my table of Scarborough
0xE6100000010CEBFF1CE6CB234B40A75CE15D2EE2D9BF
Now we have geoLocations populated in the customer table and resorts table we can start to work out distances between the customers address when they made a booking and the resort they have booked into. I will create a new field in the bookings table to store this value against each booking and call it DistanceFromResort.
ALTER TABLE tblBookings ADD DistanceFromResort INT;
I have created it as an INT as partial miles are not needed here. Now for the bookings we have I can work out the distance and populate this field as follows
UPDATE B SET B.DistanceFromResort = (R.geoLocation.STDistance(C.geoLocation) / 1000) / 8 * 5
FROM tblBookings B
INNER JOIN tblResort R ON R.ID = B.ResortID
INNER JOIN tblCustomers C ON C.ID = B.CustomerID
WHERE C.geoLocation IS NOT NULL;
The last thing I need to do now is to modify my view v_BookingData_BI and add this new field DistanceFromResort, and also the Latitude and Longitude columns of the customer to use on mapping.
ALTER VIEW [dbo].[v_BookingData_BI] AS
SELECT C.PostCode,
C.Town,
YEAR(B.BookingDate) AS BookingDate_Year,
MONTH(B.BookingDate) AS BookingDate_Month,
YEAR(B.DepartDate) AS DepartDate_Year,
MONTH(B.DepartDate) AS DepartDate_Month,
YEAR(B.ReturnDate) AS ReturnDate_Year,
MONTH(B.ReturnDate) AS ReturnDate_Month,
B.LeadTime,B.LeadTime/ 7 AS LeadTimeWeeks,
B.PassengerCount,
R.Resort,
B.BookingAmount AS Revenue,
B.ID,
B.DistanceFromResort,
C.Latitude,
C.Longitude
FROM tblCustomers C INNER JOIN tblBookings B ON C.ID = B.CustomerID INNER JOIN
tblResort R ON R.ID = B.ResortID;

Comments