[Talk-GB] UPRN wiki page

[Mark Goodge]

On 18/11/2020 11:10, Robert Whittaker (OSM lists) wrote:

> It could well vary by local authority, but looking at the pattern of
> URPNs near where I live, it certainly seems that there is one assigned
> to every public road. There is consistently exactly UPRN at one end or
> the other of each road. The pattern is quite obvious if you look at a
> housing estate with lots of little roads.

Not every street has them. Mine doesn't, for example. But others in my 
town do.

Having had a look at various examples, I'm pretty sure that every 
newbuild street that was constructed since UPRNs became a requirement 
has one, but they only get applied retrospectively to existing streets 
if either the local authority has a policy of bulk-adding them or an 
operational need arises to do so.

The fact that they are always at the extreme end of the street, though 
(a pattern I hadn't previously spotted), gave me an idea. I've run a 
load of queries against the data, and it's possible, with very high 
reliability, to find the street record UPRN for a USRN by looking for a 
UPRN which precisely matches the geometry of the street. You can see the 
results of that here, for example, where I've changed the icon for 
street records to be red instead of blue:

https://uprn.uk/map?loc=17,52.1714708,-2.2041535

If anyone wants to replicate that, the process I, broadly speaking 
(after a few false starts!) was, iterating over each USRN sequentially...

1. Find any associated UPRNs that are linked to that USRN in the OS Open 
Linked Identifiers (LIDS) database. If there are none, then presumably 
this USRN doesn't have a street record.

2. If there are linked UPRNs, then run a spatial SQL query to find 
which, if any, intersect the geometry of the USRN.

3. Any that match are street records.

Normally, there is only one street record for a USRN. I was a bit 
surprised to find some USRNs that matched multiple UPRNs in this way, 
but, on checking them, all of the UPRNs have identical coordinates! So, 
in those cases, I think what we have is a set of one or more historic 
(archived) UPRNs that share coordinates with the currently active one.

Doing this found me 301,645 UPRNs that I think are street records, which 
is in the right ballpark if a little on the low side. Picking some at 
random, and checking them against non-open sources has, so far, resulted 
in no false positives - that is, all the ones I think are street records 
really are street records. That gives me confidence to assume that a 
precise match is reliable.

However, there are false negatives. That is, there are some UPRNs that I 
know to be street records (both from a visual check on the map and 
cross-checking against non-open sources), but that don't get matched by 
this algorithm.

Having had a look, this appears to be caused by a slight mismatch 
between the two sets of coordinates. In all the ones I've checked, it's 
a simple question of a different number of significant digits - 
truncating the one with more digits results in a match.

So I think what I need to do next is modify my algorithm to look for a 
UPRN that precisely matches, or is very close to (less than 1m 
distance), the first or last node in the USRN geometry.

I'll report back when I've done that. It may take a while, though!

Mark