2

I have 1 table of 20k records. there is a final_zone field where the zone could be either 'Conservation', 'ECZ', 'Protection'. there is another field called sub_zone and the conservation and ecz final_zones each have the possibility of having the sub_zone in it, but not every record has a sub_zone. So if I st_union the geometries and group by all the columns I am left with 5 records.

GOAL:

What I want to be able to do is st_union only the geometries that spatially intersect and form a contiguous layer, and that have the same final_zone BUT I want to be able to preserve the sub_zone records in case one or any of the records has a sub_zone. I also want to preserve their original id's and geometries because at some point I am going to try and unravel these records back to their original form (that will be a separate question after I figure out this one) the reason for this is because I am trying to find areas greater than 75 acres without getting rid of the subzone

Logic so far

Have my called roll_up which is st_union of the layer ls_75_pre_resolve with final_zone (3 rows) then a st_dump to break them apart. I take that layer and throw it through a lateral to the ls_75_pre_resolve layer so every piece that has the same final_zone and intersects it is aggregated in the outside select. st_union the inner geometries to form a new merged geometry and use the array_agg function to 'roll up' the unique id's, original geometries, and sub_zone in their own column.

SQL

with roll_up2 as(select (st_dump(shape)).geom,final_zone,
            ROW_NUMBER() OVER (ORDER BY shape) as id 
            from( select st_union(geom) shape,final_zone from conflict.roll_up_test group by final_zone)t
        ),

 abid as(
    select t1.final_zone,
        t2.id,
        array_agg(t1.id) ids, --aggregates ids
        array_agg(t1.sub) sub,  --aggregates sub_zones
        array_agg(t1.geom) polys, --aggregates original geometries
        st_union(t1.geom) merge --unions the geoms that touch
        from roll_up2 t2  --this table is a st_unioned keep final_zone, then st_dump geometries
        cross join lateral(
            select p.id,p.sub,p.geom,p.final_zone
                from conflict.roll_up_test p where st_intersects(t2.geom,geom)
                and t2.final_zone=p.final_zone 
                )t1 
        group by t1.final_zone,t2.id

    ),
acres as(select *,st_area(merge)/43560  merge_acres from abid),
abid2 as(select unnest(ids) ids,final_zone,
            unnest(polys) polys,
            unnest(sub) sub_zone,
        st_union(merge) merge_diss,
        merge_acres
    from acres
    group by unnest(ids),final_zone,unnest(polys),unnest(sub),merge_acres
    )
select st_union(st_geomfromewkb(polys::geometry)) geom,
    ids, sub_zone,final_zone,merge_acres,
    st_area(st_union(st_geomfromewkb(polys::geometry)))/43560 acres --calculates area of original geometry
    from abid2 group by ids,sub_zone,final_zone,merge_acres;
  1. so this strangely works okay but I have no idea when I unnest all the array_aggs the rows match back up perfectly to eachother

  2. so the pic below this works perfect because they all became 1 large feature in the initial abid CTE so I was able to calculate their acres correctly (look at the column merge_acres they are all the same! before there were different merged_acres numbers because each surrounding feature had its own calculation

enter image description here Problem now:

the pic below that redish middle piece is surrounded by all the same zone but DOES NOT touch those outside outside pieces, so this query is not yielding accurate results for this feature.

*this pic is if you run up to and including the first abid statement

enter image description here

you can see in the attribute table 24943 is present in all the ids array and that is that middle piece. so I am trying to get it so that is just one big piece

{
"type": "FeatureCollection",
"name": "roll_up_test",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:EPSG::3424" } },
"features": [
{ "type": "Feature", "properties": { "final_zone": "Protection", "id": 6516, "ids": "{24920,24943}", "sub": "{NULL,NULL}", "polys": "{0103000020600D0000010000000B000000807E5611D62D1841007C26C6F56B2541805338299D2D1841C07CE867016C254140098EA7802D1841005C259C086C2541000734CE662D184140A8E7FD116C2541C0A17B454A2D184140E450331E6C25418025A84C622C184100E64574816C254140869DCB132A1841005FC5197E6D254180C4C851B62B18418069888D4E6D254100C71ECBDA2D1841C00DC731526C254100835E6DFF2D184100D7BD56416C2541807E5611D62D1841007C26C6F56B2541:0103000020600D00000100000012000000809C10892F25184180A92A49DC6F254180F4D8ED38251841008F8AB6B46F2541C01F1BBA40251841C0360588A26F254140D1381F4F251841C0E1E762916F2541401CDE9D6325184180D9B6DE816F25410044F6807D2518410032DA84746F254140869DCB132A1841005FC5197E6D254180006A6D8728184100977921AB6D2541404A4682BA25184100605F21736E2541C08481D323211841007D7508926D2541001A39AF2221184100F34B1E916D254180CAFE9121211841C01FB4E7906D25410057583F9A1E184140DBD9588A6B254140CC89FF681E18410051404432702541C020563A5E1E184140BB12E93671254180950F6D681E184100FE279E3371254180865070C72118410037CBB10A702541809C10892F25184180A92A49DC6F2541}" }, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ 396149.516931511461735, 701946.887012362480164 ], [ 396135.290253929793835, 701952.702945612370968 ], [ 396128.16362776234746, 701956.304972529411316 ], [ 396121.701370343565941, 701960.99590802937746 ], [ 396114.567854430526495, 701967.100226528942585 ], [ 396056.57486017793417, 702016.727095782756805 ], [ 395908.948843095451593, 702143.050333946943283 ], [ 396013.5798674300313, 702119.276431366801262 ], [ 396150.698359593749046, 701993.097221784293652 ], [ 396159.856805846095085, 701984.66941711306572 ], [ 396149.516931511461735, 701946.887012362480164 ] ] ], [ [ [ 395595.883852429687977, 702446.142903611063957 ], [ 395598.232272930443287, 702426.356525868177414 ], [ 395600.18174409493804, 702417.265664778649807 ], [ 395603.780490178614855, 702408.693175368010998 ], [ 395608.904167596250772, 702400.934988781809807 ], [ 395615.375939428806305, 702394.259477198123932 ], [ 395908.948843095451593, 702143.050333946943283 ], [ 395809.856849677860737, 702165.565380781888962 ], [ 395630.627221260219812, 702265.565180778503418 ], [ 395336.956548761576414, 702153.016521364450455 ], [ 395336.671116262674332, 702152.559173196554184 ], [ 395336.392573513090611, 702152.452546112239361 ], [ 395174.561860427260399, 701893.173537112772465 ], [ 395162.249549094587564, 702489.133303195238113 ], [ 395159.556969176977873, 702619.45522103458643 ], [ 395162.106504760682583, 702617.808898866176605 ], [ 395377.859682179987431, 702469.347253531217575 ], [ 395595.883852429687977, 702446.142903611063957 ] ] ] ] } },
{ "type": "Feature", "properties": { "final_zone": "Protection", "id": 6524, "ids": "{24912,24920,24943,24971,24944}", "sub": "{NULL,NULL,NULL,NULL,NULL}", "polys": "{0103000020600D0000010000000F00000080AB42777C1D1841807A28D75B6A25418024A6FD711D1841C0A87C059D6A254140AB31C69C1D184180412E4BBF6A25410057583F9A1E184140DBD9588A6B254180E12024A21E184140BF5F4FCB6A254140C6415FA21E1841401F88B8C56A2541C0EDA6919F1E18410019A0D8B26A254100C2ECB3951E18418066C192A06A2541C0A21E21851E18418091BB8E8F6A254140D96A706E1E184180F47068806A2541808C7272521E184180A9CCAA736A2541809C8B27321E18418074B8CA696A2541C0845CB80E1E184100B4BE22636A2541802B1869E91D1841C053D9EF5F6A254180AB42777C1D1841807A28D75B6A2541:0103000020600D0000010000000B000000807E5611D62D1841007C26C6F56B2541805338299D2D1841C07CE867016C254140098EA7802D1841005C259C086C2541000734CE662D184140A8E7FD116C2541C0A17B454A2D184140E450331E6C25418025A84C622C184100E64574816C254140869DCB132A1841005FC5197E6D254180C4C851B62B18418069888D4E6D254100C71ECBDA2D1841C00DC731526C254100835E6DFF2D184100D7BD56416C2541807E5611D62D1841007C26C6F56B2541:0103000020600D00000100000012000000809C10892F25184180A92A49DC6F254180F4D8ED38251841008F8AB6B46F2541C01F1BBA40251841C0360588A26F254140D1381F4F251841C0E1E762916F2541401CDE9D6325184180D9B6DE816F25410044F6807D2518410032DA84746F254140869DCB132A1841005FC5197E6D254180006A6D8728184100977921AB6D2541404A4682BA25184100605F21736E2541C08481D323211841007D7508926D2541001A39AF2221184100F34B1E916D254180CAFE9121211841C01FB4E7906D25410057583F9A1E184140DBD9588A6B254140CC89FF681E18410051404432702541C020563A5E1E184140BB12E93671254180950F6D681E184100FE279E3371254180865070C72118410037CBB10A702541809C10892F25184180A92A49DC6F2541:0103000020600D00000100000010000000C020563A5E1E184140BB12E93671254180A39EFFC91D184100E644C56671254100ED72B3861A1841C056B17774722541003851CB681A184140E1A8B6ED73254180D4E9C3811A1841C0B00CBD2875254180AF5FF4701C184100152CFF5774254100EBF128C51D184100414497C87325414057DC21FE1D1841C0563F93B07325414010DA20001E1841401490BBAF732541008CD5751D1E184140DC1F6DA0732541408443AE331E18418001065F8E732541401A24CA411E184180AA8D617A73254180DA3227471E1841804E2C5A65732541409C4159471E184140ED019A607325414088AC0F561E1841C05EBE8BFC712541C020563A5E1E184140BB12E936712541:0103000020600D00000100000014000000809C10892F25184180A92A49DC6F254140D64F260B251841C0B0A59075702541007C7ABB08251841C017D8BF7F702541C04FB02B0825184100F7F8A69470254100D14D5010251841804B2328A9702541409EF1CD20251841C09E0C5EBC70254100DC86992F25184100257ED7C670254140EF80EC3825184180AF8671CD70254180AD899D57251841809B93A3DB70254140AA088A7B25184100FDA055E670254180C413DFB1271841808890006971254100FCC09447281841402C4C8B8B712541C0B923F14C281841C0A3066774712541C0C5304E5F28184140FB0A2425712541003F476D00271841C0784FCF67702541C0F2A119F126184180FF71B56070254180FC25CF3026184140504F9E0770254100D4259ED0251841407FB1B6D36F254140454D80662518414058515CD96F2541809C10892F25184180A92A49DC6F2541}" }, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ 396149.516931511461735, 701946.887012362480164 ], [ 396135.290253929793835, 701952.702945612370968 ], [ 396128.16362776234746, 701956.304972529411316 ], [ 396121.701370343565941, 701960.99590802937746 ], [ 396114.567854430526495, 701967.100226528942585 ], [ 396056.57486017793417, 702016.727095782756805 ], [ 395908.948843095451593, 702143.050333946943283 ], [ 396013.5798674300313, 702119.276431366801262 ], [ 396150.698359593749046, 701993.097221784293652 ], [ 396159.856805846095085, 701984.66941711306572 ], [ 396149.516931511461735, 701946.887012362480164 ] ] ], [ [ [ 395103.116465263068676, 701741.920230701565742 ], [ 395100.497704096138477, 701774.510716699063778 ], [ 395111.19354884698987, 701791.646837279200554 ], [ 395174.561860427260399, 701893.173537112772465 ], [ 395176.535281680524349, 701797.655027367174625 ], [ 395176.593024346977472, 701794.860413528978825 ], [ 395175.892238344997168, 701785.42309644818306 ], [ 395173.425707846879959, 701776.286631777882576 ], [ 395169.282343428581953, 701767.778774783015251 ], [ 395163.609782595187426, 701760.203986778855324 ], [ 395156.611765094101429, 701753.83359269797802 ], [ 395148.538618512451649, 701748.895938530564308 ], [ 395139.680040430277586, 701745.567861199378967 ], [ 395130.352631263434887, 701743.968454949557781 ], [ 395103.116465263068676, 701741.920230701565742 ] ] ], [ [ [ 395595.883852429687977, 702446.142903611063957 ], [ 395598.232272930443287, 702426.356525868177414 ], [ 395600.18174409493804, 702417.265664778649807 ], [ 395603.780490178614855, 702408.693175368010998 ], [ 395608.904167596250772, 702400.934988781809807 ], [ 395615.375939428806305, 702394.259477198123932 ], [ 395908.948843095451593, 702143.050333946943283 ], [ 395809.856849677860737, 702165.565380781888962 ], [ 395630.627221260219812, 702265.565180778503418 ], [ 395336.956548761576414, 702153.016521364450455 ], [ 395336.671116262674332, 702152.559173196554184 ], [ 395336.392573513090611, 702152.452546112239361 ], [ 395174.561860427260399, 701893.173537112772465 ], [ 395162.249549094587564, 702489.133303195238113 ], [ 395159.556969176977873, 702619.45522103458643 ], [ 395162.106504760682583, 702617.808898866176605 ], [ 395377.859682179987431, 702469.347253531217575 ], [ 395595.883852429687977, 702446.142903611063957 ] ] ], [ [ [ 395595.883852429687977, 702446.142903611063957 ], [ 395586.78741392865777, 702522.782514117658138 ], [ 395586.183084428310394, 702527.874695532023907 ], [ 395586.042664762586355, 702538.32611820101738 ], [ 395588.078421846032143, 702548.578394278883934 ], [ 395592.201117012649775, 702558.183690033853054 ], [ 395595.899928510189056, 702563.420884281396866 ], [ 395598.230960596352816, 702566.721730694174767 ], [ 395605.903845511376858, 702573.819485530257225 ], [ 395614.884798679500818, 702579.167243868112564 ], [ 395756.467848844826221, 702644.501102700829506 ], [ 395793.895267426967621, 702661.772065527737141 ], [ 395795.235487844794989, 702650.201222531497478 ], [ 395799.826357927173376, 702610.570396281778812 ], [ 395712.106717094779015, 702515.904903195798397 ], [ 395708.275031846016645, 702512.354385361075401 ], [ 395660.20229334384203, 702467.80919886380434 ], [ 395636.154441177845001, 702441.856822945177555 ], [ 395609.625294763594866, 702444.680308111011982 ], [ 395595.883852429687977, 702446.142903611063957 ] ] ], [ [ [ 395159.556969176977873, 702619.45522103458643 ], [ 395122.499628596007824, 702643.385291278362274 ], [ 394913.675243094563484, 702778.233774863183498 ], [ 394906.198552012443542, 702966.856757201254368 ], [ 394912.441321678459644, 703124.369237445294857 ], [ 395036.238646261394024, 703019.998383194208145 ], [ 395121.289985343813896, 702948.29544261097908 ], [ 395135.533067096024752, 702936.287592612206936 ], [ 395136.032081846147776, 702935.866333611309528 ], [ 395143.365072429180145, 702928.213133700191975 ], [ 395148.920179430395365, 702919.185592696070671 ], [ 395152.447403345257044, 702909.190533950924873 ], [ 395153.788279928267002, 702898.676119282841682 ], [ 395153.837164346128702, 702896.300795949995518 ], [ 395157.5153065957129, 702718.272936783730984 ], [ 395159.556969176977873, 702619.45522103458643 ] ] ] ] } },
{ "type": "Feature", "properties": { "final_zone": "Protection", "id": 6528, "ids": "{24943,24958,24944}", "sub": "{NULL,NULL,NULL}", "polys": "{0103000020600D00000100000012000000809C10892F25184180A92A49DC6F254180F4D8ED38251841008F8AB6B46F2541C01F1BBA40251841C0360588A26F254140D1381F4F251841C0E1E762916F2541401CDE9D6325184180D9B6DE816F25410044F6807D2518410032DA84746F254140869DCB132A1841005FC5197E6D254180006A6D8728184100977921AB6D2541404A4682BA25184100605F21736E2541C08481D323211841007D7508926D2541001A39AF2221184100F34B1E916D254180CAFE9121211841C01FB4E7906D25410057583F9A1E184140DBD9588A6B254140CC89FF681E18410051404432702541C020563A5E1E184140BB12E93671254180950F6D681E184100FE279E3371254180865070C72118410037CBB10A702541809C10892F25184180A92A49DC6F2541:0103000020600D0000010000000800000000FCC09447281841402C4C8B8B7125410039C63BE7271841C0277F6A2B73254140AF6ACEC52B18418064CFF60F7425410016F5E8C52B18418053136E0F74254180A770A7D02B184180D47935FD7325410023A618E22B1841C0893B55EC7325418005A7326B2D1841C0949217BB72254100FCC09447281841402C4C8B8B712541:0103000020600D00000100000014000000809C10892F25184180A92A49DC6F254140D64F260B251841C0B0A59075702541007C7ABB08251841C017D8BF7F702541C04FB02B0825184100F7F8A69470254100D14D5010251841804B2328A9702541409EF1CD20251841C09E0C5EBC70254100DC86992F25184100257ED7C670254140EF80EC3825184180AF8671CD70254180AD899D57251841809B93A3DB70254140AA088A7B25184100FDA055E670254180C413DFB1271841808890006971254100FCC09447281841402C4C8B8B712541C0B923F14C281841C0A3066774712541C0C5304E5F28184140FB0A2425712541003F476D00271841C0784FCF67702541C0F2A119F126184180FF71B56070254180FC25CF3026184140504F9E0770254100D4259ED0251841407FB1B6D36F254140454D80662518414058515CD96F2541809C10892F25184180A92A49DC6F2541}" }, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ 395793.895267426967621, 702661.772065527737141 ], [ 395769.808373346924782, 702869.708001367747784 ], [ 396017.451578844338655, 702983.982051029801369 ], [ 396017.477497428655624, 702983.714991196990013 ], [ 396020.16351567953825, 702974.604445114731789 ], [ 396024.524071261286736, 702966.166469864547253 ], [ 396122.799465261399746, 702813.546040199697018 ], [ 395793.895267426967621, 702661.772065527737141 ] ] ], [ [ [ 395595.883852429687977, 702446.142903611063957 ], [ 395598.232272930443287, 702426.356525868177414 ], [ 395600.18174409493804, 702417.265664778649807 ], [ 395603.780490178614855, 702408.693175368010998 ], [ 395608.904167596250772, 702400.934988781809807 ], [ 395615.375939428806305, 702394.259477198123932 ], [ 395908.948843095451593, 702143.050333946943283 ], [ 395809.856849677860737, 702165.565380781888962 ], [ 395630.627221260219812, 702265.565180778503418 ], [ 395336.956548761576414, 702153.016521364450455 ], [ 395336.671116262674332, 702152.559173196554184 ], [ 395336.392573513090611, 702152.452546112239361 ], [ 395174.561860427260399, 701893.173537112772465 ], [ 395162.249549094587564, 702489.133303195238113 ], [ 395159.556969176977873, 702619.45522103458643 ], [ 395162.106504760682583, 702617.808898866176605 ], [ 395377.859682179987431, 702469.347253531217575 ], [ 395595.883852429687977, 702446.142903611063957 ] ] ], [ [ [ 395595.883852429687977, 702446.142903611063957 ], [ 395586.78741392865777, 702522.782514117658138 ], [ 395586.183084428310394, 702527.874695532023907 ], [ 395586.042664762586355, 702538.32611820101738 ], [ 395588.078421846032143, 702548.578394278883934 ], [ 395592.201117012649775, 702558.183690033853054 ], [ 395595.899928510189056, 702563.420884281396866 ], [ 395598.230960596352816, 702566.721730694174767 ], [ 395605.903845511376858, 702573.819485530257225 ], [ 395614.884798679500818, 702579.167243868112564 ], [ 395756.467848844826221, 702644.501102700829506 ], [ 395793.895267426967621, 702661.772065527737141 ], [ 395795.235487844794989, 702650.201222531497478 ], [ 395799.826357927173376, 702610.570396281778812 ], [ 395712.106717094779015, 702515.904903195798397 ], [ 395708.275031846016645, 702512.354385361075401 ], [ 395660.20229334384203, 702467.80919886380434 ], [ 395636.154441177845001, 702441.856822945177555 ], [ 395609.625294763594866, 702444.680308111011982 ], [ 395595.883852429687977, 702446.142903611063957 ] ] ] ] } },
{ "type": "Feature", "properties": { "final_zone": "Protection", "id": 6610, "ids": "{24943,24971}", "sub": "{NULL,NULL}", "polys": "{0103000020600D00000100000012000000809C10892F25184180A92A49DC6F254180F4D8ED38251841008F8AB6B46F2541C01F1BBA40251841C0360588A26F254140D1381F4F251841C0E1E762916F2541401CDE9D6325184180D9B6DE816F25410044F6807D2518410032DA84746F254140869DCB132A1841005FC5197E6D254180006A6D8728184100977921AB6D2541404A4682BA25184100605F21736E2541C08481D323211841007D7508926D2541001A39AF2221184100F34B1E916D254180CAFE9121211841C01FB4E7906D25410057583F9A1E184140DBD9588A6B254140CC89FF681E18410051404432702541C020563A5E1E184140BB12E93671254180950F6D681E184100FE279E3371254180865070C72118410037CBB10A702541809C10892F25184180A92A49DC6F2541:0103000020600D00000100000010000000C020563A5E1E184140BB12E93671254180A39EFFC91D184100E644C56671254100ED72B3861A1841C056B17774722541003851CB681A184140E1A8B6ED73254180D4E9C3811A1841C0B00CBD2875254180AF5FF4701C184100152CFF5774254100EBF128C51D184100414497C87325414057DC21FE1D1841C0563F93B07325414010DA20001E1841401490BBAF732541008CD5751D1E184140DC1F6DA0732541408443AE331E18418001065F8E732541401A24CA411E184180AA8D617A73254180DA3227471E1841804E2C5A65732541409C4159471E184140ED019A607325414088AC0F561E1841C05EBE8BFC712541C020563A5E1E184140BB12E936712541}" }, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ 395595.883852429687977, 702446.142903611063957 ], [ 395598.232272930443287, 702426.356525868177414 ], [ 395600.18174409493804, 702417.265664778649807 ], [ 395603.780490178614855, 702408.693175368010998 ], [ 395608.904167596250772, 702400.934988781809807 ], [ 395615.375939428806305, 702394.259477198123932 ], [ 395908.948843095451593, 702143.050333946943283 ], [ 395809.856849677860737, 702165.565380781888962 ], [ 395630.627221260219812, 702265.565180778503418 ], [ 395336.956548761576414, 702153.016521364450455 ], [ 395336.671116262674332, 702152.559173196554184 ], [ 395336.392573513090611, 702152.452546112239361 ], [ 395174.561860427260399, 701893.173537112772465 ], [ 395162.249549094587564, 702489.133303195238113 ], [ 395159.556969176977873, 702619.45522103458643 ], [ 395162.106504760682583, 702617.808898866176605 ], [ 395377.859682179987431, 702469.347253531217575 ], [ 395595.883852429687977, 702446.142903611063957 ] ] ], [ [ [ 395159.556969176977873, 702619.45522103458643 ], [ 395122.499628596007824, 702643.385291278362274 ], [ 394913.675243094563484, 702778.233774863183498 ], [ 394906.198552012443542, 702966.856757201254368 ], [ 394912.441321678459644, 703124.369237445294857 ], [ 395036.238646261394024, 703019.998383194208145 ], [ 395121.289985343813896, 702948.29544261097908 ], [ 395135.533067096024752, 702936.287592612206936 ], [ 395136.032081846147776, 702935.866333611309528 ], [ 395143.365072429180145, 702928.213133700191975 ], [ 395148.920179430395365, 702919.185592696070671 ], [ 395152.447403345257044, 702909.190533950924873 ], [ 395153.788279928267002, 702898.676119282841682 ], [ 395153.837164346128702, 702896.300795949995518 ], [ 395157.5153065957129, 702718.272936783730984 ], [ 395159.556969176977873, 702619.45522103458643 ] ] ] ] } },
{ "type": "Feature", "properties": { "final_zone": "Protection", "id": 6781, "ids": "{24912,24906,24943}", "sub": "{NULL,NULL,NULL}", "polys": "{0103000020600D0000010000000F00000080AB42777C1D1841807A28D75B6A25418024A6FD711D1841C0A87C059D6A254140AB31C69C1D184180412E4BBF6A25410057583F9A1E184140DBD9588A6B254180E12024A21E184140BF5F4FCB6A254140C6415FA21E1841401F88B8C56A2541C0EDA6919F1E18410019A0D8B26A254100C2ECB3951E18418066C192A06A2541C0A21E21851E18418091BB8E8F6A254140D96A706E1E184180F47068806A2541808C7272521E184180A9CCAA736A2541809C8B27321E18418074B8CA696A2541C0845CB80E1E184100B4BE22636A2541802B1869E91D1841C053D9EF5F6A254180AB42777C1D1841807A28D75B6A2541:0103000020600D0000010000000800000080AB42777C1D1841807A28D75B6A2541C0271E3DA71D184140B275AC5169254140685F6D071C184140B0798D9E68254100C38500071C18414060A1369E68254100AEDC2B051C18410053B86C9D68254180D1C5B9FA1A1841C06E9CBBC867254140FFAB2D071B184100F1592E446A254180AB42777C1D1841807A28D75B6A2541:0103000020600D00000100000012000000809C10892F25184180A92A49DC6F254180F4D8ED38251841008F8AB6B46F2541C01F1BBA40251841C0360588A26F254140D1381F4F251841C0E1E762916F2541401CDE9D6325184180D9B6DE816F25410044F6807D2518410032DA84746F254140869DCB132A1841005FC5197E6D254180006A6D8728184100977921AB6D2541404A4682BA25184100605F21736E2541C08481D323211841007D7508926D2541001A39AF2221184100F34B1E916D254180CAFE9121211841C01FB4E7906D25410057583F9A1E184140DBD9588A6B254140CC89FF681E18410051404432702541C020563A5E1E184140BB12E93671254180950F6D681E184100FE279E3371254180865070C72118410037CBB10A702541809C10892F25184180A92A49DC6F2541}" }, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ 395595.883852429687977, 702446.142903611063957 ], [ 395598.232272930443287, 702426.356525868177414 ], [ 395600.18174409493804, 702417.265664778649807 ], [ 395603.780490178614855, 702408.693175368010998 ], [ 395608.904167596250772, 702400.934988781809807 ], [ 395615.375939428806305, 702394.259477198123932 ], [ 395908.948843095451593, 702143.050333946943283 ], [ 395809.856849677860737, 702165.565380781888962 ], [ 395630.627221260219812, 702265.565180778503418 ], [ 395336.956548761576414, 702153.016521364450455 ], [ 395336.671116262674332, 702152.559173196554184 ], [ 395336.392573513090611, 702152.452546112239361 ], [ 395174.561860427260399, 701893.173537112772465 ], [ 395162.249549094587564, 702489.133303195238113 ], [ 395159.556969176977873, 702619.45522103458643 ], [ 395162.106504760682583, 702617.808898866176605 ], [ 395377.859682179987431, 702469.347253531217575 ], [ 395595.883852429687977, 702446.142903611063957 ] ] ], [ [ [ 395103.116465263068676, 701741.920230701565742 ], [ 395113.809685345739126, 701608.836835451424122 ], [ 395009.856809262186289, 701519.276319034397602 ], [ 395009.750510260462761, 701519.106699950993061 ], [ 395009.292834013700485, 701518.712343782186508 ], [ 394942.681418679654598, 701412.366427861154079 ], [ 394945.794601429253817, 701730.090529948472977 ], [ 395103.116465263068676, 701741.920230701565742 ] ] ], [ [ [ 395103.116465263068676, 701741.920230701565742 ], [ 395100.497704096138477, 701774.510716699063778 ], [ 395111.19354884698987, 701791.646837279200554 ], [ 395174.561860427260399, 701893.173537112772465 ], [ 395176.535281680524349, 701797.655027367174625 ], [ 395176.593024346977472, 701794.860413528978825 ], [ 395175.892238344997168, 701785.42309644818306 ], [ 395173.425707846879959, 701776.286631777882576 ], [ 395169.282343428581953, 701767.778774783015251 ], [ 395163.609782595187426, 701760.203986778855324 ], [ 395156.611765094101429, 701753.83359269797802 ], [ 395148.538618512451649, 701748.895938530564308 ], [ 395139.680040430277586, 701745.567861199378967 ], [ 395130.352631263434887, 701743.968454949557781 ], [ 395103.116465263068676, 701741.920230701565742 ] ] ] ] } }
]
}

would a recursive query be of use here?!!

2 Answers 2

4

If I'm understanding the problem, it looks like something you could solve using ST_ClusterDBSCAN:

SELECT
  sum(ST_Area(geom)) OVER (PARTITION BY cluster_id) AS cluster_area,
  *
FROM (
  ST_ClusterDBSCAN(geom, eps := 1e-10) 
     OVER (PARTITION BY final_zone) AS cluster_id,
  *
  FROM my_table) sq;
4
  • why eps := 1e-10?
    – ziggy
    Feb 6, 2018 at 17:41
  • Because you want to group geometries that intersect, and I was too lazy to check if eps := 0 is allowed.
    – dbaston
    Feb 6, 2018 at 17:59
  • interesting, this might work here...seems almost too easy given how overly complicated my attempt was. I'll update when I test it against my big table
    – ziggy
    Feb 6, 2018 at 18:01
  • this pretty much solved my problem with a few tweaks and querys. What an amazing function ST_ClusterDBSCAN is. Thanks!
    – ziggy
    Mar 26, 2018 at 23:07
2

Hopefully I have understood your question correctly. From what I can tell you would like to dissolve touching zones and aggregate the attributes for those zones.

The following query should do that for you. The first CTE just sets up a couple copies of of the sample you provided with a shift on the second set. This means we will have to sets of touching zones to work with.

WITH attributes AS ( -- attributes against a point feature
    SELECT objectid, final_zone, sub_zone, acres, ST_PointOnSurface(geom) point 
    FROM t1
),
polygons AS ( -- union and disaggregate polygons
    SELECT(ST_Dump(dissolved)).path pid, final_zone, (st_dump(dissolved)).geom geom
    FROM (SELECT final_zone, ST_Union(geom) dissolved
        FROM t1
        GROUP BY final_zone) d
)
-- match attributes to polygons
SELECT p.pid, p.final_zone, 
    array_agg(a.objectid), array_agg(a.sub_zone), array_agg(a.acres),
    min(geom) geom
FROM polygons p
    INNER JOIN attributes a ON ST_Contains(p.geom, a.point)
GROUP BY p.pid, p.final_zone;

I tested it against your sample set of data and a duplicate with a eastward shift applied

with t1 as ( -- sample as provided
    select 
         json_extract_path_text(value,'properties','objectid')::integer objectid
        ,json_extract_path_text(value,'properties','final_zone') final_zone
        ,json_extract_path_text(value,'properties','sub_zone') sub_zone
        ,json_extract_path_text(value,'properties','acres')::float acres
        ,ST_GeomFromGeoJSON(json_extract_path_text(value,'geometry')) geom
    from json_array_elements(json_extract_path('{
        "type": "FeatureCollection",
        "name": "ls_75_pre_resolve",
        "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:EPSG::3424" } },
        "features": [
        { "type": "Feature", "properties": { "objectid": 4260, "final_zone": "Conservation", "sub_zone": null, "acres": 2.51445222 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 480236.291342929005623, 876093.535765618085861 ], [ 480251.46650942787528, 876081.082050368189812 ], [ 480255.16269626095891, 876078.43343361467123 ], [ 480267.424154680222273, 876069.646377697587013 ], [ 480284.095381177961826, 876059.279928617179394 ], [ 480301.408666763454676, 876050.026010110974312 ], [ 480310.985747344791889, 876045.51158344745636 ], [ 480317.441115010529757, 876042.606733612716198 ], [ 480322.510986760258675, 876040.416121199727058 ], [ 480275.811933178454638, 876001.87617202848196 ], [ 480228.769376344978809, 876022.246210031211376 ], [ 480220.12700517848134, 876025.988328531384468 ], [ 480213.790731761604548, 876011.439145028591156 ], [ 480168.258998677134514, 875893.721204616129398 ], [ 480180.222557429224253, 875890.492208451032639 ], [ 480165.193059928715229, 875849.185860611498356 ], [ 480165.056249178946018, 875848.814470283687115 ], [ 480137.751841846853495, 875775.581020951271057 ], [ 480139.586811929941177, 875774.809697031974792 ], [ 480137.843049012124538, 875774.375970862805843 ], [ 480136.989704262465239, 875773.537389867007732 ], [ 480131.031710930168629, 875767.686679780483246 ], [ 480123.391962427645922, 875770.626634530723095 ], [ 480109.389365762472153, 875776.015075199306011 ], [ 480084.68469076231122, 875706.965344533324242 ], [ 480059.616171345114708, 875628.294570118188858 ], [ 480027.559805013239384, 875515.667826861143112 ], [ 480001.934528179466724, 875420.043674282729626 ], [ 480016.574262678623199, 875416.725439451634884 ], [ 480006.531631845980883, 875379.248808361589909 ], [ 479984.981478095054626, 875293.047865279018879 ], [ 479971.151125177741051, 875229.085722863674164 ], [ 479958.975952677428722, 875175.155056446790695 ], [ 479958.789273262023926, 875174.382748283445835 ], [ 479946.629192594438791, 875127.502920784056187 ], [ 479946.332933343946934, 875126.456334948539734 ], [ 479929.842808846384287, 875072.636232614517212 ], [ 479929.53605092689395, 875071.700210861861706 ], [ 479901.546605594456196, 874991.740397028625011 ], [ 479895.041369263082743, 874973.154476195573807 ], [ 479893.063026763498783, 874967.502912700176239 ], [ 479867.952840760350227, 874896.49157178401947 ], [ 479850.844935346394777, 874845.15735686570406 ], [ 479841.018183346837759, 874810.520615115761757 ], [ 479807.094694763422012, 874825.373603783547878 ], [ 479783.968100596219301, 874709.687811531126499 ], [ 479721.405889760702848, 874337.437243700027466 ], [ 479689.125770594924688, 874145.311315611004829 ], [ 479659.063166677951813, 874008.811916701495647 ], [ 479653.938505012542009, 873991.685966700315475 ], [ 479564.217555847018957, 873698.811880528926849 ], [ 479596.750299178063869, 873684.874900534749031 ], [ 479582.345800429582596, 873662.126586444675922 ], [ 479567.959674347192049, 873639.360555864870548 ], [ 479563.976086512207985, 873625.776265449821949 ], [ 479563.849446345120668, 873625.375675700604916 ], [ 479563.836323011666536, 873625.336633779108524 ], [ 479563.719525344669819, 873624.996411368250847 ], [ 479562.537113010883331, 873611.983970195055008 ], [ 479560.540397845208645, 873611.190992780029774 ], [ 479551.084708094596863, 873609.417702361941338 ], [ 479541.464320510625839, 873609.488240279257298 ], [ 479533.909873679280281, 873611.020389445126057 ], [ 479523.997163847088814, 873617.125036031007767 ], [ 479530.552925013005733, 873636.179787948727608 ], [ 479540.625083345919847, 873670.527816362679005 ], [ 479564.253973096609116, 873751.110988534986973 ], [ 479605.145951677113771, 873888.560516782104969 ], [ 479641.506771344691515, 874001.97105111181736 ], [ 479649.876505263149738, 874032.942773945629597 ], [ 479661.87615317851305, 874087.859658867120743 ], [ 479677.410899013280869, 874160.292912945151329 ], [ 479692.064741093665361, 874243.079164698719978 ], [ 479714.603409927338362, 874379.17009986191988 ], [ 479744.931105177849531, 874563.691056281328201 ], [ 479758.487836595624685, 874663.735803700983524 ], [ 479758.766051262617111, 874665.787964947521687 ], [ 479758.931733343750238, 874666.838159695267677 ], [ 479771.103625010699034, 874733.744521945714951 ], [ 479771.318191513419151, 874734.794060528278351 ], [ 479786.067833930253983, 874799.894324034452438 ], [ 479786.399198096245527, 874801.192877866327763 ], [ 479802.899165093898773, 874859.352866530418396 ], [ 479803.23249776288867, 874860.43422919511795 ], [ 479820.665861845016479, 874912.739242695271969 ], [ 479841.274088345468044, 874971.023246861994267 ], [ 479845.896454427391291, 874984.096383444964886 ], [ 479854.354114677757025, 875008.25906477868557 ], [ 479882.181814927607775, 875087.755953028798103 ], [ 479898.365181513130665, 875140.577697783708572 ], [ 479910.29265109449625, 875186.561529695987701 ], [ 479922.320514179766178, 875239.837997950613499 ], [ 479936.194174095988274, 875304.00289586186409 ], [ 479936.375932261347771, 875304.783078029751778 ], [ 479958.129169594496489, 875391.787169113755226 ], [ 479975.851903177797794, 875457.929753363132477 ], [ 479993.823323927819729, 875524.987362034618855 ], [ 480025.994847513735294, 875638.023553282022476 ], [ 480026.219584595412016, 875638.769614778459072 ], [ 480051.389809846878052, 875717.75994236767292 ], [ 480051.671305347234011, 875718.59163361787796 ], [ 480089.910730179399252, 875825.471341118216515 ], [ 480102.481243096292019, 875859.690104700624943 ], [ 480093.281130261719227, 875864.841997280716896 ], [ 480079.566262677311897, 875909.889151282608509 ], [ 480004.411229260265827, 875933.250981196761131 ], [ 480050.123080097138882, 876042.000763699412346 ], [ 480104.407748427242041, 876070.186730951070786 ], [ 480166.19929151237011, 876099.243431366980076 ], [ 480195.237947344779968, 876093.041015945374966 ], [ 480208.945925179868937, 876120.853952445089817 ], [ 480221.962959513068199, 876106.955686278641224 ], [ 480236.291342929005623, 876093.535765618085861 ] ], [ [ 480052.718547344207764, 875674.249858781695366 ], [ 480049.450837343931198, 875662.26727119833231 ], [ 480056.397017680108547, 875684.066768281161785 ], [ 480052.718547344207764, 875674.249858781695366 ] ] ] } },
        { "type": "Feature", "properties": { "objectid": 5179, "final_zone": "Conservation", "sub_zone": "CZECSZ", "acres": 0.00013773 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 480056.397017680108547, 875684.066768281161785 ], [ 480049.450837343931198, 875662.26727119833231 ], [ 480052.718547344207764, 875674.249858781695366 ], [ 480056.397017680108547, 875684.066768281161785 ] ] ] } },
        { "type": "Feature", "properties": { "objectid": 5180, "final_zone": "Conservation", "sub_zone": "CZECSZ", "acres": 0.37008089 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 480123.391962427645922, 875770.626634530723095 ], [ 480131.031710930168629, 875767.686679780483246 ], [ 480136.989376179873943, 875773.537061780691147 ], [ 480093.656129512935877, 875657.313540950417519 ], [ 480014.554253596812487, 875364.482433699071407 ], [ 479972.742001261562109, 875194.960791110992432 ], [ 479972.599941179156303, 875194.399440534412861 ], [ 479942.091800093650818, 875076.592917613685131 ], [ 479940.841146428138018, 875072.495156779885292 ], [ 479905.951780512928963, 874973.586890034377575 ], [ 479870.116550344973803, 874871.997854366898537 ], [ 479850.889882761985064, 874806.198445282876492 ], [ 479841.018183346837759, 874810.520615115761757 ], [ 479850.844935346394777, 874845.15735686570406 ], [ 479867.952840760350227, 874896.49157178401947 ], [ 479893.063026763498783, 874967.502912700176239 ], [ 479895.041041180491447, 874973.154476195573807 ], [ 479901.546605594456196, 874991.740397028625011 ], [ 479929.53605092689395, 875071.700210861861706 ], [ 479929.842808846384287, 875072.636232614517212 ], [ 479946.332933343946934, 875126.456334948539734 ], [ 479946.629192594438791, 875127.502920784056187 ], [ 479958.789273262023926, 875174.382748283445835 ], [ 479958.975952677428722, 875175.155056446790695 ], [ 479971.151125177741051, 875229.085722863674164 ], [ 479984.981478095054626, 875293.047865279018879 ], [ 480006.531631845980883, 875379.248808361589909 ], [ 480016.574262678623199, 875416.725439451634884 ], [ 480001.934528179466724, 875420.043674282729626 ], [ 480027.559805013239384, 875515.667826861143112 ], [ 480059.616171345114708, 875628.294570118188858 ], [ 480084.68469076231122, 875706.965344533324242 ], [ 480109.389365762472153, 875776.015075199306011 ], [ 480123.391962427645922, 875770.626634530723095 ] ] ] } },
        { "type": "Feature", "properties": { "objectid": 5181, "final_zone": "Conservation", "sub_zone": "CZECSZ", "acres": 0.03532003 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 480226.07384368032217, 876016.508032530546188 ], [ 480180.222557429224253, 875890.492208451032639 ], [ 480168.258998677134514, 875893.721204616129398 ], [ 480213.790731761604548, 876011.439145028591156 ], [ 480220.12700517848134, 876025.988328531384468 ], [ 480228.769376344978809, 876022.246210031211376 ], [ 480226.07384368032217, 876016.508032530546188 ] ] ] } }
        ]
        }','features')) j
    union all
    select 
         json_extract_path_text(value,'properties','objectid')::integer objectid
        ,json_extract_path_text(value,'properties','final_zone') final_zone
        ,json_extract_path_text(value,'properties','sub_zone') sub_zone
        ,json_extract_path_text(value,'properties','acres')::float acres
        ,ST_Translate( -- Move this set 50000 east
            ST_GeomFromGeoJSON(json_extract_path_text(value,'geometry')) 
            ,50000
            ,0
          ) geom
    from json_array_elements(json_extract_path('{
        "type": "FeatureCollection",
        "name": "ls_75_pre_resolve",
        "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:EPSG::3424" } },
        "features": [
        { "type": "Feature", "properties": { "objectid": 4260, "final_zone": "Conservation", "sub_zone": null, "acres": 2.51445222 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 480236.291342929005623, 876093.535765618085861 ], [ 480251.46650942787528, 876081.082050368189812 ], [ 480255.16269626095891, 876078.43343361467123 ], [ 480267.424154680222273, 876069.646377697587013 ], [ 480284.095381177961826, 876059.279928617179394 ], [ 480301.408666763454676, 876050.026010110974312 ], [ 480310.985747344791889, 876045.51158344745636 ], [ 480317.441115010529757, 876042.606733612716198 ], [ 480322.510986760258675, 876040.416121199727058 ], [ 480275.811933178454638, 876001.87617202848196 ], [ 480228.769376344978809, 876022.246210031211376 ], [ 480220.12700517848134, 876025.988328531384468 ], [ 480213.790731761604548, 876011.439145028591156 ], [ 480168.258998677134514, 875893.721204616129398 ], [ 480180.222557429224253, 875890.492208451032639 ], [ 480165.193059928715229, 875849.185860611498356 ], [ 480165.056249178946018, 875848.814470283687115 ], [ 480137.751841846853495, 875775.581020951271057 ], [ 480139.586811929941177, 875774.809697031974792 ], [ 480137.843049012124538, 875774.375970862805843 ], [ 480136.989704262465239, 875773.537389867007732 ], [ 480131.031710930168629, 875767.686679780483246 ], [ 480123.391962427645922, 875770.626634530723095 ], [ 480109.389365762472153, 875776.015075199306011 ], [ 480084.68469076231122, 875706.965344533324242 ], [ 480059.616171345114708, 875628.294570118188858 ], [ 480027.559805013239384, 875515.667826861143112 ], [ 480001.934528179466724, 875420.043674282729626 ], [ 480016.574262678623199, 875416.725439451634884 ], [ 480006.531631845980883, 875379.248808361589909 ], [ 479984.981478095054626, 875293.047865279018879 ], [ 479971.151125177741051, 875229.085722863674164 ], [ 479958.975952677428722, 875175.155056446790695 ], [ 479958.789273262023926, 875174.382748283445835 ], [ 479946.629192594438791, 875127.502920784056187 ], [ 479946.332933343946934, 875126.456334948539734 ], [ 479929.842808846384287, 875072.636232614517212 ], [ 479929.53605092689395, 875071.700210861861706 ], [ 479901.546605594456196, 874991.740397028625011 ], [ 479895.041369263082743, 874973.154476195573807 ], [ 479893.063026763498783, 874967.502912700176239 ], [ 479867.952840760350227, 874896.49157178401947 ], [ 479850.844935346394777, 874845.15735686570406 ], [ 479841.018183346837759, 874810.520615115761757 ], [ 479807.094694763422012, 874825.373603783547878 ], [ 479783.968100596219301, 874709.687811531126499 ], [ 479721.405889760702848, 874337.437243700027466 ], [ 479689.125770594924688, 874145.311315611004829 ], [ 479659.063166677951813, 874008.811916701495647 ], [ 479653.938505012542009, 873991.685966700315475 ], [ 479564.217555847018957, 873698.811880528926849 ], [ 479596.750299178063869, 873684.874900534749031 ], [ 479582.345800429582596, 873662.126586444675922 ], [ 479567.959674347192049, 873639.360555864870548 ], [ 479563.976086512207985, 873625.776265449821949 ], [ 479563.849446345120668, 873625.375675700604916 ], [ 479563.836323011666536, 873625.336633779108524 ], [ 479563.719525344669819, 873624.996411368250847 ], [ 479562.537113010883331, 873611.983970195055008 ], [ 479560.540397845208645, 873611.190992780029774 ], [ 479551.084708094596863, 873609.417702361941338 ], [ 479541.464320510625839, 873609.488240279257298 ], [ 479533.909873679280281, 873611.020389445126057 ], [ 479523.997163847088814, 873617.125036031007767 ], [ 479530.552925013005733, 873636.179787948727608 ], [ 479540.625083345919847, 873670.527816362679005 ], [ 479564.253973096609116, 873751.110988534986973 ], [ 479605.145951677113771, 873888.560516782104969 ], [ 479641.506771344691515, 874001.97105111181736 ], [ 479649.876505263149738, 874032.942773945629597 ], [ 479661.87615317851305, 874087.859658867120743 ], [ 479677.410899013280869, 874160.292912945151329 ], [ 479692.064741093665361, 874243.079164698719978 ], [ 479714.603409927338362, 874379.17009986191988 ], [ 479744.931105177849531, 874563.691056281328201 ], [ 479758.487836595624685, 874663.735803700983524 ], [ 479758.766051262617111, 874665.787964947521687 ], [ 479758.931733343750238, 874666.838159695267677 ], [ 479771.103625010699034, 874733.744521945714951 ], [ 479771.318191513419151, 874734.794060528278351 ], [ 479786.067833930253983, 874799.894324034452438 ], [ 479786.399198096245527, 874801.192877866327763 ], [ 479802.899165093898773, 874859.352866530418396 ], [ 479803.23249776288867, 874860.43422919511795 ], [ 479820.665861845016479, 874912.739242695271969 ], [ 479841.274088345468044, 874971.023246861994267 ], [ 479845.896454427391291, 874984.096383444964886 ], [ 479854.354114677757025, 875008.25906477868557 ], [ 479882.181814927607775, 875087.755953028798103 ], [ 479898.365181513130665, 875140.577697783708572 ], [ 479910.29265109449625, 875186.561529695987701 ], [ 479922.320514179766178, 875239.837997950613499 ], [ 479936.194174095988274, 875304.00289586186409 ], [ 479936.375932261347771, 875304.783078029751778 ], [ 479958.129169594496489, 875391.787169113755226 ], [ 479975.851903177797794, 875457.929753363132477 ], [ 479993.823323927819729, 875524.987362034618855 ], [ 480025.994847513735294, 875638.023553282022476 ], [ 480026.219584595412016, 875638.769614778459072 ], [ 480051.389809846878052, 875717.75994236767292 ], [ 480051.671305347234011, 875718.59163361787796 ], [ 480089.910730179399252, 875825.471341118216515 ], [ 480102.481243096292019, 875859.690104700624943 ], [ 480093.281130261719227, 875864.841997280716896 ], [ 480079.566262677311897, 875909.889151282608509 ], [ 480004.411229260265827, 875933.250981196761131 ], [ 480050.123080097138882, 876042.000763699412346 ], [ 480104.407748427242041, 876070.186730951070786 ], [ 480166.19929151237011, 876099.243431366980076 ], [ 480195.237947344779968, 876093.041015945374966 ], [ 480208.945925179868937, 876120.853952445089817 ], [ 480221.962959513068199, 876106.955686278641224 ], [ 480236.291342929005623, 876093.535765618085861 ] ], [ [ 480052.718547344207764, 875674.249858781695366 ], [ 480049.450837343931198, 875662.26727119833231 ], [ 480056.397017680108547, 875684.066768281161785 ], [ 480052.718547344207764, 875674.249858781695366 ] ] ] } },
        { "type": "Feature", "properties": { "objectid": 5179, "final_zone": "Conservation", "sub_zone": "CZECSZ", "acres": 0.00013773 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 480056.397017680108547, 875684.066768281161785 ], [ 480049.450837343931198, 875662.26727119833231 ], [ 480052.718547344207764, 875674.249858781695366 ], [ 480056.397017680108547, 875684.066768281161785 ] ] ] } },
        { "type": "Feature", "properties": { "objectid": 5180, "final_zone": "Conservation", "sub_zone": "CZECSZ", "acres": 0.37008089 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 480123.391962427645922, 875770.626634530723095 ], [ 480131.031710930168629, 875767.686679780483246 ], [ 480136.989376179873943, 875773.537061780691147 ], [ 480093.656129512935877, 875657.313540950417519 ], [ 480014.554253596812487, 875364.482433699071407 ], [ 479972.742001261562109, 875194.960791110992432 ], [ 479972.599941179156303, 875194.399440534412861 ], [ 479942.091800093650818, 875076.592917613685131 ], [ 479940.841146428138018, 875072.495156779885292 ], [ 479905.951780512928963, 874973.586890034377575 ], [ 479870.116550344973803, 874871.997854366898537 ], [ 479850.889882761985064, 874806.198445282876492 ], [ 479841.018183346837759, 874810.520615115761757 ], [ 479850.844935346394777, 874845.15735686570406 ], [ 479867.952840760350227, 874896.49157178401947 ], [ 479893.063026763498783, 874967.502912700176239 ], [ 479895.041041180491447, 874973.154476195573807 ], [ 479901.546605594456196, 874991.740397028625011 ], [ 479929.53605092689395, 875071.700210861861706 ], [ 479929.842808846384287, 875072.636232614517212 ], [ 479946.332933343946934, 875126.456334948539734 ], [ 479946.629192594438791, 875127.502920784056187 ], [ 479958.789273262023926, 875174.382748283445835 ], [ 479958.975952677428722, 875175.155056446790695 ], [ 479971.151125177741051, 875229.085722863674164 ], [ 479984.981478095054626, 875293.047865279018879 ], [ 480006.531631845980883, 875379.248808361589909 ], [ 480016.574262678623199, 875416.725439451634884 ], [ 480001.934528179466724, 875420.043674282729626 ], [ 480027.559805013239384, 875515.667826861143112 ], [ 480059.616171345114708, 875628.294570118188858 ], [ 480084.68469076231122, 875706.965344533324242 ], [ 480109.389365762472153, 875776.015075199306011 ], [ 480123.391962427645922, 875770.626634530723095 ] ] ] } },
        { "type": "Feature", "properties": { "objectid": 5181, "final_zone": "Conservation", "sub_zone": "CZECSZ", "acres": 0.03532003 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 480226.07384368032217, 876016.508032530546188 ], [ 480180.222557429224253, 875890.492208451032639 ], [ 480168.258998677134514, 875893.721204616129398 ], [ 480213.790731761604548, 876011.439145028591156 ], [ 480220.12700517848134, 876025.988328531384468 ], [ 480228.769376344978809, 876022.246210031211376 ], [ 480226.07384368032217, 876016.508032530546188 ] ] ] } }
        ]
        }','features')) j
)
6
  • hmmm this may be more simpler than mine and this does seem to work however it does not get me to the next step of unraveling the old geometries
    – ziggy
    Dec 14, 2017 at 18:18
  • im also not sure if I get the min(geom) why min?
    – ziggy
    Dec 14, 2017 at 18:19
  • @ziggy just a nasty hack to get back a single geometry without doing anything too expensive. geom has to be in some sort of aggregate function since it's not part of the group by clause
    – MickyT
    Dec 14, 2017 at 18:22
  • okay so I actually updated my query this morning, can I update my code on my question and have you take a look? its a bit different and I figured out a different way to roll up then unravel the above example
    – ziggy
    Dec 14, 2017 at 18:23
  • @ziggy I'll have a look. Not sure that I know exactly what you are after yet.
    – MickyT
    Dec 14, 2017 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.