Wednesday, June 16, 2010

Some Query Regarding Spatial Data

DECLARE @g geography;
SET @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
SELECT @g as geo, @g.STArea() as Area, 'Blue' as [Color], 10 as [Thickness];

DECLARE @g geography;
SET @g = geography:: STGeomFromText('POLYGON((74.53125000000001 28.613459424004418, 75.05859375 17.560246503294912, 83.40820312500001 18.562947442888283, 83.84765624999998 27.527758206861872, 79.8046875 25.482951175355335, 74.53125000000001 28.613459424004418))', 4326)
SELECT @g as indiatest, 'black' as [Color], 1 as [Thickness];;

DECLARE @a geography;
SET @a = geography:: STGeomFromText('
POLYGON((61.17187500000001 29.840643899834415, 66.357421875 29.76437737516313, 66.62109375 30.826780904779777, 67.67578124999998 31.20340495091738, 69.08203124999998 31.503629305773003, 69.43359375000001 32.62087018318112, 70.224609375 32.99023555965106, 71.19140625000001 34.16181816123038, 71.455078125 35.88905007936091, 71.71875000000003 37.16031654673676, 71.279296875 38.34165619279594, 69.609375 37.718590325588146, 67.14843750000001 37.23032838760387, 65.0390625 37.09023980307206, 63.544921875 35.81781315869662, 61.08398437500001 35.24561909420682, 60.820312500000014 34.016241889667015, 60.732421875000014 32.472695022061494, 64.951171875 31.877557643340015, 61.17187500000001 29.840643899834415))
', 4326)
SELECT @a,@a.STArea() as AreaOfAfganisthan;

DECLARE @c geography;
SET @c = geography:: STGeomFromText('POLYGON((25.04882812499999 31.693275163494224, 25.136718750000018 21.823428188945517, 36.9140625 21.90499674823705, 35.59570312500001 23.928691516534553, 33.75000000000002 27.56931904193159, 32.431640624999986 29.423012724559165, 34.365234375 27.880518540948774, 34.980468750000014 29.499538055197778, 34.27734375000001 31.168316945375416, 32.69531250000001 30.716023241367484, 31.289062500000014 31.093083441658976, 29.091796875000014 30.640433251773864, 25.224609375000014 31.468653088536332, 25.04882812499999 31.693275163494224))', 4326)
SELECT @c as egypt, 'red' as [Color], 1 as [Thickness];

DECLARE @b geography;
SET @b = geography:: STGeomFromText('
POLYGON((-1.9335937500000142 34.99640427042621, -1.4062499999999953 32.215280296416935, -8.525390625000002 28.654602089188238, -8.525390625000002 26.944271796662917, 2.8124999999999907 19.686729292076876, 4.658203125000007 18.940236111954757, 12.128906250000002 23.606946962243896, 9.580078125000007 26.158068192666434, 9.84375 28.03578433217748, 9.931640624999996 30.109652836744534, 9.228515625000016 31.917354820822254, 7.910156249999986 33.54383701210014, 8.613281249999998 36.706008903301424, 6.15234375 36.706008903301424, 3.3398437500000093 36.63551355486138, 0 36.494329231411086, -1.23046875 35.28389270803397, -1.9335937500000142 34.99640427042621))
', 4326)
SELECT @b as algeria, 'blue' as [Color], 1 as [Thickness];


DECLARE @b geography;
SET @b = geography:: STGeomFromText('
POLYGON((-8.613281249999998 27.254629577800077, -8.569335937500001 25.958044673317847, -12.041015625000007 25.83944940206318, -12.041015625000007 23.32208001137844, -13.051757812500014 23.1605633090483, -12.963867187500014 21.371244370618306, -16.699218750000014 21.28937435586042, -16.87500000000001 21.125497636606276, -16.040039062499996 19.683970235888427, -16.215820312499993 19.145168196205283, -15.908203125 17.97873309555617, -16.43554687499999 16.13026201203474, -15.908203125 16.3833911236084, -15.205078124999987 16.3833911236084, -14.458007812500005 16.3833911236084, -13.974609374999987 16.3833911236084, -13.535156249999998 15.919073517982425, -13.271484375000005 15.707662769583493, -12.832031249999985 15.199386048560008, -12.348632812499998 14.902321826141783, -11.733398437500014 14.77488250651626, -11.249999999999996 15.453680224345847, -10.854492187500005 14.987239525774233, -10.327148437499985 15.284185114076446, -9.140624999999985 15.496032414238634, -8.129882812500012 15.326571801420842, -6.767578125000016 15.368949896534717, -5.581054687500015 15.368949896534717, -5.27343749999999 15.792253570362445, -5.537109375000016 16.46769474828897, -5.844726562500008 18.93746442964186, -6.416015624999993 24.926294766395582, -4.833984375000002 24.926294766395582, -6.811523437500015 26.11598592533351, -8.613281249999998 27.254629577800077))

', 4326)
SELECT @b as mauritania, 'blue' as [Color], 1 as [Thickness];

//linke for points with in polygon
http://social.msdn.microsoft.com/Forums/en/sqlspatial/thread/f6e57f97-3c4d-4e62-965b-ab15f51ad45e
//

//count points inside polygon
SELECT count(*)
FROM TestPolygon a, TestPoint b
WHERE b.geom.STIntersects(a.geom) = 1
//

//insert query geography mauritania
DECLARE @h geography;
SET @h = geography::STGeomFromText('
POLYGON((-8.613281249999998 27.254629577800077, -8.569335937500001 25.958044673317847, -12.041015625000007 25.83944940206318, -12.041015625000007 23.32208001137844, -13.051757812500014 23.1605633090483, -12.963867187500014 21.371244370618306, -16.699218750000014 21.28937435586042, -16.87500000000001 21.125497636606276, -16.040039062499996 19.683970235888427, -16.215820312499993 19.145168196205283, -15.908203125 17.97873309555617, -16.43554687499999 16.13026201203474, -15.908203125 16.3833911236084, -15.205078124999987 16.3833911236084, -14.458007812500005 16.3833911236084, -13.974609374999987 16.3833911236084, -13.535156249999998 15.919073517982425, -13.271484375000005 15.707662769583493, -12.832031249999985 15.199386048560008, -12.348632812499998 14.902321826141783, -11.733398437500014 14.77488250651626, -11.249999999999996 15.453680224345847, -10.854492187500005 14.987239525774233, -10.327148437499985 15.284185114076446, -9.140624999999985 15.496032414238634, -8.129882812500012 15.326571801420842, -6.767578125000016 15.368949896534717, -5.581054687500015 15.368949896534717, -5.27343749999999 15.792253570362445, -5.537109375000016 16.46769474828897, -5.844726562500008 18.93746442964186, -6.416015624999993 24.926294766395582, -4.833984375000002 24.926294766395582, -6.811523437500015 26.11598592533351, -8.613281249999998 27.254629577800077))
', 4326);
insert into Test (geog) values(@h)
///

------------------------------------------------------------------------------------------------------
DECLARE @a geography;
SET @a = geography:: STGeomFromText('
POLYGON((78.486328125 32.472695022061494, 79.18945312500001 32.43561304116276, 79.58496093750001 32.9164853473144, 79.58496093750001 33.76088200086919, 79.4091796875 34.23451236236986, 78.7939453125 35.02999636902566, 78.310546875 35.2815006578912, 77.56347656249998 35.353216101238225, 77.03613281250001 35.13787911963418, 76.59667968750001 34.813803317113155, 75.9375 34.56085936708384, 74.39941406250001 34.885930940753155, 73.8720703125 34.59704151614415, 74.13574218750003 33.90689555128868, 74.17968750000001 33.35806161277886, 73.828125 33.174341551002065, 75.23437499999998 32.175612478499325, 74.61914062500001 31.01527898171125, 74.00390625 30.25906720321302, 73.564453125 29.45873118535532, 72.7734375 28.729130483430154, 71.98242187500001 28.265682390146462, 70.79589843750001 27.722435918973456, 70.18066406249998 27.916766641249076, 69.56542968750001 26.824070780470194, 70.048828125 26.352497858154, 70.53222656250001 25.60190226111575, 71.1474609375 24.56710835257599, 69.873046875 24.20688962239801, 68.818359375 24.126701958681657, 68.33496093750001 24.006326198751114, 68.818359375 22.958393318086322, 69.697265625 22.593726063929296, 70.57617187500001 23.1605633090483, 70.18066406249998 22.350075806124863, 69.0380859375 22.024545601240312, 69.56542968750001 21.412162229725403, 70.88378906250001 20.79720143430697, 71.98242187500001 20.961439614096853, 72.37792968750001 21.739091217718563, 73.74023437499998 21.77990534252963, 72.7734375 20.756113874762068, 72.86132812500001 18.979025953255266, 72.94921875000003 18.687878686034196, 73.12500000000001 17.853290114098012, 73.6962890625 15.792253570362445, 74.26757812500001 14.519780046326085, 74.48730468750001 13.068776734357692, 75.05859375 12.511665400971018, 75.673828125 11.307707707765438, 76.5087890625 9.622414142924805, 76.64062500000001 9.10209673872643, 77.4755859375 8.10273857778318, 78.26660156250001 8.841651120809132, 79.05761718750001 9.579084335882534, 79.716796875 10.703791711680736, 79.9365234375 11.910353555774111, 80.33203125000001 13.49647276575895, 80.33203125000001 15.665354182093274, 81.29882812499998 15.961329081596634, 82.3974609375 17.056784609942543, 83.93554687500001 17.89511430374914, 85.34179687500003 19.186677697957833, 86.39648437500001 20.014645445341365, 86.74804687500001 21.453068633086772, 88.2861328125 21.493963563064458, 89.25292968750001 21.983801417384686, 90.615234375 22.30942584120018, 91.1865234375 22.87744046489713, 90.04394531250001 23.443088931121785, 90.08789062500003 24.246964554300913, 91.75781250000001 24.846565348219744, 92.1533203125 24.966140159912964, 92.021484375 25.918526162075153, 90.35156250000001 25.918526162075153, 89.38476562500001 25.878994400196202, 90.39550781250001 26.745610382199025, 92.373046875 27.137368359795584, 91.62597656250003 27.722435918973456, 89.8681640625 27.644606381943326, 87.62695312500001 27.41078570257701, 85.56152343750003 27.800209937418252, 83.6279296875 28.226970038918328, 81.95800781250001 28.613459424004418, 80.20019531249998 28.6905876542507, 80.50781250000003 29.954934549656133, 81.03515625 30.486550842588475, 79.23339843750001 31.20340495091738, 78.7939453125 31.278550858946534, 78.486328125 32.472695022061494))
', 4326)
SELECT @a as india, 'blue' as [Color], 1 as [Thickness];


DECLARE @b geography;
SET @b = geography:: STGeomFromText('
POLYGON((116.89453125000001 49.781264058178344, 114.52148437500003 50.17689812200105, 113.02734375 49.43955695894084, 110.390625 48.92249926375824, 108.10546875 49.2104204456503, 107.75390625000001 49.894634395734215, 106.34765625000001 50.28933925329178, 104.67773437499998 50.23315183247224, 103.35937499999998 50.12057809796007, 102.39257812500003 50.625073063414355, 102.04101562499998 51.124212757826875, 100.986328125 51.39920565355377, 98.96484375000001 51.944264879028765, 98.349609375 51.56341232867588, 97.998046875 51.013754657188194, 98.349609375 50.0641917366591, 97.47070312500003 49.66762782262193, 96.50390625000001 49.83798245308484, 95.36132812500001 49.83798245308484, 94.5703125 50.28933925329178, 93.251953125 50.625073063414355, 92.46093750000003 50.68079714532164, 91.0546875 50.23315183247224, 90.263671875 49.95121990866204, 89.56054687500001 49.95121990866204, 89.56054687500001 49.553725513475776, 88.85742187500001 49.553725513475776, 88.41796875 49.325121991040014, 87.451171875 49.03786794532642, 88.24218750000001 48.45835188280866, 89.033203125 47.93106634750977, 90.13183593750003 47.635783590864854, 90.70312500000001 47.21956811231548, 91.09863281249998 46.468132992155546, 91.0546875 45.67548217560646, 90.7470703125 45.367584368849776, 91.53808593750003 45.05800143539828, 92.59277343750003 44.902577996288855, 93.73535156250003 44.93369638969467, 94.6142578125 44.43377984606822, 95.31738281250001 44.08758502824516, 95.8447265625 43.3890819391175, 96.45996093750001 42.71473218539458, 97.77832031250001 42.617791432823445, 99.31640625 42.488301979602255, 100.7666015625 42.488301979602255, 101.77734375 42.42345651793832, 102.83203125000001 41.96765920367817, 104.15039062500001 41.672911819602085, 105.20507812500001 41.47566020027823, 106.87499999999998 42.2285173562085, 108.50097656250003 42.32606244456202, 109.73144531250003 42.32606244456202, 110.96191406250001 42.940339233631825, 112.06054687500003 43.644025847699496, 111.5771484375 44.08758502824516, 111.533203125 44.62175409623326, 112.54394531250001 44.995882618165446, 113.42285156250001 44.68427737181225, 114.25781249999998 44.995882618165446, 114.78515625000001 45.49094569262733, 115.70800781249997 45.39844997630408, 116.4111328125 45.82879925192133, 117.02636718750001 46.316584181822186, 117.77343749999998 46.619261036171515, 118.6083984375 46.80005944678731, 119.31152343750001 46.73986059969267, 119.53125000000003 46.468132992155546, 119.8388671875 46.73986059969267, 119.794921875 47.1897124644842, 119.35546874999997 47.338822694822, 118.56445312500004 47.78363463526376, 118.03710937499998 47.98992166741419, 117.50976562500001 47.606163043868726, 116.5869140625 47.901613541420765, 115.97167968750001 47.66538735632655, 115.62011718749998 48.07807894349861, 116.01562500000001 48.60385760823253, 116.32324218750001 48.95136647094771, 116.89453125000001 49.781264058178344))
', 4326)
SELECT @b as mangolia, 'blue' as [Color], 1 as [Thickness];

DECLARE @c geography;
SET @c = geography:: STGeomFromText('
POLYGON((32.95898437500001 -26.922069916732795, 32.10205078125001 -26.863280626766265, 32.10205078125001 -26.431228064506442, 32.08007812499999 -25.958044673317847, 32.1240234375 -25.145284610685063, 32.058105468750014 -24.327076540018634, 31.66259765624999 -23.7048945023249, 31.574707031249993 -23.443088931121785, 31.574707031249993 -23.079731762449878, 31.420898437500017 -22.41102852155869, 31.047363281250007 -22.41102852155869, 30.344238281249993 -22.41102852155869, 29.72900390625001 -22.207749178410846, 29.113769531249996 -22.2280904167845, 28.91601562500001 -22.51255695405145, 28.520507812499993 -22.59372606392932, 28.146972656249986 -22.91792293614603, 27.663574218749996 -23.281719175600035, 26.916503906249986 -23.7048945023249, 26.850585937500007 -24.347096633808512, 26.38916015625 -24.666986385216248, 25.993652343750014 -24.766784522874452, 25.708007812500003 -25.264568475331586, 25.576171875000007 -25.72073513441213, 24.916992187499993 -25.78010711842222, 24.060058593749996 -25.72073513441213, 23.48876953125001 -25.34402602913433, 23.04931640624999 -25.363882272740255, 22.74169921875 -25.918526162075153, 22.236328124999996 -26.352497858154, 21.81884765624999 -26.745610382199025, 21.291503906250003 -26.843677401113002, 20.8740234375 -26.863280626766265, 20.58837890624999 -26.824070780470204, 20.63232421874999 -26.450902223672646, 20.8740234375 -26.076520559856973, 20.7421875 -25.681137335685328, 20.43457031250001 -25.22482017676504, 20.28076171875 -24.926294766395596, 20.017089843750007 -24.786734541988878, 20.061035156250003 -28.34306490482549, 19.709472656250014 -28.536274512989916, 19.357910156249993 -28.63274679922588, 19.24804687500001 -28.94086176940554, 18.874511718750003 -28.921631282421316, 18.083496093749993 -28.863918426224565, 17.446289062499993 -28.748396571187406, 17.402343749999996 -28.497660832963475, 17.49023437499999 -28.362401735238236, 17.292480468750014 -28.265682390146477, 17.116699218749986 -28.033197847676376, 16.87500000000001 -28.05259082333983, 16.853027343749993 -28.304380682962783, 16.457519531250003 -28.652030630362262, 16.853027343749993 -29.20971322586816, 17.116699218749986 -29.688052749856776, 17.292480468750014 -30.353916372297064, 17.644042968750003 -31.052933985705163, 17.885742187500014 -31.353636941500987, 18.17138671874999 -31.672083485607402, 18.369140625 -32.17561247849935, 18.347167968749986 -32.76880048488168, 18.01757812500001 -32.84267363195431, 17.929687500000014 -32.99023555965109, 18.039550781249993 -33.247875947924385, 18.369140625 -33.46810795527896, 18.479003906250017 -33.925129700072, 18.237304687500007 -34.10725639663118, 18.369140625 -34.379712580462204, 18.61083984375001 -34.08906131584997, 18.80859374999999 -34.21634468843463, 18.764648437499993 -34.41597338448186, 19.028320312500014 -34.52466147177173, 19.40185546874999 -34.57895241036948, 19.643554687500003 -34.70549341022544, 20.061035156250003 -34.831841149828676, 20.43457031250001 -34.50655662164561, 21.027832031250014 -34.45221847282654, 21.70898437500001 -34.41597338448186, 22.126464843750014 -34.10725639663118, 22.499999999999993 -34.08906131584997, 22.807617187500014 -34.30714385628804, 23.269042968750017 -34.21634468843463, 23.48876953125001 -33.99802726234877, 23.862304687499986 -34.05265942137599, 24.41162109374999 -34.21634468843463, 24.873046874999993 -34.21634468843463, 25.026855468750007 -34.03445260967644, 25.46630859374999 -34.05265942137599, 26.037597656250007 -33.815666308702774, 26.696777343749993 -33.77914733128647, 27.31201171875001 -33.50475906922609, 28.01513671874999 -32.953368145799345, 28.652343749999993 -32.565333160841035, 29.619140625 -31.59725256170666, 30.256347656249996 -31.034108344903487, 30.695800781250014 -30.315987718557892, 30.937499999999993 -29.85970144212676, 31.728515625000003 -28.979312036722437, 32.36572265625001 -28.63274679922588, 32.38769531249999 -28.130127737874002, 32.58544921875 -27.664068965384512, 32.95898437500001 -26.922069916732795))
', 4326)
SELECT @c as sa, 'blue' as [Color], 1 as [Thickness];

DECLARE @d geography;
SET @d = geography:: STGeomFromText('
POLYGON((16.479492187499986 -28.613459424004418, 17.138671875000003 -28.226970038918342, 17.578124999999986 -28.690587654250684, 18.4130859375 -28.806173508854804, 19.20410156250001 -28.921631282421316, 19.863281249999993 -28.420391085674303, 20.17089843749999 -22.105998799750576, 21.049804687499993 -22.024545601240326, 21.137695312499993 -18.437924653474393, 22.763671875000014 -18.06231230454674, 21.533203125000017 -18.06231230454674, 19.467773437500003 -17.978733095556183, 18.45703125 -17.43451055152288, 14.985351562499991 -17.476432197195503, 13.754882812499993 -17.476432197195503, 13.403320312500001 -17.14079039331665, 12.524414062499994 -17.266727823520504, 11.777343750000014 -17.266727823520504, 11.90917968750001 -18.271086109608877, 12.788085937499987 -18.812717856407786, 13.623046874999998 -20.385825381874276, 13.886718749999991 -21.453068633086783, 14.633789062500003 -22.59372606392932, 14.545898437500007 -24.04646399966657, 14.853515624999998 -25.403584973186706, 15.292968749999987 -26.824070780470204, 15.512695312500012 -27.56672143040971, 15.600585937500008 -26.70635985763354, 16.479492187499986 -28.613459424004418))
', 4326)
SELECT @d as namibia, 'blue' as [Color], 1 as [Thickness];

No comments: