Merging valid geography polygons generate invalid polygon?

Using Microsoft SQL Server 11.0.3128 (SQL Server 2012 Express)

I have 2 valid geography polygons that can be merged with other polygons except each other. Individually both return STIsValid()=1, and when merged they ALSO return STIsValid()=1. However, Spatial Results can only display them individually. When clicking the Spatial Results tab on the merged results the following error comes up:

24144: this operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly. (Microsoft.SqlServer.Types)

Is the merged result really invalid? or is it that Spatial Results has a bug?

merge function being used:

  • geography::UnionAggregate([Geo])
  • geography::UnionAggregate([Geo].MakeValid())

Polygon A:

GEOMETRYCOLLECTION (LINESTRING (-118.28436600000003 33.708803999999844, -118.28463400000008 33.708732000000055), LINESTRING (-118.28429600000008 33.708813999999883, -118.28436600000003 33.708803999999844), LINESTRING (-118.28476500000014 33.708691999999822, -118.28483099999998 33.708677999999964), LINESTRING (-118.28363899999991 33.708910999999958, -118.28389400000013 33.708883999999962, -118.28425900000009 33.708826000000158), LINESTRING (-118.28176230499078 33.708838455359775, -118.28203200000004 33.708877999999864), POLYGON ((-118.28232021585335 33.708904222275287, -118.28225400000021 33.708900999999841, -118.28203200000004 33.708877999999864, -118.28225399999988 33.708900999999926, -118.28232021585335 33.708904222275287)), POLYGON ((-118.28168069190481 33.708824935432695, -118.28165399999966 33.708816999999904, -118.28158399999992 33.708806000000195, -118.2816540000001 33.708817000000096, -118.28168069190481 33.708824935432695)), LINESTRING (-118.28065300044184 33.708680500538492, -118.28098399999995 33.708731000000121, -118.28129199999987 33.708734999999976), POLYGON ((-118.27946299999988 33.708426999999979, -118.27941400000033 33.7084179999999, -118.27913500000021 33.708348999999991, -118.27889699999983 33.708197999999804, -118.27913499999988 33.708349000000062, -118.27941399999999 33.708417999999988, -118.27946299999988 33.708426999999979)), POLYGON ((-118.29291899999994 33.70535600000008, -118.29279400000003 33.705018000000067, -118.29263599999992 33.705070000000205, -118.29257400000017 33.705095000000085, -118.29195400000006 33.70511800000007, -118.29183799999994 33.705152999999974, -118.291719 33.705196999999735, -118.29151799999981 33.705215000000109, -118.291403 33.70518, -118.29132900000016 33.705123999999842, -118.29128599999984 33.704994000000092, -118.29127799999996 33.704968999999849, -118.29125500000021 33.704917999999957, -118.291173 33.704849000000117, -118.29115799999992 33.704855999999808, -118.29111499999991 33.704876000000183, -118.29091800000003 33.705019999999912, -118.29079500000032 33.705034999999967, -118.29069200000004 33.705112999999812, -118.29051500000017 33.705199999999977, -118.29046600000008 33.705213999999714, -118.29030900000005 33.705241000000036, -118.2902720000001 33.705229999999879, -118.29021500000003 33.705224000000143, -118.29005900000028 33.705230000000128, -118.28970100000006 33.705261000000206, -118.28961500000008 33.705293000000054, -118.28958600000027 33.705296000000018, -118.28947899999977 33.705358000000039, -118.28940500000013 33.705365000000263, -118.28876999999979 33.705349999999868, -118.28865899999998 33.705347999999788, -118.28855999999982 33.705410000000043, -118.28847600000003 33.705467, -118.28837299999982 33.705516000000287, -118.28800500000003 33.705761999999972, -118.28798199999999 33.7057770000002, -118.28795400000017 33.70579100000014, -118.28753400000004 33.70608799999998, -118.28714999999995 33.70637400000011, -118.28710200000016 33.706404000000191, -118.28701999999994 33.706442999999794, -118.28695400000001 33.706466000000148, -118.28678599999998 33.706511000000063, -118.2866989999999 33.706520999999988, -118.28664599999996 33.7065390000002, -118.28655999999988 33.706549000000081, -118.28650599999992 33.706565999999995, -118.28638699999988 33.706582999999853, -118.28611600000021 33.706658000000004, -118.28599200000002 33.706775999999856, -118.285984 33.7068100000002, -118.2859590000001 33.706837000000235, -118.28595099999994 33.706871000000127, -118.28588500000006 33.706955, -118.28587699999996 33.706989000000149, -118.28583599999989 33.707030000000053, -118.28580699999972 33.707106999999851, -118.28578199999986 33.707156999999825, -118.28577199999997 33.707213000000024, -118.28573699999988 33.707409999999939, -118.2857199999999 33.707451000000155, -118.28543999999988 33.7076859999999, -118.28542400000012 33.707707000000234, -118.28540700000008 33.707761000000069, -118.28541499999989 33.707987999999943, -118.28538200000011 33.708079000000076, -118.28518499999988 33.708247999999891, -118.28530299999981 33.708597999999888, -118.28336900000006 33.70933799999996, -118.28393100000017 33.70996300000008, -118.2840920000001 33.709924000000122, -118.28425400000008 33.709917000000104, -118.28447300000008 33.709965999999881, -118.28461299999992 33.710008, -118.28473800000008 33.710045000000051, -118.28512200000004 33.710215999999939, -118.28533799999998 33.710295000000123, -118.28520600000004 33.71048600000006, -118.28564600000006 33.710808000000057, -118.28565600000002 33.710932000000042, -118.28569099999996 33.711043000000032, -118.28581299999988 33.7110809999999, -118.28588099999992 33.711244000000065, -118.28606799999991 33.711454000000074, -118.2860839999999 33.711586999999994, -118.28609399999985 33.71164799999999, -118.28606400000015 33.711848999999937, -118.28574600000012 33.71245200000002, -118.285633 33.712665999999949, -118.28548499999992 33.712949000000116, -118.28548699999992 33.712997000000023, -118.28565100000017 33.713334000000081, -118.285798 33.7136239999999, -118.28575799999989 33.713992999999967, -118.28593600000006 33.714203000000019, -118.28592699999987 33.714256999999989, -118.28587800000005 33.714694999999963, -118.28591499999992 33.714976999999926, -118.2856250000001 33.715868999999913, -118.2854499999999 33.716334000000039, -118.28545000000007 33.716569999999933, -118.28549399999994 33.716625000000029, -118.28434999999992 33.718110999999951, -118.28416299999984 33.718403999999929, -118.28396199999999 33.718645999999886, -118.28344899999992 33.719261999999922, -118.28338000000011 33.719780000000057, -118.28306600000009 33.720315000000092, -118.28311100000003 33.720415000000131, -118.28329599999995 33.72074699999996, -118.28356300000011 33.721252000000014, -118.28391399999998 33.721861000000132, -118.28411000000014 33.722331999999973, -118.28420099999987 33.722606000000034, -118.28430699999998 33.72297999999995, -118.28440999999991 33.723537999999976, -118.28442300000003 33.723630000000014, -118.28463099999993 33.723572999999938, -118.28478600000017 33.723521000000034, -118.28488199999988 33.723503999999927, -118.28571300000009 33.723231999999932, -118.28606800000001 33.723126999999963, -118.28687400000005 33.722897999999915, -118.28694600000006 33.722877999999874, -118.28730800000007 33.722772999999926, -118.28743200000012 33.722736999999881, -118.28769400000022 33.722661000000087, -118.28798000000012 33.722582999999986, -118.287978 33.721749999999872, -118.28797699999994 33.721479999999879, -118.28797699999998 33.720970000000122, -118.28797600000013 33.720689000000107, -118.28797000000003 33.720606999999916, -118.28793800000004 33.720172999999917, -118.28793699999993 33.718926999999944, -118.28793700000008 33.718793000000083, -118.287935 33.716971000000129, -118.28793399999986 33.716025999999907, -118.28793300000012 33.715165000000049, -118.28793100000006 33.713352000000022, -118.28793000000006 33.711524000000054, -118.28792899999979 33.710575999999918, -118.28792799999997 33.70962800000008, -118.29063999999988 33.709626000000064, -118.29121599999992 33.709624999999896, -118.29284299999993 33.709624000000133, -118.29284200000001 33.708744999999936, -118.29284199999996 33.707864999999913, -118.29284099999981 33.706978999999968, -118.29284099999985 33.706501999999958, -118.29291899999994 33.70535600000008)), POLYGON ((-118.2772759999993 33.70789399999996, -118.27705599999973 33.707805000000029, -118.27702299999993 33.7077820000001, -118.27654599999985 33.707535000000149, -118.27611100000017 33.707299999999925, -118.27563400000018 33.707052999999824, -118.27528899999976 33.706864999999858, -118.2738189999999 33.706046000000065, -118.27180399999988 33.704964000000281, -118.27193500000008 33.705136000000081, -118.27709799999995 33.707905000000075, -118.27725400000016 33.707900000000016, -118.2772759999993 33.70789399999996)))

Polygon B:

POLYGON ((-118.28798 33.722583, -118.287694 33.722660999999995, -118.287432 33.722736999999995, -118.287308 33.722773, -118.286946 33.722878, -118.286874 33.722898, -118.286068 33.723127, -118.285713 33.723231999999996, -118.284882 33.723504, -118.284786 33.723521, -118.28463099999999 33.723573, -118.284423 33.72363, -118.284474 33.723994999999995, -118.284566 33.724441, -118.284697 33.724407, -118.284779 33.724385, -118.284804 33.724489999999996, -118.284855 33.724737, -118.28491700000001 33.725041, -118.284888 33.725141, -118.284602 33.726047, -118.283906 33.726952, -118.28358399999999 33.72721, -118.282782 33.727854, -118.281068 33.728755, -118.280929 33.728828, -118.28048 33.728997, -118.28047699999999 33.729659999999996, -118.280472 33.730567, -118.280468 33.731474, -118.281389 33.731477, -118.283563 33.731485, -118.28573399999999 33.731493, -118.287939 33.731501, -118.287941 33.730593, -118.287946 33.729686, -118.287953 33.728778999999996, -118.287957 33.727872, -118.28796 33.726966, -118.287964 33.726058, -118.287968 33.725151, -118.287973 33.724244, -118.287977 33.723338, -118.287979 33.722992999999995, -118.28798 33.722837, -118.28798 33.722583))



August 30th, 2013 2:28am

Hi JMDPR,

In SQL Server, if we execute a SELECT statement which processes invalid geometry objects and we will get an error 24144 message. The STIsValid() function can be used to find invalid geometries. The MakeValid() function can be used to correct any geometries which are STIsValid() report as invalid. To correct invalid geometries one can do this:

UPDATE tablename SET geom = geom.MakeValid() WHERE geom.STIsValid() = 0;

Thanks,
Sofiya Li

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2013 5:31am

OK, I think I have this figured out.  First, there does appear to be a bug in the Geography code.  It's a tricky one, however, since the MakeValid() method cannot "fix" the resulting object from the "merge" (STUnion) operation.

The problem occurs on the common edge between between Polygon A and Polygon B.  While this edge appears to be a common edge, the coordinate defining the edges do not match precisely between the two polygons:

When the two polygons are "merged" the common edge yields coordinate string which overlap by very small amounts.  This is most likely due to different vertex densities used to define each the common edge in each of the 2 polygons.  It is a bit surprising that MakeValid() cannot fix this.  I suspect that this is because is yields a series of braided interior rings which go outside the scope of what MakeValid() can handle - hence the bug.

So, given this, how can we work-around the issue?  One way is to generalize Polygon A with a small tolerance, such that it does not change the integrity of the spatial object but removes vertices which are (most likely) unneeded. To do this, I used the Reduce() method to remove vertices which were less than or equal to 7 cm apart.  Here are the vertex count before and after the generalization for Polygon A:

select @a.STNumPoints() --Results: 196
select @a.Reduce(.07).STNumPoints() --Results: 168

If we merge the two polygons together, after generalizing Polygon A by 7 cm, the resultant spatial object is valid and will behave like a valid object.  Here is the T-SQL:

--"Merge" spatial objects together
select @a.Reduce(.07).STUnion(@b) 

Here is the resultant object (visually):

You will notice the two short interior lines pointed to by the red lines.  These interior lines are artifacts from the common edge and give us a clue as to the origin of the issue.  The artifacts are very small and can be removed by additional processing if needed.

- Ed

September 5th, 2013 4:22am

It turns out this is one is even stranger than I thought.  And it is not apparently related to the definition of the mutual edge.

Here is an interesting development... When I merge Polygon 1 and Polygon 2 using STUnion() and then execute Reduce(0), the resulting spatial object can be displayed in the Spatial results tab:

  select @a.STUnion(@b).Reduce(0)

After this sql has been run once, at least in my instance of SSMS, the next time I execute:

  select @a.STUnion(@b)

it works fine in the Spatial Results tab! 

So, a couple of observations:

1. The object that results from select @a.STUnion(@b) is indeed valid (or Reduce would not execute)

2. It looks like something in SSMS Spatial results tab is causing the issue

In your case, using Geography::UnionAggregate(), this does not work.  The following does, however:

  geography::UnionAggregate([Geo]).Reduce(.07)

Let me know if this works for you.

Thanks,

- Ed

Free Windows Admin Tool Kit Click here and download it now
September 6th, 2013 6:06pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics