4

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.

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 :-) – Michal Zimmermann Feb 12 '14 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. – Alexandre Neto Feb 12 '14 at 14:50
  • If you are worried about uniqueness across three columns, I would suggest searching for GUID or UUID identificators. – Michal Zimmermann Feb 12 '14 at 15:03
  • What version of Postgres/PostGIS? Table inheritance might be a good solution. – Scro Feb 12 '14 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 – Alexandre Neto Feb 12 '14 at 16:09
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.

  • 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. – Alexandre Neto Feb 13 '14 at 9:58
  • 1
    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. – Alexandre Neto Feb 17 '14 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 '14 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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