๐ IDs vs Spatial Relationships
What is an ID and what IDs are in OS Data?
An ID uniquely identifies each row of data or geographical feature.
OSID (Ordnance Survey Identifier) โ The OS NGD primarily uses a new identifier called the OSID to uniquely identify features. The OSID will be used persistently and will allow the unique identification of records in the OS NGD. It should be noted that the OSID will not be unique across the OS NGD, but rather only unique at a feature type level. The reason for this is, when possible, the same OSID will be used on multiple features when they represent the same geographical feature.
UPRN (Unique Property Reference Number) โ Is a unique numeric identifier for every spatial address in Great Britain. It applies a โcommon standardโ for addressable buildings and objects. One that is machine-readable, and in a consistent reference format.
USRN (Unique Street Reference Number) - is a unique and persistent identifier for every street, road, track, path, cycle track or cycle way in Great Britain.โ
TOID (Topographic Identifier) - Is a unique and persistent identifier for each and every feature found in OS MasterMap products. A TOID shows unique identifiers for a wide range of landscape and built environment features, with a generalised location, extracted from OS MasterMap products.โ
What do OS Identifiers look like?
OSID โ 9d697b8b-b351-4e9a-9c7b-d7d0d155f513โ
UPRN โ 906483712โ
USRN โ 7905946โ
TOID โ osgb4000000073338613
Using IDs to join dataโ
IDs in OS Data
OS produces various datasets of unique identifiers for a wide range of landscapes and built environment features for projects that require greater data connectivity, all available for free via the OS website.โ
OS Open Linked Identifiers โ a dataset containing the authoritative relationships between Unique Property Reference Numbers (UPRNs), Unique Street Reference Numbers (USRNs) and Topographic Object Identifiers (TOIDs).โ
OS Linked Identifiers API โ gives direct access to identifiers for a large number of feature types such as address records, building outlines, road surface area, road names, road sections and street records as well as the relationships between them that we provide links for, without having to download, store, or manage large, complex datasets.โ
OS Open TOID โ shows unique identifiers for a wide range of landscape and built environment features, with a generalised location, extracted from OS MasterMap products.โ
OS Open USRN - enables users to share and link data related to UPRNs, which can be spatially analysed and visualised using their accurate location.โ
OS Open UPRN โ an open dataset containing all USRNs from OS MasterMap Highways Network with a simplified line geometry.โ
Spatial Querying to join data
Spatial relationships indicate how two geometries interact with one another. They are a fundamental capability for querying geometry.
Spatial Query Examples
To make it easy to determine common spatial relationships, the OGC defines a set of named spatial relationship predicates. Examples of these in PostGIS are as follows:
Relationship | Result |
---|---|
ST_CONTAINS | Returns TRUE if geometry A contains geometry B |
ST_WITHIN | Returns TRUE if geometry A is within geometry B.โ |
ST_DWITHIN | Returns TRUE if the geometries are within a given distance.โ |
ST_OVERLAPSโ | Returns TRUE if geometry A and B "spatially overlap".โ |
ST_CROSSES | Compares two geometry objects and returns TRUE if their intersection "spatially crosses"; that is, the geometries have some, but not all interior points in common.โ |
ST_TOUCHES | Returns TRUE if A and B intersect, but their interiors do not intersect. โ |
ST_INTERECTS | Returns TRUE if two geometries intersect. Geometries intersect if they have any point in common.โ This would return a set of the results that is the same as all of the relationships listed above combined |
ST_DISJOINT | Returns TRUE if two geometries intersect. Geometries intersect if they have any point in common.โ |
ST_EQUALS | Returns TRUE if the given geometries are "topologically equalโ, i.e., the geometries have the same dimension, and their point-sets occupy the same space.โ |
SQL query using a spatial join
Return all train stations within 10km of my location.โ
Indexingโ
Indexing columns that you are querying or trying to join on has huge benefits. Below are a few examples, however it will be beneficial to take a look for yourself to understand fully the benefits of indexing.โ
Indexes allow database engines to locate specific rows based on the indexed column faster without needing to scan the entire table.โ
Indexes make data easier to navigate and retrieve information by transforming the data into an organised structure.โ
They can help to ensure data consistency. Unique indexes ensure no two rows in the table have the same key values, helping to prevent duplicate rows.โ
When To Use IDs vs When To Use Spatial Relationshipsโ
IDs
Advantages
Simple and fast.โ
Works with non-spatial data.โ
Easy to understand and implement.โ
Disadvantages
Requires datasets to have at least one common field.โ
Doesnโt consider spatial data, therefore limits analysis capabilities.โ
When?
You have a common field and need a quick and straightforward join.
Spatial Relationships
Advantagesโ
Utilises spatial relationships between features.โ
Data can be analysed and visualised based on spatial criteria.
Disadvantages
Increased complexity and computational intensity. โ
Requires spatial data and GIS tools.
When?
Spatial data is crucial to your analysis or could help to give greater insight in your analysis. This could be for geographic studies or working with spatial data layers. โ
Disclaimer:
Spatial querying can be accomplished in multiple Programs/Software using various programming languages. โ
For the purposes of this Lightning Talk, when referring to spatial querying, we will be using PostGIS in PostgreSQL. โ
Spatial querying can also be accomplished using any software/programs that have spatial functionality.โ
This content has been developed from what was originally a Lightning Talk PowerPoint slide set. These slides are available to PSGA members to view and download from the PSGA members area of the OS website
Last updated