top of page

Response to Case Study - Request 2

Nov 1, 2022
3 min read

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;




 
 
 

Recent Posts

See All
Welcome to DK Travel

To demonstrate some of my skills, I have made up a ficticious travel agency called DK Travel and they are going to be asking me for...

 
 
 
Some Initial Setup

Before I start answering requests from DK Travel, I need to set up an initial customer database and due to GDPR cannot use real data. I...

 
 
 
Random Booking Data

As mentioned in the previous post, I can generate random data using T-SQL or Python. I have decided to do the work in Python and...

 
 
 

Comments


bottom of page