Homogeneous vs Heterogeneous schema in Postgis

Question

Introduction

I'm working on creating a spatial database in Postgis, to be viewed and edited trough QGIS to help managing some farmland areas.

One of my goals is the have a centralized place where all actions to develop in farm are listed. The actions all have (kind of) the same structure (attributes) and would be stored in a non spatial table. Each action will be related to a particular feature in the farm (a road, a sign pole, a gate, a fence...).

The problem

Since features will have to be represented in different geometry types, (at least) two options arise.

The homogeneous schema

Each geometry type is stored in a different table (one for points, one for lines and one for polygons). This makes opening and editing the tables in QGIS (and other GIS clients) much easier as qgis does not accept tables with multiple geometries. In the other hand I can't seem to find a way to consistently manage a unique key in the three tables to use as foreign key in the actions non-spatial table.

the heterogeneous schema

All features are stored in the same table, where the geometry column is of the type geometry(geometry, 3763). This will make very easy to manage a unique key to relate to the actions non-spatial table. On the other hand, viewing and editing the table trough QGIS will require the creation of VIEWS to separate the different geometry types, and INSTEAD TRIGGERS to redirect edits on views into the original table.

The question

Are this my only options? Is it possible to use the homogeneous schema for this case? If yes, how?

Please enlight my database schema creativity

Answer 1

Note that I am attempting this answer with little experience in table inheritance. Practically everything I know about it can be found in the PostGis Cookbook and in the PostgreSQL docs.

Consider the following tables:

CREATE TABLE features (
  gid SERIAL PRIMARY KEY,
  the_class varchar(20),
  the_type varchar(30),
  status varchar(50)
);

CREATE TABLE feature_points (
  geom geometry(point, 3763)
) INHERITS (features);

CREATE TABLE feature_lines (
  geom geometry(multilinestring, 3763)
) INHERITS (features);

CREATE TABLE feature_polygons (
  geom geometry(multipolygon, 3763)
) INHERITS (features);

Now insert a row into each table. I'm going to be lazy and not create any geometry:

INSERT INTO feature_lines
  (the_class, the_type, status)
VALUES
  ('class1', 'type1', 'good');

INSERT INTO feature_points
  (the_class, the_type, status)
VALUES
  ('class1', 'type2', 'bad');

INSERT INTO feature_polygons
  (the_class, the_type, status)
VALUES
  ('class1', 'type3', 'ugly');

Now check the results:

SELECT * FROM features;
 gid | the_class | the_type | status 
-----+-----------+----------+--------
   2 | class1    | type2    | bad
   1 | class1    | type1    | good
   3 | class1    | type3    | ugly
(3 rows)

You can see that your "gid" field contains a unique value across the geometry tables. However, there are serious caveats here. Indexes and constraints are not inherited by child tables. The only reason this works is because the SERIAL type creates an auto incrementing sequence. If you try to insert your own "gid"s into the child tables -- and you can -- Postgres will not complain.