From an external API I am receiving polygons which I put into an array. The polygons are, each of them, counties in the USA and as such are of no interest to me. The array of polygons also makes loading them onto my web application very heavy and slow. What I am interested in, however, is the outer bounds of what these county-polygons combine.
I would have hope to make a MultiPolygon or ST_ExteriorRing from a ST_GeomFromText but I only get errors in my Postgresql when I do that.
Below is an example of an array of Polygons, let's call it "multipleCounties". I have tried to do a ST_GeomFromText(multipleCounties) but get error: "ERROR: function st_geomfromtext(json) does not exist" (Postgis is installed, I tend to get this as a generic error message).
So, how can I reduce this large array of Polygons into one MultiPolygon in Postgresql?
[
{
"type": "Polygon",
"coordinates":
[
[
[
-96.2044982,
44.1972122
],
[
-96.19869990000001,
44.197311400000004
],
[
-96.07869720000001,
44.1966133
],
[
-96.06369780000001,
44.1965103
],
[
-96.06349940000001,
44.182312
],
[
-96.0635986,
44.139911600000005
],
[
-96.06389610000001,
44.11581030000001
],
[
-96.06439970000001,
44.01391220000001
],
[
-96.0641937,
44.000011400000005
],
[
-96.0639953,
43.9501113
],
[
-96.0639953,
43.89161300000001
],
[
-96.0641937,
43.88521190000001
],
[
-96.0641937,
43.87001030000001
],
[
-96.06439970000001,
43.856410900000014
],
[
-96.06369780000001,
43.849212600000016
],
[
-96.07279960000001,
43.84901040000002
],
[
-96.12529750000002,
43.84871290000002
],
[
-96.19999690000002,
43.84871290000002
],
[
-96.23219290000002,
43.84851070000002
],
[
-96.28549950000001,
43.84881210000002
],
[
-96.29159540000002,
43.84871290000002
],
[
-96.30690000000001,
43.84891120000002
],
[
-96.34439840000002,
43.84891120000002
],
[
-96.38990020000001,
43.84931180000002
],
[
-96.39519500000002,
43.849212600000016
],
[
-96.40179440000001,
43.84941100000002
],
[
-96.40569300000001,
43.84931180000002
],
[
-96.45349880000002,
43.84961310000001
],
[
-96.45329280000001,
43.85221090000001
],
[
-96.45329280000001,
43.89121240000001
],
[
-96.45349880000002,
43.89851370000001
],
[
-96.45329280000001,
43.906612300000006
],
[
-96.45339960000001,
43.944713500000006
],
[
-96.4531936,
43.963413200000005
],
[
-96.45339960000001,
43.9738121
],
[
-96.45309440000001,
44.007713300000006
],
[
-96.45339960000001,
44.02461240000001
],
[
-96.45309440000001,
44.04521170000001
],
[
-96.4531936,
44.09291070000001
],
[
-96.45299530000001,
44.111312800000015
],
[
-96.45269770000002,
44.19691080000001
],
[
-96.44499960000002,
44.19721220000001
],
[
-96.42469780000002,
44.19711300000001
],
[
-96.33889770000002,
44.19731140000001
],
[
-96.31189720000002,
44.197513500000014
],
[
-96.28569790000002,
44.19741050000001
],
[
-96.24029540000002,
44.197513500000014
],
[
-96.2044982,
44.1972122
]
]
]
},
{
"type": "Polygon",
"coordinates":
[
[
[
-95.8482971,
44.6307106
],
[
-95.8287963,
44.6308135
],
[
-95.7878952,
44.6306114
],
[
-95.75029749999999,
44.6302108
],
[
-95.68560019999998,
44.6299133
],
[
-95.64429469999997,
44.630413
],
[
-95.60399619999997,
44.6299133
],
[
-95.60389699999998,
44.6157112
],
[
-95.60409539999998,
44.600212
],
[
-95.60419459999997,
44.5641136
],
[
-95.60439299999997,
44.5550117
],
[
-95.60429379999998,
44.5425109
],
[
-95.59489439999997,
44.5422134
],
[
-95.59459679999998,
44.5363121
],
[
-95.59449759999998,
44.530014
],
[
-95.59479519999998,
44.5119133
],
[
-95.59449759999998,
44.5084114
],
[
-95.59459679999998,
44.5003128
],
[
-95.59479519999998,
44.496612500000005
],
[
-95.59439839999997,
44.493911700000005
],
[
-95.59439839999997,
44.472213700000005
],
[
-95.59389489999998,
44.428512500000004
],
[
-95.59409329999998,
44.399414
],
[
-95.59389489999998,
44.355812
],
[
-95.59399409999997,
44.3423118
],
[
-95.59339899999998,
44.2976112
],
[
-95.59329979999998,
44.2545127
],
[
-95.59349819999998,
44.195713
],
[
-95.64389799999998,
44.195110299999996
],
[
-95.68359369999997,
44.195510799999994
],
[
-95.70299529999997,
44.19591139999999
],
[
-95.72369379999996,
44.19591139999999
],
[
-95.74339289999996,
44.19621269999999
],
[
-95.75449369999995,
44.19611349999999
],
[
-95.78009789999996,
44.19631189999999
],
[
-95.80489339999995,
44.19611349999999
],
[
-95.83619679999995,
44.19641109999999
],
[
-95.91689299999996,
44.19631189999999
],
[
-95.94629659999995,
44.19651029999999
],
[
-95.99159999999995,
44.19641109999999
],
[
-96.00319669999995,
44.19661329999999
],
[
-96.04369349999995,
44.19631189999999
],
[
-96.06369779999994,
44.19651029999999
],
[
-96.07869719999994,
44.196613299999996
],
[
-96.07869719999994,
44.233310599999996
],
[
-96.07919309999994,
44.254711099999994
],
[
-96.07929989999994,
44.274810699999996
],
[
-96.07929989999994,
44.3136138
],
[
-96.07909389999993,
44.3363113
],
[
-96.07929989999994,
44.3496131
],
[
-96.07919309999994,
44.3539123
],
[
-96.07929989999994,
44.364311199999996
],
[
-96.07919309999994,
44.39241019999999
],
[
-96.07939909999995,
44.430011699999994
],
[
-96.07929989999995,
44.43311299999999
],
[
-96.07989499999995,
44.45901099999999
],
[
-96.07979579999996,
44.50311269999999
],
[
-96.08020009999996,
44.53941339999999
],
[
-96.08009329999996,
44.54341119999999
],
[
-96.08289329999995,
44.54341119999999
],
[
-96.09299459999995,
44.54411309999999
],
[
-96.09289549999995,
44.58691019999999
],
[
-96.09329979999995,
44.60411069999999
],
[
-96.09289549999995,
44.61581029999999
],
[
-96.09289549999995,
44.63051219999999
],
[
-96.05309289999995,
44.63021079999999
],
[
-96.01219939999996,
44.63011159999999
],
[
-95.99199669999996,
44.63051219999999
],
[
-95.95520009999996,
44.63081349999999
],
[
-95.86939999999996,
44.63091269999999
],
[
-95.8482971,
44.6307106
]
]
]
},
{
"type": "Polygon",
"coordinates":
[
[
[
-97.8511962,
43.8499107
],
[
-97.8278961,
43.8496131
],
[
-97.79010000000001,
43.8499107
],
[
-97.7371978,
43.8498115
],
[
-97.66059870000001,
43.8492126
],
[
-97.63529960000001,
43.8492126
],
[
-97.60919950000002,
43.8489112
],
[
-97.60869590000001,
43.832912400000005
],
[
-97.60889430000002,
43.80101390000001
],
[
-97.60879510000002,
43.76031110000001
],
[
-97.60820000000002,
43.74571220000001
],
[
-97.60729980000002,
43.73531340000001
],
[
-97.60669700000003,
43.71661370000001
],
[
-97.60679620000002,
43.710311800000014
],
[
-97.60639950000002,
43.670013400000016
],
[
-97.60659790000003,
43.65921020000002
],
[
-97.60699460000002,
43.60061260000002
],
[
-97.60719290000002,
43.54261390000002
],
[
-97.60699460000002,
43.51681130000002
],
[
-97.60709380000002,
43.499813000000024
],
[
-97.62599940000001,
43.499813000000024
],
[
-97.63959500000001,
43.49961090000002
],
[
-97.73880000000001,
43.49971380000002
],
[
-97.74639890000002,
43.49961090000002
],
[
-97.75090020000002,
43.499813000000024
],
[
-97.75619500000002,
43.49961090000002
],
[
-97.79199980000001,
43.49951170000002
],
[
-97.90519710000001,
43.49961090000002
],
[
-97.93779750000002,
43.49931330000002
],
[
-97.95800010000002,
43.49941250000002
],
[
-97.96459960000001,
43.49961090000002
],
[
-97.96489710000002,
43.507011400000025
],
[
-97.96489710000002,
43.51341240000002
],
[
-97.96629330000002,
43.57731240000002
],
[
-97.96649930000002,
43.60071180000002
],
[
-97.96709440000002,
43.62971110000002
],
[
-97.96679680000003,
43.644210800000025
],
[
-97.96689600000002,
43.648712100000026
],
[
-97.96649930000002,
43.678413300000024
],
[
-97.96829980000003,
43.75111380000003
],
[
-97.96839900000002,
43.761413500000025
],
[
-97.96819300000001,
43.76681130000002
],
[
-97.96839900000002,
43.77771370000002
],
[
-97.96839900000002,
43.79061120000002
],
[
-97.96809380000002,
43.81261060000002
],
[
-97.96839900000002,
43.81841270000002
],
[
-97.96799460000003,
43.825412700000015
],
[
-97.96749870000002,
43.85011290000001
],
[
-97.95139310000002,
43.85061260000001
],
[
-97.90670010000002,
43.85061260000001
],
[
-97.8511962,
43.8499107
]
]
]
}
]
CodePudding user response：
Your data value is a JSON array which contains geojson polygon feature objects. st_GeomFromText() expects a 
SELECT
st_asgeojson(st_union(st_geomfromgeojson(poly_json))) as multipoly
FROM
json_array_elements(counties_json) polys(poly_json)
st_union will reduce the size by about 70% for the data sample you provided. Keep in mind though that significant size reduction will only only occur when where your county polygons are contiguous.
If you only need a rough approximation of the area, you can use st_envelope() to get the minimum bounding rectangle of each county polygon and then st_union() those together:
SELECT
st_asgeojson(st_union(st_envelope(st_geomfromgeojson(poly_json)))) as multipoly
FROM
json_array_elements(counties_json) polys(poly_json)
This approach reduces the size of your sample data by about 98%, but will result in false positive matches if you intersect it with other data.
If you are only using these boundaries to determine if some point/polygon/line data intersects them, you could consider saving the counties directly as postgis geometries in a table and using st_intersects(county_geom,some_other_geom) to determine if an intersection exists between the counties and other features. This will prevent you from needing to load the geojson representation in your application layer at all -just make sure you create a GIST INDEX on the geometry column for efficient index lookups if you go this route.