I'm trying to get the SQL MakeValid() function to work on a geography type but am getting the following error:
Could not find method 'MakeValid' for type 'Microsoft.SqlServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types'
Code:
Declare @geog as geography
SET @geog = geography::STGeomFromText('POLYGON ((-2.424322583649551 51.766039463818529, -2.4241488183228088 51.766052678841731, -2.424194397230818 51.766277292054326, -2.4241958884279757 51.7662817822255, -2.4242257124338082 51.76637158564435, -2.4242555365589946 51.766461389054, -2.4242555449925356 51.766462288134591, -2.4243299371274967 51.766668815007293, -2.4244053001611516 51.766978736082308, -2.4244038426664365 51.7669778422364, -2.4244175760163276 51.767051519724667, -2.4243913430782484 51.767190077575506, -2.4243913683876293 51.7671927748169, -2.4243781431692644 51.767327689229205, -2.4243781769142241 51.767331285550981, -2.4243791892662987 51.767439175203613, -2.4243801172612578 51.767538074050073, -2.4243809608976541 51.767627982090858, -2.4243810115159774 51.767633376573258, -2.4243963459761644 51.767723232267969, -2.4243978288050743 51.767726823354948, -2.4244278229278633 51.767834608304675, -2.4244293057629713 51.767838199391171, -2.4245332736001965 51.768107556999041, -2.424533298918869 51.76811025423995, -2.4247696294664332 51.768585928738887, -2.424769621022036 51.768585029658695, -2.4249175730111578 51.768908174508162, -2.4249175814585922 51.7689090735883, -2.4251244980745823 51.769338099945394, -2.4251677654691819 51.76947011263762, -2.4251692399646978 51.7694728046335, -2.4252284735443466 51.76960745685237, -2.4252554289013371 51.769699967679237, -2.4252858406970681 51.769851807284134, -2.4252858237869948 51.76985000912422, -2.4253014144812144 51.769966837059236, -2.4253014313919765 51.769968635219094, -2.4253166839404194 51.770049499952826, -2.4253181500081249 51.77005129286659, -2.4253482324583846 51.770168068329284, -2.4253482409147575 51.770168967409177, -2.4254066611038954 51.7703710557556, -2.425422328418859 51.770495975381621, -2.4254228866440908 51.770555314650615, -2.4254229712238393 51.770564305448879, -2.4253970890943979 51.770585977815195, -2.4255368188761106 51.770650207758436, -2.4255799556938475 51.77061408709401, -2.4255970411727783 51.770581657227744, -2.425596787329753 51.770554684833542, -2.4255961950309053 51.7704917492466, -2.4255961611853216 51.770488152927335, -2.4255804849324023 51.770362334245533, -2.4255789850001155 51.770356945015834, -2.4255190726021096 51.770150366605414, -2.425519081061871 51.7701512656853, -2.4254890066415307 51.770035389346859, -2.4254752114021696 51.769955418467909, -2.4254752283198311 51.769957216627724, -2.4254596371804693 51.769840388716595, -2.425459620263497 51.769838590556716, -2.4254291994362367 51.769685851917778, -2.4254277164604554 51.769682260845322, -2.4253978032789933 51.769583466992707, -2.4253963203091033 51.769579875919788, -2.4253370862670929 51.769445223785887, -2.4253385607718494 51.769447915779629, -2.4252938184031327 51.769313211156479, -2.4252908778629565 51.769308726247708, -2.4250839429057875 51.768877902024286, -2.4250854004753015 51.7688787958613, -2.4249374473862861 51.768555651223927, -2.4249359898301308 51.76855475738499, -2.4247011152367284 51.7680799770635, -2.4245971633471757 51.76781241776559, -2.4245701345202448 51.767711815029131, -2.4245548250386473 51.767624656598834, -2.4245540063763364 51.7675374458012, -2.4245530780014009 51.767438546956953, -2.4245520989935962 51.767334253628441, -2.4245653405749077 51.767201137358796, -2.4245929882852737 51.7670589779118, -2.4245928616789332 51.767045491704835, -2.4245776114671176 51.766964626830521, -2.4245761539694675 51.766963732986753, -2.4245007475996729 51.766649316620367, -2.4245007054073313 51.766644821217682, -2.4244263125338019 51.766438294453849, -2.4244263209707295 51.766439193534424, -2.4243964965217972 51.7663493901685, -2.4243681634012937 51.766264076939812, -2.4243681634058651 51.766264076962344, -2.4243666721922215 51.76625958679336, -2.4243681634012937 51.766264076939812, -2.424322583649551 51.766039463818529))',4326);
set @geog = @geog.MakeValid()
The above code is not working, however, the same Makevalid() function used on a geometry type works fine:
Declare @g as geometry;
SET @g = geometry::STGeomFromText('POLYGON ((370814 207569.3, 370826 207570.7, 370823 207595.7, 370818.9 207616.2, 370818.9 207616.3, 370813.9 207639.3, 370808.9 207673.8, 370809 207673.7, 370808.1 207681.9, 370810 207697.3, 370810 207697.6, 370811 207712.6, 370811 207746.6, 370810 207756.6, 370809.9 207757, 370807.9 207769, 370807.8 207769.4, 370800.8 207799.4, 370800.8 207799.7, 370784.8 207852.7, 370784.8 207852.6, 370774.8 207888.6, 370774.8 207888.7, 370760.8 207936.5, 370757.9 207951.2, 370757.8 207951.5, 370753.8 207966.5, 370752 207976.8, 370750 207993.7, 370750 207993.5, 370749 208006.5, 370749 208006.7, 370748 208015.7, 370747.9 208015.9, 370745.9 208028.9, 370745.9 208029, 370742 208051.5, 370741 208065.4, 370741 208073, 370742.8 208075.4, 370733.2 208082.6, 370730.2 208078.6, 370729 208075, 370729 208064.6, 370730 208050.6, 370730.1 208050, 370734.1 208027, 370734.1 208027.1, 370736.1 208014.2, 370737 208005.3, 370737 208005.5, 370738 207992.5, 370738 207992.3, 370740 207975.3, 370740.1 207974.9, 370742.1 207963.9, 370742.2 207963.5, 370746.2 207948.5, 370746.1 207948.8, 370749.1 207933.8, 370749.3 207933.3, 370763.3 207885.3, 370763.2 207885.4, 370773.2 207849.4, 370773.3 207849.3, 370789.2 207796.4, 370796.2 207766.6, 370798 207755.4, 370799 207745.7, 370799 207713.4, 370798 207698.6, 370796 207682.8, 370796 207681.3, 370797 207672.3, 370797.1 207672.2, 370802.1 207637.2, 370802.1 207636.7, 370807.1 207613.7, 370807.1 207613.8, 370811.00000000012 207594.29999999941, 370811.00000000006 207594.2999999997, 370811.1 207593.8, 370811.00000000012 207594.29999999941, 370814 207569.3))',0);
set @g = @g.MakeValid()
I appreciate any help
As stated here, For SQL SERVER 2008
This is working for SQL Server 2008. After loading the shape as a geometry, use MakeValid() to correct it, then reload into a geography.
DECLARE @gt NVARCHAR(MAX)
DECLARE @gm GEOMETRY
DECLARE @gmvalid GEOMETRY
SET @gmvalid = @gm.MakeValid()
SET @gt = @gmvalid.STAsText()
IF LEFT(@gt,7 ) = 'POLYGON'
BEGIN
SET @gg = GEOGRAPHY::STPolyFromText(@gt, 4326)
END
ELSE
BEGIN
SET @gg = GEOGRAPHY::STMPolyFromText(@gt, 4326)
END