Fandotech IT Business Solutions

SQL Server Spatial "Topology Rules" Rule!

Tweet

 

I am a little obsessed?about maps, and it's?all?my Dad's fault.? One of my earliest memories is how he?turned my least favorite meals into a geography game.? He would cut my food into the shapes of different states, and would then ask me if I could eat, for example,?the whole state of Connecticut.? After stuffing that piece of food into my mouth, I'd laugh hysterically at the thought of eating an entire state, forgetting that I'd just eaten the food I hated most.?

Luckily for me, this obsession has helped me professionally, since??I've ended up working?with Geographic Information Systems (GIS).? This technology?can be a powerful tool for helping people visualize?large amounts of data by presenting the data as a map.??The technology can improve the quality of spatial data, and can help determine where things should be located to accomplish real-world objectives.

GIS provides?"topology rules" to ensure that the?location of something makes sense in relation to?other things.? A house can't be located within an ocean.?? Each salesperson for a particular company should have an exclusive sales territory.??Driveways connect to streets.??And so on.

Microsoft provides a suite of tools for enforcing topology rules using SQL Server Spatial, which has been available since SQL Server 2008.? Much to my surprise, this?technology provides even more power and flexibility in defining topology rules than ESRI's ArcSDE 9.3 product.? And using ESRI's Data Interoperability extension, ESRI products can read and write directly from SQL Server Spatial.

Using SQL Server Spatial and its topology rules, I was able to solve a challenging problem involving the placement of vessel moorings to eliminate vessel collisions.? Vessels?move around their moorings due to tides and winds, and the pattern of movement depends on factors including the length of chains and the length of the vessel, as well as whether the vessel is attached to one or two moorings.?

The first step to solve this problem was to?calculate?the potential swing areas for each vessel based on the current information in the database.? For the geeks out there, I did this using a user-defined function that?returns a geography data type.

The second step to solve this?problem was to?create topology rules that identify and prevent potential collisions between?vessels.? A small overlap in vessel swing areas is?not?a problem, because similar boats will swing in similar directions at the same time - if there are?winds from the North, for example, all the vessels will typically?be found at the southern end of their swing areas.?? But significant overlaps in swing areas?are definitely a problem.? Using SQL Server's spatial functions, we can?determine the percentage overlap between?each set of?swing areas, and can create a topology rule that the moorings cannot be placed if?the overlap?percentage?is too high.? This ability to implement a topology rule based on a quantifiable measurement is very powerful.??For comparison, ESRI's SDE topology rules allow you to prevent overlaps or require overlaps, but they don't allow you to?allow a certain degree?of overlap.

Using SQL Server Spatial, you also have significant flexibility in?handling?data that violate your topology rules.??On the stricter end of the spectrum, you can prevent bad data from being stored in the database at all.? On the looser end of the spectrum, you can allow storage of?the "bad" data and create a query that identifies topology violations.? In the middle of the spectrum, you could warn about topology violations during the update process but?allow overrides.?

In summary, SQL Server Spatial has a powerful arsenal of tools to help validate and optimize the location of objects.? It is definitely worth exploring to see if it might meet your needs.

P.S.? Now that I'm a father myself, I tried cutting my daughter's least favorite foods into the shape of various states.? It hasn't worked at all.? She must be a lot smarter than I was at her age.

Brad Hurley