NielsenData.com

NielsenData Blog by Jared Nielsen

Milliman.com - Retirement Planning Consulting Firm

11. March 2014 Nielsendata Customer Stories (0)

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.

e-Barnett.com Wholesale Distributor of Electrical and Plumbing Products

10. March 2014 Jared Nielsen Customer Stories (0)
I had the privilege of working with Barnett Brass and Copper as they were progressing beyond a dialup bulletin board system (BBS) for processing electronic orders for their network of 65 warehouse locations and tens of thousand plumbers and electricians.  It was a great leap of faith on their part to partner with my nascent startup Innovative Hardware, Inc. which focused on helping companies upgrade mainframe or JD Edwards database systems into more modern SQL Server based systems that could scale to meet their volume requirements.  

Brick and Mortar distribution companies need the benefits of solid e-Commerce technologies and Barnett was a visionary at a very early time in the late nineties.  They selected me to develop five online storefronts including their Plumbing, Gas Supply, Electrical, Maintenance Products and a Spanish language website to help them service their Puerto Rico operations.

I had the distinct privilege to work directly with Donna Barrow in their data management team who similarly was an astonishingly great mentor and leader as I brought some advanced technologies and she blended that with key business goals and objectives.  I learned so much from her as she taught me the inner workings of large scale distribution companies and the home construction product segment.

When we started, their initial location was a relatively old warehouse with burglar bars and chipped paint on Lenox Avenue on the north side of Jacksonville.  With around $600mm in revenue, they were a solid player in the home construction and professional contractor supply area.  They had recently gone public as BNTT:NASDAQ and were prepared for growth.

As we began however, they didn't have the budget allocated to finance such a significant project at that particular time of the fiscal year.  I was eager to demonstrate what we could accomplish in a short period of time so I took a leap and proposed that our company could help them self-fund the project.  We began work immediately by working with their Manufacturing suppliers to present a very compelling partnership with 10 of their top suppliers.  By launching brand new websites without having to wait for the fiscal year to come around, we were able to offer top placements to each sponsoring manufacturer with the top five listings in each product category.  

This negotiation with the manufacturers paid off as 12 additional manufacturer sponsors came online, each for $50,000 apiece.  This helped us raise $1.2mm in cash capital to finance the project on behalf of Barnett.  They were ecstatic at the speed of ramping up the budget and we were able to deploy.   We continued with them for two years until they decided to go private and were subsequently rolled up in a reverse merger with Wilmar.   Now they belong to an extremely large conglomerate called Interline Brands and are worth over $2 billion dollars.

I was humbled and pleased to have been part of such a large and successful project.

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

8. March 2014 Nielsendata Aviation, SQL Server (0)
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.

Aviation Technology #2 - Great Circle Routes using Geospatial Data

4. March 2014 Jared Nielsen Aviation, SQL Server (0)
It turns out that planets aren't flat and there are no dragons beyond the margin of the page.  This means that you can't simply "connect the dots" on a globe that is warped into a two dimensional (2D) projection.  Mapping technologies have may projection types but for our purposes we are going to base our conversation on the EPSG Projection 4326 which is commonly used to create a nice rectangular shape for a map (highly popular with GPS navigation systems).  Setting the projection type is critical to your code because you will get strange "offsets" in your data if you keep them all on the same drawing surface size and shape.  In our previous example for Aviation Technology - #1 we used this projection type in the following conversion from textual data to a projection based geography point object:

DECLARE @StartPoint geography
SELECT @StartPoint = geography::STPointFromText('POINT(148.47539133747426 -20.031355678341452)', 4326)
Now we are going to use our knowledge of great circle routes to extend our point above to a connected "Route" as an aircraft would fly.  

DECLARE @StartPoint geography, @EndPoint geography, @Route geography
SELECT @StartPoint = geography::STPointFromText('POINT(148.47539133747426 -20.031355678341452)', 4326)
SELECT @EndPoint = geography::STPointFromText('POINT(115.45019875220305 -21.382500474560892)', 4326)
SELECT @Route = geography::Parse(geometry::Parse(@StartPoint.STUnion(@EndPoint).ToString()).STConvexHull().ToString())
SELECT @route

The ability to create a Great Circle Route is necessary to begin true geospatial calculations for Aviation using Microsoft SQL Server and the Geography datatype.  Next we are going to explore the use of the geography datatype to identify FIR Boundary Intersections.

Aviation Technology - #1 - Microsoft Geography Coordinates Use Long/Lat

4. March 2014 Jared Nielsen Mind Tricks for Business, Aviation, SQL Server (0)
Microsoft SQL Server, Postgres and Oracle are all database platforms that support the Geography datatype and enable developers with the benefits of spatial database systems.  This article will explore the use of spatial database query leveraging geography objects which are constructed from samples of latitude and longitude coordinate pairs.

(X, Y) = (Long, Lat) - not Lat/Long

One of the first demystifiers is to understand how Microsoft has implemented the typical concept of "Lat/Long" or Latitude and Longitude.
The Globe is mapped into a grid structure and while we are generally used to referring to Latitude and Longitude, Microsoft has adopted an "X/Y" approach to the order of terms.  Where we would normally use latitude before longitude, Microsoft functions take their parameters with Longitude first and Latitude second.  In the globe to the right you will see the vertical bars as longitudes or "X" coordinates and the horizontal bars as latitudes or "Y" coordinates.  As an example you can convert a text coordinate pair to a geospatial point as follows:

DECLARE @StartPoint geography
SELECT @StartPoint = geography::STPointFromText('POINT(148.47539133747426 -20.031355678341452)', 4326)
You will see that we're plotting a point near Australia and the X value = the Longitude value.  This is important to consider as you troubleshoot your geospatial queries and discover strange bugs where your geography objects get transposed and show up near the south pole somewhere.