One to Many Relationship in QGIS with PostGIS

Does anyone know of any tool that can do a One-to-Many relationship in QGIS?

I have my data in a PostGIS database. I would like to be able to query spatial layers that are related to spatial or non spatial tables and vice versa. I believe there was a way in ArcGIS 9.x that allowed to do something similar.

Answer

Use a spatial table, called location, and another non-spatial table, sample. To make it spatial, a view is used called location_sample. The below schema is using the PostGIS 2.0 typmod syntax:

CREATE TABLE location(
  gid serial NOT NULL,
  geom geometry(Point,4326),
  name character varying(50) NOT NULL,
  CONSTRAINT location_pkey PRIMARY KEY (gid),
  CONSTRAINT name_unique UNIQUE (name)
);
CREATE INDEX location_geom_idx ON location USING gist (geom);

CREATE TABLE sample(

  sid serial NOT NULL,
  name character varying(50) NOT NULL,
  location_name character varying(50),
  CONSTRAINT sample_pkey PRIMARY KEY (sid),
  CONSTRAINT location_name_fkey FOREIGN KEY (location_name)
      REFERENCES location (name) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX fki_location_name_fkey ON sample USING btree (location_name);


CREATE VIEW location_sample AS
  SELECT sample.sid, location.geom, sample.location_name, sample.name
  FROM location
  LEFT JOIN sample ON sample.location_name = location.name;

You should be able to load up location_sample in QGIS or whatever GIS you are using. Assign each sample with a location_name, and it will appear at that location. If you are using QGIS 1.8, there is an extra step to consider. The "primary key" for this view is sid (think "sample ID").

How I've set up the foreign key between location and sample is:

• if you type a location_name in sample that does not exist, or is typed incorrectly (spaces, dashes, case, etc.), it will not allow you to use it (i.e., MATCH SIMPLE)


• if you rename a location (in the name field), then all samples connected to it will update their location_name fields (i.e., ON UPDATE CASCADE)



• if you delete a location row, then all samples connected to it will be deleted (i.e., ON DELETE CASCADE)

Read up on the foreign key constraints to get different behaviours, which might better match your situation.

You can also summarize sample values using aggregate functions, like count, min, avg, etc, and make this a similar spatial view. This makes most sense if you add numeric columns to your non-spatial table.