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.

enter image description here

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.

enter image description here

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

  • 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. I don't get that. If it is a foreign key, there is nothing you need to manage as it manages itself really well :-) Feb 12, 2014 at 14:29
  • When I create a row in the table "action", I have to identify the feature that the action belongs to. Generally, I would point to the feature gid, as I know its going to be unique and not null, and more important will manage itself like you said. Having the features scattered by three tables, I don't know if I can automatically create new keys that are unique in the three, so I can use it unambiguously in the "actions" table. Feb 12, 2014 at 14:50
  • If you are worried about uniqueness across three columns, I would suggest searching for GUID or UUID identificators. Feb 12, 2014 at 15:03
  • What version of Postgres/PostGIS? Table inheritance might be a good solution.
    – Scro
    Feb 12, 2014 at 15:26
  • Postgres 9.2.2 Postgis 2.0.1. I will have a better look on the table inheritance (Postgis in action has some examples on that). Thanks Feb 12, 2014 at 16:09

1 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 (
  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)
  ('class1', 'type1', 'good');

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

INSERT INTO feature_polygons
  (the_class, the_type, status)
  ('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.

  • I think I can control some of those caveat in the client side. Even in "normal" tables you can always insert your own gid values, and postgis won't complain (unless they are not unique). I will give this schema a try, to see how QGIS handles it. Feb 13, 2014 at 9:58
  • 2
    I have tested this approach, and QGIS works well with it, opening and editing the child tables normally. I confirm the caveat not inherit the constraints, and postgis won't complain even if there are non-unique values in gid. If I disable editing this field in QGIS (which is always a good idea), this solves my common gid problem, and I can create a 1:n relation with the actions table. Feb 17, 2014 at 15:09
  • That is what I was trying to impart... Have an "id" field that you do not populate manually. Thanks.
    – Scro
    Feb 17, 2014 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.