Merging the Asset Data

In order to create the BRIG dataset we need to run the following command to create a view on the Insight database. This is a one off process which could be done using the SQL editor in PGAdmin III.

Create BRIG View

DROP VIEW "BRIG";
 
CREATE OR REPLACE VIEW "BRIG" AS 
 SELECT 
    national_street_gazetteer.symlink, 
    buffer(national_street_gazetteer.geometry,1), 
    (national_street_gazetteer.address::text || ', '::text) || national_street_gazetteer.town::text AS DESC, 
    'nsg' AS asset_type, 
    1 AS sort_order
 FROM national_street_gazetteer
UNION ALL
 SELECT 
    bridges.symlink::character varying AS symlink, 
    buffer(bridges.geometry,2), 
    bridges."location" AS DESC, 
    'bridges' AS asset_type, 
    0 AS sort_order
 FROM bridges
ORDER BY sort_order;
 
ALTER TABLE "BRIG" OWNER TO "Insight";
 
DELETE FROM geometry_columns WHERE f_table_name LIKE 'BRIG';
INSERT INTO geometry_columns SELECT '','public', 'BRIG', 'geometry',2, 27700, 'GEOMETRY';