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.
Best Answer
Use a spatial table, called
location
, and another non-spatial table,sample
. To make it spatial, a view is used calledlocation_sample
. The below schema is using the PostGIS 2.0 typmod syntax:You should be able to load up
location_sample
in QGIS or whatever GIS you are using. Assign eachsample
with alocation_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 issid
(think "sample ID").How I've set up the foreign key between
location
andsample
is: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
)location
(in thename
field), then all samples connected to it will update theirlocation_name
fields (i.e.,ON UPDATE CASCADE
)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, likecount
,min
,avg
, etc, and make this a similar spatial view. This makes most sense if you add numeric columns to your non-spatial table.