NielsenData.com

NielsenData Blog by Jared Nielsen

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.
Comments are closed