NielsenData.com

NielsenData Blog by Jared Nielsen

Earth's Magnetic Field is About to Flip... South! Is your Aircraft Ready?

Magnetic North

In the early days of ocean exploration, navigators would maintain meticulous logs and one of the elements that they would record was the relative position of "Magnetic North" compared to "True North".  Turns out the fluid molten metal that swims around the Earth's core tends to gurgle around, dragging one or more "Magnetic Norths" with them.  At times there have been multiple recordings of Magnetic North reported in different areas so it's hardly a true measure to navigate against without consistent and accurate error correcting.

Modern navigators developed compasses that would help them precisely identify their position relative to the magnetic poles first using magnetic compasses, but then these were subjugated to backup status with the invention of the electro-mechanical gyrocompass which used a spinning disc countered by the Earth's rotation to find "True North" instead of relying on magnetism which introduced variable errors into the navigation.  This device had moving parts however and could have errors introduced when swift changes in direction impeded the accuracy of the spinning disc to react swiftly enough.  Enter the Ring Laser Gyrocompass or the Fiber Optic Gyrocompass which eliminated the moving parts in favor of shifts to wave patterns (leveraging the unique effect of Sagnac Interference).

Earth as a Magnet Has Variation

If you think of the Earth as magnet where north "hovers" near the rotational north pole and south hovers near the rotational south pole, they are constantly attracting each other with magnetic forces entering the planet in a concentric "donut" shape along continuously shifting lines measured by the angle of "Magnetic Dip".  This angle of penetration of the planet by the magnetic fields is 0 degrees at the magnetic equator and approaches 90 degrees (perpendicular to the tangent of the Earth's surface) at the magnetic poles.  In a stable environment, this is how the Earth is accustomed to working, so aviation equipment that relies on magnetism for geolocation or orientation can make predictable measurements.

There is also variation between the True North and Magnetic North as the magnetic poles roam about the planet and that is measured frequently to provide error correction based on this magnetic variation.  This is charted in reference diagrams that are updated from time to time.

Your Aircraft or Ship Is a Magnet

That gets even more complicated when you construct aircraft from magnetically influenceable materials (any ferrous containing materials).  This is caused by the manufacturing process such as hammering, forming, shaping or molten casting.  

Permanent Magnetism

At the time of cooling, any molten iron bearing material will "fuse" in the direction of the poles based on where the metal component was "pointing" in relation to magnetic north.  This "encodes" in the metal not only the current direction of magnetic north but also the intensity or strength of the magnetic field at that time.  Geologists even detected this effect in volcanic strata where clay based (iron containing) materials were suddenly heated and cooled.  This effect is called Permanent Magnetism of the material. 

Induced Magnetism

In softer iron materials, as you move a ship or aircraft that contains them through the magnetic fields on the planet, there is another subtle influence of magnetic shift in the craft itself as it "inherits" magnetic strength and polarity based on where it's located at the time.

Sub-Permanent Magnetism

And we can't stop there.  In situations where there is persistent vibration, welding or aging of materials, their magnetic polarity and strength can be impacted or altered over time that is independent from their original permanent magnetism or environmentally induced magnetism.

All three factors must be considered within an aircraft as physical magnetic correction is applied.

What Happens When None of This Remains True?

Magnetism was a trusted and relied upon source of data for navigation, communications and geolocation.  However, the magnet can become confused when turbulence deep within the molten core forces multiple poles to "split off" from the traditional magnetic forces and then you end up with rifts in the traditional magnetic donut that permeates the earth... now you have a mottled magnetic shield and gaps begin to appear.

ESA and the SWARM Satellite Constellation

Enter the European Space Agency and a critical measuring system called SWARM.  This mission serves the planet by measuring the geomagnetic forces that surround the Earth.  In their latest briefing on June 19th, 2014 the SWARM team announced that there are new weaknesses in the Earth's magnetic field that are changing ten times faster than previously expected.  This means that the scientific community was expecting a 5% weakening of the magnetic field per century, but that's now being measured as weakening 5% every decade.  

They published a recent image showing a magnetic fissure opening up over the Western Hemisphere as indicated by the blue areas of the magnetic field's strength.

Magnetic Polar Flip

Here comes the real fun.  When the poles start to scramble about and the magnetic field gets weaker and weaker, it indicates that there is a chance for an imminent "polar flip".  The Polar Flip happens when magnetic North shifts so significantly that it "snaps" in place on the southern pole rather than the northern pole.  While human society has never experienced this before, scientists are coming to the conclusion that one is now due.

Geologists have been able to comb through strata where volcanic events "recorded" the intensity and direction of the magnetic poles throughout the centuries and have estimated that a polar flip happens every few hundred thousand years.  The challenge they face is Earth is long overdue for one as it hasn't happened in the last 780,000 years.  In the past they had estimated that it was due to happen 1,500 years from now, but these new measurements from the ESA SWARM satellite constellation suggest that the pace may not be linear or certain... and in fact may be speeding up.

While it sounds pretty dramatic, our seasons are dictated by the Earth's relative position to the Sun in orbit and other factors that are independent of magnetic north.  The most significant impact would be the relative lack of protection the Earth enjoys from solar winds and radiation from the Sun that stronger magnetic fields protect us from.  One nice side effect of a full "magnetopause" event is that the Northern Lights would be visible all around the planet for a period of time.

The Impact on Aviation

The impact on aviation is likely minimal as well.  Components like the Aeronautical Magnetic Compass would require changes to the compensating magnets mounted on them (in a radically different placement of course) and there is some question about how embedded electromagnets would be impacted with polarity reversals.  When you dig into the subcomponents of AC Synchro Systems that leverage Magnasyn systems that contain permanent magnet rotors there is some question about how that would impact fuel sensors and oil pressure gauges.

Milliman.com - Retirement Planning Consulting Firm

Milliman and Robertson
Actuarial Consultants

I started my career under the mentoring of my own father, Victor Nielsen, and Mark Trieb, who was one of the principals of what started as a US based retirement planning and consulting firm.  As the firm grew, it merged with Bacon & Woodrow which propelled the company into global operations.  The culmination of this formed Milliman.com as one of the larger actuarial firms in the world.

My experience with the Dallas office of Milliman and Robertson showed that even within a loose affiliation of principal-owned business units, the Dallas office excelled in entrepreneurial distinction, by developing software in Dallas that served the needs of all of the other branch offices.  It was clear to me that Mark Trieb was ambitious and driven which put him at odds with some of the other principals from time to time, but I witnessed his steadfast technological focus that drove innovation throughout the entire company.  

I learned a lot from that and it has influenced my future roles in many large corporations from that point forward.  One of the most valuable opportunities included their tolerance of my developing nascent software development skills.  I progressed over the years from a mailroom clerk, network administration, database developer to a fully trained software developer.  This opportunity was a unique position that helped launch my computer-centric career.

I had the pleasure of learning from Panom Rujopakarn, Chaiboon Leelaphunt, and Risana Tilkanont who were software developers from Thailand with exceptional expertise in MDBS solutions.  They were kind enough to share their knowledge with me and helped me explore software development with real world business experience.

My sister has continued with the organization as a highly qualified leader in their Denver office after decades with the company.  My brother also has continued with them as an extremely talented software development consultant.  My father retired from the company after decades of loyal service.  This legacy of support between Milliman and my family has helped our families grow and thrive and for that, they have my thanks.

Aviation Technology #3 - FIR Boundary Intersection using Spatial Data and Flight Routes

We can use SQL Spatial technologies to help us calculate intersections between geospatial objects. In our case we are going to plot a route of an aircraft in a round trip flight. We are then going to decide from the SQL Server which countries this flight intersects and which FIR boundaries it will need to file overflight permits.  To get this started we are going to need to create some sample data.  

Creation of the Route of the Aircraft

Let's begin by creating the route for this round trip flight.  We will be plotting this flight from the west of Australia, east across it, down into the center of Australia and back again.  Note how we are leveraging Great Circle Route plotting for our example legs in our route.  Here is the code to make that happen (remember to use long/lat in the correct order):

declare @route geography
set @route = geography::STLineFromText('LINESTRING(148.47539133747426 -20.031355678341452,110.45019875220305 -21.382500474560892,125.45019875220305 -31.382500474560892,148.47539133747426 -20.03135567834145)',4326)

Creation of the Country and FIR Boundaries

First we need to prepare some sample data.  In this example we will create the Country of Australia as a geospatial shape, and we will be using the two airspace boundaries that overlay this country.  We will also create the FIR boundaries that overlap the country of Australia.  We will end up with the following shapes:

We are going to need quite a bit of detail for this to paint a true contour of these country and FIR boundaries.  Bear with me and we'll tie this up with a really cool intersection query.

declare @Country TABLE (name nvarchar(100), shape geography)

insert into @Country select 'Australia', geography::STGeomFromText('MULTIPOLYGON (((142.15774724248377 -10.189721505889402 0 0, 142.14358746472502 -10.051666001380251 0 0, 142.089693069458 -10.133890151977539 0 0, 142.15774724248377 -10.189721505889402 0 0)), ((142.28552444834591 -10.264723021326967 0 0, 142.33053779602051 -10.171945571899414 0 0, 142.22580011934789 -10.146111330574273 0 0, 142.28552444834591 -10.264723021326967 0 0)), ((142.32107588366944 -10.548610527836342 0 0, 142.31802563472951 -10.513890415321134 0 0, 142.29220771789551 -10.533889770507812 0 0, 142.32107588366944 -10.548610527836342 0 0)), ((142.23080634658845 -10.568888317714642 0 0, 142.22662442665779 -10.525277888336259 0 0, 142.19134712219238 -10.565834045410156 0 0, 142.23080634658845 -10.568888317714642 0 0)), ((142.27804753769257 -10.643611151036556 0 0, 142.31415104692167 -10.58416564655978 0 0, 142.24969673156738 -10.587499618530273 0 0, 142.27804753769257 -10.643611151036556 0 0)), ((142.18332046689764 -10.77027965693744 0 0, 142.26581001281738 -10.683610916137695 0 0, 142.11523487174375 -10.660558345702242 0 0, 142.18332046689764 -10.77027965693744 0 0)), ((132.62384391986859 -11.279167855262875 0 0, 132.57885999067059 -11.02250095528151 0 0, 132.51248359680176 -11.143056869506836 0 0, 132.62384391986859 -11.279167855262875 0 0)), ((136.53610131512468 -11.455001431232622 0 0, 136.72497749328613 -11.206943511962891 0 0, 136.7719133299511 -11.019721640434682 0 0, 136.53610131512468 -11.455001431232622 0 0)), ((133.391358029667 -11.543890362951245 0 0, 133.50555610656738 -11.503889083862305 0 0, 133.46942564978491 -11.460832689967818 0 0, 133.391358029667 -11.543890362951245 0 0)), ((133.3838822054143 -11.667498776777636 0 0, 133.48245599818986 -11.586940731426456 0 0, 133.36691474914551 -11.614721298217773 0 0, 133.3838822054143 -11.667498776777636 0 0)), ((136.17831662123217 -11.68777735870291 0 0, 136.47052192687988 -11.519166946411133 0 0, 136.47885349359532 -11.46610790905086 0 0, 136.17831662123217 -11.68777735870291 0 0)), ((136.01275907389999 -11.719168149531615 0 0, 136.05386713594285 -11.659997629614193 0 0, 135.95996284484863 -11.684165954589844 0 0, 136.01275907389999 -11.719168149531615 0 0)), ((132.56024338782976 -11.724445697284562 0 0, 132.64303779602051 -11.648056030273438 0 0, 132.51388594646332 -11.643890959416751 0 0, 132.56024338782976 -11.724445697284562 0 0)), ((136.51608253522346 -11.798613029218661 0 0, 136.58941841125488 -11.780555725097656 0 0, 136.62466686971831 -11.740279578672425 0 0, 136.51608253522346 -11.798613029218661 0 0)), ((130.01639312400428 -11.77972025488352 0 0, 130.491060256958 -11.68861198425293 0 0, 130.342471753025 -11.323610639995012 0 0, 130.01639312400428 -11.77972025488352 0 0)), ((136.47357292923181 -11.911113113918224 0 0, 136.4949658607926 -11.803052622566222 0 0, 136.46942329406738 -11.840555191040039 0 0, 136.47357292923181 -11.911113113918224 0 0)), ((135.04748635828574 -11.945555896072314 0 0, 135.11551094055176 -11.934444427490234 0 0, 135.06497266705679 -11.913606659042143 0 0, 135.04748635828574 -11.945555896072314 0 0)), ((130.95886314506993 -11.938888893169974 0 0, 131.539155960083 -11.461666107177734 0 0, 131.27499487354478 -11.189165822245778 0 0, 130.57330383826019 -11.349445412908985 0 0, 130.37744440226234 -11.17055531248932 0 0, 130.95886314506993 -11.938888893169974 0 0)), ((134.94497892343395 -12.061392361545481 0 0, 134.98578071594238 -12.03416633605957 0 0, 134.911377388382 -12.022222654474994 0 0, 134.94497892343395 -12.061392361545481 0 0)), ((136.15832425634414 -12.0713911080268 0 0, 136.28192329406738 -12.035556793212891 0 0, 136.30636195626931 -11.976942969639023 0 0, 136.15832425634414 -12.0713911080268 0 0)), ((134.90997440698814 -12.121112717005845 0 0, 134.92776679992676 -12.072776794433594 0 0, 134.86691620549297 -12.066666004733285 0 0, 134.90997440698814 -12.121112717005845 0 0)), ((136.82052393609882 -12.140556874212292 0 0, 136.82608116482803 -12.072222477849834 0 0, 136.80053901672363 -12.094165802001953 0 0, 136.82052393609882 -12.140556874212292 0 0)), ((132.40469926990505 -12.140833027669453 0 0, 132.40219319309756 -12.072221915284924 0 0, 132.34024238586426 -12.0897216796875 0 0, 132.40469926990505 -12.140833027669453 0 0)), ((136.1616539106509 -13.531111482963192 0 0, 136.15081977844238 -13.485832214355469 0 0, 136.09885105269228 -13.36110926928102 0 0, 136.1616539106509 -13.531111482963192 0 0)), ((136.10552766939162 -13.818054429774024 0 0, 136.28830146789551 -13.733331680297852 0 0, 136.19885496697864 -13.664723628449558 0 0, 136.10552766939162 -13.818054429774024 0 0)), ((126.51193368803231 -13.9094453202213 0 0, 126.60637092590332 -13.895553588867188 0 0, 126.50249227740373 -13.877776958847694 0 0, 126.51193368803231 -13.9094453202213 0 0)), ((136.94165553673116 -14.277780572311341 0 0, 136.69940376281738 -14.120832443237305 0 0, 136.91247717411215 -13.774446743693039 0 0, 136.71164131164551 -13.835832595825195 0 0, 136.378023147583 -14.216388702392578 0 0, 136.94165553673116 -14.277780572311341 0 0)), ((135.6994044480717 -14.907777044440909 0 0, 135.764986038208 -14.904167175292969 0 0, 135.72829918394123 -14.836663097184461 0 0, 135.6994044480717 -14.907777044440909 0 0)), ((125.13831998165344 -14.648612037242424 0 0, 125.15941913629119 -14.438335275232648 0 0, 125.08471870422363 -14.617221832275391 0 0, 125.13831998165344 -14.648612037242424 0 0)), ((129.62994556247526 -14.879445704658844 0 0, 129.63080024719238 -14.850831985473633 0 0, 129.58053903549632 -14.802779346925469 0 0, 129.62994556247526 -14.879445704658844 0 0)), ((129.59247024914728 -14.966660151693983 0 0, 129.58941841125488 -14.907777786254883 0 0, 129.501925749898 -14.78861598040476 0 0, 129.59247024914728 -14.966660151693983 0 0)), ((128.44107589704493 -15.037503754725174 0 0, 128.35636069873033 -14.870273269206 0 0, 128.35717964172363 -14.959999084472656 0 0, 128.44107589704493 -15.037503754725174 0 0)), ((124.94331456864823 -15.030003633202949 0 0, 124.92442907215582 -14.944998478067308 0 0, 124.87468910217285 -14.95222282409668 0 0, 124.94331456864823 -15.030003633202949 0 0)), ((128.19192586230707 -15.188614747777155 0 0, 128.12051365327105 -15.043051959052885 0 0, 128.11468696594238 -15.131942749023438 0 0, 128.19192586230707 -15.188614747777155 0 0)), ((124.81609478009374 -15.281110993411794 0 0, 124.8499927520752 -15.247220993041992 0 0, 124.79748711385042 -15.239722175345687 0 0, 124.81609478009374 -15.281110993411794 0 0)), ((136.851351586663 -15.634166935356562 0 0, 136.87857258883824 -15.501943538191416 0 0, 136.84192085266113 -15.549722671508789 0 0, 136.851351586663 -15.634166935356562 0 0)), ((124.42638202406226 -15.315000272072337 0 0, 124.43748069537266 -15.249439385195377 0 0, 124.33664894104004 -15.296112060546875 0 0, 124.42638202406226 -15.315000272072337 0 0)), ((136.52997274404657 -15.645280430685487 0 0, 136.597749710083 -15.616109848022461 0 0, 136.57940692936978 -15.511115818707026 0 0, 136.52997274404657 -15.645280430685487 0 0)), ((136.74191398344985 -15.752223730370172 0 0, 136.809419723768 -15.650277745376675 0 0, 136.73413517239402 -15.64805819095292 0 0, 136.74191398344985 -15.752223730370172 0 0)), ((124.5224961146638 -15.445276422678951 0 0, 124.62164497375488 -15.412221908569336 0 0, 124.54221712385889 -15.261669664767753 0 0, 124.45942878723145 -15.366943359375 0 0, 124.5224961146638 -15.445276422678951 0 0)), ((136.66721043695802 -15.776667002196675 0 0, 136.71301296300095 -15.697778746135732 0 0, 136.63498296548087 -15.676111371619214 0 0, 136.66721043695802 -15.776667002196675 0 0)), ((137.06720522253659 -15.829446353274374 0 0, 137.00305486411185 -15.591383018811406 0 0, 136.93441904327076 -15.698888445693777 0 0, 137.06720522253659 -15.829446353274374 0 0)), ((139.14026471794779 -16.755002104883548 0 0, 139.7363600334497 -16.497218608973849 0 0, 139.30664417563526 -16.462497329095584 0 0, 139.14026471794779 -16.755002104883548 0 0)), ((139.11218533422166 -16.862216059267887 0 0, 139.13998265856938 -16.810552245332048 0 0, 139.09079165336973 -16.82666854697576 0 0, 139.11218533422166 -16.862216059267887 0 0)), ((139.57607960956722 -17.095832350084681 0 0, 139.50277948526207 -16.996664264715886 0 0, 139.39886700644831 -17.09138985637167 0 0, 139.57607960956722 -17.095832350084681 0 0)), ((146.29940943588863 -18.489444978703936 0 0, 146.26971626281738 -18.308334350585937 0 0, 146.08331199878128 -18.256111685506948 0 0, 146.29940943588863 -18.489444978703936 0 0)), ((146.86774207828262 -19.164994524225236 0 0, 146.86969189571721 -19.106669225856244 0 0, 146.77609128688124 -19.131113328322861 0 0, 146.86774207828262 -19.164994524225236 0 0)), ((148.47134923899418 -20.046383571994077 0 0, 148.48217964172363 -20.006111145019531 0 0, 148.44971131577395 -19.970830959682953 0 0, 148.47134923899418 -20.046383571994077 0 0)), ((148.93579018111362 -20.171944993395396 0 0, 148.97024353442865 -20.048608036550362 0 0, 148.88189901274239 -20.130278682350973 0 0, 148.93579018111362 -20.171944993395396 0 0)), ((149.00637842849892 -20.318330956982546 0 0, 148.97552729914455 -20.155000702197821 0 0, 148.95553779602051 -20.291389465332031 0 0, 149.00637842849892 -20.318330956982546 0 0)), ((149.06665405174033 -20.526669059167176 0 0, 149.07498362101819 -20.486943226223843 0 0, 149.03692979222666 -20.501663112264374 0 0, 149.06665405174033 -20.526669059167176 0 0)), ((115.3038606719465 -20.872779356663919 0 0, 115.45498760022197 -20.782501043080142 0 0, 115.43525921366432 -20.667216123388645 0 0, 115.3038606719465 -20.872779356663919 0 0)), ((150.28692699889962 -21.687497784961323 0 0, 150.30523609704707 -21.660001430853249 0 0, 150.25387180740057 -21.63804976681978 0 0, 150.28692699889962 -21.687497784961323 0 0)), ((150.3369158924053 -21.775562519694258 0 0, 150.35885771069624 -21.733330255396115 0 0, 150.29443738706973 -21.744442840466458 0 0, 150.3369158924053 -21.775562519694258 0 0)), ((149.90082122301465 -22.228328281952066 0 0, 149.90833126783102 -22.046945079336115 0 0, 149.86578559875488 -22.17388916015625 0 0, 149.90082122301465 -22.228328281952066 0 0)), ((150.49163313922674 -22.354720083911623 0 0, 150.56024007380503 -22.304446002794712 0 0, 150.48996069780196 -22.213054859091088 0 0, 150.49163313922674 -22.354720083911623 0 0)), ((149.73550902214743 -22.42305505734555 0 0, 149.76080722887932 -22.363889046553155 0 0, 149.73357982107029 -22.340553706485874 0 0, 149.73550902214743 -22.42305505734555 0 0)), ((150.98218524143752 -23.195554161709218 0 0, 150.97830590464554 -23.150554577517912 0 0, 150.94721188818733 -23.195555889203444 0 0, 150.98218524143752 -23.195554161709218 0 0)), ((151.2694373542441 -23.780551638438098 0 0, 151.20303535461426 -23.528888702392578 0 0, 151.01999339998846 -23.454447986810784 0 0, 151.2694373542441 -23.780551638438098 0 0)), ((151.3821714060756 -23.882779224893998 0 0, 151.32690332760865 -23.753050626631012 0 0, 151.33053779602051 -23.813335418701172 0 0, 151.3821714060756 -23.882779224893998 0 0)), ((153.0036027994939 -25.339997299234419 0 0, 152.99246406555176 -25.307777404785156 0 0, 152.94885806913769 -25.272504220379229 0 0, 153.0036027994939 -25.339997299234419 0 0)), ((153.07718370564814 -25.798613887916126 0 0, 153.28164611838298 -24.699170360611575 0 0, 152.94302466535393 -25.558331444549754 0 0, 153.07718370564814 -25.798613887916126 0 0)), ((113.11499556886784 -24.995827017085084 0 0, 113.13971138000488 -24.926666259765625 0 0, 113.14721695425685 -24.760001353164085 0 0, 113.11499556886784 -24.995827017085084 0 0)), ((113.06165563241696 -25.278615290250297 0 0, 113.07361030578613 -25.233608245849609 0 0, 113.10164970883866 -25.097217294271427 0 0, 113.06165563241696 -25.278615290250297 0 0)), ((113.20915630168064 -26.140833424468482 0 0, 112.95110397444334 -25.488608334845512 0 0, 112.95332527160645 -25.786945343017578 0 0, 113.20915630168064 -26.140833424468482 0 0)), ((153.42526752506939 -27.36277634447119 0 0, 153.45413002243552 -27.017783023750471 0 0, 153.41497993469238 -27.248054504394531 0 0, 153.42526752506939 -27.36277634447119 0 0)), ((153.45136790295885 -27.729157157924973 0 0, 153.53857612609863 -27.416664123535156 0 0, 153.43359812677727 -27.416114564978329 0 0, 153.45136790295885 -27.729157157924973 0 0)), ((133.58942445553259 -32.311665717044974 0 0, 133.67498900633055 -32.240553754035737 0 0, 133.5335996112976 -32.30444407565151 0 0, 133.58942445553259 -32.311665717044974 0 0)), ((159.07080709384635 -31.599994896449708 0 0, 159.10190354034279 -31.57111195183159 0 0, 159.07080535325042 -31.523611294304548 0 0, 159.07080709384635 -31.599994896449708 0 0)), ((134.48664203621036 -33.779997993379354 0 0, 134.54024838760208 -33.69666440510936 0 0, 134.48025739993739 -33.71416356476881 0 0, 134.48664203621036 -33.779997993379354 0 0)), ((115.66081795099707 -32.233616659907241 0 0, 115.68026684569877 -32.228332637067126 0 0, 115.64888043721716 -32.152227578081479 0 0, 115.66081795099707 -32.233616659907241 0 0)), ((137.35800273892298 -34.54028283656266 0 0, 137.36578285454337 -34.477773553039611 0 0, 137.33441376005453 -34.515556530685821 0 0, 137.35800273892298 -34.54028283656266 0 0)), ((123.20749288445042 -34.111944237537053 0 0, 123.21581957990679 -34.086110387970166 0 0, 123.16776542720227 -34.09444386276224 0 0, 123.20749288445042 -34.111944237537053 0 0)), ((122.24693132885875 -34.15166658419195 0 0, 122.25554847717285 -34.124443054199219 0 0, 122.22998101989569 -34.112219829445849 0 0, 122.24693132885875 -34.15166658419195 0 0)), ((136.20275973257216 -35.076955660491329 0 0, 136.19580268859863 -35.026390075683594 0 0, 136.08664053245585 -34.947213996315341 0 0, 136.20275973257216 -35.076955660491329 0 0)), ((136.49524719423079 -35.175564208432462 0 0, 136.46664698813393 -35.138609544302007 0 0, 136.43441708822021 -35.158051142344121 0 0, 136.49524719423079 -35.175564208432462 0 0)), ((136.53442731583692 -35.912499636908542 0 0, 138.11274229604044 -35.869723199946542 0 0, 137.5849611645917 -35.650837989960557 0 0, 136.53442731583692 -35.912499636908542 0 0)), ((118.47192236508018 -34.938329020894358 0 0, 118.47638192770673 -34.9180597872644 0 0, 118.42469855556132 -34.907770751065705 0 0, 118.47192236508018 -34.938329020894358 0 0)), ((145.49273844846678 -38.374444475984319 0 0, 145.30582388474207 -38.296949041255246 0 0, 145.27108940856772 -38.363335610591406 0 0, 145.49273844846678 -38.374444475984319 0 0)), ((145.36136333084829 -38.56916959280948 0 0, 145.31165163637726 -38.467772987088587 0 0, 145.11359175939751 -38.528884462634664 0 0, 145.36136333084829 -38.56916959280948 0 0)), ((146.54470719319389 -38.800277414371493 0 0, 146.6555556810448 -38.769721513828735 0 0, 146.46774141800117 -38.758051435833842 0 0, 146.54470719319389 -38.800277414371493 0 0)), ((146.39413797014558 -39.147224327614396 0 0, 146.22302436828613 -38.712501525878906 0 0, 146.87356758117676 -38.651664733886719 0 0, 147.76443672180176 -37.979995727539062 0 0, 149.97384834289551 -37.520553588867187 0 0, 150.16247749328613 -35.9405517578125 0 0, 150.84051704406738 -35.082778930664062 0 0, 151.45276069641113 -33.319168090820313 0 0, 152.52969551086426 -32.403610229492188 0 0, 153.05246162414551 -31.034999847412109 0 0, 153.62552070617676 -28.666664123535156 0 0, 153.03387641906738 -27.179443359375 0 0, 153.18191719055176 -25.949443817138672 0 0, 151.93719673156738 -24.221664428710937 0 0, 151.68386860690848 -23.988885124293361 0 0, 151.53857528543747 -24.089169070202537 0 0, 150.86718940734863 -23.505001068115234 0 0, 150.63721115484631 -22.343892085317279 0 0, 150.5930191594698 -22.586113195593157 0 0, 150.03415023579419 -22.14972276377463 0 0, 150.0433086131149 -22.65083424839732 0 0, 149.66110420227051 -22.496665954589844 0 0, 149.214693069458 -21.080001831054687 0 0, 148.69165229797363 -20.624443054199219 0 0, 148.93551826477051 -20.532779693603516 0 0, 148.77746772766113 -20.232498168945313 0 0, 146.27527046203613 -18.883888244628906 0 0, 146.33691596984863 -18.536666870117188 0 0, 146.00943183898926 -18.238052368164063 0 0, 146.10663032531738 -17.683609008789063 0 0, 145.878023147583 -17.062778472900391 0 0, 145.96051216125488 -16.895278930664062 0 0, 145.40359687805176 -16.46221923828125 0 0, 145.31579780578613 -14.945554733276367 0 0, 144.51053016592917 -14.168332225088305 0 0, 143.78219880977309 -14.413331725550767 0 0, 143.42914009094238 -12.614444732666016 0 0, 142.51275718820716 -10.866944001784249 0 0, 142.141939163208 -10.956666946411133 0 0, 142.02887153625488 -12.062221527099609 0 0, 141.58941701432573 -12.54694509782982 0 0, 141.75247374911061 -12.467221862554922 0 0, 141.94079908655456 -12.875831363375758 0 0, 141.79858636358435 -12.685833278929504 0 0, 141.58386421203613 -12.989166259765625 0 0, 141.42691230773926 -16.079166412353516 0 0, 140.49496686641297 -17.640836270001238 0 0, 139.26053047180176 -17.342498779296875 0 0, 139.01748847961426 -16.903053283691406 0 0, 138.19024848937988 -16.704998016357422 0 0, 137.730806350708 -16.246109008789063 0 0, 136.76581001281738 -15.904445648193359 0 0, 135.456636428833 -14.941110610961914 0 0, 136.02026557922363 -13.762500762939453 0 0, 135.92386054992676 -13.281389236450195 0 0, 136.35052756406569 -13.052498873772986 0 0, 136.45800792461543 -13.252502246682353 0 0, 136.49441719055176 -12.779167175292969 0 0, 136.98190498352051 -12.356943130493164 0 0, 136.56219670087702 -11.934443415097583 0 0, 136.039706014915 -12.471666950350643 0 0, 136.02304177978024 -12.111944726235064 0 0, 135.66970869105407 -12.196667944831466 0 0, 135.90802252236969 -11.763054911483019 0 0, 135.23135469675944 -12.294444203175727 0 0, 133.18304634094238 -11.716665267944336 0 0, 132.33746563760562 -11.129164860148009 0 0, 132.14331245422363 -11.133333206176758 0 0, 132.1997064253863 -11.412779820500775 0 0, 131.97995137816318 -11.125555445422931 0 0, 131.766939163208 -11.316389083862305 0 0, 132.09246960649557 -11.526393142533131 0 0, 132.48996140617371 -11.476944828510566 0 0, 132.69107246398926 -11.655000686645508 0 0, 132.62774848937988 -12.04194450378418 0 0, 132.75442695617676 -12.132776260375977 0 0, 132.38360833015909 -12.380000805546063 0 0, 131.02719298142043 -12.145552275687578 0 0, 130.89636430553702 -12.640276501765177 0 0, 130.58304207184068 -12.395278279457461 0 0, 130.69525337219238 -12.702499389648438 0 0, 130.14081001281738 -12.924167633056641 0 0, 130.26443672180176 -13.325277328491211 0 0, 129.88611030578613 -13.445278167724609 0 0, 129.37024116516113 -14.333332061767578 0 0, 129.944429397583 -14.767778396606445 0 0, 129.64386177062988 -14.837221145629883 0 0, 129.73245452202542 -15.197224363669605 0 0, 128.53027781576023 -14.759167127607377 0 0, 128.38189888000488 -14.802499771118164 0 0, 128.45245552062988 -15.046388626098633 0 0, 128.35080155284237 -15.044722571380984 0 0, 128.31497388472525 -14.907778667038853 0 0, 128.19412422180176 -15.056667327880859 0 0, 128.28997821293615 -15.405278886266183 0 0, 128.12924348931088 -15.212887914733944 0 0, 128.01361384439417 -15.500000565317706 0 0, 128.16943550109863 -14.702777862548828 0 0, 127.42082405090332 -13.949722290039063 0 0, 126.86276545665483 -13.749163620464435 0 0, 126.28777320737539 -14.23305420605328 0 0, 126.01944004668192 -13.919166667050977 0 0, 126.03720350632447 -14.516666603515048 0 0, 125.71887285614612 -14.400277625629528 0 0, 125.63720397376014 -14.635000043208054 0 0, 125.60693476018652 -14.223054173343874 0 0, 125.5888843536377 -14.549444198608398 0 0, 125.13553810119629 -14.741388320922852 0 0, 125.43332099914551 -15.138612747192383 0 0, 124.82554817199707 -15.155834197998047 0 0, 125.18181055789978 -15.520686904866736 0 0, 124.70221480415606 -15.25249875875776 0 0, 124.65637397766113 -15.479721069335938 0 0, 124.4477481842041 -15.486387252807617 0 0, 124.40138368411567 -15.87110868084341 0 0, 124.72831844323542 -15.810557095137673 0 0, 124.40054512023926 -16.329444885253906 0 0, 124.8994312286377 -16.415000915527344 0 0, 124.23858711885708 -16.406664733502186 0 0, 123.96443336082635 -16.245550755692527 0 0, 123.89137565412349 -16.378885200908659 0 0, 123.72943331619985 -16.138886940641942 0 0, 123.5697193145752 -16.171943664550781 0 0, 123.7088794708252 -16.430278778076172 0 0, 123.42442512512207 -16.50250244140625 0 0, 123.91832078544115 -17.204445871767259 0 0, 123.59665187625315 -16.989996759681571 0 0, 123.57527557853381 -17.597498517786402 0 0, 122.9177665860974 -16.415554645249511 0 0, 122.17498970031738 -17.243331909179688 0 0, 122.35331916809082 -18.105554580688477 0 0, 121.0274829864502 -19.59222412109375 0 0, 119.08249092102051 -19.967498779296875 0 0, 117.68802833557129 -20.675830841064453 0 0, 116.70749092102051 -20.649166107177734 0 0, 114.65109443664551 -21.840000152587891 0 0, 114.15387002036023 -22.5277802927546 0 0, 114.01805350547266 -21.850832433405909 0 0, 113.38971138000488 -24.429443359375 0 0, 114.25916481018066 -25.846668243408203 0 0, 114.22083473205566 -26.306388854980469 0 0, 114.06609725307889 -26.461946439913941 0 0, 113.87886254698807 -26.02888869078464 0 0, 113.71193267194985 -26.1952785726623 0 0, 113.46943787651756 -25.54083654334584 0 0, 113.39166450500488 -25.718055725097656 0 0, 113.85582160949707 -26.507503509521484 0 0, 113.6413753895463 -26.65277930380239 0 0, 113.27916272592661 -26.018334512903238 0 0, 113.22442817687988 -26.239166259765625 0 0, 114.88553810119629 -29.20111083984375 0 0, 115.04553413391113 -30.506946563720703 0 0, 115.73943519592285 -31.865276336669922 0 0, 115.71111488342285 -33.269996643066406 0 0, 115.36360316542896 -33.63305721340376 0 0, 114.98872585336119 -33.522535258339474 0 0, 115.00804328918457 -34.2630615234375 0 0, 116.46331962559016 -35.000840065441132 0 0, 117.93441963195801 -35.125831604003906 0 0, 120.00499153137207 -33.928886413574219 0 0, 123.54081916809082 -33.905830383300781 0 0, 124.28193855285645 -32.985557556152344 0 0, 125.96832466125488 -32.26611328125 0 0, 127.26776313781738 -32.278335571289063 0 0, 128.98745918273926 -31.694164276123047 0 0, 131.15219290779297 -31.474166183490379 0 0, 132.20385865529394 -32.031669337856883 0 0, 132.76443780676738 -31.950830522235343 0 0, 133.41720800373201 -32.213330746871165 0 0, 133.62219288541149 -32.098335680615321 0 0, 133.95218086242676 -32.394721984863281 0 0, 133.85190191405118 -32.545008019828536 0 0, 134.18414386779202 -32.486662236228717 0 0, 134.269136428833 -33.159721374511719 0 0, 134.70913887023926 -33.179168701171875 0 0, 135.50692951906677 -34.618057273247082 0 0, 135.10580711100567 -34.599997248967213 0 0, 135.95413207946217 -35.010285743416418 0 0, 135.93274116516113 -34.541946411132812 0 0, 137.20996284484863 -33.666107177734375 0 0, 137.77832221984863 -32.992500305175781 0 0, 137.77331998622793 -32.52361355444296 0 0, 137.95025825500488 -33.559440612792969 0 0, 137.44998359680176 -34.151390075683594 0 0, 137.43774785420499 -34.93472307757083 0 0, 137.01999292948267 -34.901109636303083 0 0, 136.83053845486003 -35.2547240184697 0 0, 137.74469184875488 -35.13861083984375 0 0, 138.09802403597763 -34.1372195629134 0 0, 138.51416206359863 -35.028053283691406 0 0, 138.09302039322429 -35.620834151046481 0 0, 139.35522852791178 -35.372497008643208 0 0, 139.33718743163192 -35.694159000222179 0 0, 139.09674240511379 -35.615885021202068 0 0, 139.667755265224 -36.23082902400472 0 0, 139.07734782880104 -35.682632651780182 0 0, 139.82052803039551 -36.54583740234375 0 0, 139.81442451477051 -37.299728393554688 0 0, 140.52997016906738 -38.000282287597656 0 0, 141.57134923223381 -38.417220962792918 0 0, 142.38888656828371 -38.36472181344341 0 0, 143.54385352439752 -38.860002381583328 0 0, 144.92913714730238 -37.869162177772075 0 0, 145.13244819641113 -38.140281677246094 0 0, 144.898868643113 -38.5044493648489 0 0, 145.44134685983246 -38.226104182783828 0 0, 145.55719184875488 -38.376388549804687 0 0, 145.41607856750488 -38.54583740234375 0 0, 146.39413797014558 -39.147224327614396 0 0)), ((147.29998419057063 -39.4822215332521 0 0, 147.31553161691158 -39.431942955319826 0 0, 147.28525346568057 -39.473886107980611 0 0, 147.29998419057063 -39.4822215332521 0 0)), ((147.3535769314577 -39.499440037667092 0 0, 147.34441891356502 -39.448051385612352 0 0, 147.30746097078668 -39.487503228666156 0 0, 147.3535769314577 -39.499440037667092 0 0)), ((143.95773655604987 -40.109997254457028 0 0, 144.147495201935 -39.927219788202372 0 0, 143.97301512179254 -39.573331502435224 0 0, 143.95773655604987 -40.109997254457028 0 0)), ((144.941346070043 -40.458894339779654 0 0, 144.9497067699578 -40.38722630703333 0 0, 144.83218026136737 -40.433607153695881 0 0, 144.941346070043 -40.458894339779654 0 0)), ((148.17746293801488 -40.256947214044949 0 0, 148.27942085266113 -39.965835571289063 0 0, 147.88189188909539 -39.754175031491535 0 0, 148.17746293801488 -40.256947214044949 0 0)), ((144.75555443850496 -40.59665351605247 0 0, 144.77969627356541 -40.409438863823254 0 0, 144.71746391043132 -40.503891726243 0 0, 144.75555443850496 -40.59665351605247 0 0)), ((144.92637076435537 -40.722497278248156 0 0, 145.01608294808233 -40.695549911716689 0 0, 144.92414868270754 -40.615279868500906 0 0, 144.92637076435537 -40.722497278248156 0 0)), ((148.4758059433241 -40.443880914842104 0 0, 148.35467426387137 -40.31554825265539 0 0, 147.99355701774715 -40.420554923006776 0 0, 148.4758059433241 -40.443880914842104 0 0)), ((148.20026394641658 -40.594156851767387 0 0, 148.21578522899907 -40.50194399702152 0 0, 148.09720350133347 -40.533885882922341 0 0, 148.20026394641658 -40.594156851767387 0 0)), ((148.33331344839124 -42.358891858131365 0 0, 148.31912422180176 -42.312774658203125 0 0, 148.23108492964241 -42.301940809133235 0 0, 148.33331344839124 -42.358891858131365 0 0)), ((148.01416194609055 -42.753060055529183 0 0, 148.17053568174759 -42.663888635629789 0 0, 148.02109160086539 -42.618053452485825 0 0, 148.01416194609055 -42.753060055529183 0 0)), ((147.42970039994486 -43.253613266845733 0 0, 147.35995502796564 -43.073615471667708 0 0, 147.29192562172349 -43.164445472068657 0 0, 147.42970039994486 -43.253613266845733 0 0)), ((147.12301034099974 -43.421939716675105 0 0, 147.3627296865584 -43.398055121497151 0 0, 147.29193443465465 -43.261951732547793 0 0, 147.12301034099974 -43.421939716675105 0 0)), ((146.91669343485896 -43.617847237284849 0 0, 147.31747431974762 -42.846673876741775 0 0, 147.99524500985157 -43.229715832025612 0 0, 147.84301948547363 -42.86944580078125 0 0, 148.18386114165179 -41.943050595021333 0 0, 148.36551904768024 -42.215007704049405 0 0, 148.22329708170315 -40.850838912953762 0 0, 146.58609225891121 -41.186664834823823 0 0, 145.10772895812988 -40.821670532226563 0 0, 144.70135551019939 -40.759167292599614 0 0, 144.682466506958 -41.22332763671875 0 0, 145.55191153026485 -42.344167939145848 0 0, 145.20523135685349 -42.256949931408464 0 0, 145.496919631958 -42.957778930664063 0 0, 146.2363620371512 -43.32722477366913 0 0, 146.038293170735 -43.4980554866261 0 0, 146.91669343485896 -43.617847237284849 0 0)), ((158.83331663555131 -54.749720126761126 0 0, 158.88216923099378 -54.711391235985225 0 0, 158.95745624621341 -54.474714167748139 0 0, 158.83331663555131 -54.749720126761126 0 0)))', 4326);

declare @FIR TABLE (FIR nvarchar(100), shape geography)

insert into @FIR select 'YMMM', geography::STGeomFromText('POLYGON ((150 -44.9997260537555, 163 -44.9997260537555, 163 -17.6663927537555, 161.25 -13.9997260537554, 155 -13.9997260537554, 155 -11.9997260537555, 144 -11.9997260537555, 144.0666667 -11.7163927537555, 144.0333333 -11.4997260537555, 144.0333 -11.4997260537555, 144.0833 -9.94972605375545, 144.2333333 -9.39972605375539, 143.8666667 -9.13305935375541, 142.8 -9.31639275375544, 142.05 -9.26639275375532, 141.000001415928 -9.62797437297196, 141 -9.83305935375546, 139.6666667 -9.83305935375546, 135 -6.99972605375548, 126.8333333 -9.33305935375541, 123.3333333 -11.9997260537555, 114.5 -12, 115.008888888889 -14.00361111, 118.3666667 -17.8830593537555, 118.4166667 -18.3830593537555, 118.685706842077 -18.3679386135347, 118.954446891404 -18.3876875558113, 119.2183863899 -18.4419754547144, 119.473105271957 -18.5298931773698, 119.71433788512 -18.6499684087042, 119.938044424877 -18.8001903075844, 120.140478587292 -18.9780431813896, 120.31825030652 -19.1805486150854, 120.468382526581 -19.4043153492817, 120.588361056664 -19.6455960719943, 120.676176675042 -19.9003501730455, 120.730358776533 -20.1643114101835, 120.75 -20.4330593537555, 122.0166667 -21.5497260537555, 122.014884777466 -21.5478109353692, 122.094800028916 -21.484592748599, 122.192782523398 -21.4566210248782, 122.29402834071 -21.4681219391791, 122.383240513691 -21.517357848278, 122.4469402072 -21.59688982675, 122.475503200252 -21.6947015948601, 122.464613984528 -21.7960150270089, 122.417070398278 -21.8834043322974, 124.25 -23.4497260537554, 126.4833333 -23.3663927537554, 128.4666667 -23.2163927537555, 130.9666667 -21.8497260537555, 131.5666667 -21.1830593537555, 136.15 -21.4663927537555, 136.6333333 -22.2997260537554, 138.3833333 -26.2330593537555, 143.5 -28.9997260537555, 146.5333333 -28.9997260537555, 148.8333333 -32.0997260537555, 150.55 -33.4497260537555, 150.7 -33.3330593537554, 151.2333333 -33.1997260537554, 151.4333333 -33.2497260537554, 151.8 -33.4663927537555, 151.9 -33.5830593537555, 152.0166667 -33.9830593537555, 151.6666667 -34.3497260537555, 152.9333333 -35.3163927537555, 152.829216394514 -35.5882242475943, 152.692007952474 -35.8449469277062, 152.523850848166 -36.0825513875162, 152.327371301446 -36.2973268022429, 152.105637862336 -36.4859188832835, 151.862113487473 -36.6453822643414, 151.600601456837 -36.7732265011433, 151.325185975433 -36.8674549663426, 151.040168387582 -36.9265960321591, 150.75 -36.9497260537554, 150.3166667 -38.1830593537555, 151 -42.9997260537554, 150.6666667 -43.8497260537555, 150 -44.5663927537555, 150 -44.9997260537555))', 4326);

insert into @FIR select 'YBBB', geography::STGeomFromText('POLYGON ((75.0000000000001 -90, 75.4971751412436 -90, 75.9943502824866 -90, 76.4915254237295 -90, 76.9887005649724 -90, 77.4858757062154 -90, 77.9830508474583 -90, 78.4802259887012 -90, 78.9774011299442 -90, 79.4745762711871 -90, 79.9717514124301 -90, 80.468926553673 -90, 80.9661016949159 -90, 81.4632768361589 -90, 81.9604519774018 -90, 82.4576271186447 -90, 82.9548022598877 -90, 83.4519774011306 -90, 83.9491525423735 -90, 84.4463276836165 -90, 84.9435028248594 -90, 85.4406779661023 -90, 85.9378531073452 -90, 86.4350282485882 -90, 86.9322033898311 -90, 87.4293785310741 -90, 87.926553672317 -90, 88.4237288135599 -90, 88.9209039548029 -90, 89.4180790960458 -90, 89.9152542372888 -90, 90.4124293785317 -90, 90.9096045197746 -90, 91.4067796610176 -90, 91.9039548022605 -90, 92.4011299435034 -90, 92.8983050847463 -90, 93.3954802259893 -90, 93.8926553672322 -90, 94.3898305084751 -90, 94.8870056497181 -90, 95.384180790961 -90, 95.8813559322039 -90, 96.3785310734469 -90, 96.8757062146898 -90, 97.3728813559327 -90, 97.8700564971757 -90, 98.3672316384186 -90, 98.8644067796616 -90, 99.3615819209045 -90, 99.8587570621474 -90, 100.35593220339 -90, 100.853107344633 -90, 101.350282485876 -90, 101.847457627119 -90, 102.344632768362 -90, 102.841807909605 -90, 103.338983050848 -90, 103.836158192091 -90, 104.333333333334 -90, 104.830508474577 -90, 105.32768361582 -90, 105.824858757063 -90, 106.322033898306 -90, 106.819209039548 -90, 107.316384180791 -90, 107.813559322034 -90, 108.310734463277 -90, 108.80790960452 -90, 109.305084745763 -90, 109.802259887006 -90, 110.299435028249 -90, 110.796610169492 -90, 111.293785310735 -90, 111.790960451978 -90, 112.288135593221 -90, 112.785310734464 -90, 113.282485875707 -90, 113.77966101695 -90, 114.276836158193 -90, 114.774011299435 -90, 115.271186440678 -90, 115.768361581921 -90, 116.265536723164 -90, 116.762711864407 -90, 117.25988700565 -90, 117.757062146893 -90, 118.254237288136 -90, 118.751412429379 -90, 119.248587570622 -90, 119.745762711865 -90, 120.242937853108 -90, 120.740112994351 -90, 121.237288135594 -90, 121.734463276837 -90, 122.231638418079 -90, 122.728813559322 -90, 123.225988700565 -90, 123.723163841808 -90, 124.220338983051 -90, 124.717514124294 -90, 125.214689265537 -90, 125.71186440678 -90, 126.209039548023 -90, 126.706214689266 -90, 127.203389830509 -90, 127.700564971752 -90, 128.197740112995 -90, 128.694915254238 -90, 129.192090395481 -90, 129.689265536723 -90, 130.186440677966 -90, 130.683615819209 -90, 131.180790960452 -90, 131.677966101695 -90, 132.175141242938 -90, 132.672316384181 -90, 133.169491525424 -90, 133.666666666667 -90, 134.16384180791 -90, 134.661016949153 -90, 135.158192090396 -90, 135.655367231639 -90, 136.152542372882 -90, 136.649717514125 -90, 137.146892655367 -90, 137.64406779661 -90, 138.141242937853 -90, 138.638418079096 -90, 139.135593220339 -90, 139.632768361582 -90, 140.129943502825 -90, 140.627118644068 -90, 141.124293785311 -90, 141.621468926554 -90, 142.118644067797 -90, 142.61581920904 -90, 143.112994350283 -90, 143.610169491526 -90, 144.107344632769 -90, 144.604519774011 -90, 145.101694915254 -90, 145.598870056497 -90, 146.09604519774 -90, 146.593220338983 -90, 147.090395480226 -90, 147.587570621469 -90, 148.084745762712 -90, 148.581920903955 -90, 149.079096045198 -90, 149.576271186441 -90, 150.073446327684 -90, 150.570621468927 -90, 151.06779661017 -90, 151.564971751413 -90, 152.062146892656 -90, 152.559322033898 -90, 153.056497175141 -90, 153.553672316384 -90, 154.050847457627 -90, 154.54802259887 -90, 155.045197740113 -90, 155.542372881356 -90, 156.039548022599 -90, 156.536723163842 -90, 157.033898305085 -90, 157.531073446328 -90, 158.028248587571 -90, 158.525423728814 -90, 159.022598870057 -90, 159.5197740113 -90, 160.016949152542 -90, 160.514124293785 -90, 161.011299435028 -90, 161.508474576271 -90, 162.005649717514 -90, 162.502824858757 -90, 163 -90, 163 -45, 150 -45, 150 -44.5666669999999, 150.666667 -43.85, 151 -43, 150.316667 -38.183333, 150.75 -36.9499999999999, 151.040168387582 -36.9268699784037, 151.325185975433 -36.8677289125872, 151.600601456837 -36.7735004473878, 151.862113487473 -36.6456562105859, 152.105637862336 -36.4861928295281, 152.327371301446 -36.2976007484874, 152.523850848166 -36.0828253337608, 152.692007952474 -35.8452208739507, 152.829216394514 -35.5884981938389, 152.93333315 -35.31666685, 151.666667 -34.35, 152.016667 -33.983333, 151.9 -33.583333, 151.8 -33.466667, 151.433333 -33.2499999999999, 151.233333 -33.2, 150.7 -33.3333329999999, 150.55 -33.45, 148.833333 -32.1, 146.533333 -29, 143.5 -29, 138.383333 -26.233333, 136.633333 -22.3, 136.15 -21.466667, 131.566667 -21.183333, 130.966667 -21.85, 128.466667 -23.216667, 126.483333 -23.366667, 124.25 -23.4499999999999, 122.417070398278 -21.8836782785419, 122.464613984528 -21.7962889732535, 122.475503200252 -21.6949755411046, 122.4469402072 -21.5971637729946, 122.383240513691 -21.5176317945225, 122.29402834071 -21.4683958854237, 122.192782523398 -21.4568949711228, 122.094800028916 -21.4848666948436, 122.016975654239 -21.5464308614369, 122.016667 -21.55, 120.75 -20.4333329999999, 120.730358776533 -20.1645853564281, 120.676176675042 -19.90062411929, 120.588361056664 -19.6458700182389, 120.468382526581 -19.4045892955263, 120.31825030652 -19.1808225613299, 120.140478587292 -18.9783171276342, 119.938044424877 -18.8004642538289, 119.71433788512 -18.6502423549488, 119.473105271957 -18.5301671236144, 119.2183863899 -18.4422494009589, 118.954446891404 -18.3879615020559, 118.685706842077 -18.3682125597792, 118.4166667 -18.3833333, 118.41667 -18.3833, 118.3666667 -17.8833333, 115.008888888889 -14.00361111, 114.5 -12, 107 -12, 92.0000000000001 -1.99999999999994, 78.0000000000001 -1.99999999999994, 75.0000000000001 -5.99999999999994, 75.0000000000001 -90))', 4326);

Geospatial Queries

We now have all of the sample data that we need and we can start the fun part!  Let's test some interesting queries that we can now do with our geospatial data using Microsoft SQL Server.

Which FIR Boundaries require a permit filing for this Route?


select @route.STDifference(shape).STIntersection(shape) from @FIR where [FIR] in ('YMMM', 'YBBB')
We get the result of an extremely simple geospatial query now that makes our life so much simpler now that the hard work has been put into the data.  This means that the YMMM and YBBB FIR boundaries need to have permits filed for this particular route.

We can plot this with the use of a Visualizer table in the SQL Enterprise Manager:

declare @Visualizer TABLE (shape geography)
insert into @Visualizer select @route
insert into @Visualizer select shape from @Country where name='Australia'
insert into @Visualizer select shape from @FIR where [FIR] in ('YMMM','YBBB')
select * from @Visualizer
Now we can see our results with the route overlaid on the country and FIR boundary geospatial objects

Where did we specifically intersect the FIR boundaries?

This is also a very valid question which we can ask geospatially from our objects:

select @route.STDifference(shape).STIntersection(shape) from @FIR where [FIR] in ('YMMM', 'YBBB')
See the geospatial output of our use of the STDifference and STIntersection methods


We can also use the ToString() method to distill from the objects what the actual Longitude / Latitudes of each intersection point is.

select @route.STDifference(shape).STIntersection(shape).ToString() from @FIR where [FIR] in ('YMMM', 'YBBB')

MULTIPOINT ((138.19591735759164 -25.824510281745816), (135.79425900665544 -21.448890882698397), (131.07476561455346 -21.730227054732104), (122.43279515998726 -21.897081726878927))
MULTIPOINT ((131.07502400305512 -21.730215326242416), (122.43247767877105 -21.897079571616668))
Now you know the precise points where the aircraft is intruding on the airspace perimeter and knowledge of which permits need to be filed (or at least one indicator of possible permit filing requirements.