{"id":1489,"date":"2011-11-07T17:59:13","date_gmt":"2011-11-08T01:59:13","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=1489"},"modified":"2013-11-13T18:52:46","modified_gmt":"2013-11-14T02:52:46","slug":"extract-and-sum-classified-areas-by-grid-cell","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=1489","title":{"rendered":"Extract and Sum Classified Areas by Grid Cell"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2012\/06\/PostgreSQL_logo.3colors.120x120.png\" alt=\"PostgreSQL_logo.3colors.120x120\" width=\"120\" height=\"120\" class=\"alignright size-full wp-image-575\" \/><\/p>\n<p>Given a set of classified polygons, in this example classified into three categories by name, apply a reference grid, and sum the areas for each classification into float fields in a destination table. This code contains test harness pieces in it also, so that a smaller example can be executed and verified, before applying the process to large sets.<\/p>\n<pre>\r\ndrop table if exists ca_lt1 cascade; --test_geom_out;\r\ncreate table ca_lt1 --test_geom_out\r\nas    select \r\n  g.gridcode, \r\n  g.the_geom,\r\n  SUM(d.acres_urban) as acres_urban,\r\n  SUM(d2.acres_constrained) as acres_constrained,\r\n  SUM(d3.acres_greenfield) as acres_greenfield\r\nFROM \r\n  grid150m_uniq g\r\n  --test_grid g\r\nLEFT JOIN \r\n( select \r\n     sum(st_area( st_intersection( land.wkb_geometry, g2.the_geom))) as acres_urban,\r\n     g2.gridcode\r\n  from \r\n     grid150m_uniq g2, --test_geom land\r\n     ca_landtypes_df land\r\n   where st_intersects( land.wkb_geometry, g2.the_geom) AND\r\n     land.landtype = 'Urban' \r\n     AND st_intersects( st_geomfromEWKT('SRID=3310;POLYGON((-147472.090  71129.874, -113634.730  71129.874,   -113634.730  50253.940,   -147472.090 50253.940,  -147472.090 71129.874))'), g2.the_geom)\r\n  group by g2.gridcode\r\n   ) d\r\nON ( g.gridcode = d.gridcode )\r\n\r\nLEFT JOIN \r\n( select \r\n     sum(st_area( st_intersection( land.wkb_geometry, g2.the_geom))) as acres_constrained,\r\n     g2.gridcode\r\n  from \r\n     grid150m_uniq g2, --test_geom land\r\n     ca_landtypes_df land\r\n   where st_intersects( land.wkb_geometry, g2.the_geom) AND\r\n     land.landtype = 'Constrained' \r\n     AND st_intersects( st_geomfromEWKT('SRID=3310;POLYGON((-147472.090  71129.874, -113634.730  71129.874,   -113634.730  50253.940,   -147472.090 50253.940,  -147472.090 71129.874))'), g2.the_geom)\r\n  group by g2.gridcode\r\n   ) d2\r\nON ( g.gridcode = d2.gridcode )\r\n\r\nLEFT JOIN \r\n( select \r\n     sum(st_area( st_intersection( land.wkb_geometry, g2.the_geom))) as acres_greenfield,\r\n     g2.gridcode\r\n  from \r\n     grid150m_uniq g2, --test_geom land\r\n     ca_landtypes_df land\r\n   where st_intersects( land.wkb_geometry, g2.the_geom) AND\r\n     land.landtype = 'Greenfield' \r\n     AND st_intersects( st_geomfromEWKT('SRID=3310;POLYGON((-147472.090  71129.874, -113634.730  71129.874,   -113634.730  50253.940,   -147472.090 50253.940,  -147472.090 71129.874))'), g2.the_geom)\r\n  group by g2.gridcode\r\n   ) d3\r\nON ( g.gridcode = d3.gridcode )\r\n\r\n  WHERE st_intersects( st_geomfromtext('SRID=3310;POLYGON((-147472.090  71129.874, -113634.730  71129.874,   -113634.730  50253.940,   -147472.090 50253.940,  -147472.090 71129.874))'), g.the_geom)\r\n\r\nGROUP BY \r\n  g.gridcode,\r\n  g.the_geom;\r\n\r\n<\/pre>\n<hr \/>\n<p><a href=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/sandag_landtypes_sm.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/sandag_landtypes_sm.png\" alt=\"sandag_landtypes_sm\" width=\"792\" height=\"660\" class=\"alignnone size-full wp-image-1494\" srcset=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/sandag_landtypes_sm.png 792w, http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/sandag_landtypes_sm-300x250.png 300w\" sizes=\"(max-width: 792px) 100vw, 792px\" \/><\/a><\/p>\n<p><a href=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/sandag_minus_parcels3.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/sandag_minus_parcels3.png\" alt=\"sandag_minus_parcels3\" width=\"792\" height=\"639\" class=\"alignnone size-full wp-image-1495\" srcset=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/sandag_minus_parcels3.png 792w, http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/sandag_minus_parcels3-300x242.png 300w\" sizes=\"(max-width: 792px) 100vw, 792px\" \/><\/a><\/p>\n<p><a href=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/p_gridded.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/p_gridded.png\" alt=\"p_gridded\" width=\"973\" height=\"440\" class=\"alignnone size-full wp-image-1497\" srcset=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/p_gridded.png 973w, http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2011\/11\/p_gridded-300x135.png 300w\" sizes=\"(max-width: 973px) 100vw, 973px\" \/><\/a><\/p>\n<hr \/>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Given a set of classified polygons, in this example classified into three categories by name, apply a reference grid, and sum the areas for each classification into float fields in a destination table. This code contains test harness pieces in it also, so that a smaller example can be executed and verified, before applying the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1489"}],"collection":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1489"}],"version-history":[{"count":10,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1489\/revisions"}],"predecessor-version":[{"id":1515,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1489\/revisions\/1515"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1489"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1489"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1489"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}