Geography Datatype

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.

Great Circle Routes using Geospatial Data

March 4, 2014 at 7:03 AMJared Nielsen

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 - #1we 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.

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.