Charting the trend of romantic property names

Blog post by Scott (business administrative assistant) and Fiona (GIS analyst).

Here in the Information and Analysis team at Registers of Scotland we extract and analyse data held by our registers and from third party organisations. Some of the data we analyse can provide some interesting insights into how the people of Scotland refer to their homes.

We’ve previously told you about how both festive and spooky themes can be traced in Scotland’s house names but how do we actually manipulate the data to arrive at our findings? We’re going to show you in this post.

Bringing the data together

Our aim? Interrogate every house name in Scotland to extract themes. Warning, this gets techy… First Fiona, our GIS analyst, created a table and count of all current house names in Scotland. She did this by using the Ordnance Survey (OS) AddressBase Premium address dataset stored in a PostgreSQL database in conjunction with a Structured Query Language (SQL) query.

Scott working at desk
Scott working in our Edinburgh office

AddressBase Premium is made up of nine different tables which hold all addresses, geographic and deliverable, in Scotland (in our case). The tables hold historical, current, provisional, alternative and Gaelic addresses. All nine tables need to be linked to extract the total amount of information about addresses. To find out about house names we only needed to look at three out of the nine tables.

Getting picky: Unique Property Reference Numbers

For this list we were only interested in house names that are made up of words (i.e. no random characters and numbers) and occur in current delivery addresses (i.e. an address which can receive mail, not land or vacant buildings. However, if you’re interested in finding out more about abandoned, neglected or detrimental land visit the Register of Applications by Community Bodies to Buy Land).

The SQL query created to extract the required data pulls all house names and the number of times they occur (line one) by linking three of the tables (line two) by Unique Property Reference Number (UPRN) (line three), ensuring only current delivery addresses with house names are pulled (line four).

Line five allows the ‘COUNT’ in Line one to calculate and show the number of times each house name occurs by ‘grouping’ every instance of each house name together then counting how many there are.

Creating the house name table, showing SQL used
Creating the house name table, showing SQL used

Finally line six orders the output by the number of time the same house name occurs and secondly by alphabetic.

383,756 addresses were identified as having a house name and from these 139,017 unique house names were found. The two column output was then saved within the database as a new table to be used in the future.

Coding the language of love

Using this new table, we were able to put our extensive romantic vocabulary to use by searching for house names that were Valentine’s Day related. The most popular Valentine’s-inspired house names include the word ‘rose’.

This perhaps isn’t surprising, given that the White Rose of Scotland (Scots Rose) is ‘probably our most emblematic native plant… next to the thistle.’ Although this emblem is entwined with Bonnie Prince Charlie, it’s become a recurring symbol in Scottish culture – which may also account for its proliferation on property nameplates. You can see the variety of house names which include ‘rose’ in the table below.

Scott on computer
Searching for Valentine’s related house names, showing SQL used

Some of Scotland’s more unique – and undeniably romantic – house names include two ‘Sweetheart Cottages’, eight properties featuring the name ‘Valentine’ and a singular ‘Beau Cottage’. But how many of these appear on Scotland’s digital, map-based land register?

A simple search of our land register databases show that currently 360 registered titles include a Rose Cottage address. Funnily enough, this figure almost exactly mirrors the latest estimated land title coverage value which sits at 66.7% at end January 2019 (66.3% of properties with Rose Cottage in their address are on the digital land register). 

Although our registers can only hint at the histories behind these romantic property names, we have no doubt that each hide a veritable love story. See more of our property data on our website.

Don’t go breaking our hearts…

Stay in touch with our teams! You can follow us on Twitter (where we post several times a day) and on FacebookLinkedIn and YouTube for more from Scotland’s land and property registers. And don’t forget to let us know what you thought of this blog by commenting below!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.