A practical walkthrough of spatial aggregation and LSOA/output area analysis to better understand Commercial Property Density.

Introduction

Understanding where commercial activity clusters is fundamental to urban planning, investment analysis, and economic development. This walkthrough demonstrates how to map Commercial Property Density across local authorities by aggregating Unique Property Reference Numbers (UPRNs) through output areas and Lower Layer Super Output Areas (LSOAs) are all available in a single Doorda dataset.

What makes this analysis possible — and reliable — is that OS AddressBase Premium has been merged directly into this dataset. Rather than stitching together a commercial property list and a separate address reference file, the authoritative AddressBase Premium geography is already embedded in every record and it is what turns an approximate exercise into a precise, reproducible one.

The analysis of Commercial Property Density is enhanced by a variety of factors including the geographical distribution of properties and the economic indicators surrounding them.

What Is AddressBase Premium?

Each record in the dataset provides insights into each location enabling the modelling of Commercial Property Density, allowing for more targeted urban planning strategies.

AddressBase Premium is the gold standard of British address data — a definitive, UPRN-level reference framework maintained by Ordnance Survey under the Public Sector Geospatial Agreement (PSGA). It provides an authoritative, unique identifier for every addressable location in Great Britain, along with a clean geographic hierarchy from output area to LSOA to local authority.

Understanding Commercial Property Density can lead to better investment decisions and economic growth in various regions.

Unlike other Commercial Property datasets this is not a bolt-on; it is merged in, meaning each commercial property record already carries its authoritative UPRN and its full geographic hierarchy. This embedded AddressBase Premium linkage is what enables reliable spatial joins and reproducible density analysis.

The Foundation: AddressBase Premium Embedded with Commercial Property

The scale of the merged Commercial Property dataset demonstrates its coverage:

Metric	Value
Total commercial property records	2,519,662
Distinct UPRNs (AddressBase Premium)	2,036,587
Distinct postcodes	523,233
Distinct output areas (2021)	158,729
Distinct LSOAs (2021)	42,718
Distinct local authorities	157
Records carrying a UPRN	2,120,809
Records with coordinates (lat/long)	2,490,208
Records with an output area	2,489,063
Records with an LSOA	2,489,063

Coverage spans all four nations of the UK:

Country	Records	Distinct UPRNs	Local Authorities
England	2,019,336	1,806,333	92
Scotland	274,453	113,212	32
Wales	125,814	116,140	22
Northern Ireland	69,460	—	11

The takeaway is striking: over 2 million distinct UPRNs, each tied to a single address with clean output area and LSOA, across all local authorities. Because the AddressBase Premium hierarchy is embedded, every commercial location can be rolled up from the finest census geography to authority level without any external join — the spatial linkage is already done.

Why the Embedded AddressBase Premium Matters

Merging AddressBase Premium into our commercial property delivers three decisive advantages:

• Authoritative UPRNs — a stable, unique identifier for every addressable location, eliminating ambiguity in property counts. With 2.04 million distinct UPRNs across 2.52 million records, deduplication is both necessary and reliable.
• Clean geographic hierarchy — each record carries output_area_2021 and lsoa_2021 codes (present on ~2.49 million records), allowing seamless roll-up from the finest census geography to local authority level.
• Reliable spatial joins — coordinates are present on 2.49 million records, and administrative fields (local_authority, county, country) are consistent and joinable, so aggregation is trustworthy rather than approximate.

This means density figures are reproducible and comparable across regions — essential for any serious spatial analysis.

The Data Model

The core fields used in this walkthrough:

Field	Purpose
uprn	Unique Property Reference Number (AddressBase Premium)
output_area_2021	Finest census output area
lsoa_2021	Lower Layer Super Output Area
local_authority	Administrative authority
latitude / longitude	Point location for spatial work
category_description / sub_category	Commercial classification
postcode	Address linkage

Step 1: How to Aggregate UPRNs to Output Areas

The first stage of any density analysis is counting distinct properties at the finest geography. Because a single UPRN should represent one addressable location, we count distinct UPRNs per output area to avoid double-counting from multiple records.

SELECT
    output_area_2021,
    COUNT(DISTINCT uprn) AS commercial_uprn_count
FROM commercial_real_estate_creos01
WHERE output_area_2021 IS NOT NULL
GROUP BY output_area_2021
ORDER BY commercial_uprn_count DESC;

This produces a clean output-area-level density table — the atomic building block for everything that follows.

Step 2: Rolling Up to LSOA Level

Output areas nest neatly within LSOAs, so we aggregate upward. LSOAs are the standard geography for socio-economic analysis and typically contain around 1,500 residents, making them ideal for comparing commercial concentration against population context.

SELECT
    lsoa_2021,
    COUNT(DISTINCT uprn) AS commercial_uprn_count
FROM commercial_real_estate_creos01
WHERE lsoa_2021 IS NOT NULL
GROUP BY lsoa_2021
ORDER BY commercial_uprn_count DESC;

Step 3: Aggregating to Local Authority

Finally, we roll up to local authority level to produce the headline density map. Here we also capture the number of distinct LSOAs and output areas represented, giving a sense of geographic spread.

SELECT
    local_authority,
    COUNT(DISTINCT uprn)              AS total_commercial_uprns,
    COUNT(DISTINCT lsoa_2021)         AS lsoa_count,
    COUNT(DISTINCT output_area_2021)  AS output_area_count
FROM commercial_real_estate_creos01
WHERE local_authority IS NOT NULL
GROUP BY local_authority
ORDER BY total_commercial_uprns DESC;

Running this with the embedded AddressBase Premium data reveals the most commercially dense authorities when it comes to Commercial Property Density:

Local Authority	Commercial UPRNs	LSOAs	Output Areas	UPRNs per Output Area
Inner London	148,968	1,949	7,638	19.50
Outer London	117,245	2,940	9,263	12.66
Greater Manchester	89,423	1,683	6,272	14.26
West Midlands	88,255	1,695	5,730	15.40
West Yorkshire	83,305	1,398	5,420	15.37
Kent	50,399	932	3,621	13.92
Essex	47,553	879	3,159	15.05
South Yorkshire	42,499	849	3,004	14.15
Lancashire	42,306	758	3,090	13.69
Merseyside	39,879	906	3,084	12.93

Step 4: How to Calculate Property Density per LSOA / Output Area

Raw counts favour large authorities. To measure genuine density, normalise by the number of output areas — effectively commercial UPRNs per unit of geography:

SELECT
    local_authority,
    COUNT(DISTINCT uprn) AS total_commercial_uprns,
    COUNT(DISTINCT output_area_2021) AS output_area_count,
    ROUND(
        CAST(COUNT(DISTINCT uprn) AS DOUBLE) /
        NULLIF(COUNT(DISTINCT output_area_2021), 0), 2
    ) AS uprns_per_output_area
FROM commercial_real_estate_creos01
WHERE local_authority IS NOT NULL
GROUP BY local_authority
ORDER BY uprns_per_output_area DESC;

This is where the embedded AddressBase Premium geography earns its keep. Inner London’s 19.5 commercial UPRNs per output area — the highest in the country — reveals a compact, high-intensity commercial core that raw totals alone would obscure. The normalised view distinguishes genuine concentration from sheer size.

Step 5: Spatial Join for Mapping

Because each record carries coordinates (present on 2.49 million records), we can feed the aggregated output straight into a GIS or mapping library. A representative point per LSOA can be derived by averaging coordinates, then plotted as a choropleth or heat map:

SELECT
    lsoa_2021,
    local_authority,
    COUNT(DISTINCT uprn) AS commercial_uprn_count,
    AVG(CAST(latitude  AS DOUBLE)) AS centroid_lat,
    AVG(CAST(longitude AS DOUBLE)) AS centroid_lon
FROM commercial_real_estate_creos01
WHERE lsoa_2021 IS NOT NULL
GROUP BY lsoa_2021, local_authority
ORDER BY commercial_uprn_count DESC;

The resulting table is map-ready: each row is an LSOA with a density value and a representative coordinate.

Practical Considerations

• Distinct counts are essential. Always use COUNT(DISTINCT uprn) — with 2.52 million records mapping to 2.04 million distinct UPRNs, multiple records can reference the same property, and AddressBase Premium’s UPRN is the reliable deduplication key.
• Handle nulls. Filter out null geography codes before aggregating to avoid phantom “unknown” regions.
• Choose the right denominator. Output areas are the finest and most sensitive to local clustering; LSOAs are better for socio-economic comparison; local authorities suit strategic overviews.
• Classification filters. Narrow to specific category_description or sub_category values to isolate retail, office, industrial, or hospitality density separately.

Frequently Asked Questions

Conclusion

This walkthrough illustrates the importance of understanding Commercial Property Density in making informed real estate decisions.

The ability to map commercial property density at every scale — from a single output area to national coverage — rests on one thing: OS AddressBase Premium is merged with Commercial Property. With over 2 million distinct UPRNs, 158,729 output areas, 42,718 LSOAs and 157 local authorities all carrying authoritative geographic linkage, analysts can aggregate and map with confidence rather than approximation. The workflow above provides a reproducible foundation for planning, investment targeting, and economic insight, all anchored to the gold standard of British address data — embedded, not bolted on.

---

This walkthrough uses the Doorda commercial real estate dataset with OS AddressBase Premium embedded (CREOS), providing authoritative UPRN-level geographic linkage for spatial aggregation and density mapping.