Microsoft Sql Server

Image

FIR Boundary Intersection using Spatial Data and Flight Routes

March 8, 2014 at 10:03 PMJared Nielsen

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)

Plot of Private Jet Route by Jared Nielsen in SQL Server Geographic Datatype

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:
Country Boundaries of Australia from ESRI ShapeFile by Jared NielsenYMMM and YBBB FIR Boundaries in Microsoft SQL Server by Jared Nielsen
 
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')

FIR Boundary ResultsWe 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
 

Visualization of Route Across FIR Boundaries Using SQL Server Datatype by Jared Nielsen

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
 

FIR Boundary Intersection Points Calculated Using SQL Server Geography Datatype by Jared Nielsen

 
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.

Microsoft Geography Coordinates Use Long/Lat

March 4, 2014 at 7:03 AMJared Nielsen
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.