Getting ready

The data we will be using is hydrology data that has been modified from engineering "blue lines" (see the following screenshot), that is, hydrologic data that is very detailed and meant to be used at scales approaching 1:600. The data in their original application aided in detailed digital terrain modeling.

While useful in itself, the data was further manipulated, separating the linear features from area features, with additional polygonization of area features as shown in the following screenshot:

Finally, the data was classified into basic waterway categories as follows:

In addition, a process was undertaken to generate centerlines for polygon features such as streams, which are effectively linear features, as follows:

Hence, we have three separate but related datasets:

Now, let us look at the structure of the tabular data. The ogrinfo utility can help us with this as shown in the following command that is run on the command line:

> ogrinfo cuyahoga_hydro_polygon.shp -al -so
INFO: Open of `cuyahoga_hydro_polygon.shp'
 using driver `ESRI Shapefile' successful.

Layer name: cuyahoga_hydro_polygon
Geometry: Polygon
Feature Count: 6237
Extent: (1694482.287974, 552986.308029) - (2947684.750393, 1200045.105669)
Layer SRS WKT:
PROJCS["NAD_1983_StatePlane_Ohio_North_FIPS_3401_Feet",
 GEOGCS["GCS_North_American_1983",

 PARAMETER["Latitude_Of_Origin",39.66666666666666],
 UNIT["Foot_US",0.3048006096012192]]
Name: String (30.0)
AREA: Real (19.11)
PERIMETER: Real (19.11)
hyd_type: String (50.0)
geom_type: String (15.0)

Executing this command on each of the shapefiles, we see the following fields that are common to all the shapefiles:

It is by understanding our common fields that we can apply inheritance to completely structure our data.

How to do it...

Now that we know our common fields, creating an inheritance model is easy. First, we will create a parent table with the fields common to all the tables, using the following query:

CREATE TABLE chp02.hydrology (
 gid SERIAL PRIMARY KEY,
 "name" text,
 hyd_type text,
 geom_type text,
 the_geom geometry
);

If you are paying attention, you will note that we also added a geometry field as all of our shapefiles implicitly have this commonality. To establish inheritance for a given table, we need to declare only the additional fields that the child table contains, using the following query:

CREATE TABLE chp02.hydrology_centerlines (
 "length" numeric
) INHERITS (chp02.hydrology);

CREATE TABLE chp02.hydrology_polygon (
 area numeric,
 perimeter numeric
) INHERITS (chp02.hydrology);

CREATE TABLE chp02.hydrology_linestring (
 sinuosity numeric
) INHERITS (chp02.hydrology_centerlines);

Now we are ready to load our data using the following commands:

If we view our parent table, we will see all the records in all the child tables. A viewing of any of the child tables will just reveal the specific table of interest.

How it works...

PostgreSQL table inheritance allows us to enforce essentially hierarchical relationships between tables. In this case, we leverage inheritance to allow for commonality between related datasets. Now, if we want to query data from these tables, we can query directly from the parent table as follows, depending on whether we want a mix of geometries or just a targeted dataset.

SELECT * FROM chp02.hydrology

From any of the child tables, we could use the following query:

SELECT * FROM chp02.hydrology_polygon

See also

It is possible to extend this concept in order to leverage and optimize storage and querying by using the CHECK constrains in conjunction with inheritance. For more info, see the Extending inheritance – table partitioning recipe.