1

I have a municipal polygon shapefile of 380 municipal records with receptor(schools, hospitals etc..) sums for each municipality. I am trying to use Postgres/PostGIS to dissolve this municipal polygon shapefile layer to county polygon shapefile that contains the sums of the receptors by county

I know I can use the ArcGIS dissolve tool but i want to learn it in postgres

I know how to do the qry where it just returns the records summed but i do not know how to include the geometry for the new county polygon shapefile.

select distinct county as county, sum(schools) as schools,
sum(childcare), sum(respopint) as rez, etc... into countyreport
from munsumreport
group by county;

when I include the geom field, it just returns the the municipal amount of records. when I do sum(geom) as geom in the select and include in the groupby clause it gives me an error - do i have to sum the shape area? not sure what to do

  • Check my answer here: gis.stackexchange.com/questions/200438/… what you want to do is use a sub-query to first dissolve the geometry, then join your calculations back to your geometry by the unique ID of your geometry features... let me know if you need more help... – DPSSpatial Sep 6 '16 at 15:59
  • ahh okay I see what you are doing - I will give that a try. – ziggy Sep 6 '16 at 16:06
  • what about the st_union functions used on the geometry? – ziggy Sep 6 '16 at 16:06
  • see the answer just provided below... – DPSSpatial Sep 6 '16 at 16:19
  • 1
    Welcome to gis.stackexchange! Please edit the title of your question to include enough information for future visitors to be able to find this thread when looking for the same problem. – underdark Sep 6 '16 at 17:14
6

You want ST_Union for your geometry instead of sum:

select distinct ST_Union(geom) as geom, county as county, sum(schools) as schools,
sum(childcare), sum(respopint) as rez, etc... into countyreport
from munsumreport
group by county;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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