In my post last Wednesday on Orientation and Geodetic Data, I motivated the need to correctly orient polygons when loading geodetic data into Microsoft SQL Server. Today, I’ll continue by describing a simple approach to addressing this problem and look at some of its consequences.

I’d like to discuss an interesting pitfall of using the following algorithm: take your lat/long data, pretend it is on a plane (the “plate carrée” projection), and use a non-geodetic algorithm to fix the ring orientations. This is the strategy FME uses currently, and Ed Katibah demonstrates how to do the same thing inside SQL Server in an earlier blog post. I expect improvements in both systems (FME and SQL Server) in future (we plan to handle this more gracefully, and a future version of SQL Server may include a function that reorients polygons geodetically).

This algorithm breaks down in three cases:

1) Geometries that cross the 180 degree meridian. This is the most common trouble and the easiest to work around. This comes in two variants. In the first case, the offending geometry is broken into an aggregate of two pieces: the part east of the meridian (>= -180 longitude) and the past west of the meridian (<= +180 longitude). The trouble with this data is that SQL Server doesn’t like aggregates of areas that share a common edge. In the second case, the offending geometry is all in one piece, but adjacent vertices jump between longitude values just east of -180 and just west of +180. Often, this can be worked-around by adding 360 to all of the negative longitudes.

2) Geometries that enclose a pole. As before, these come in two variants: either the data has been formatted for viewing in two dimensions, with a line from (-180,+-90) to (+180,+-90), or it has not. In the first case, there will be a common edge or sliver at the +-180 degree meridian, and orientation must be handled specially. In the second case, it is not easy to determine that a pole has been enclosed, orientation is ambiguous, and the data may appear to self-intersect.

3) Extremely large, narrow polygons. This is the most interesting (and rare) case. It turns out that a polygon can have one orientation when we pretend the lat/long data is planar and another orientation when we treat it geodetically.

Imagine a flight from Vancouver (Canada), to Edmonton, to Amsterdam, and then back to Vancouver. When viewed in plate carrée, this path looks clockwise. However, when viewed on the globe, the path looks counterclockwise because the flight from Amsterdam to Vancouver is much further north than the path from Vancouver to Edmonton. This situation can also occur for polygons that enclose a small area, as long as they are sufficiently narrow.

*Figure 1: Viewed geodetically, the path is counterclockwise (blue). Viewed on a plane, the path is clockwise (orange).*

As alluded to above, an alternative approach that works in many of these cases is to reproject each feature to an appropriate local coordinate system before determining whether or not orientation changes are required. We commonly use similar strategies when solving problems that mix geodetic and planar data, e.g. “starting with airport locations in lat/long, buffer them all by 10 km, and output the result in lat/long.”

*Figure 2: The same path viewed in the north-pole gnonomic projection. (The gnomonic projection has the interesting property that a straight line drawn between any two points traces the shortest path between those points on the globe.) The polygon is counterclockwise, as in the geodetic case. For smaller features, you might use a dynamically generated coordinate system centered on the feature in question instead.*

As you’ve seen, there are some limitations to simpler alternatives to solving this orientation problem. Particularly interesting to me was the fact that small polygons far from the 180 meridian and poles might reverse orientation when approximating geodetic data as planar. So when it comes to loading data into SQL Server, the best compatability is achieved by using their algorithm which computes orientation geodetically.

**Spatial Data**

**Spatial Databases**

**SQL Server**

Paul Nalos