Case Study: Joe s Burger and Malt Shop

Case Study: Joe's Burger and Malt Shop

SCENARIO

ESSENCE OF THE CASE

Here are the essential elements in this case:

• Gather location information.

• Build a table for the location information.

• Write a function to calculate the distance.

• Write a query, or a stored procedure, to find the appropriate locations.

Joe, the owner of Joe's Burger and Malt Shop, has a full SQL Server database system that he uses to track store performance, payroll, accounting, and human resources. He's looking for a way to figure out where to build the next store, so that it's not too close to the other stores. He's already worked to find a list of all the current store locations, and a list of about 50 locations in the city to put his new store. How can Joe figure out where to put his new store?

ANALYSIS

The first thing to do is put the stores all on a big map. Then lay a grid on top of the map and assign each store a coordinate pair. Then create a location table, with each store's ID number and its coordinates, like this:

 create table Location ( id int, xcoord int, ycorrd int ) 

Put all the existing stores into this location table. Then create another table with the same layout, called ProspectiveLocations, and populate it with the data on the prospective locations. Then write a stored procedure that uses the Distance function defined earlier to find the prospective store location that is farthest away from the other stores:

[View full width]

 
 [View full width] 
 CREATE PROCEDURE FindNewStoreLocations AS BEGIN      Populate table of prospective-to-existing distances SELECT PL.ID AS PLID, L.ID AS LID, master.dbo.distance(PL.Xcoord, PL.Ycoord, L.XCoord, L.  Ycoord) AS Distance INTO #Distances FROM ProspectiveLocation PL, Location L SELECT PLID, MIN(distance) FROM #Distance GROUP BY PLID ORDER BY 2 DESC END 

This gives Joe a list of all the prospective locations with the distance to the closest store, so Joe can look at the list and make his decision.

If you take a close look at the second SELECT statement in that stored procedure, you'll notice it uses a GROUP BY statement to find the closest distance. If you just did something like this:

 SELECT PLID, Distance from #Distance order by 2 desc 

This wouldn't properly filter the list. For example, if there was a store in the middle of town, a store in the northeast corner of town, and a prospective store in the southwest corner of town, you'd end up with the prospect in the southwest corner of town rated fairly highly, even though there are stores fairly close to it, like the one in the middle of town.