Skip to content
 

Slice a Polygon by a Grid

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:

pg_logo

--------------------------------------------------------------------
-- take a single census place polygon and make a table of it
-- transform from LonLat to EPSG:3310
--------------------------------------------------------------------
create table davis_place3310 as
SELECT
 gid, statefp10,placefp10,placens10,geoid10,
 name10,namelsad10,lsad10,classfp10,pcicbsa10,pcinecta10,
 mtfcc10,funcstat10,aland10,awater10,intptlat10,intptlon10,

 ST_SetSRID( ST_Transform( the_geom, 3310), 3310) as wkb_geometry
FROM tl_2010_06_place10 where namelsad10 = 'Davis city';
--------------------------------------------------------------------
alter table davis_place3310 add primary key (gid);
create index tdavis_geom_idx on davis_place3310 using GIST (wkb_geometry);
---------------------------------------------------------------------

--------------------------------------------------------------------
-- now make a sliced result with a grid and input poly
--------------------------------------------------------------------
create table davis_sliced as
SELECT
 g.ogc_fid, --< the pkey of the reference grid  ST_Intersection(o.wkb_geometry, g.wkb_geometry) as wkb_geometry,  ST_Area(ST_Intersection(o.wkb_geometry, g.wkb_geometry)) as shape_area FROM  davis_place3310 as o,  davis_grid as g WHERE  ST_INTERSECTS(o.wkb_geometry, g.wkb_geometry); -------------------------------------------------------------------------- alter table davis_sliced add primary key(ogc_fid); create index tds_geom_idx on davis_sliced using GIST(wkb_geometry); --------------------------------------------------------------------------

 A Single Input Polygon (Davis city) Partially Overlaps the Reference Grid


Next, More Than One Polygon Gridded:


drop table if exists davis_place3310 cascade;
create table davis_place3310 as
SELECT
 gid, statefp10,placefp10,placens10,geoid10,
 name10,namelsad10,lsad10,classfp10,pcicbsa10,pcinecta10,
 mtfcc10,funcstat10,aland10,awater10,intptlat10,intptlon10,

 ST_SetSRID( ST_Transform( the_geom, 3310), 3310) as wkb_geometry
FROM tl_2010_06_place10
WHERE namelsad10 in ( 'Davis city', 'West Sacramento city' );
--------------------------------------------------------------------
alter table davis_place3310 add primary key (gid);
create index tdavis_geom_idx on davis_place3310 using GIST (wkb_geometry);
---------------------------------------------------------------------
-- N Polygons can be intersected in one statement calling GROUP BY
-- a spatial aggregate is defined and invoked
---------------------------------------------------------------------
drop table if exists davis_sliced cascade;
create table davis_sliced as
SELECT
 g.ogc_fid, --< the pkey of the reference grid  ST_Union(ST_Intersection(o.wkb_geometry, g.wkb_geometry)) as wkb_geometry,  sum( ST_Area(ST_Intersection(o.wkb_geometry, g.wkb_geometry))) as shape_area FROM  davis_place3310 as o,  davis_grid as g WHERE  ST_INTERSECTS(o.wkb_geometry, g.wkb_geometry) GROUP BY  g.ogc_fid, g.wkb_geometry; -------------------------------------------------------------------------- alter table davis_sliced add primary key(ogc_fid); create index tds_geom_idx on davis_sliced using GIST(wkb_geometry); --------------------------------------------------------------------------

screenshot