Joshua Arnold

The latest direction in our Social Networking Platform has focused my attention on the latest fad in web technology: location-based services. I’ve stumbled upon a couple of functions and stored procedures for SQL Server 2005 that can be extremely useful in accomplishing this that I’d like to share.

1. Find items within n miles of the current location

• Solution Overview:

Given some GeoCoordinate c,
calculate some GeoCoordinate min and some GeoCoordinate max
such that min and max represent points along a rectangle with a center coordinate of c.

• SQL Server Implementation:

1. Calculate the distance of some item and the current location

• Solution Overview:

Given some GeoCoordinate c1 and some GeoCoordinate c2,
calculate the distance d, in miles, between c1 and c2.

• SQL Server Implementation:

3. Example Use

Consider the following sample population in some table GeoItem:

1    Yaupon Creek    30.363530    -97.968620
2    Windy Point    30.415200    -97.900840
3    Winns Heliport    30.401310    -97.960290
4    West Ridge Middle School    30.303820    -97.891400
5    White Rock Cemetery    30.309930    -97.962510
6    Wild Basin Wilderness Park    30.309930    -97.821670
7    Renaissance Austin Hotel    30.393300    -97.749600
8    Zachary T and Sallie Lee Scott Senior House    30.302710    -97.779450
9    West Bull Creek    30.359370    -97.787790
10    West Bull Creek Preserve    30.381310    -97.802510

The following query can be used to retrieve nearby items:

DECLARE @CurrentLatitude NUMERIC(18, 6), @CurrentLongitude NUMERIC(18, 6),
@MinLat NUMERIC(18, 6), @MinLon NUMERIC(18, 6), @MaxLat NUMERIC(18, 6),
@MaxLon NUMERIC(18, 6), @Distance NUMERIC(18, 6);
SET @CurrentLatitude = 30.372110
SET @CurrentLongitude = -97.878540
SET @Distance = 15.0; -- 15 miles

EXEC dbo.CalculateGeoExtremas@MinLat = @MinLat OUTPUT, @MinLon = @MinLon OUTPUT,
@MaxLat = @MaxLat OUTPUT, @MaxLon = @MaxLon OUTPUT, @Latitude = @CurrentLatitude,
@Longitude = @CurrentLongitude, @Distance = @Distance;

SELECT @MinLat, @MinLon, @MaxLat, @MaxLon

SELECT g.geoItemId, g.itemName, dbo.CalculateDistance(@CurrentLatitude, @CurrentLongitude,
g.latitude, g.longitude) AS distance
FROM   GeoItem AS g
WHERE  (g.latitude BETWEEN @MinLat AND @MaxLat) AND g.longitude BETWEEN @MinLon AND @MaxLon
ORDER BY distance DESC

which yields:

14    William Braxton Barr House    13.837094
15    Winn Elementary School    13.276661
22    Wheless Lane Church of Christ    12.589629
13    Woodcliff Baptist Church    12.517584
29    Springhill Suites By Marriott Austi)n North    12.317296
21    Westview Middle School    11.683721
27    Esd Austin-North Central    11.041094
20    Young Chapel African Methodist Episcopal Chur)ch    10.991294
19    Hampton Inn Austin North    10.880793
18    Embassy Suites Hotel Austin-North    10.865407
17    La Quinta Inn Austin Highland Mall    10.827897
16    North Austin Plaza Hotel and Suites    10.742710
36    Best Western Atrium North    10.642601
25    Habitat Suites    10.460957
46    Seton Medical Center H-4 Heliport    9.172898
42    The Specialty Hospital of Austin    9.072573
45    Seiders Spring    9.067145
30    Fairfield Inn And Suites By Marriott Austin Northwest    9.013800
41    The Brown School    8.732896
32    Casa Lago Resort    8.709709
24    Northcross Suites Austin Nw    8.612063
26    Esa Austin-Arboretum    8.548290
37    Towneplace Suites By Marriott Austin Northwest)    8.450223
40    Texas Military Forces Museum    8.250134
35    Courtyard by Marriott Austin Arboretum    8.200893
12    Westminster Presbyterian Church    8.161520
7    Renaissance Austin Hotel    7.818169
43    Spicewood Spring    7.798504
8    Zachary T and Sallie Lee Scott Senior House    7.604458
44    Steck Valley Park    7.229184
11    West Lake Hills    6.857748
23    Red Bud    6.610425
5    White Rock Cemetery    6.593043
9    West Bull Creek    5.477373
6    Wild Basin Wilderness Park    5.469529
1    Yaupon Creek    5.398852
3    Winns Heliport    5.269975
4    West Ridge Middle School    4.776967
33    Courtyard by Marriott Downtown Conven)tion Center    4.752998
34    Courtyard by Marriott Austin Airport    4.752998
10    West Bull Creek Preserve    4.573183
39    KUT-FM    4.051770
28    Vintage Villas    3.828173
38    Ossiport Heliport    3.392164
2    Windy Point    3.258134

---

It’s fairly straight forward and very easy to do. Check back for updates as I continue working on this project. I’ll be posting code examples of WCF services and AJAX implementations for dealing with this data and plotting it using the Google Maps API.

If you’d like to play with the data and the code mentioned in this post, you can Download the Database Script Here.

This is the third post in a series of entries that I am using to describe the architecture used in my most recent social networking project. While some of the architecture is omitted from these discussions, I would like to share some of the tricks that my team used to decrease development time and meet the rough and ambitious deadlines that were placed upon us. This entry will continue the discussion on web controls with an emphasis on distinguishing between server and user controls.

During the domain modeling phase of the application, we noticed that Comments were utilized by several different areas of the application:

The first piece of the application to utilize the control was the User profile. As mentioned in the previous post, we constructed useful server controls to facilitate a consistent layout throughout the application. Using one of these controls, the LayoutModule, we constructed what we referred to as a CommentContainer to display comments.

As we expanded the functionality of the control, we realized we needed/wanted two things:

1. AJAX comment posting, deleting
2. Pagination

In order to allow for this functionality, we needed to abstract out the functionality that was required using a variation of the Bridge pattern:

Note: The CommentProvider is an extension of a generic EntityProvider that is used to define the general functionality shared by all of the controls that utilize this pattern.

After the CommentContainer and CommentProvider were finalized, adding comment functionality for additional pages was a piece of cake and the code is very easy to maintain.

This is the second post in a series of entries that I am using to describe the architecture used in my most recent social networking project. While some of the architecture is omitted from these discussions, I would like to share some of the tricks that my team used to decrease development time and meet the rough and ambitious deadlines that were placed upon us. This entry will continue the discussion on role-based security but it will focus on creating role-based web controls.

First, I will discuss the design of the commonly used controls. The layout of the application consistently made use of a Cascading Style Sheet (CSS) class named “module”. This was essentially a tabbed container with varying content that was used consistently throughout our mockups. The other variation of this style was a similar construct without the tabs. This spawned two controls that became the basis of the site:

• LayoutModule
• TablessModule

These controls use a template container approach to ensure that the proper classes are used but allow for complete customization of the inner content. The following is an example using the TablessModule:

<rr:TablessModule ID="contactModule" runat="server">
	<HeaderTemplate>
		<h2>
			Company Information</h2>
		<hr />
	</HeaderTemplate>
	<ContentTemplate>
		<p>Content here</p>
	</ContentTemplate>
</rr:TablessModule>

After creating the skeleton structure of the majority of our pages, we began to observe a pattern: we were showing/hiding controls based on roles that the principal user belonged to. This then spawned a series of controls which utilized the notion of “AllowedRoles” and “DeniedRoles” which then controlled their visibility.

Consider the following helper method:

/// <summary>
/// Parses the specifed <see cref="System.String"/> values into a comma separated
/// list of values.
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public static string GetCommaSeparatedString(params string[] values)
{
	if (values == null || values.Length == 0)
	{
		return String.Empty;
	}

	StringBuilder retVal = new StringBuilder();
	int last = values.Length - 1;
	for (int i = 0; i < values.Length; ++i)
	{
		retVal.Append(values[i]);
		if (i != last)
		{
			retVal.Append(",");
		}
	}
	return retVal.ToString();
}

Given a TablessModule “userInfoModule”, consider the following example:

userInfoModule.AllowedRoles = SiteUtil.GetCommaSeparatedString(SiteRoles.User);
userInfoModule.DeniedRoles= SiteUtil.GetCommaSeparatedString(SiteRoles.Friend);

This is especially helpful for profile links that are useful for other users viewing your profile. We don’t want to display links like “Send a Message” when you’re looking at yourself. The example above grants Users in the Role ‘User’ to view the module and explicitly denies access to Users in the Role ‘Self’. A simple translation would be:

Given some TablessModule containing information for some User x, some User y may view that TablessModule if that User y is in the Role ‘User’ and that User y is not that User x.

Once again, this was another step towards readability and simplicity for business-rule implementation. Stating the allowed and denied roles in code is easily understandable, in my opinion. This concludes the role-based web control section of this discussion.

Next up: Reusable user controls

This is the first post in a series of entries that I will use to describe the architecture used in my most recent social networking project. I will discuss the thought processes surrounding each major design decision and explain how the design of the application has helped my team reach the very rough and ambitious deadlines that were placed upon us. This entry will focus on leveraging role-based security and how business rules can easily be written using this approach.

After the information modeling phase of the application was complete, I felt it necessary to focus on the rules that will govern the application’s privacy settings and overall user experience. Given the nature of the application, privacy settings were going to be a large part of every operation that we did so it was important that these rules were clearly defined in a central location. While establishing these rules, we realized that the majority of the rules could be define via roles. Let’s take a look at one rule and observe the evolution as we moved towards a role-based approach.

First iteration:

Some User x may create a Comment for some User y’s profile if at least one of the following is true:

1. y is equal to x
2. x is a friend of y

Role-based version:

Some User x may create a Comment for some User y’s profile if at least one of the following is true:

1. y is in the role ‘Self’
2. x is in the role ‘Friend’

This isn’t a huge evolution by any means but it introduces artificial roles that the application must be made aware of. It was time to design a solution that could utilize these artificial roles. The first step was to clearly establish the URL patterns used throughout the application. These URLs represent the very base structure - that is, the URLs that are used underneath any URL rewriting. For the purpose of this article, let’s take a look at a user’s profile page URL:

~/Users/Profile.aspx?UserId=x

For the majority of the projects that I’ve worked on in the past, the validation of these rules would occur by making a call to the business layer in the code behind file of the page. This may work for a variety of cases, but I was beginning to find the restrictions of validating these rules at such a late part in the request lifecycle (e.g. location restriction using roles, role aware controls). To overcome this restriction, we introduced the a series of role-based management HttpModules. Knowing the URL structure, we could rest assured that the “UserId” query string parameter will be present for any User-based viewing action (this was placed in a configuration manager to prevent any potential typos). If the UserId parameter is present, then we can execute a custom stored procedure to retrieve a value that indicates the artificial roles that user belongs to and add the principal user to them.

The code to handle these rules was then pulled into a series of ‘RuleValidator’ classes that were code-based representations of the business rules. The aforementioned rule could be represented in code with the following statement:

/// <summary>
/// Returns a flag indicating the result of rule R102:
///
/// Some User x may create a Comment on some User y's profile if at least one
/// of the following are true:
///
/// <list type="bullet">
/// <item>y is equal to x</item>
/// <item>x is in the role 'Friend'</item>
/// </list>
/// </summary>
/// <param name="pCurrentProfile">The <see cref="UserDetail"/> currently being viewed.</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException"><paramref name="pCurrentProfile"/> was null.</exception>
public static bool IsAuthorizedToCreateComment(UserDetail pCurrentProfile)
{
    if (pCurrentProfile == null)
    {
        throw new ArgumentNullException("pCurrentProfile");
    }
    IPrincipal _user = HttpContext.Current.User;
    return (_user.IsInRole(SiteRoles.Self) || _user.IsInRole(SiteRoles.Friend));
}

After the initial pattern was created for the role-based manager modules (abstracted out into a base role manager class), it was a piece of cake to implement them for the rest of the application modules (e.g. groups, events, photos). That concludes the role-based injection section of the architecture.

Next up: Role-based web controls.