Adding Building Heights to Topo

PL/pgSQL

--The following SQL queries are for OS MasterMap Building Heights in PostGIS
 
--Create table
create table BHA_data(
OS_TOPO_TOID character varying (20),
OS_TOPO_TOID_VERSION smallint,
BHA_ProcessDate date,
TileRef character varying (6),
AbsHMin real,
AbsH2 real,
AbsHMax real,
RelH2 real,
RelHMax real,
BHA_Conf smallint
);
 
--Load the Building Heights CSV file
COPY bha_data FROM 'path to bha.csv' DELIMITER ',' CSV HEADER;
 
--If the just want to remove the osgb then they could run the following command
 
UPDATE bha_data SET os_topo_toid = trim('osgb' from os_topo_toid);
COMMIT;
 
--Or if they want it as a separate column then
ALTER TABLE bha_data ADD COLUMN os_topo_toid_osgb character varying (20);
COMMIT;
UPDATE bha_data SET os_topo_toid_osgb = trim('osgb' from os_topo_toid);
COMMIT;
 
--If they want to remove the osgb and add in the extra zeroes then they could do this
UPDATE bha_data set os_topo_toid =
(CASE
WHEN char_length(os_topo_toid) = 20 THEN trim('osgb' from os_topo_toid)
ELSE ('000' || trim('osgb' from os_topo_toid))
END);
COMMIT;
 
--Or as a separate column:
ALTER TABLE buildingheights.bha_data ADD COLUMN os_topo_toid_zeroes character varying (20);
COMMIT;
UPDATE buildingheights.bha_data set os_topo_toid_zeroes =
(CASE
WHEN char_length(os_topo_toid) = 20 THEN trim('osgb' from os_topo_toid)
ELSE ('000' || trim('osgb' from os_topo_toid))
END);
COMMIT;
 
--Create indexes for faster joining, obviously do it on the correct TOID column
CREATE INDEX bha_toid ON bha_data(OS_TOPO_TOID);
 
--I then added an extra column to my topographicarea table and set the height
ALTER TABLE osmm.topographicarea ADD COLUMN building_height decimal;
 
UPDATE osmm.topographicarea SET building_height = bha_data.relhmax FROM bha_data WHERE osmm.topographicarea.fid = bha_data.os_topo_toid;
COMMIT;