Database Reference
In-Depth Information
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'llusethisinthecontextofa
WITH
blocktodothenormalizationitself.This
willprovideuswithatableofuniqueIDsforeachsegmentinourfirstcolumn,along
withtheassociated
label
column.Forgoodmeasure,wewilldothisasa
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;
Nowthatwehaveatableoftherelations,weneedatableofthegeometriesassoci-
ated with
gid
. This, in comparison, is quite easy, as shown in the following query:
