Use St_ExteriorRing, or similar, to fill up 'holes' in Multipolygon geojson

Question

I have an application that reads geojson from an external API. The geojson could be either Polygon or Multipolygon depending on the day.

These are examples of the two

Multipolygon

{"type":"MultiPolygon","coordinates":[[[[-99.51346135949517,49.00303923244011],[-100.15,47.95],[-102.31,47.45],[-108.58,46.38],[-109.18,46.08],[-111.49,44.89],[-112.76,44.42],[-114.63,43.9],[-116.74,43.72],[-118.71000000000001,42.86],[-118.87,42.36],[-118.11,42.11],[-116.35,42.63],[-115.24,42.27],[-114.45,42.12],[-112.6,42.27],[-111.36,42.76],[-110.7,42.98],[-107.75,43.9],[-106.11,43.66],[-104.91,42.66],[-104.77,41.68],[-104.04,40.6],[-103.49,39.34],[-102.87,39.26],[-102.41,38.64],[-102.31,37.41],[-101.91,36.68],[-101.2,36.36],[-100,36.46],[-98.74,38.68],[-98.39,38.95],[-97.53,39.53],[-97.02,39.44],[-96.43,39.32],[-95.05,38.49],[-93.51,37.47],[-93.13,37.15],[-91.08,35.28],[-88.52,34.63],[-86.34,34.86],[-85.31,35.74],[-84.18,36.63],[-83.04,36.79],[-81.59,36.01],[-81.13,35.08],[-80.45,34.34],[-80.32,34.26],[-79.57,33.82],[-78.81808696186206,33.27775502057361],[-78.931,32.928],[-79.294,32.69],[-79.725,32.427],[-80.061,32.203],[-80.21,32.07],[-80.436,31.966],[-80.764,31.633],[-81.09,30.74],[-80.989,30.128],[-80.765,29.498],[-80.349,28.83],[-80.197,28.552],[-80.271,28.232],[-79.788,27.148],[-79.7,26.777],[-79.766,26.228],[-79.812,25.576],[-79.965,25.158],[-80.143,24.859],[-80.331,24.705],[-80.635,24.487],[-81.054,24.361],[-81.782,24.208],[-82.816,24.169],[-83.288,24.221],[-83.28,24.513],[-83.238,24.798],[-83.163,25.012],[-82.313,25.017],[-82.368,25.134],[-82.51,25.288],[-82.632,25.453],[-81.934,25.61],[-82.143,26.09],[-82.324,26.171],[-82.535,26.48],[-82.719,26.892],[-83.105,27.532],[-83.07,27.615],[-83.183,27.781],[-83.185,28.105],[-83.156,28.373],[-83.014,28.465],[-82.99,28.551],[-83.026,28.753],[-83.273,28.827],[-83.421,29.093],[-83.625,29.29],[-83.895,29.642],[-84.073,29.73],[-84.171,29.626],[-84.467,29.438],[-85.018,29.246],[-85.336,29.335],[-85.675,29.436],[-85.767,29.696],[-85.985,29.876],[-86.462,30.068],[-87.771,29.878],[-88.406,29.863],[-88.432,29.714],[-88.695,29.394],[-88.581,29.249],[-88.619,29.022],[-88.937,28.691],[-89.175,28.63],[-89.399,28.577],[-89.712,28.693],[-89.828,28.916],[-90.102,28.755],[-90.3616055222485,28.728920906440788],[-90.59,29.18],[-90.87,30.19],[-90.74,31.24],[-90.18,31.97],[-89.72,32.76],[-90.62,34.34],[-93.28,37.05],[-95,37.73],[-96.23,37.81],[-97.39,37.44],[-98.65,36.55],[-100.01,35.09],[-101.27,34.54],[-102.23,34.53],[-103.01,34.66],[-103.73,34.32],[-103.8,33.75],[-103.99,33.41],[-104.32,32.69],[-105.35,32.06],[-105.9701240038253,31.300129741791523],[-106.325,31.547],[-106.448,31.769],[-107.166,31.772],[-107.78,31.773],[-108.182,31.777],[-108.213,31.305],[-109.08,31.308],[-109.891,31.327],[-110.732,31.321],[-111.103,31.32],[-112.189,31.67],[-112.66904089087537,31.81540516722906],[-112.7,32.69],[-111.95,33.62],[-110.88,34.28],[-109.67,34.89],[-108.43,35.58],[-107.8,36.94],[-107.19,38.87],[-106.27,40.84],[-106.23,41.55],[-106.45,42.34],[-107.19,42.6],[-109.05,42.48],[-110.95,41.14],[-112.4,40.46],[-115.28999999999999,39.87],[-117.96000000000001,39.46],[-119.89,39.24],[-121.74,39.12],[-123.06,39.74],[-123.78,41.24],[-123.28999999999999,43.33],[-121.28999999999999,45.26],[-119.8,46.42],[-118.65,47.32],[-119.25,48],[-120.4,48.34],[-121.84015239078087,48.98691337339889],[-121.659,48.986],[-120.472,48.999],[-119.253,48.999],[-117.737,49.015],[-116.09,48.994],[-114.493,48.985],[-113.116,49.003],[-111.3,48.99],[-109.984,48.991],[-109.64,48.997],[-108.993,48.996],[-107.31,48.993],[-105.605,49.009],[-103.573,48.991],[-101.079,48.988],[-99.687,49.004],[-99.51346135949517,49.00303923244011]],[[-84.43,34.91],[-86.05,34.1],[-86.37,33],[-85.19,32.64],[-84.24,32.79],[-83.28,33.27],[-82.39,33.89],[-82.05,34.63],[-82.23,35.26],[-83.17,35.46],[-84.43,34.91]]],[[[-88.87125436792684,48.17251743575732],[-88.4,47.64],[-88.31,46.8],[-88.89,45.98],[-89.68,45.43],[-90.53,43.89],[-90.58,42.8],[-90.29,42.37],[-89.65,41.69],[-88.48,41.31],[-86.62,41.26],[-83.61,41.42],[-81.71,41.54],[-80.43,42.18],[-80.35384895547264,42.36541123884922],[-80.167,42.399],[-79.353,42.673],[-78.966,42.881],[-79.194,43.433],[-78.597,43.655],[-77.369,43.615],[-76.813,43.628],[-76.377,44.151],[-75.828,44.443],[-75.494,44.756],[-74.876,45.025],[-71.551,45.021],[-71.366,45.294],[-70.884,45.379],[-70.38,45.841],[-70.319,46.114],[-70.03,46.658],[-69.251,47.478],[-68.967,47.467],[-68.967,47.24],[-68.497,47.296],[-68.287,47.417],[-67.785,47.096],[-67.744,46.598],[-67.724,45.726],[-67.403,45.548],[-67.372,45.222],[-67.182,45.297],[-67.043,45.044],[-66.976,44.938],[-66.991,44.84],[-66.86,44.821],[-67.302,44.414],[-67.279,44.152],[-67.925,43.906],[-68.184,43.802],[-68.352,43.817],[-68.538,43.761],[-68.681,43.688],[-68.902,43.581],[-69.149,43.51],[-69.388,43.422],[-69.557,43.333],[-69.738,43.3],[-69.962,43.13],[-70.094,42.939],[-70.041,42.768],[-69.908,42.491],[-69.795,42.31],[-69.595,42.133],[-69.483,42.001],[-69.443,41.721],[-69.548,41.498],[-69.488,41.199],[-69.663,41.018],[-69.946,40.916],[-70.34,40.942],[-70.838,40.931],[-71.244,40.859],[-71.608,40.77],[-72.156,40.586],[-72.567,40.462],[-72.944,40.361],[-73.2,40.303],[-73.648,40.236],[-73.751,39.803],[-73.839,39.533],[-74.085,39.198],[-74.24431116799899,39.091621252336154],[-74.72,40.43],[-74.13,41.32],[-73.27,42.18],[-73.28,42.96],[-73.69,43.37],[-75.34,43.41],[-76.08,42.83],[-76.99,42.2],[-77.99,41.95],[-79.57,41.64],[-81.97,41],[-84.85,40.86],[-88.66,40.91],[-89.93,41.4],[-91.14,41.9],[-91.69,42.6],[-92.39,43.51],[-92.06,44.4],[-91.92,45.58],[-92.15,46.97],[-92.73,48.56],[-92.72981693138672,48.56141140114221],[-92.611,48.546],[-92.049,48.36],[-91.504,48.094],[-90.835,48.281],[-90.646,48.094],[-90.054,48.116],[-89.35,47.985],[-88.87125436792684,48.17251743575732]]]]}

Polygon:

{"type":"Polygon","coordinates":[[[-95.28501052631579,48.998],[-94.58,48.27],[-92.76,47.02],[-90.19,46.11],[-88.05,46.5],[-86.48,47.07],[-85.95569942586447,47.33464695646842],[-85.896,47.31],[-84.848,46.885],[-84.707,46.516],[-84.142,46.508],[-84.043,46.128],[-83.963,46.047],[-83.859,46.044],[-83.754,46.112],[-83.517,46.102],[-83.444,45.977],[-83.53,45.879],[-83.584,45.814],[-82.528,45.336],[-82.199,44.145],[-82.103,43.62],[-82.288,43.266],[-82.307,43.137],[-82.454,42.997],[-82.483,42.619],[-82.704,42.431],[-82.729,42.36],[-83.068,42.27],[-83.189,42.064],[-83.08,41.93],[-83.103,41.858],[-82.7,41.693],[-82.32,41.713],[-81.62,42.066],[-81.185,42.216],[-80.167,42.399],[-79.353,42.673],[-78.966,42.881],[-79.194,43.433],[-78.597,43.655],[-77.369,43.615],[-76.813,43.628],[-76.377,44.151],[-75.828,44.443],[-75.494,44.756],[-75.18869307056579,44.888892498410684],[-75.38,44.11],[-75.46,41.79],[-74.54,40.67],[-73.68141943423038,40.095508592021794],[-73.751,39.803],[-73.839,39.533],[-74.085,39.198],[-74.395,38.991],[-74.504,38.814],[-74.716,38.67],[-74.701,38.442],[-74.762,38.239],[-75.086,37.666],[-75.263,37.548],[-75.356,37.334],[-75.645,36.894],[-75.371,36.199],[-75.12,35.649],[-75.166,35.243],[-75.279,34.984],[-75.429,34.931],[-75.726,34.857],[-76.265,34.375],[-76.54,34.248],[-76.776,34.354],[-76.936,34.358],[-77.521,34.032],[-77.684,33.615],[-77.95,33.516],[-78.163,33.577],[-78.569,33.483],[-78.808,33.309],[-78.931,32.928],[-79.294,32.69],[-79.725,32.427],[-80.061,32.203],[-80.21,32.07],[-80.436,31.966],[-80.764,31.633],[-81.09,30.74],[-80.989,30.128],[-80.765,29.498],[-80.349,28.83],[-80.197,28.552],[-80.271,28.232],[-79.788,27.148],[-79.7,26.777],[-79.766,26.228],[-79.812,25.576],[-79.965,25.158],[-80.143,24.859],[-80.331,24.705],[-80.635,24.487],[-81.054,24.361],[-81.782,24.208],[-82.816,24.169],[-83.288,24.221],[-83.28,24.513],[-83.238,24.798],[-83.163,25.012],[-82.313,25.017],[-82.368,25.134],[-82.51,25.288],[-82.632,25.453],[-81.934,25.61],[-82.143,26.09],[-82.324,26.171],[-82.535,26.48],[-82.719,26.892],[-83.105,27.532],[-83.07,27.615],[-83.183,27.781],[-83.185,28.105],[-83.156,28.373],[-83.014,28.465],[-82.99,28.551],[-83.026,28.753],[-83.273,28.827],[-83.421,29.093],[-83.625,29.29],[-83.895,29.642],[-84.073,29.73],[-84.171,29.626],[-84.467,29.438],[-85.018,29.246],[-85.336,29.335],[-85.675,29.436],[-85.767,29.696],[-85.985,29.876],[-86.462,30.068],[-87.771,29.878],[-88.406,29.863],[-88.432,29.714],[-88.695,29.394],[-88.581,29.249],[-88.619,29.022],[-88.937,28.691],[-89.175,28.63],[-89.399,28.577],[-89.712,28.693],[-89.828,28.916],[-90.102,28.755],[-90.54,28.711],[-90.745,28.711],[-91.083,28.732],[-91.315,28.893],[-91.556,28.983],[-91.655,29.121],[-91.742,29.058],[-92.079,29.114],[-92.164,29.181],[-92.629,29.233],[-93.243,29.441],[-93.588,29.43],[-93.777,29.35],[-93.86371715145437,29.3490574222668],[-94.43,30.11],[-95.48,32.28],[-95.9,34.79],[-97.03,36.61],[-98.25,37.5],[-99.48,37.64],[-100.37,37.51],[-100.95,36.99],[-101.57,36.09],[-102.05,35.51],[-103.01,35.26],[-103.97,35.42],[-104.89,36.02],[-105.46,36.22],[-106.3,35.84],[-104.73,33.68],[-104.99,32.67],[-106.17,32.34],[-107.24,32.43],[-107.23,31.95],[-107.16324568439407,31.771988491717522],[-107.166,31.772],[-107.78,31.773],[-108.182,31.777],[-108.213,31.305],[-109.08,31.308],[-109.891,31.327],[-110.732,31.321],[-111.103,31.32],[-111.5068844073521,31.450165324653078],[-111.32,31.76],[-110.88,32.62],[-111.06,34.11],[-110.37,35.35],[-109.05,36.35],[-108.06,37.78],[-107.21,39.63],[-105.82,41.42],[-105.1,41.94],[-105.06,42.36],[-105.26,42.78],[-105.62,43.15],[-106.56,43.87],[-107.39,44.07],[-109.01,43.9],[-111.36,43.17],[-113.32,43.43],[-114.49,43.16],[-115.82,42.89],[-116.23,42.73],[-118.37,43.02],[-120.51,43.78],[-121.1,44.75],[-119.58,46.35],[-119.09,47.98],[-119.44802702702702,48.999],[-119.253,48.999],[-117.737,49.015],[-116.09,48.994],[-114.493,48.985],[-113.116,49.003],[-111.3,48.99],[-109.984,48.991],[-109.64,48.997],[-108.993,48.996],[-107.31,48.993],[-105.605,49.009],[-103.573,48.991],[-101.079,48.988],[-99.687,49.004],[-98.242,48.996],[-96.798,48.991],[-95.874,48.998],[-95.28501052631579,48.998]],[[-90.2,32.05],[-88.72,30.79],[-88.05,30.65],[-87.21,30.8],[-86.45,31.65],[-85.88,32.11],[-85.02,32.55],[-83.71,32.58],[-82.55,33.06],[-81.69,33.45],[-81.33,34.29],[-80.13,35.88],[-79.91,37.41],[-79.69,38.28],[-80.79,38.96],[-83.11,38.16],[-85.63,38.75],[-88.01,38.14],[-90.04,37.53],[-91.48,36.43],[-92.4,35.03],[-92.58,34.06],[-91.41,32.64],[-90.2,32.05]],[[-109.65,45.06],[-100.16,42.48],[-99.01,41.67],[-97.4,38.75],[-95.53,35.91],[-94.12,35.24],[-93.06,35.66],[-90.26,38.1],[-90.85,40.46],[-92.65,43.69],[-94.58,44.37],[-98.8,44.91],[-102.84,46.18],[-110.55,46.83],[-111.85,46.61],[-112.49,46.05],[-111.89,45.39],[-109.65,45.06]]]}

The polygons almost always have a 'hole' in them, so I use:

ST_SetSRID(St_ExteriorRing(ST_GeomFromGeoJSON(mygeojson))

This works fine for Polygon but not for Multipolygon. I can understand why, as it may be a difficult task to "fill up the holes" of multiple geometries.

Is there a way to solve this so that multipolygons also can use ST_ExteriorRing (or the likes of it)? Conditional solutions are fine, I can find out which are polygons and multipolygons so the solution only needs to be for multipolygon.

Answer 1

One option is to ST_Dump the polygon, apply ST_ExteriorRing, and finally - if necessary - recreate the MultiPolygon with ST_Collect, e.g.

SELECT 
  ST_Collect(ST_MakePolygon(ST_ExteriorRing(geom)))
FROM (SELECT gid,(ST_Dump(mygeojson::geometry)).* FROM mytable) j
GROUP BY gid;

The query assumes each geometry has an identifier. In this case called gid. In case the geometries have no identifier, you might wanna create one with the window function ROW_NUMBER():

SELECT 
  ST_Collect(ST_MakePolygon(ST_ExteriorRing(geom)))
FROM (
  SELECT 
    ROW_NUMBER() OVER w AS gid,
    (ST_Dump(mygeojson::geometry)).* 
  FROM mytable
  WINDOW w AS (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
) j
GROUP BY gid;

It gets much shorter if you can live with Polygons ;)

SELECT 
  ST_MakePolygon(ST_ExteriorRing((ST_Dump(mygeojson::geometry)).geom))
FROM mytable