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:
--------------------------------------------------------------------
-- 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
