# Friday, August 9, 2013

Spatial data types were introduced in SQL Server 2008. These data types allow you to store location information directly in your data rows and quickly query that information to create shapes and to determine locations, distances, and points within a given shape.

SQL Server defines two types of spatial data - Geography and Geometry. Both data types track locations as x, y coordinates. The difference is that the Geography data type takes into account the curve of the Earth, while the Geometry data type does not. Because of this, Geography data is often best suited for mapping locations by Latitude and Longitude and computing distances between locations that are far apart. With Geometry data, you can define your own coordinate (such as location in a warehouse) and you should focus on locations that are close enough that the Earths' curve is inconsequential (for example, the distance between storage bins in a warehouse or stations on a shop floor).

Both data types store x, y coordinates in a binary format and each can be used to query locations. A couple advantages of these data types are

  1. We can define database columns with these data types
  2. Built-in functions exist to do things like draw shapes and compute distances. We can call these functions within a SQL SELECT statement.
  3. These columns can be indexed, making calculations very fast.

Imagine a table with the following structure.

CREATE TABLE [dbo].[CustomerAddresses](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [varchar](250) NOT NULL,
    [StreetAddress] [varchar](250) NOT NULL,
    [City] [varchar](250) NOT NULL,
    [State] [varchar](20) NOT NULL,
    [PostalCode] [char](25) NOT NULL,
    [Geo] [geography] NULL,
    [Latitude] [decimal](18, 14) NULL,
    [Longitude] [decimal](18, 14) NULL
    [id] ASC

The Geo column of this table is a Geography data type and is designed to store binary information about the location coordinates.

We can index this column with the following command:

CREATE SPATIAL INDEX SIndx_CustomerAddress_geo_col1 
   ON dbo.CustomerAddress(Geo); 

If we have a table populated with latitude and longitude data (Fig 1), we can easily convert this data to the Geography data type using the Geography::Point function static extension method.


Fig. 1 - Latitude and Longitude data

We convert x and y coordinates to the Geography data type using the Geography::Point function static extension method. The Geography::Point function takes 3 parameters: longitude, latitude, and a code representing the SRID, which is a well-known coordinate system. The most popular system is represented by 4326 and defines 0 latitude at the equator and 0 longitude at a line passing through Greenwich, England. It’s worth pointing out that the Geography::Point parameters list Longitude before Latitude, even though most coordinate systems list Latitude first. This can be confusing, but it’s something you will want to be aware of.
For this table, the SQL statement is

UPDATE GeoDemo.dbo.CustomerAddress
    SET Geo = Geography::Point(Latitude,Longitude, 4326)
    WHERE Latitude IS NOT NULL AND Longitude IS NOT NULL 

The SQL above populates a column of type Geography with point data representing latitude and longitude, as shown in Fig. 2.


One very useful extension method of a Geography Data Type is Distance, which accepts a parameter of another Geography point and returns the distance in meters between the 2 points.
The following code calculates the distance (in miles) between 2 points in our table.

    @GeoLocation2 GEOGRAPHY,
    @MetersPerMile DECIMAL,
    @Distance DECIMAL (18,14)
SET @MetersPerMile = 1609.344
SET @GeoLocation1 = (SELECT Geo FROM dbo.CustomerAddress WHERE Id = 1)
SET @GeoLocation2 = (SELECT Geo FROM dbo.CustomerAddress WHERE Id = 2)
SET @Distance =     
    (SELECT @GeoLocation1.STDistance(@GeoLocation2)/@MetersPerMile)
PRINT @Distance 
PRINT 'miles between Point 1 and Point 2' 

Finally, we can use the STDistance extension method from within a SQL SELECT statement to calculate all points within a certain radius of a given point. Below is an example listing all addresses within 100 miles of Address #1

DECLARE @CustomerGeoLocation GEOGRAPHY,
        @RadiusInMiles INT,
        @MetersPerMile DECIMAL,
        @RadiusInMeters DECIMAL (22,14),
        @CenterId INT 

SET @CenterId = 1
SET @CustomerGeoLocation = (SELECT Geo FROM dbo.CustomerAddress WHERE Id = @CenterId)
SET @RadiusInMiles = 100
SET @MetersPerMile = 1609.344
SET @RadiusInMeters = @RadiusInMiles * @MetersPerMile
@CustomerGeoLocation.STDistance(c.Geo)/@MetersPerMile AS DistanceInMiles,
    FROM dbo.CustomerAddress c
    WHERE c.Geo.STDistance(@CustomerGeoLocation) <= @RadiusInMeters
    AND c.id <> @CenterId
    ORDER BY DistanceInMiles 

In this article, we introduced the SQL Server Spatial data types and showed how to use the Geography extension methods to calculate distance and find nearby locations.

Friday, August 9, 2013 10:33:00 AM (GMT Daylight Time, UTC+01:00)
# Wednesday, August 7, 2013

A few months ago, I discovered that I was doing way too much public whining on social media outlets. I used Facebook and Twitter as an outlet for my frustrations- real, imagined, and overemphasized. After I made this unflattering discovery, I decided two tings:
1. This was not the image I wanted to project to the world via my online presence.
2. This was not the attitude I wanted to permeate my life.

I've always believed that a good way to start changing one's attitude is by changing one's behavior. So I made a commitment to focus on the good things in my life - to wake up each morning and think of something in my life for which I am grateful.

I was inspired to do so by a couple talks I heard recently - one by Layla Driscoll at KalamazooX, titled "Find Your Happy; and one by Elizabeth Naramore at Stir Trek, titled "Stop Drinking Spoiled Milk". Layla and Elizabeth each keep a daily log of things for which they are grateful. They each shared some entries from their own logs and pointed out how this helped keep them focused on the important things in life. I decided to make a similar commitment to focusing on gratitude and to make my gratitudes public in the hope that others would be inspired as I was. I started on Facebook, posting a new gratitude each morning and later began cross-posting these on Twitter as well.

I find myself grateful for a variety of things. Sometimes, I think of something good that happened yesterday; sometimes about the anticipation of something coming today; and sometimes it's about a larger something that affects my life for years.

The results has been extremely positive. Although I still occasionally get down and I sometimes whine about the bad parts of my life, I find that my daily focus on gratitude has helped to counteract any lasting effects of depressing circumstances.

In addition, many people have responded to these posts - online and in person - and told me they enjoyed them. I've even seen others doing the same. I don't know if they were inspired by me or by the people who inspired me but I don't care. When I'm reading the thoughts of friends on social media, I'd much rather hear about how they are focused on the positives of their life than be overwhelmed with negativity.

And for that I am grateful.

Below is a partial list of gratitudes I have posted to date.:

Today I am grateful for the Mud Hens game last night with Kevin and Tim.

Today I am grateful for a tasty pulled pork meal at Slow's Barbecue on a Sunday afternoon.

Today I am grateful for
1. Having my family over for a cookout yesterday
2. Seeing my mother for the first time since my father's funeral
3. An excellent Chris Hillman & Herb Pedersen concert last night.

1. An unexpected Greektown dinner last night with my cousin, sister, brother, and sister-in-law.
2. Today's barbecue that will bring most of my family to my house.

Today I am grateful that I found a couple $20 bills in a jacket I haven't worn for months.

Today I am grateful that my mother and cousin are coming to town for the first time in years.

Today I am grateful for a delicious sushi dinner last night with my son and my final visit to East Lansing while he is a student there.

Today I am grateful for my cameras, which have allowed me to preserve moments of my life better than my own memory could.

Today I am grateful to read the tweets of @nprscottsimon as he chronicles his last days with his dying mother.

Today I am grateful I had the chance to catch up with Mike and Toni yesterday at lunch. It has been too long.

Today I am grateful for a long walk from one end of the runway to another yesterday afternoon with a sunny sky above me and a light breeze at my back.

Today I'm grateful that I made it to LearnSomething at Fanzoo Technology last night for the first time in a long time and that I made significant progress on a demo for my next presentation.

Today I am grateful for a great time last night with family and friends at the screenings for the Detroit 48 Hour Film Project.

Today I am grateful I didn't wake up with the hangover I deserve.

Today I am grateful to awaken to the sound of gently-falling rain on my bedroom window.

Today I am grateful for the time spent with my sister Denise, who passed away 4 years ago today. She has inspired me in so many ways.

Today I am grateful that Microsoft named me an MVP for the 4th consecutive year.

Today I am grateful that, for nearly 2 decades, my boys and I have maintained a tradition of going out to lunch together after church each Sunday.

Today I am grateful for this:
IMG_4721-S[1] IMG_5207-S[1]

Today I am grateful that my brother is staying with me. It's a rare visit, since he lives near Sydney, Australia.

Today I am grateful for the opportunity to deliver a eulogy at my father's funeral.

Today I am grateful that my mother, who has been through a very rough time these past 5 years, is now as strong as I have ever known her.

Today I am grateful that, years ago, I stumbled into a career that encourages me to learn every day.

Wednesday, August 7, 2013 2:46:38 AM (GMT Daylight Time, UTC+01:00)
# Tuesday, August 6, 2013
# Monday, July 29, 2013
Monday, July 29, 2013 4:22:00 PM (GMT Daylight Time, UTC+01:00)
# Saturday, July 27, 2013

Are you a TFS user or interested in using this product? In the past, it was necessary to install TFS onto a central server or virtual machine before using it. No more. Now, Team Foundation Services allows you to access a TFS server hosted by Microsoft.

The service is free for small teams (<5 people) while it is in Preview, but I have not seen any future pricing announcements.

Get started by navigating to visualstudio.com and signing up for an account. Note the URL, up to ".visualstudio.com". This is the server name. You will need this to integrate with Visual Studio solutions. In my case, it is "https://giard.visualstudio.com".

At the home page, click the [New Team Project] button (Fig. 1)


The "Create Team Project" dialog displays (Fig. 2). Enter a project name and description;


Next, select a Process template. The choices are:

  • Microsoft Visual Studio Scrum 3.0 - Preview
  • MSF for Agile Software Development 7.0 - Preview
  • MSF for CMMI Process Improvement 7.0 - Preview

Finally, select a Version Control repository. Currently, TFS and Git are supported. Click [Create Project] and you will be ready to start using TFS within a few seconds.

The navigation is simple and intuitive.

You can add and remove team members by clicking the Manage All Members link.

You can enter a new Task, Bug, Issue, Feature, or Test Case using dialogues similar to those found in the Visual Studio Team Explorer.

The source control repository can be seen by clicking the "Code" link on the top menu. From here, you can download files but not upload them.

TFS source control is easiest to use when you integrate it with Visual Studio. Open a solution in Visual Studio and select File | Source Control | Add Solution to Source Control

You may be prompted to add a TFS server. If so, use the URL ending in ".visualstudio.com" that you noted from above. (Fig. 3)


Select the Team project to which this solution belongs (Fig. 4) and click the [Connect] button.cl


From here you check-in, check-out, branch, merge, and get latest in the same way that you would use a TFS server within your enterprise.

Microsoft is adding new features to this Team Foundation Service each week. You can follow the progress on Brian Harry's blog at http://blogs.msdn.com/b/bharry/archive/tags/tfservice/.

Team Foundation Service allows you to manage projects on your own or with organizations that don't have the hardware and/or time to set up their own TFS server. It can be a simple solution to integrating your project with Application Lifecycle Management tools.

Agile | ALM | TFS | Visual Studio
Saturday, July 27, 2013 7:39:05 PM (GMT Daylight Time, UTC+01:00)
# Wednesday, July 24, 2013

What I liked about "Continuous Integration in .NET" by Marcin Kawalerowicz and Craig Berntson is that it does not assume any prior knowledge of continuous integration (CI) by the reader. It begins by discuss CI - its theory, goals, and tools - and it moves on from there.

It's often difficult for an organization to achieve CI all at once, so this book walks the reader through the various pieces of CI - source control, automated build, unit testing, continuous feedback, analysis, deployment. They go into detail on each concept, showing step-by-step how to get there with a variety of tools.

Kawalerowicz and Berntson take care not to focus on a single tool. The implementation of each concept is shown using Cruise Control .NET, Team City, and Team Foundation Server.

Continuous Integration in .NET is a very good book to get you up and running with automated build and deployment processes and moving into continuous integration, even if you have no experience with these concepts.

Agile | books
Wednesday, July 24, 2013 8:34:00 PM (GMT Daylight Time, UTC+01:00)
# Tuesday, July 23, 2013

Recently, I wrote my first Windows Phone 8 application. In order to share my application with the world, I needed to publish it in the Windows Phone app store and in order to publish it, I needed to register with the Windows Phone Dev Center. Most dev centers like this charge about $100 a year and I believe that is Microsoft’s usual fee.

But currently, they are running a special and charging only $19. This offer is good until August 27. If you are thinking of writing an app for Windows Phone 8, this is a good time to do it. You can register and pay your $19 at https://dev.windowsphone.com/en-us/join?logged_in=1.

Tuesday, July 23, 2013 8:10:00 PM (GMT Daylight Time, UTC+01:00)
# Monday, July 22, 2013
Monday, July 22, 2013 9:23:00 PM (GMT Daylight Time, UTC+01:00)

We can all agree that some code is better than others. But if you write code that compiles and meets all the user's requirements, is it possible to improve this code?

Robert C. Martin's answer is an emphatic "Yes". Although sloppy code can meet short-term requirements, it quickly becomes difficult to maintain. Clean Code, Martin argues, is easier to read, understand, and test; and safer to change. Our goal should be to write Clean Code.

Robert's C. Martin (sometimes affectionately referred to as "Uncle Bob") has compiled some guidance on writing clean code into a book with the self-describing title "Clean Code".

Martin did not write the entire book- he enlisted other software developers active in the Software Craftsmanship movement to contribute. Tim Ottinger, Michael Feathers, James Gremming, Jeff Langr, Kevin Wampler, and Brett Schuchert each contributed at least one chapter, outlining a specific idea of Clean Code.

Among the key concepts: Keep classes and methods small and narrowly focused, give meaningful names to variables; don't use comments as a replacement for difficult to read code; and avoid output parameters and an excessive number of parameters.

He follows up advice on craftsmanship with a set of case studies in which he describes the refactoring of existing code bases.

Although most of the examples are in Java and I am primarily a .NET developer, I found this book very useful and applicable to any language - particularly an object-oriented language, such as C#.

Code Complete was a good book for me to read when I did. I am in the process of refactoring some code that is very difficult to maintain. I knew that it is not clean, but found myself unable to articulate exactly why.

The book is not for beginners. You should have a solid understanding of your language and of OOP concepts before tackling it. But it provides excellent guidance on writing readable, maintainable and testable code.

Agile | books
Monday, July 22, 2013 9:13:00 AM (GMT Daylight Time, UTC+01:00)
# Tuesday, July 16, 2013

This morning, my first Windows Phone 8 app was approved and is now available in the Windows Phone store. You can download and install it on your phone from here or search for "MathFlashGame" in the store.

The game simulates arithmetic flash cards, displaying an addition, subtraction, or multiplication problem. The user supplies the answer and the game tells him if that answer is correct. You have the option of limiting the operators and the range of numbers displayed.

I began the app about 4 weeks ago as a learning exercise. Not only have I never written a mobile application, but I have very little experience with XAML. Everything was new to me, from the data binding to local storage to the mechanism for responding to a change in phone orientation.

I learned a lot on this project and I am inspired to write more applications for this platform. The experience was fun. If you are considering writing a Windows Phone app, I encourage you to take the plunge. There are a lot of helpful resources online and the cost for a developers license is currently very low (only $19).

And if you have a chance, please check out my app and let me know what you think.


Tuesday, July 16, 2013 11:49:23 AM (GMT Daylight Time, UTC+01:00)