Simple Question : Using automation can we get an idea of completions on Scotlands Housing Land Supply sites only from open source data?

Here I use the Ordnance Survey’s Open Source list of UPRNs and compare them against Housing Land Supply obtained from Spatial Hub (around 2023).

Firstly I went to Ordnance Survey UPRN site and got a link to all UK UPRNs. I downloaded this and then filtered out only Scotland addresses – ( Less addresses will allow my computer to cope a bit better with the millions of addresses).

• Download all UPRN address as Geopackage – OS UPRN link
• View geopackage in QGIS
• Filter out so only Scotland relevant UPRNs (practical step as no housing supply polygons available for RUK)
• Saved Scotland only UPRN as shape file
• Upload Shapefile into a locally hosted Postgresql 16 database that already had spatial hubs housing land supply polygon layer in it. I used my helpful shapefile into postgres tool for this – Link
• Checked to see both the UPRNs and Housing Land Supply boundaries in QGIS – Edinburgh South below – this confirms that the points look like they are displaying correctly and that there appears to be completions within some housing land supply sites. I frequently drive past this area of Edinburgh and I know from personal experience that there are a number of complete new housing sites in this area.

• Next I compared both lists

SELECT 
    t044.uprn,
    t007.site_refer,
    t007.pkid,
    t007.local_auth
FROM 
    t044osuprnmarch2025 AS t044
JOIN 
    t007housinglandsupply2023osgb27700 AS t007
ON 
    ST_Within(t044.geom, t007.geom);

• I could have at that point made the above into a view and created that join dynamically but with so many points it will be quite slow so just for practicality I take a snap shot of the above into a table.

CREATE TABLE t045uprnonhousinglandsupply AS
SELECT 
    t044.geom,
    t044.uprn,
    t007.site_refer,
    t007.pkid,
    t007.local_auth
FROM 
    t044osuprnmarch2025 AS t044
JOIN 
    t007housinglandsupply2023osgb27700 AS t007
ON 
    ST_Within(t044.geom, t007.geom);

• Next I display the resulting data on a map and we immediately see that the query appears to have correctly removed any UPRNs formerly outside of the housing land supply polygons.

• Now if I had a completion date against each of these UPRN combined Housing Land Supply points I could have run a pivot query so that each row showed the Local Authority and Site Reference and along the top completions by year and the value of each cell would be a count of the relative UPRN points… showing completions by year.
• That would in effect be an up-to-date completions on all Housing Land Supply Polygons for the whole of Scotland (below is what this looked like for the whole of Scotland)