Create ITN Lookup table

If you want to add road name or road numbers to the roadlinks it is a two step process.

The first is to create a lookup table which requires creating and running a function.

CREATE OR REPLACE FUNCTION itn.lookuptable() RETURNS integer AS $$
DECLARE
	road RECORD;
	rlink_toid char(20);
	r_toid char(20);
	current_rec int := 0;
	total_rec int := 0;
BEGIN
	RAISE NOTICE 'Creating lookup table...';
 
	CREATE TABLE IF NOT EXISTS itn.lookup (
		id		serial CONSTRAINT lookup_pkey PRIMARY KEY,
		road_toid	char(20),
		roadlink_toid	char(20)
	);
 
	RAISE NOTICE 'Looping through all records in road table...';
 
	SELECT COUNT(*) INTO total_rec FROM itn.road;
	
	FOR road IN SELECT DISTINCT fid, networkmember_ref FROM itn.road LOOP
 
		FOREACH rlink_toid IN ARRAY road.networkmember_ref LOOP
			INSERT INTO itn.lookup (road_toid, roadlink_toid) VALUES (road.fid, rlink_toid);
		END loop;
 
		current_rec := current_rec + 1;
 
		IF current_rec % 10000 = 0 THEN
			RAISE NOTICE 'Processing record % of %', current_rec, total_rec;
		END IF;
	END LOOP;
 
	RAISE NOTICE 'Done processing records.';
 
	RAISE NOTICE 'Creating indexes on Lookup table.';
 
	CREATE INDEX idx_lookup_roadtoid ON itn.lookup(road_toid);
	CREATE INDEX idx_lookup_roadlinktoid ON itn.lookup(roadlink_toid);
 
	RETURN 1;
END;
$$
LANGUAGE plpgsql;
 
SELECT itn.lookuptable();

Then you need to create and run a second function to loop through the road records and update the roadlink table:

CREATE OR REPLACE FUNCTION itn.roadlinkupdater() RETURNS integer AS $$
DECLARE
	road RECORD;
	rlink RECORD;
	is_proute boolean;
	is_trunkroad boolean;
	current_rec int := 0;
	total_rec int := 0;
BEGIN
	RAISE NOTICE 'Modifying roadlink table...';
 
	ALTER TABLE itn.roadlink ADD COLUMN road_name text;
	ALTER TABLE itn.roadlink ADD COLUMN road_number text;
	ALTER TABLE itn.roadlink ADD COLUMN is_primary boolean;
	ALTER TABLE itn.roadlink ADD COLUMN is_trunk boolean;
 
	RAISE NOTICE 'Looping through all records in road table...';
 
	SELECT COUNT(*) INTO total_rec FROM itn.road;
	
	FOR road IN SELECT * FROM itn.road LOOP
 
		-- Determine if this road is primary route or trunk
		CASE road.descriptiveterm
			WHEN 'Primary Route' THEN
				is_proute := true;
			WHEN 'Trunk Road' THEN
				is_trunkroad := true;
			ELSE
				is_proute := false;
				is_trunkroad := false;
		END CASE;
 
		--IF road.roadname SIMILAR TO '(A|B|M)[0-9]*' THEN
		IF road.roadname ~ '.*[0-9].*' THEN
			UPDATE
				itn.roadlink
			SET
				road_number = road.roadname,
				is_trunk = is_trunkroad,
				is_primary = is_proute
			WHERE
				fid IN (SELECT roadlink_toid FROM itn.lookup WHERE road_toid = road.fid);
		ELSE
			UPDATE
				itn.roadlink
			SET
				road_name = road.roadname
			WHERE
				fid IN (SELECT roadlink_toid FROM itn.lookup WHERE road_toid = road.fid);
		END IF;
 
		current_rec := current_rec + 1;
 
		RAISE NOTICE 'Processing record %', current_rec;
 
		IF current_rec % 10000 = 0 THEN
			RAISE NOTICE 'Processing record % of %', current_rec, total_rec;
		END IF;
	END LOOP;
 
	RAISE NOTICE 'Done processing records.';
 
	RETURN 1;
END;
$$
LANGUAGE plpgsql;
 
SELECT itn.roadlinkupdater();

You can then add two new indexes to the new road_name and road_number columns:

CREATE INDEX idx_roadlink_roadname ON itn.roadlink(road_name);
COMMIT;
CREATE INDEX idx_roadlink_roadnumber ON itn.roadlink(road_number);
COMMIT;