Hi Alban,
I plainly agree on the uniqueness thing. and on the fact that a PK with only a geometry column can be considered somehow "ill suited".
That said, the PK we finally use contains, as said, 3 columns:
--an id (integer column)
--a topology describer (the text column)
--and the geometry column.
this set IS unique in our dataset. and helps provide some quality checks (wrong id, non coherent tolology,. and some geometry errors..)
anyway, my original post was about the fact that we must create a PK based on some kind of work around the limited btree length behaviour thus creating an index that will never be of any use, just for technical reasons.
People in this list have been quite clear that the btree limitation will NOT change. Which ends the point.
regards
On Thu, Jun 10, 2021 at 10:33 PM Alban Hertroys <haramrae@xxxxxxxxx> wrote:
> On 8 Jun 2021, at 22:50, Thomas Kellerer <shammat@xxxxxxx> wrote:
>
> Marc Millas schrieb am 03.06.2021 um 22:51:
>> on a table we need a primary key and to get a unique combinaison, we need 3 columns of that table:
>> 1 of type integer,
>> 1 of type text,
>> 1 of type geometry
>>
>
> How do you define the "uniqueness" of the geometry?
That is actually the big question here. Multiple “unique” geometries can specify the same geometry!
A geom as simple as a line from (0,0) - (1,0) can just as easily be specified as (1,0) - (0,0). That’s the simplest case, and one could argue that the point of origin is different, but the next example would be a triangle starting at the same origin but traversed in different directions. It gets harder the more vertices a polygon has.
I would argue that a geometry type is ill-suited as a primary key column candidate.
Now, of course, the OP could have a case where their geometries are guaranteed to be unique regardless, but they’d better make sure before adding them to the PK.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
