是否可以使用纬度和经度的舍入值向 Postgres 表添加约束？

我有一个存储纬度和经度数据的表。像下面这样：

CREATE TABLE geo_sample
( 
  id uuid DEFAULT uuid_generate_v4 (),
  latitude FLOAT NOT NULL,
  longitude FLOAT NOT NULL,
  PRIMARY KEY (id)
);

一段时间后，我们意识到我们不想允许输入彼此太接近的地理坐标，因此我们认为可以使用约束来帮助强制执行这一点。我的想法是，如果我们将坐标四舍五入到小数点后 n 位，这将确保地理坐标永远不会彼此堆叠得太紧密。

我试过这个：

ALTER TABLE geo_sample
ADD CONSTRAINT unique_areas UNIQUE (ROUND(latitude::numeric, 3), ROUND(longitude::numeric, 3));

但这不起作用——这是一个语法错误。

在这种情况下，我能够通过使用唯一索引来实现我想要的：

CREATE UNIQUE INDEX unique_areas ON geo_sample (ROUND(latitude::numeric, 3), ROUND(longitude::numeric, 3));

请注意，我必须强制纬度和经度为数字，否则会出现有关函数签名的错误（？）。仅供参考：如果存在违反条件的行，这将失败并显示非常通用的错误消息“无法创建唯一索引”。

我的问题是：是否可以使用约束而不是索引来做到这一点？更广泛地说，什么时候可以使用修改函数（例如LOWER()以避免重复的电子邮件地址）以帮助加强数据完整性？

您可以创建一个供约束使用的函数。

CREATE FUNCTION no_similar_XY_in_my_data(x float, y float)
RETURNS boolean AS $$
SELECT NOT EXISTS (
  SELECT 1 FROM geo_sample 
    WHERE round(geo_sample.latitude::numeric,3) = round(y::numeric,3) AND  round(geo_sample.longitude::numeric,3) = round(x::numeric,3)
  );
$$ LANGUAGE sql;

ALTER TABLE geo_sample ADD CONSTRAINT no_similar_XY CHECK (no_similar_XY_in_my_data(longitude,latitude));

insert into geo_sample(longitude,latitude) values (1.23456, 9.876543);
INSERT 0 1
insert into geo_sample(longitude,latitude) values (1.23456, 9.876543);
ERROR:  new row for relation "geo_sample" violates check constraint "no_similar_xy"
DETAIL:  Failing row contains (2, 9.876543, 1.23456).

话虽这么说，四舍五入协调一致的意愿not确保两个位置彼此不靠近。使用小数点后 3 位舍入时，0.12349999 将舍入为 0.123，而 0.12350000000 将舍入为 0.124，尽管这两个点几乎位于同一位置。

正确的方法是使用 PostGIS 扩展，将点保存为几何图形并计算点之间的实际距离。该函数也可以用在检查约束中。

CREATE FUNCTION no_nearby_point_in_my_data(g geometry)
RETURNS boolean AS $$
SELECT NOT EXISTS (
  SELECT 1 FROM geo_sample 
  WHERE ST_DWithin( my_data.geom, g, 0.001));
$$ LANGUAGE sql;