Finding Nearest Stores using SSRS Map Reports

April is turning out to be a busy month for me. Apart from taking the beta SQL Server 2012 certification exams and some reviews, I am also speaking at the Charlotte SSUG and at SQL Saturday #118 (Madison) and #130 (Jacksonville). I have also submitted at a couple of other SQL Saturdays, so if you do see me speaking at a SQL Saturday close to you, don’t forget to give a shout! As my topic for the first two sessions are on Spatial Reporting in SSRS, I was preparing for it and that is when I thought of posting one of my demos as a blog.

This post will teach you how to implement a report to find the nearest stores using the spatial features in SQL Server and SSRS. As some of you might know, I am living in Charlotte and Harris Teeter is one of the grocery stores that I frequent. For this demo, I would be finding some of the nearest Harris Teeter stores from a list of my hangouts. Follow the instructions below to replicate it:-

1) Create the table structure for storing the locations of Harris Teeter.

CREATE TABLE [dbo].[Harris Teeter](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Location] [varchar](255) NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [PostCode] [varchar](20) NULL,
    [GeoL] [geography] NULL
) ON [PRIMARY]
Also, create the table structure for storing a list of the hangouts.
CREATE TABLE [dbo].[MyHangouts](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Location] [varchar](255) NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [PostCode] [varchar](20) NULL,
    [GeoL] [geography] NULL
) ON [PRIMARY]

2) Load some sample data for Harris Teeter stores in Charlotte.

INSERT INTO [dbo].[Harris Teeter] ( [Location], [PostCode], [Latitude], [Longitude] )
VALUES ('Quail Corners - 204','NC 28210-5803',35.116603,-80.858247),
('Old Towne Mall - 294','NC 28226-7159',35.117117,-80.824515),
('Morrocroft Village - 160','NC 28211-3570' ,35.152698,-80.825796),
('Park Selwyn Terrace - 35', 'NC 28209',35.1616695,-80.8492303),
('Colony Place - 4','NC 28226',35.106549,-80.806327),
('Park Road - 218','NC 28209-2229',35.1767066,-80.8510191),
('Cotswold Mall - 208','NC 28211-2802',35.177524,-80.801119),
('Arboretum - 30','NC 28226',35.096321,-80.78463),
('Myers Park - 12','NC 28207',35.1901493,-80.8231644),
('Kenilworth Commons - 61','NC 28203',35.2026843,-80.8455712),
('Ballantyne Commons - 11','NC 28277',35.05313,-80.848995),
('Stonecrest Shopping Center - 66','NC 28277',35.059911,-80.816675),
('Providence Commons - 45','NC 28277',35.066452,-80.7717459),
('Sardis Crossing - 171','NC 28270',35.138476,-80.740138),
('Central Avenue Location - 201','NC 28205-5108',35.219757,-80.809982),
('The Shops at Blakeney - 27','NC 28277',35.036336,-80.806711),
('Uptown Charlotte - 205','NC 28202-1603',35.2330664,-80.846148),
('Rea Village Shopping Center - 40','NC 28277',35.052441,-80.770867),
('The Shoppes at Ardrey Kell - 317','NC 28277',35.02431,-80.847881),
('Plantation Market - 147','NC 28105-6725',35.082745,-80.732972),
('Steele Croft - 88','NC 28278',35.103305,-80.990847),
('Matthews Township - 157','NC 28105',35.125179,-80.710001),
('Mintworth Commons - 174','NC 28227',35.172913,-80.709081),
('Weddington Corners Shopping Center - 343','NC 28104',35.023379,-80.760665)

For getting the data, I used the Harris Teeter website to get the address of the stores in Charlotte and then used this site to geocode the address to latitude and longitude. Using the same method, I also populated the MyHangouts table with the data below

INSERT INTO [dbo].[MyHangouts] ( [Location], [PostCode], [Latitude], [Longitude] )
VALUES ('Home','NC 28210',35.1537875,-80.8502022),
('Office','NC 28211',35.1493742,-80.8272008),
('PetSmart','NC 28217',35.1385661,-80.8764557),
('The EpiCentre','NC 28202-2538',35.225324,-80.842187),
('Library','NC 28211',35.1513557,-80.8225257)

3) Once that is done, we will have to convert the Latitude and Longitude to spatial data of type geography. Execute the code below for the same:-

UPDATE [Harris Teeter]
SET [GeoL] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
UPDATE [MyHangouts]
SET [GeoL] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

4) Create a new report and a datasource pointing to the database where the tables are stored. Create a dataset query named DST_Hangouts for selecting the data from the [MyHangouts] table.

5) Create a new report parameter which will source data from DST_Hangouts. The value will be the spatial field - GeoL while the label will be the Location.

6) Create another report parameter – RP_Cnt which will be of integer data type. This will be used for specifying the number of closest stores you want to see.

7) Make another dataset query – DST_HT which will filter the list of hangouts based on the parameter selected. This dataset query will be used in the map to show the selected hangout.

8) Now, make the pivotal dataset query of this post – DST_TopHT which will be used in the map to calculate the closest stores from the selected hangout

SELECT        TOP (@RP_Cnt) Location, GeoL.STDistance(@RP_MyLoc) AS distance, GeoL, Latitude, Longitude
FROM            [Harris Teeter]
ORDER BY distance

9) Optionally, we can also include a spatial query – DST_Circle which will draw a circle of 1 km diameter around the selected hangout so that we get an idea of the scale.

SELECT        ID, Location, Latitude, Longitude, PostCode, GeoL.STBuffer(1000) AS GeoLocation
FROM            MyHangouts AS H
I have filtered this dataset by the selected hangout in the Filters tab of the daataset properties.

10) Now drag and drop a map from the toolbox and then add two point layers based on the datasets - DST_Top HT and DST_HT. Also add a Bing maps layer and a polygon layer for the dataset DST_Circle.

You can see that I have used the marker type as PushPin for the DST_HT point layer and circles for the DST_TopHT point layer to differentiate both of them. It would be good to add a table also which will show the top stores and the distance.

11) Now preview the report and you should be able to see the top N stores nearest to your selected hangout.

You can change the selections or the top count value and see that your report changes accordingly. You will also notice that the Bing Maps layer is data aware and centres / zooms dynamically based on the data.

You can do much more like visualizing the colour of your points based on the distance or any other measure in your warehouse, which will be helpful in making a decision. For eg, there might be a store which is not the closest but is running a sale as shown in the image below.