Database Reference
In-Depth Information
CREATE TABLE chp02.trails_geom AS
SELECT gid, the_geom
FROM chp02.trails;
How it works...
Inthisexample,wehavegeneratedauniquelistofpossiblerecordsinconjunction
withasearchfortheassociatedrecords,inordertobuildtablerelationships.Inone
table,wehavethegeometryandauniqueIDofeachspatialrecord;inanothertable,
wehavethenamesassociatedwitheachofthoseuniqueIDs.Nowwecanexplicitly
leverage those relationships.
First, we need to establish our unique IDs as primary keys with the following query:
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 with 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 enforce referential integrity for queries
such as the following:
SELECT geo.gid, geo.geom, names.label FROM
chp02.trails_geom AS geo, chp02.trails_names
AS names
WHERE geo.gid = names.gid
There's more...
If we had multiple fields we wanted to normalize, we could write
CREATE TABLE
queries for each of them.
