The first thing we need to do is find all the fields that don't have ampersands and use those as our unique list of available trails. In our case, we can do this, as every trail has at least one segment that is uniquely named and not associated with another trail name. This approach will not work with all datasets, so be careful in understanding your data before applying this approach to that data.

To select the fields ordered without ampersands, we use the following query:

SELECT DISTINCT label_name, res 
  FROM chp02.trails 
  WHERE label_name NOT LIKE '%&%' 
  ORDER BY label_name, res; 

It will return the following output:

Next, we want to search for all the records that match any of these unique trail names. This will give us the list of records that will serve as relations. The first step in doing this search is to append the percent (%) signs to our unique list in order to build a string on which we can search using a LIKE query:

SELECT '%' || label_name || '%' AS label_name, label_name as   label, res FROM 
  (SELECT DISTINCT label_name, res 
    FROM chp02.trails 
    WHERE label_name NOT LIKE '%&%' 
    ORDER BY label_name, res 
  ) AS label; 

Finally, we'll use this in the context of a WITH block to do the normalization itself. This will provide us with a table of unique IDs for each segment in our first column, along with the associated label column. For good measure, we will do this as a CREATE TABLE procedure, as shown in the following query:

CREATE TABLE chp02.trails_names AS WITH labellike AS 
( 
SELECT '%' || label_name || '%' AS label_name, label_name as   label, res FROM 
  (SELECT DISTINCT label_name, res 
    FROM chp02.trails 
    WHERE label_name NOT LIKE '%&%' 
    ORDER BY label_name, res 
  ) AS label 
) 
SELECT t.gid, ll.label, ll.res 
  FROM chp02.trails AS t, labellike AS ll 
  WHERE t.label_name LIKE ll.label_name 
  AND 
  t.res = ll.res 
  ORDER BY gid;

If we view the first rows of the table created, trails_names, we have the following output with pgAdmin:

Now that we have a table of the relations, we need a table of the geometries associated with gid. This, in comparison, is quite easy, as shown in the following query:

CREATE TABLE chp02.trails_geom AS 
  SELECT gid, the_geom 
  FROM chp02.trails;