Fandotech IT Business Solutions

SQL Server Spatial "Topology Rules" Rule!

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

The Fine Ancient Arts of Cartography and "Map Folding"

Those of us who create and use paper maps are familiar with their many challenges. Paper?maps have been around since the dawn of ancient civilizations, or at least since the Egyptians began sketching out the route to their neighborhood?pyramid?on papyrus.?Ever since then, they have perplexed even the most ambidextrous quadrupeds among us. Furthermore, many among us are just plain confused by the cryptic symbols used on these origami torture devices. To understand?this secret language, ?it helps to know what you are looking at.

The numerous maps created by Fuss and O'Neill Technologies G.I.S. department can vary from the very simple to the incredibly complex, but they all contain some basic "furniture" that helps the map reader gather information. It can be considered the "Cartographer's Trinity".

• First and foremost, is the map title—without it we'd be lost (pun intended). This is pretty straight forward—it should tell you what the map is intended to show or where in the world the map is representing. Keeping it simple is the best advice. A title such as, "Map of the United States" works better than, "Map showing places that I have never been to before, but intend to visit at some point blah, blah, blah". The title should be descriptive of the content, but not so elaborate that it detracts from the actual map


• Second is the map legend. This can get pretty complicated and out of hand if you allow it to. Finding the right balance between useful and useless information is integral. It is important to keep mindful of the map purpose when deciding on the legend. Clearly recognizable symbols, such as ponds and rivers, don't have to be included in the legend. The symbols should differ enough from one another to prevent visual confusion.


• Third is the scale bar or scale text. This let's the reader know if they'll need to?travel a few feet, or a few miles between locations shown on the map. The key here is a usable common unit of measure— i.e. one inch equals?x feet or meters or miles or Smoot.

Based on the overall simplicity of the map, the legend may be omitted.

Deciphering the map information is only the first of two?puzzels to be faced. Once you have conquered reading the map, and arrived safely at your planned destination without stopping to ask directions at?many 7-Eleven's along the way, the?ordeal of re-folding the map to its original shape can be as daunting as the journey itself.

[caption id="attachment_204" align="alignleft" width="186" caption="look familiar?"][/caption]

In the days long before the in-vehicle navigation systems, I recall as a young child the wadded up crumple of paper that my parents kept in the glove compartment. It usually only appeared on family summer vacations that involved the above-mentioned stop at?several 7-Eleven's along our trip, and strong encouragement from my mother. I remember fondly the confounding expressions my dad's face?would make as he attempted to return the gas station "souvenir" to its original shape and size. It wasn't until I found myself on one of those college-age "road trips" that I too was facing the perplexing challenge of re-folding the map. In an attempt to pass along?additional wisdom and experience of that stressful time, I offer the following lesson in the fine ancient art of "Map Folding":

1. Begin with the map laying flat on a hard surface, such as the hood or roof of your vehicle.


2. Most large?folded road maps begin by folding in a fan-like pattern, alternating the fold direction, in equal width.


3. Depending on the length of the strip that you created with the folds, it will fold into thirds, bottom up, and then top folded down, or it will fold in half, and then in half again creating a rectangular shape.

Paper road maps are becoming less visible every year, as the cost of in-vehicle navigation systems becomes less and less expensive. I must admit though, I keep my paper map neatly folded in my glove compartment. It reminds me of a simpler time of family vacations, unplanned stops at small town 7-Eleven's, and the echo of "are we there yet???"

Thad

Google vs. ESRI for Managing Moorings

Since Fandotech implements Geographic Information Systems (GIS) solutions for our clients that are based upon ESRI technology.? ESRI is the leading provider of Geographic Information System (GIS) software nationwide, and Fandotech is an ESRI? business partner.? During our sales process, our potential clients?sometimes ask us?to explain why we?promote ESRI technology instead of using freely available mapping software such as Google Maps.??We worked on a project recently that demonstrates why.

Fandotech has a web-based product that is designed to allow communities throughout the United States to effectively manage their boat moorings.? Part of this product is the ability to view the locations of existing moorings, and to determine where new moorings can be placed.

Part of Fandotech's approach to this product is to provide an entry-level system that municipalities can implement at no cost, with the ability to upgrade to more sophisticated capabilities for a fee.? In keeping with this approach, we have an entry-level mapping module that uses Google Maps, and a premium mapping module that uses ESRI server products.

The entry-level (Google Maps) module allows users to view the locations of selected moorings and/or all moorings, and allows a user to get information about a mooring from our database, as shown in Figure 1.

Figure 1:? Mooring Mapping Module using Google Maps

The premium mapping module, using ESRI technology,?provides much better capabilities to the user, as shown in Figure 2.

Figure 2:? Mooring Mapping Module using ESRI

These additional capabilities include:

• The ability to view many additional types of spatial data about this harbor that were already available in ESRI format from local, state, and federal sources.? This included on-shore parcel boundaries, historical aerial photos, nautical charts, flood zones, etc.


• Using information about where the mooring is supposed to be, and where the mooring is now, we can use ESRI technology to determine?how far a boat owner?needs to move their mooring, and in what direction, in order to be in compliance with their mooring permit.


• The ability to determine the suitability of a proposed mooring location.? Unlike addresses, which have a fixed location, moored boats can move in a circular area surrounding the mooring, based on wind and tide conditions.? Using the boat length, the minimum depth, and the length of the chain attached to the mooring, we can use ESRI technology to determine the area where the boats could be found, and can determine whether there is any overlap between those areas.? If there is an overlap, the boats could potentially collide.? Also, we can use ESRI technology to analyze whether the the entire circular area for each boat has sufficient water depth so that the boat will not run aground.

There is no doubt that Google Maps is?an affordable, easy-to-use mapping capability.??In this example and in many others, however,?we find?that the power and flexibility of ESRI's technology helps us to provide solutions that fully?meet people's needs.