英文:
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 &&
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论