是否可以使用纬度和经度的舍入值向 Postgres 表添加约束? [英] Is it possible to add a constraint to a Postgres table using rounded values for latitude and longitude?

问题描述

我有一个存储纬度和经度数据的表.类似于以下内容:

I have a table that stores latitude and longitude data. Something like the following:

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

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

After a while, we realized that we didn't want to allow geographical coordinates to be entered that are too close to one another, so we thought we could use a constraint to help enforce this. My thinking was that if we rounded the coordinates to n decimal places, that would ensure that the geographical coordinates would never be piled too closely on top of one another.

我试过了:

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

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

But that doesn't work -- it's a syntax error.

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

In this case, I was able to achieve what I wanted by using a unique index:

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

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

Note that I had to force the latitude and longitude to be numeric, otherwise it got an error about the function signature (?). FYI: this will fail with a very generic error message "could not create unique index" if there are rows present which violate the condition.

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

My question is: is it possible to do this using a constraint instead of an index? And more broadly, when is it ok to use modifying functions (like LOWER() to avoid duplicate email addresses) to help enforce data integrity?

推荐答案

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

You can create a function to be used by the constraint.

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).

话虽如此，四舍五入协调将不能确保两个位置彼此不靠近.使用 3 位小数四舍五入，0.12349999 将四舍五入为 0.123，而 0.12350000000 将四舍五入为 0.124，尽管这两个点几乎在同一位置.

That being said, rounding the coordinated will not ensure that two locations are not near each others. Using rounding at 3 decimals, 0.12349999 will round to 0.123 while 0.12350000000 will round to 0.124, despite the fact that the two points are almost at the same place.

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

The proper way of doing it would be to use the PostGIS extension, to save the points as a geometry and to compute the real distance between the points. This function can also be used in the check constraint.

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;

这篇关于是否可以使用纬度和经度的舍入值向 Postgres 表添加约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！