{"id":1048,"date":"2013-03-05T19:29:00","date_gmt":"2013-03-06T03:29:00","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=1048"},"modified":"2020-04-13T08:39:17","modified_gmt":"2020-04-13T15:39:17","slug":"slice-a-polygon-by-a-grid","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=1048","title":{"rendered":"Slice a Polygon by a Grid"},"content":{"rendered":"<p><script type=\"text\/javascript\">\/\/ < ![CDATA[\n\/\/ < ![CDATA[\njQuery(document).ready(function($) { $(\"#gallery a\").lightBox({fixedNavigation:true});});\n\/\/ ]]><\/script><\/p>\n<p>Sometimes there are polygons with too many vertices to process comfortably. For that or other reasons, you may want to cut one or more polygons with a reference grid. Here is an example using the <strong>US 2010 Census<\/strong> and a set of grid cells in <a href=\"http:\/\/spatialreference.org\/ref\/epsg\/3310\/\" title=\"EPSG:3310\" target=\"_blank\" rel=\"noopener noreferrer\">EPSG:3310<\/a>:<\/p>\n<p><img decoding=\"async\" alt=\"pg_logo\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2012\/06\/PostgreSQL_logo.3colors.120x120.png\" width=\"108px\" height=\"108px\" align=\"right\" \/><br \/>\n<code><br \/>\n--------------------------------------------------------------------<br \/>\n-- take a single census place polygon and make a table of it<br \/>\n--  transform from LonLat to EPSG:3310<br \/>\n--------------------------------------------------------------------<br \/>\ncreate table davis_place3310 as<br \/>\nSELECT<br \/>\n&nbsp;gid,   statefp10,placefp10,placens10,geoid10,<br \/>\n&nbsp;name10,namelsad10,lsad10,classfp10,pcicbsa10,pcinecta10,<br \/>\n&nbsp;mtfcc10,funcstat10,aland10,awater10,intptlat10,intptlon10,<\/p>\n<p>&nbsp;ST_SetSRID( ST_Transform( the_geom, 3310), 3310) as wkb_geometry<br \/>\nFROM tl_2010_06_place10 where namelsad10 = 'Davis city';<br \/>\n--------------------------------------------------------------------<br \/>\nalter table davis_place3310 add primary key (gid);<br \/>\ncreate index tdavis_geom_idx on davis_place3310 using GIST (wkb_geometry);<br \/>\n---------------------------------------------------------------------<\/p>\n<p>--------------------------------------------------------------------<br \/>\n-- now make a sliced result with a grid and input poly<br \/>\n--------------------------------------------------------------------<br \/>\ncreate table davis_sliced as<br \/>\nSELECT<br \/>\n&nbsp; g.ogc_fid,   -- the pkey of the reference grid<\/p>\n<p>&nbsp; ST_Intersection(o.wkb_geometry, g.wkb_geometry) as wkb_geometry,   <\/p>\n<p>&nbsp; ST_Area(ST_Intersection(o.wkb_geometry, g.wkb_geometry)) as shape_area<\/p>\n<p>FROM <\/p>\n<p>&nbsp; davis_place3310 as o,<br \/>\n&nbsp; davis_grid as g<\/p>\n<p>WHERE<br \/>\n&nbsp; ST_INTERSECTS(o.wkb_geometry, g.wkb_geometry);<br \/>\n--------------------------------------------------------------------------<br \/>\nalter table davis_sliced add primary key(ogc_fid);<br \/>\ncreate index tds_geom_idx on davis_sliced using GIST(wkb_geometry);<br \/>\n--------------------------------------------------------------------------<br \/>\n<\/code><\/p>\n<div id=\"gallery\"><a href=\"wp-content\/uploads\/2013\/03\/davis_gridded.png\"><br \/>\n<img decoding=\"async\" src=\"wp-content\/uploads\/2013\/03\/davis_gridded-300x221.png\" alt=\"grids\" align=\"middle\" \/><br \/>\n<\/a>\n<\/div>\n<p>&nbsp;A Single Input Polygon (Davis city) Partially Overlaps the Reference Grid<\/p>\n<hr \/>\n<p>Next, More Than One Polygon Gridded:<\/p>\n<p><code><br \/>\ndrop table if exists davis_place3310 cascade;<br \/>\ncreate table davis_place3310 as<br \/>\nSELECT<br \/>\n&nbsp;gid,   statefp10,placefp10,placens10,geoid10,<br \/>\n&nbsp;name10,namelsad10,lsad10,classfp10,pcicbsa10,pcinecta10,<br \/>\n&nbsp;mtfcc10,funcstat10,aland10,awater10,intptlat10,intptlon10,<\/p>\n<p>&nbsp;ST_SetSRID( ST_Transform( the_geom, 3310), 3310) as wkb_geometry<br \/>\nFROM tl_2010_06_place10<br \/>\nWHERE namelsad10 in ( 'Davis city', 'West Sacramento city'  );<br \/>\n--------------------------------------------------------------------<br \/>\nalter table davis_place3310 add primary key (gid);<br \/>\ncreate index tdavis_geom_idx on davis_place3310 using GIST (wkb_geometry);<br \/>\n---------------------------------------------------------------------<br \/>\n--  N Polygons can be intersected in one statement calling GROUP BY<br \/>\n--   a spatial aggregate is defined and invoked<br \/>\n---------------------------------------------------------------------<br \/>\ndrop table if exists davis_sliced cascade;<br \/>\ncreate table davis_sliced as<br \/>\nSELECT <\/p>\n<p>&nbsp; g.ogc_fid,   -- the pkey of the reference grid<\/p>\n<p>&nbsp; ST_Union(ST_Intersection(o.wkb_geometry, g.wkb_geometry)) as wkb_geometry,    <\/p>\n<p>&nbsp; sum( ST_Area(ST_Intersection(o.wkb_geometry, g.wkb_geometry))) as shape_area<\/p>\n<p>FROM <\/p>\n<p>&nbsp; davis_place3310 as o,<\/p>\n<p>&nbsp; davis_grid as g<\/p>\n<p>WHERE<br \/>\n&nbsp; ST_INTERSECTS(o.wkb_geometry, g.wkb_geometry)<\/p>\n<p>GROUP BY<br \/>\n&nbsp; g.ogc_fid, g.wkb_geometry;<br \/>\n--------------------------------------------------------------------------<br \/>\nalter table davis_sliced add primary key(ogc_fid);<br \/>\ncreate index tds_geom_idx on davis_sliced using GIST(wkb_geometry);<br \/>\n--------------------------------------------------------------------------<\/p>\n<p><\/code><\/p>\n<p>screenshot<\/p>\n<div id=\"gallery\"><a href=\"wp-content\/uploads\/2013\/03\/davis_plus_gridded-1024x530.png\"><br \/>\n<img decoding=\"async\" src=\"wp-content\/uploads\/2013\/03\/davis_plus_gridded-300x155.png\" alt=\"grids2\" align=\"middle\" \/><br \/>\n<\/a>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes there are polygons with too many vertices to process comfortably. For that or other reasons, you may want to cut one or more polygons with a reference grid. Here is an example using the US 2010 Census and a set of grid cells in EPSG:3310: &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211; &#8212; take a single census place polygon and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","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\/1048"}],"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=1048"}],"version-history":[{"count":51,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1048\/revisions"}],"predecessor-version":[{"id":3673,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1048\/revisions\/3673"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1048"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1048"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1048"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}