2

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

5
  • 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... Sep 6, 2016 at 15:59
  • ahh okay I see what you are doing - I will give that a try.
    – ziggy
    Sep 6, 2016 at 16:06
  • what about the st_union functions used on the geometry?
    – ziggy
    Sep 6, 2016 at 16:06
  • see the answer just provided below... Sep 6, 2016 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, 2016 at 17:14

1 Answer 1

7

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 and acknowledge you have read our privacy policy.

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