Around the 1995 timeframe, two very different systems opened my eyes to the power of geospatial data, particularly how it can be used to enable an analyst to make better business decisions. At the time, I was working in IT at the hub sorting facility for an overnight freight company that specialized in heavyweight cargo. This company not only had a fleet of aircraft flying freight around the country, but also operated a large fleet of trucks (both linehaul and LTL).
Shortly after leaving that company, I was tasked with working on an e-commerce system that had some unique sales territory requirements. My knowledge of geospatial concepts helped to solve the problem efficiently and, to the customer's delight, cheaply.
How about you? How does geospatial data fit into your line of business?
ASD
The first system that piqued my interest was an Aircraft Situation Display (ASD) that we had running in our Operations Control Center. Radar stations across the country help Air Traffic Control identify aircraft based on their assigned transponder codes, as well as determine the location, altitude, direction, and airspeed of the aircraft. This information is consumed locally at each ATC location, but is also forwarded on to a central location in Virginia to be consolidated. There, it is scrubbed (i.e., military and other sensitive flight information is filtered out) and published as the Aircraft Situation Display to Industry (ASDI) data feed.
As an approved vendor, we were permitted to access the data feed at our location via a dedicated data line. The raw stream at the time was text based, which was parsed and then stored in a database. The client application displayed a map of the entire United States, and plotted the location of selected aircraft onto the map (even though the stream contained information for thousands of aircraft in the air at any one time, we were only interested in less than 50 of them).
Before ASD, our Operations knew when a plane took off, and about how long it would be in the air, but until the pilot was in radio range of the airport (about 75 miles out), it was hard to plan for the exact arrival of the flight and schedule a crew to process its cargo. The up-to-date information provided by ASD allowed analysts visualize the health of the system, when aircraft were being re-routed due to weather, which flights would be arriving first, or how far behind the first arrivals a higher priority flight might be. In other words, having access to near real-time geospatial information about each flight enabled our operations to make better decisions about how to process that night's cargo.
Today, there are a few public websites that allow you to access the ASD information in much the same way that we did privately a decade ago. To get a taste of what I just described, check out my current favorite: FlightAware.com.
Route Optimization
Solving an optimization problem is the apotheosis of computing science. Yet, the vast number of systems being created today do nothing more than retrieve data from a database and display it on a website. I've lost count of how many such reporting systems that I personally created, yet I remember each and every optimization system that I've ever worked on over the years because they solved real problems and saved millions of dollars collectively (that would have otherwise been wasted by inefficiency). The first optimization system that I had the pleasure to see in action also utilized geospatial information.
A little known fact about air freight is that most of the transit time takes place on a truck. Cargo must be trucked from field locations to a major airport. After a few hours on a plane, it arrives at a centralized sorting facility, then onto another plane for a few more hours, and then back onto a truck for delivery to another field station. Finally, the freight is usually placed onto a smaller truck for delivery to the address of its final destination.
The company that I worked for wanted to make the final step - delivery from the field station to the final destination - more efficient. They basically wanted to say "Here is a list of deliveries that have to be made during business hours today, now show me the least number of trucks that I need to use." It was a fascinating problem to solve, and more fascinating that the computer (running Windows 95 at the time) was actually able to do it!
The process started with a manifest of deliveries for a given day. From this list, each customer's address was geocoded (converted into a Latitude/Longitude pair). The volume and weight of each piece of freight also had to be known so that it could be determined how many deliveries would fit onto a truck.
This information was then combined with a model of the delivery territory created using software running on top of MapInfo (and MapBASIC). Aside from roadway information provided by MapInfo, the model also included custom defined data such as average speeds for different times of the day, impassible territory, road construction and closures to avoid, etc. I'm likely simplifying the amount and complexity of the information that went into these models, because there were literally weeks of setup required per city.
But, in the end, the optimizer would do its thing and create models of optimal delivery routes for the entire manifest, taking in consideration the length of time it takes to drive between points, how long it should take to offload a piece of freight at a location, and how much freight overall could even fit onto a single truck, etc. The analyst using the system could then play with different parameters, say to adjust the length of the driver's lunch break, or to use fewer trucks overall, and see what the new optimized routes looked like.
Now, to my knowledge, this system was never utilized on a daily basis as part of actual operations, but rather, only during contract negotiations with LTL carriers within a certain market. By being able to include real geospatial data in the optimization process, my company was able to negotiate the use of the fewest number of trucks to handle the volume of a typical day. Each truck would be then be utilized to its fullest, instead of paying for more trucks than are actually needed and not maximizing the efficiency. The software also helped to determine standard delivery routes in a city based on the number of contracted trucks.
Sales Territories
The first e-commerce website that I created was contracted by a company that used a franchise model for its brick-and-mortar stores. That is, there might have been only one company-owned store, but all other locations bearing its name were actually franchises.
When this company made the jump to electronic sales on the web, it was realized that by contract, individual sales had to be credited to the franchise store whose territory included the customer's location. For the most part, a territory was defined as a 50-mile radius surrounding the franchise location. If a customer happened to be more than 50-miles away from all physical locations, then the sale could be safely credited to the company store.
At the time that this website was created, mapping and geocoding were expensive technologies. Google Maps and Virtual Earth did not exist, so being able to cheaply geocode individual customers in a real time fashion was not practical. So, instead, we purchased a database that included geocodes for the centroid of every zip code in the USA. We also paid to have each franchise location's address geocoded (there were less than 100 stores in total).
As new stores were added, a batch process would calculate the distance from each store's location to the centroid of each zip code. Zip codes that were more than 50 miles from any of the franchise locations were assigned to the company store. Otherwise, the closest store to a zip code was "awarded" that zip code as part of its territory. In this way, we didn't need to geocode each and every customer location - we could just use the customer's zip code to determine which franchise's territory that customer belonged to.
I think that at the time, I simply used the Great Circle Distance formula to determine the distance between two given points:
c = 2 * asin( sqrt( ( sin(( lat1 - lat2 )/2) )^2
+ cos( lat1 ) * cos( lat2 )
* ( sin(( lon1 - lon2 )/2) )^2 ) )
* radiusOfEarth
This particular formula wasn't very difficult to implement in a T-SQL stored procedure, but it's also not extremely accurate.
Your Turn
I'm excited by the level of support for geospatial data that is included with SQL Server 2008. When I think back at all of the systems that I have worked with in the past, most of the geospatial processing was performed in the middle tier, when it would have been far more ideal to do some of the things in the database. Because of this, I have made it a mission of mine to come up to speed on current spatial concepts.
I'd love to hear about how you were able to use geospatial data in your existing line of business applications, and also how you see Spatial data types in the database helping to improve the design of your application.