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 would identify each 10 meter square. I'm oversimplifying my response, but once you look through how the USNG works, you'll see 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, addressed locations. 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 <pe...@3xe.co.uk> Sent: Tuesday, May 12, 2020 3:56 PM To: Peter J. Holzer <hjp-pg...@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 property and 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 does exist in some of the other fields too and I am already facing a fair amount of join complexity in my schema so I am trying to 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 Specification on Addresses" Guidelines. I haven't yet had the opportunity to try out the above suggestions but I will post again when I have.
