Thursday, December 11, 2008
I've previously mentioned that the SQL Server 2008 Spatial data types are freely available for use in your .NET applications, regardless of whether you have SQL Server 2008 or not. This allows you to incorporate some powerful spatial capabilities right into your application.
(Look for "Microsoft SQL Server System CLR Types" on this page: http://www.microsoft.com/downloads/details.aspx?FamilyID=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en )
However, in most usage scenarios, there will come a time when you have an instance of a SQL Server spatial object in your .NET application, and need to commit it to your SQL Server 2008 database. How would you do this, without losing fidelity or resorting to serialization of the object to WKT first?
The solutions is to create a Parameter object of type System.Data.SqlDbType.Udt. Then set the UdtTypeName parameter to the SQL Server-recognized type name (i.e., for SqlGeometry, you would simply use Geometry).
The following code demonstrates executing an UPDATE statement that sets the value of a Spatial field to a newly constructed object.
using (SqlConnection conn = new SqlConnection("Server=.;Integrated Security=true;Initial Catalog=scratch"))
{
using (SqlCommand cmd = new SqlCommand("UPDATE fe_2007_us_zcta500 SET Boundary=@boundary WHERE id=@id", conn))
{
SqlParameter id = cmd.Parameters.Add("@id", System.Data.SqlDbType.Int);
SqlParameter boundary = cmd.Parameters.Add("@boundary", System.Data.SqlDbType.Udt);
boundary.UdtTypeName = "geometry";
SqlGeometry geom = SqlGeometry.Parse("POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))");
boundary.Value = geom;
id.Value = 123;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
Monday, December 08, 2008
As a matter of principal, not to mention terms of service, I don't often click on ads that appear on my own blog. But, when I see an ad on my site that interests me, I follow it (I believe Google knows who I am, so I don't get "credit" for that click).
Such was the case the other day when I saw a flashy plasma-lamp-like animated advertisement in my site's sidebar with the words "They're not just new forms of data... It's a new form of energy. SQL Server 2008".
I won't ask you to click on the ad from my site, but give the destination site a whirl to learn about the cool things that SQL Server 2008 can do (including something that I'm very interested in: spatial querying):
http://www.microsoft.com/servers/sql/2008/sqlserverenergy/en/us/default.aspx
Thursday, December 04, 2008
Need a reason to come to CodeMash 2009?
Check out this amazing line-up of sessions:
http://codemash.org/SessionList.aspx
Wow! Simply.... Wow!
The schedule is now live, too:
http://codemash.org/includes/CM-2009-Schedule.pdf
Tuesday, November 25, 2008
I have mentioned before how the Ring Orientation for the exterior ring of a Polygon is significant when instantiating a SqlGeography object. In this case, a Counter-Clockwise orientation is required so that as an observer walks along the path, the interior of the Polygon is always to their left.
But, what I have never really seen documented (or paid attention to, at least) is the fact that the interior rings, or holes, of a Polygon also have specific Ring Orientation requirements.
In keeping with the "Left-handed" rule, interior rings must be defined in a Clockwise manner - the opposite orientation of the shape's exterior ring. This is because holes within a Polygon are considered to be part of the exterior of the shape, so the observer walking in a Clockwise direction is still keeping the Polygon's interior to their left.
(I should note here that the Ring Orientation for SqlGeography is the exact opposite of ESRI's ShapeFile format, which is why Ring Orientation has been on my mind for the past few days).
Monday, November 24, 2008
A Ring is a list of points such that the starting point and ending point are the same (forming a closed shape). The order the you define the points that make up a Ring - known as Ring Orientation - is significant, for various data formats (including SQL Server's Geography type) imply special meaning for rings that are defined in a clockwise manner as opposed to a counter-clockwise manner.
Given a list of points with no additional context, it can be difficult to determine the Ring Orientation being used.
For example, suppose that you have a generic list of points that represent the boundary of a postal code, and that you wish to use these points in order to construct a Polygon instance using the SqlGeography type. SqlGeography happens to use "Left-handed" ordering, so that as an observer walks along the set of points in the order defined, the "inside" of the polygon is always to their left. This also implies that the exterior ring of a Polygon is defined in a counter-clockwise manner.
If you try to define a polygon with an area greater than a single hemisphere (this is a nice way to say "if you screw up and use the wrong orientation"), then the SqlGeography type will throw an exception. So, aside from using Try-Catch, what can you do?
While researching solutions to this problem, I stumbled upon a paper entitled "A Winding Number and Point-in-Polygon Algorithm" from the Colorado State University. It turns out that a simple algorithm with O(n) complexity can be used to determine if a point is within a Polygon, and a side effect also provides the Ring Orientation. The key to this algorithm is determining the trend of the ring at each crossing of an axis.
Since I was only interested in Ring Orientation (and not point enclosure detection), I didn't need to use this particular algorithm. Instead, I took inspiration from the winding concept, and created a simpler derivative algorithm:
- Iterate the point collection and determine the extreme "left" and "right" points
- Normalize the line segments connected to these points so that they each have the same "X" dimension length
- Compare the "Y" values of the normalized segments to establish the trend through that extreme point (i.e., is the "previous" segment above or below the "next" segment)
- In the spirit of the Winding algorithm, use opposite orientations for the left and right points so that the results coincide with one another
- A negative result (negative indicates Clockwise orientation, positive result indicates Counter-Clockwise orientation, and a result of zero would be undefined
I've actually written (and posted) several versions of this algorithm, each time discovering some edge case exception that would cause me to take down the post and rewrite the algorithm. I believe the code below works for all simple polygons on a Cartesian coordinate system (read: I have more testing to see if this will work with an ellipsoidal model, like SqlGeography).
Note: The following code is generic in nature, and as such, I've defined my own Point structure instead of using a SqlGeometry or SqlGeography, etc.
struct Point
{
public double X { get; set; }
public double Y { get; set; }
}
enum RingOrientation : int
{
Unknown = 0,
Clockwise = -1,
CounterClockwise = 1
};
RingOrientation Orientation(Point[] points)
{
// Inspired by http://www.engr.colostate.edu/~dga/dga/papers/point_in_polygon.pdf
// This algorithm is to simply determine the Ring Orientation, so to do so, find the
// extreme left and right points, and then check orientation
if (points.Length < 4)
{
throw new ArgumentException("A polygon requires at least 4 points.");
}
if (points[0].X != points[points.Length - 1].X || points[0].Y != points[points.Length - 1].Y)
{
throw new ArgumentException("The array of points is not a polygon. The first and last point must be identical.");
}
int rightmostIndex = 0;
int leftmostIndex = 0;
for (int i = 1; i < points.Length; i++)
{
if (points[i].X < points[leftmostIndex].X)
{
leftmostIndex = i;
}
if (points[i].X > points[rightmostIndex].X)
{
rightmostIndex = i;
}
}
Point p0; // Point before the extreme
Point p1; // The extreme point
Point p2; // Point after the extreme
double m; // Holds line slope
double lenP2x; // Length of the P1-P2 line segment's delta X
double newP0y; // The Y value of the P1-P0 line segment adjusted for X=lenP2x
RingOrientation left_orientation;
RingOrientation right_orientation;
// Determine the orientation at the Left Point
if (leftmostIndex == 0)
p0 = points[points.Length - 2];
else
p0 = points[leftmostIndex - 1];
p1 = points[leftmostIndex];
if (leftmostIndex == points.Length - 1)
p2 = points[1];
else
p2 = points[leftmostIndex + 1];
m = (p1.Y - p0.Y) / (p1.X - p0.X);
if (double.IsInfinity(m))
{
// This is a vertical line segment, so just calculate the dY to
// determine orientation
left_orientation = (RingOrientation)Math.Sign(p0.Y - p1.Y);
}
else
{
lenP2x = p2.X - p1.X;
newP0y = p1.Y + (m * lenP2x);
left_orientation = (RingOrientation)Math.Sign(newP0y - p2.Y);
}
// Determine the orientation at the Right Point
if (rightmostIndex == 0)
p0 = points[points.Length - 2];
else
p0 = points[rightmostIndex - 1];
p1 = points[rightmostIndex];
if (rightmostIndex == points.Length - 1)
p2 = points[1];
else
p2 = points[rightmostIndex + 1];
m = (p1.Y - p0.Y) / (p1.X - p0.X);
if (double.IsInfinity(m))
{
// This is a vertical line segment, so just calculate the dY to
// determine orientation
right_orientation = (RingOrientation)Math.Sign(p1.Y - p0.Y);
}
else
{
lenP2x = p2.X - p1.X;
newP0y = p1.Y + (m * lenP2x);
right_orientation = (RingOrientation)Math.Sign(p2.Y - newP0y);
}
if (left_orientation == RingOrientation.Unknown)
{
return right_orientation;
}
else
{
return left_orientation;
}
}
void Test()
{
// Simple triangle - left extreme point is vertically "in between" line segments
Point[] points = new Point[]
{
new Point(5,-1),
new Point(0,0),
new Point(5,1),
new Point(5,-1)
};
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
Array.Reverse(points);
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
// Case where both line segments are above the left extreme point
points = new Point[]
{
new Point(2,1),
new Point(0,0),
new Point(1,1),
new Point(2,1)
};
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
Array.Reverse(points);
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
// Case where both line segments are below the left extreme point
points = new Point[]
{
new Point(2,-1),
new Point(0,0),
new Point(1,-1),
new Point(2,-1)
};
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
Array.Reverse(points);
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
// Case where line segment is vertical (slope cannot be determined)
points = new Point[]
{
new Point(0,0),
new Point(0,1),
new Point(1,1),
new Point(1,0),
new Point(0,0)
};
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
Array.Reverse(points);
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
// Case where angle thru left extreme point is a right angle
points = new Point[]
{
new Point(0,0),
new Point(1,1),
new Point(1,-1),
new Point(0,0)
};
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
Array.Reverse(points);
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
// Real-world case from a SHP file
points = new Point[]
{
new Point(-156.92467299999998,20.738695999999997),
new Point(-156.924636,20.738822),
new Point(-156.924608,20.73894),
new Point(-156.92458,20.739082),
new Point(-156.92460599999998,20.739234),
new Point(-156.924551,20.739326),
new Point(-156.924507,20.739241999999997),
new Point(-156.924482,20.739082),
new Point(-156.924466,20.738854999999997),
new Point(-156.924387,20.738602999999998),
new Point(-156.924308,20.738325),
new Point(-156.924239,20.738063999999998),
new Point(-156.92424,20.737887999999998),
new Point(-156.924285,20.737811999999998),
new Point(-156.924475,20.73762),
new Point(-156.92458299999998,20.737603999999997),
new Point(-156.924754,20.737579),
new Point(-156.924851,20.737731),
new Point(-156.924956,20.738101),
new Point(-156.924909,20.738343999999998),
new Point(-156.924818,20.738487),
new Point(-156.92467299999998,20.738695999999997)
};
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.Clockwise);
Array.Reverse(points);
System.Diagnostics.Debug.Assert(Orientation(points) == RingOrientation.CounterClockwise);
}
Thursday, September 25, 2008
There are a few events taking place in October that I would like to promote:
Day of .NET in Ann Arbor
Saturday, October 18, 2008
The fourth Day of .NET in Ann Arbor event will take place on the campus of Washenaw Community College in Ann Arbor (or is it actually Ypsilanti? I can never tell). The original conference was a collaboration between GANG, AADND, and NWNUG. This year, we have officially added GLUG.net and West Michigan to the list of groups who are assisting in the organization and promotion. So, for those of you following along at home, this event is the product of FIVE regional user groups, and like its predecessors, should be a great day of learning and networking.
The session list has been posted, and there should be something on the schedule for everyone. Registration is free, and all that we ask is that if you do sign up, then please show up. There's nothing worse than wasting sponsorship dollars by ordering too much food or too many T-Shirts (though, this year, we're going to try to get Pizza back on the menu, so food waste should hopefully be minimized).
http://www.dayofdotnet.org/AnnArbor/Fall2008/Sessions.aspx
Wally McClure appearing at Northwest Ohio .NET User Group
Monday, October 20, 2008
"Hello. I'm Wally McClure. You might remember me from my many books published by Wrox Press with my picture on them. Or, the ASP.NET Podcast, where on the website, you'll find pictures of my books with my pictures on them. Or, the very popular T-Shirt with my picture on the back...."
I met Wally this summer at TechEd Developer in Orlando, where one of the first experiences was being crammed into the back seat of a car between him and Keith Elder. He seemed to take a liking to me, and let's just say that my phone now receives more random text messages than ever before. :-)
In all seriousness, Wally was supposed to have appeared at NWNUG in June, just after TechEd, but had to back out due to work commitments (Steve Smith ended up filling in for him). We're thrilled that he was still interested in coming to Toledo, and we were able to arrange an October 20th date (note that this is a Monday, not the regular Tuesday meeting night).
Watch the NWNUG website for further details about the meeting topic.
Tuesday, September 16, 2008
I use Windows 2008 Server as a workstation. I find that certain things actually work better (like Sleep Mode, for instance, which is weird because servers shouldn't need to sleep, but Vista would often not go to sleep and drain my battery the entire couple of hours that it continued to run in my computer bag, and Windows Server 2008 has never done that.... but I digress).
However, when the new Zune 3.0 software was released today, I found, like many others, that the software explicitly listed three operating systems that were supported, and Server 2008 was unfortunately not one of them. The install refused to continue.
While experimenting, I found a way to install the Zune 3.0 software manually. It appears to be fully functional on my machine, but I assume no responsibility and will provide no support if you decide to follow these instructions on your own machine.
First, I noticed that the setup bootstrap (zunesetuppkg-x86.exe) says where it's extracting its files to:
(This was weird to me, since the E: drive on my machine is an external USB hard drive...)
Next, I opened that directory in Explorer. There, I found a "packages" directory:
And inside of there, I found a MSI file called Zune-x86.msi:
Ok, learn from my trial-and-error here: uninstall any previous version of the Zune software, because this MSI will not do it for you, and will not install if a previous version exists. Other than that, I just double-clicked on the MSI, and a minute later, I had the 3.0 software on my machine!
Note that I am currently behind a ISA Server proxy/firewall that requires authentication with Active Directory. Therefore, in order to download the firmware update, I had to change my UseLmCompat to "0" per this blog post:
http://jasonfollas.com/blog/archive/2007/11/13/how-to-get-the-zune-2.2-firmware-when-your-corporate.aspx
Monday, September 15, 2008
Dear Microsoft,
We've been pals for a long time, right? You put out all of these great technologies, and I help others to use them within their own organization. We have a good thing going, you and me.
But, I have a problem. You see, whenever I press this one special key on my keyboard, mostly by accident due to its proximity to ESC and ~, you give me an excuse to stop working and go get coffee. This isn't very productive. I don't always want an excuse to get coffee.
Can you please add a Cancel button or something? Your modal dialog is not very friendly at times.
BFF,
-Jason
Saturday, August 23, 2008
Last week, I travelled to Philadelphia to work out of my company's office in Exton for a week. Before leaving, I took my youngest daughter for a hike in the park, which was a two-fold treat for her: she got to spend time with Daddy, and as an extra bonus, she got to ride in Daddy's truck. Being a two-seater, it is not often used unless I'm spending some 1-on-1 time with one of the kids.
After returning from out hike, I parked it on the street in front of the house. It looked something like this picture that I took in 2003 right before buying it:
Well, fast forward to very early Friday Morning. I was sleeping in my hotel room, when my cellphone began to ring. I think the alarm clock took quite a few swipes of my fist before I realized that it was not the loud noise that was bothering me. I stumbled out of bed and picked up the phone, only to hear my wife tell a tale of firetrucks and flames and the entire neighborhood observing some bonfire that was taking place on the street in front of my house.
I had actually had a bad dream just a bit earlier, and was relieved to find out that it was only a dream. I think part of me expected the same to happen in this case, but no such luck. My truck - the one that I had just paid off a few months ago - was ablaze.
Now, the truck looks a little more like this:
Notice if you will that there is no hood on the truck. It was a steel hood, and is nowhere to be found. So, it was either removed by the firefighters and they took it with them, or it simply melted away.
The worst part now is that we have to wait until Monday (2 more days as I write this) for the insurance company to tow the shell away to their evaluation center... once there, the lady told me, they would then make the determination as to whether it could be repaired or not. I just giggled to myself.
But, until then, there's a tarp-wrapped burned-out truck serving as a landmark for those trying to locate my house. It's the one with the nose sitting on the asphalt.
UPDATE: The truck was hauled away shortly ago. While speaking with the neighbors who came out to watch, I learned that another neighbor filmed it AND UPLOADED TO YOUTUBE! Thanks, Andy!
Monday, August 18, 2008
Have you ever gotten an idea stuck in your head? One that you start your day thinking about in the shower, and then try as you might, you just can't get rid of it?
That's what happened to me a few months ago. Specifically, I started thinking about Twitter, and the problems that it was experiencing. At that time, the "Fail Whale" was making very frequent appearances, indicating that Twitter was having problems keeping up with the demands being made on it. My Tweeps (social networking friends on Twitter), all with short attention spans like myself, began chattering about moving to a more reliable platform.
But, in my estimation, Twitter was still the best platform to remain on. Among other things, it was actually the most "mature" in its class - if you can call something a mere two years old as being mature.
So, Jason, what is Tourniquet?
After quite a few weeks of thinking about the various problems that I was aware of, I came up with a pretty simple solution. I needed to bounce some ideas off of a sounding board, so I fired off an email to some of my friends: Alan Stevens, Keith Elder, and Micheal Eaton.
The contents of that email still serve as a pretty good overview for my vision, which has been realized as "Tourniquet":
Think of a multi-faceted approach to fixing Twitter's issues that ultimately concentrates on reducing the number of calls that you make into Twitter itself, as well as provides transparent access to Twitter data during Twitter downtimes:
1. A personal Twitter proxy.
This is simply an API passthrough service that you host yourself. The thought process here is that Witty and other clients could be configured as to what server is accessed to execute an API call (i.e., by default, it's http://www.twitter.com/, but someone hosting a Twitter proxy would be able to specify something like http://thisismydomainyo.com/tourniquet/ ). Aside from the server, there's nothing different about the request or response. That is, the client might hit http://thisismydomainyo.com/tourniquet/statuses/friends_timeline.xml instead of
http://www.twitter.com/statuses/friends_timeline.xml.
This is not a public/shared service - it would be intended for just the user.
2. Obfuscation/encryption
Network Nazis suck, and so do people who brag about having smart phones and data plans. :-) There may be other reasons why someone would want the URL obfuscated and the response from Twitter encrypted when transfered between the Twitter Proxy and the client. But, that's what I'm talking about. Clients would need to be modified to support encryption/obfuscation before the user can utilize it.
3. Caching
While the Tourniquet proxy is fetching the information from Twitter, it might as well cache it to some form of persistent storage. This can be used to save some calls into the Twitter API, especially for historical data (which is also useful for when they disable access to historical data during times of heavy demand)
4. Store and Forward
Is Twitter down? Damn! But, no worries with Tourniquet! Your status update is saved until Twitter comes back up. This is really no different than other store-and-forward services, except you're not giving your twitter credentials to some unknown third party website.
5. Automated fetching
Perhaps Tourniquet can periodically fetch your timeline for you and cache it, either by means of some external triggering or by a timer. Then, when you hit the proxy to check for updates, it's already there (and your current request would likely trigger another fetch just to make sure that it has the latest data)
6. Tribe-Net Sharing/Synchronization
Here's where the service gets interesting: @keithelder and @jfollas follow each other, and both run Tourniquet. So, both proxies can be configured to be able to sync statuses between each other, hopefully saving some Twitter API hits that count towards your hourly usage. (I'm thinking that Direct Messages can somehow be used to announce Tourniquet endpoints). If Twitter is down and there are some status updates that are available (but not yet on Twitter), those can be propogated across the cloud via proxy-to-proxy synchronization. Eventually, they'll show up as actual Twitter statuses.
I picture the communication resembling something like this:
@jfollas's proxy calls @keithelder's proxy, and announces the highest message ID for each person that @jfollas follows. @keithelder's proxy returns a list statuses for each of those people that it has cached where the message ID is higher (plus any new/unpublished statuses that are in store-and-forward). @keithelder's proxy will need to call @jfollas's proxy to reciprocate the process.
Another possibility is to also synchronize a single person, perhaps with the goal of maintaining a cache of the last 100 tweets per person on your follow list. In this case, @jfollas's proxy will call @keithelder's proxy and list all of the message id's that it has cached for the person. If @keithelder is also following that person (or otherwise happens to have some tweets cached for the person), then any new messages (or any in-between messages that @jfollas's proxy might have missed) will be supplied in the response.
That was the original concept. The name "Tourniquet" came from the same place where all good project names come from: the Thesaurus. I simply looked for synonyms for the word "bandage", stumbled upon this word, and discovered that it was not already well-known as a software product.
Alright, sounds good. Where is Tourniquet?
Tourniquet is not a product, per se. It's a project, and an open source project at that (MIT license). You can download the source and do just about anything with it from the project site on Codeplex:
http://codeplex.com/tourniquet
To run Tourniquet, you will need to grab the release from Codeplex, set up a database (i.e., run the create scripts), copy the files from the release to your webserver, and then set up a new web application on your webserver. If this sounds too complicated, then perhaps you should wait until it's a little more refined before checking it out. I'm just sayin'... :-P
As I wrote the code, I tried to keep in mind that not everyone has a really sweet hosting deal. Therefore, I targeted what I thought to be the lowest common denominators: ASP.NET 2.0 and SQL Server 2005.
Classifying SQL Server as a LCD, though, bothered me, for a lot of "common man" hosting plans do not include access to a database at all. But, being a SQL Server MVP, I found this to be the quickest way to build the prototype and release the project to Codeplex. The persistance layer actually uses the Provider model, so my goal is to make alternative providers that do not require SQL Server (i.e., maybe XML on the filesystem, or Amazon SimpleDB, etc).
The point that I'd like to drive home is that the current codebase is very much a proof-of-concept or prototype, albeit a fully functional one (I've been using it for a few weeks). People may point at my code and say "Why did you do this like that? Where are your tests? This code sucks!" and that's okay. In its current form, it does most of what I outlined in the email above, so I'm content (working software is the #1 measure of success).
I encourage anyone who wants to participate in taking this prototype to the next level to join the development team. Contact me through the site, Codeplex, or Twitter (@jfollas). I'd be very happy if some enthusiastic people could take on the development of parts of the system and run with it.