The first step in this case may be to prepare the data. If we had a monolithic contour table called cuy_contours_2, we could choose to clip the data to a series of rectangles that will serve as our table partitions; in this case, chp02.contour_clip, using the following query:
CREATE TABLE chp02.contour_2_cm_only AS SELECT contour.elevation, contour.gid, contour.div_10, contour.div_20, contour.div_50, contour.div_100, cc.id, ST_Intersection(contour.the_geom, cc.the_geom) AS the_geom FROM chp02.cuy_contours_2 AS contour, chp02.contour_clip as cc WHERE ST_Within(contour.the_geom,cc.the_geom OR ST_Crosses(contour.the_geom,cc.the_geom);
We are performing two tests here in our query. We are using ST_Within, which tests whether a given contour is entirely within our area of interest. If so, we perform an intersection; the resultant geometry should just be the geometry of the contour.
The ST_Crosses function checks whether the contour crosses the boundary of the geometry we are testing. This should capture all the geometries lying partially inside and partially outside our areas. These are the ones that we will truly intersect to get the resultant shape.
In our case, it is easier and we don't require this step. Our contour shapes are already individual shapefiles clipped to rectangular boundaries, as shown in the following screenshot:
![](assets/fe831299-188c-4bc0-96e4-eb3047a474a2.png)
Since the data is already clipped into the chunks needed for our partitions, we can just continue to create the appropriate partitions.
Much like with inheritance, we start by creating our parent table using the following query:
CREATE TABLE chp02.contours ( gid serial NOT NULL, elevation integer, __gid double precision, the_geom geometry(MultiLineStringZM,3734), CONSTRAINT contours_pkey PRIMARY KEY (gid) ) WITH ( OIDS=FALSE );
Here again, we maintain our constraints, such as PRIMARY KEY, and specify the geometry type (MultiLineStringZM), not because these will propagate to the child tables, but for any client software accessing the parent table to anticipate such constraints.
Now we may begin to create tables that inherit from our parent table. In the process, we will create a CHECK constraint specifying the limits of our associated geometry using the following query:
CREATE TABLE chp02.contour_N2260630 (CHECK
(ST_CoveredBy(the_geom,ST_GeomFromText
('POLYGON((2260000, 630000, 2260000 635000, 2265000 635000,
2265000 630000, 2260000 630000))',3734)
)
)) INHERITS (chp02.contours);
We can complete the table structure for partitioning the contours with similar CREATE TABLE queries for our remaining tables, as follows:
CREATE TABLE chp02.contour_N2260635 (CHECK
(ST_CoveredBy(the_geom,ST_GeomFromText
('POLYGON((2260000 635000, 2260000 640000,
2265000 640000, 2265000 635000, 2260000 635000))', 3734) )
)) INHERITS (chp02.contours); CREATE TABLE chp02.contour_N2260640 (CHECK
(ST_CoveredBy(the_geom,ST_GeomFromText
('POLYGON((2260000 640000, 2260000 645000, 2265000 645000,
2265000 640000, 2260000 640000))', 3734) )
)) INHERITS (chp02.contours); CREATE TABLE chp02.contour_N2265630 (CHECK
(ST_CoveredBy(the_geom,ST_GeomFromText
('POLYGON((2265000 630000, 2265000 635000, 2270000 635000,
2270000 630000, 2265000 630000))', 3734)
)
)) INHERITS (chp02.contours); CREATE TABLE chp02.contour_N2265635 (CHECK
(ST_CoveredBy(the_geom,ST_GeomFromText
('POLYGON((2265000 635000, 2265000 640000, 2270000 640000,
2270000 635000, 2265000 635000))', 3734) )
)) INHERITS (chp02.contours); CREATE TABLE chp02.contour_N2265640 (CHECK
(ST_CoveredBy(the_geom,ST_GeomFromText
('POLYGON((2265000 640000, 2265000 645000, 2270000 645000,
2270000 640000, 2265000 640000))', 3734) )
)) INHERITS (chp02.contours); CREATE TABLE chp02.contour_N2270630 (CHECK
(ST_CoveredBy(the_geom,ST_GeomFromText
('POLYGON((2270000 630000, 2270000 635000, 2275000 635000,
2275000 630000, 2270000 630000))', 3734) )
)) INHERITS (chp02.contours); CREATE TABLE chp02.contour_N2270635 (CHECK
(ST_CoveredBy(the_geom,ST_GeomFromText
('POLYGON((2270000 635000, 2270000 640000, 2275000 640000,
2275000 635000, 2270000 635000))', 3734) )
)) INHERITS (chp02.contours); CREATE TABLE chp02.contour_N2270640 (CHECK
(ST_CoveredBy(the_geom,ST_GeomFromText
('POLYGON((2270000 640000, 2270000 645000, 2275000 645000,
2275000 640000, 2270000 640000))', 3734) )
)) INHERITS (chp02.contours);
And now we can load our contours shapefiles found in the contours1 ZIP file into each of our child tables, using the following command, by replacing the filename. If we wanted to, we could even implement a trigger on the parent table, which would place each insert into its correct child table, though this might incur performance costs:
shp2pgsql -s 3734 -a -i -I -W LATIN1 -g the_geom N2265630 chp02.contour_N2265630 | psql -U me -d postgis_cookbook