Postgres: 在某一距离内创建点的唯一索引

huangapple go评论46阅读模式
英文:

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

问题

我想在coordinates字段上创建一个唯一索引,但确切的值唯一性意义不大,因为坐标可能以很小的容差指定,从而使它们在实际上变得不唯一。问题是,是否可以构建唯一索引,以便,例如,1个点和另一个点,如果它们位于第一个点周围100米的半径内,将被视为1个(相同的)点,并且将导致唯一索引约束异常?

谢谢

英文:

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

答案1

得分: 4

你可以为点周围的小缓冲区创建一个排除约束,以防止这些缓冲区的边界框重叠:

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

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 &&
);

huangapple
  • 本文由 发表于 2023年6月18日 18:50:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/76500152.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定