I've been following this thread with some interest.
Was wondering if you ever thought about binding the textual address to a USNG location. https://usngcenter.org/
You can easily add individual locations within something like a farm field with as few as eight unique digits that
wouldidentify each 10 meter square. I'm oversimplifying my response, but once you look through how the USNG works,
you'llsee the benefit for using it as a address/location uniqueness enforcing tool.
It would easily allow for locating many different locations inside of a larger addressed location, as well as non,
addressedlocations. The USNG location can be thought of as a unique address unto itself, and works across the planet.
No two are alike.
Bobb
-----Original Message-----
From: Peter Devoy <peter@3xe.co.uk>
Sent: Tuesday, May 12, 2020 3:56 PM
To: Peter J. Holzer <hjp-pgsql@hjp.at>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Enforcing uniqueness on [real estate/postal] addresses
Think Before You Click: This email originated outside our organization.
>Is is possible to have two entries which have the same
>address_identifier_general, street and postcode, but different
>descriptions?
Unfortunately, yes. The data comes from gov't systems to regulate the development/alteration of arbitrary pieces of
propertyand those pieces do not always have a postal address. E.g. a farmer may one year apply to erect a wind turbine
in"field north of Foo Cottage"
and the next year apply to demolish "barnhouse west of Foo Cottage".
Now, I know what you are thinking, there is a normalization opportunity and you may well be right. However, the problem
doesexist in some of the other fields too and I am already facing a fair amount of join complexity in my schema so I am
tryingto figure out my options :)
>(What is an address_identifier_general, btw?)
Address identifier composed by numbers and/or characters. I'm using the terminology from the EU's "INSPIRE Data
Specificationon Addresses" Guidelines.
I haven't yet had the opportunity to try out the above suggestions but I will post again when I have.
