Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

In Spatialite (3.0.1), having a normal (non-spatial) table, I would like to relate it to two different spatial tables, with different geometries types (multipolygons and polylines), using the same column as a foreign key, and constraint it to their primary keys.

The general idea would be to, while inserting a new record in the non-spatial table, one can choose to relate it to a line or a polygon from two distinct spatial tables.

Example:

CREATE TABLE sp1_polygons
  poly_id TEXT NOT NULL PRIMARY KEY;

SELECT AddGeometryColumn(
  'sp1_polygons', 'geometry',
  27493, 'MULTIPOLYGON', 'XY');

CREATE TABLE sp2_lines
  poly_id TEXT NOT NULL PRIMARY KEY;

SELECT AddGeometryColumn(
  'sp2_lines', 'geometry',
  27493, 'MULTILINESTRING', 'XY');

CREATE TABLE t1 (
  t1_id INTEGER NOT NULL PRIMARY KEY,
  geo_id TEXT NOT NULL,
  CONSTRAINT fk_sp1
    FOREIGN KEY (geo_id) 
    REFERENCES sp1 (sp1_id)),
  CONSTRAINT fk_sp2
    FOREIGN KEY (geo_id) -- Equal to before
    REFERENCES sp2 (sp2_id));

In my particular case, the non-spatial table represent management actions (harvesting, planting, cutting, fixing, etc...) in a farm, while the spatial tables represent homogeneus plots (polygons) to be managed (farmland, forest, natural vegetation), or homogeneus lines (water streams, natural fences, etc...).

share|improve this question

1 Answer 1

up vote 1 down vote accepted

AFAIK, there's no problem having two foreign key constraints, with one column referencing two different columns in two foreign tables. However, that means that every row in the table with the constraints must reference an existing entry in both of the other tables. So you will not be able to relate some of the rows of t1 to sp1_lines, and some to sp1_polygons. Each entry in t1 will need to have a geo_id which actually exists in both spatial tables.

It is possible, on the other hand, to create a trigger which checks that the new.geo_id existes in either the lines table OR the polygons table. Something like:

CREATE TRIGGER "test_geo_id" BEFORE INSERT ON "t1"
FOR EACH ROW BEGIN

SELECT RAISE(ROLLBACK, 'No geo_id in either lines or polygons')
WHERE (SELECT count(l.id), count(p.id) FROM
sp1_lines AS l, sp1_polygons AS p 
WHERE l.id = NEW.geo_id OR p.id=NEW.geo_id) < 1

END

But, I think you have another design flaw in the tables you described above: How will you insure that an id value in sp1_lines is not duplicated in sp1_polygons?

share|improve this answer
    
The sp1_lines and sp1polygons foreign keys would be set with sugestive names, like A01, A02, ... for Agriculture plots, F01, F02, F03 ... for Forest ones. It will be set in the beggining of the project and it is not expected to be changed. Yet, you are right it is a flaw. I would like to be able to share the unique list in both table also. –  Alexandre Neto Oct 21 '12 at 18:34
    
All the more reason that two constraints on the same geo_id column would not work... –  Micha Oct 22 '12 at 6:41
    
Thanks @Micha, I will try to review my design. –  Alexandre Neto Oct 22 '12 at 7:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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