I'm looking for advice on how best to manage the following problem with a PostGIS table.
A query produces a table of geographic sectors (polygons) from several other reference tables such as administrative zones (multipolygons) and boundaries (lines) used to divide the zones according to management issues. A unique integer code is assigned to each sector based on a 10-digit model, imposed by the application into which the sectors are subsequently loaded: the first two describe the nature of the sector, the next 5 correspond to its administrative zone and the last 3 to a sequence number within the administrative zone, guaranteeing the uniqueness of the code. This sequence number is assigned according to the geographical position of the sectors within their administrative zone: from northwest to southeast, following the "natural" reading direction of most languages.
Reference tables regularly evolve, both in terms of their geometry and their other attributes: modification of an administrative perimeter, change of name, creation of a new boundary, and so on. The table of geographical areas must therefore be updated accordingly. When you run the query, you get an up-to-date table, but one in which the numbering may have changed: if a sector is carved up by a new boundary, then all the sectors that follow in the numbering order will have a new number.
The challenge is to find a perennial identifier that can be used to update modified sectors and guarantee referential integrity with other tables. In this case, I don't know how to go about it. I've been thinking about a digital fingerprint, but I don't know if it's a good idea and with which columns to calculate it.
Could you help me? Thank you very much. Sincerely
Thank you for your reply. It made me realize that I'd forgotten to specify one thing: the code I'm talking about has a uniqueness constraint, but it's not the primary key. The primary key is a serial id. The principle is therefore to generate the objects and their unique code with a query, then insert them into a table. The id is assigned at the time of insertion. But when this table already contains objects from a previous version, I don't know how to match them with the new ones (which have no id, only the unique code, but which may have changed between the two versions for a given object) to know which ones to insert, update or delete. I can imagine that my modelling must not be correct, but this principle is no less practical for generating up-to-date objects when I need them... A final clarification: the format of the unique code is imposed by the application in which the Thank you for your reply. It made me realize that I'd forgotten to specify one thing: the code I'm talking about has a uniqueness constraint, but it's not the primary key. The primary key is a serial id. The principle is therefore to generate the objects and their unique code with a query, then insert them into a table. The id is assigned at the time of insertion. But when this table already contains objects from a previous version, I don't know how to match them with the new ones (which have no id, only the unique code, but which may have changed between the two versions for a given object) to know which ones to insert, update or delete. I can imagine that my modelling must not be correct, but this principle is no less practical for generating up-to-date objects when I need them... Last clarification: the format of the unique code is imposed by the application in which the objects are to be loaded.