PostGIS Cookbook - Second Edition

How it works...

In this example, we have generated a unique list of possible records in conjunction with a search for the associated records, in order to build table relationships. In one table, we have the geometry and a unique ID of each spatial record; in another table, we have the names associated with each of those unique IDs. Now we can explicitly leverage those relationships.

First, we need to establish our unique IDs as primary keys, as follows:

ALTER TABLE chp02.trails_geom ADD PRIMARY KEY (gid); 

Now we can use that PRIMARY KEY as a FOREIGN KEY in our trails_names table using the following query:

ALTER TABLE chp02.trails_names ADD FOREIGN KEY (gid) REFERENCES chp02.trails_geom(gid); 

This step isn't strictly necessary, but does ...