Postgres: Create unique index on point within certain distance around it

Question

I have a folowing table in postgres 15

create table places
(
    id          bigint generated always as identity
        constraint pk_places
            primary key,
    name        varchar(128) not null,
    address     varchar(256) not null,
    region_name varchar      not null
        constraint fk_places_region_name_regions
            references regions
            on update cascade on delete restrict,
    coordinates geography(Point, 4326),
    constraint uq_places_name
        unique (name, region_name)
);

alter table places
    owner to postgres;

create index idx_places_coordinates
    on places using gist (coordinates);

I would like to create a unique index on coordinates field but exact value being unique makes little sence as coordinates might be specified with tiny tolerance to each other which effectively makes them non-unique. Question - is it possible to construct unique index such a way that, for example, 1 point and another point that would be located for example in a radius of 100 meters around first 1 would be considered as 1 (same) point and in return would conjure up unique index constraint exception?

Thank you

Answer 1

You can create an exclusion constraint for a small buffer around the points that prevents the bounding boxes of these buffers from overlapping:

ALTER TABLE places ADD EXCLUDE USING gist (
   (st_buffer(coordinates, 50, 'quad_segs=1')) WITH &&
);