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

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

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

问题

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

谢谢

英文:

I have a folowing table in postgres 15

  1. create table places
  2. (
  3. id bigint generated always as identity
  4. constraint pk_places
  5. primary key,
  6. name varchar(128) not null,
  7. address varchar(256) not null,
  8. region_name varchar not null
  9. constraint fk_places_region_name_regions
  10. references regions
  11. on update cascade on delete restrict,
  12. coordinates geography(Point, 4326),
  13. constraint uq_places_name
  14. unique (name, region_name)
  15. );
  16. alter table places
  17. owner to postgres;
  18. create index idx_places_coordinates
  19. 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

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

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

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

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

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:

确定