drop table if exists adhoc.bduk_fmp_work_may_2026_20260601_geom;

select distinct on (b.uprn)
       b.*,

       bt.building_type,
       bt.building_use,
       bt.building_premises,
       bt.building_premises_residential,
       a.geom

        into adhoc.bduk_fmp_work_may_2026_20260601_geom

       from adhoc.bduk_fmp_work_may_2026_20260601 b

inner join basedata.abp a on b.uprn = a.uprn

inner join basedata.toid_building_type bt on a.cross_reference = bt.toid

--where a.uprn is null
--where bt.toid is null

order by b.uprn
;

alter table adhoc.bduk_fmp_work_may_2026_20260601_geom
    add constraint bduk_fmp_work_may_2026_20260601_geom_pk
        primary key (uprn);



create index bduk_fmp_work_may_2026_20260601_geom_idx on adhoc.bduk_fmp_work_may_2026_20260601_geom using gist (geom);



-- a view that can be replaced easily with new datasets
create view adhoc.vw_bduk_uprn_latest as
select * from adhoc.bduk_fmp_work_may_2026_20260601_geom

