sql - Postgres - 如何在插入时自动调用 ST_SetSRID(ST_MakePoint(lng, lat), 4326)？

Question

我正在使用 postGIS，我对 SQL 不是很熟悉。

我可以成功地插入到我的 markers 表中，只要我做这样的事情(伪代码!):

'INSERT INTO markers(created_by, title, description, lat, lng, geography)\
       values($1, $2, $3, $4::decimal, $5::decimal, ST_SetSRID(ST_MakePoint($5::decimal, $4::decimal), $6))',
      [
        data.created_by,
        data.title,
        data.description,
        data.lat,
        data.lng,
        4326
      ]'

如您所见，我正在从我的应用程序代码中执行 ST_SetSRID(ST_MakePoint($5::decimal, $4::decimal), $6)。

我想避免这种情况，而是将其放在数据库端。由于我可以在数据库端执行诸如自动时间戳之类的操作，我想知道我是否也可以在表本身上执行 ST_SetSRID(ST_MakePoint(lng, lat), 4326)。

这样，应用程序只需要提供纬度和经度，数据库就会执行ST_SetSRID(ST_MakePoint())操作。

谢谢

编辑:我不确定将谁的答案标记为 +1，因为两者都提供了非常好的帮助和建议。我希望 future 的读者能够阅读这两本书并希望有所收获!

Answer 1

您可以使用触发器来完成此操作。您的插入调用不会处理几何图形，只会处理经纬度和其他非空间字段，触发函数将创建几何图形。我们不要忘记在更新行时做同样的事情。请注意，我对 SRID 进行了硬编码，因为无法将额外的动态参数传递给触发器函数。如果需要，在你的表中添加一个字段来保存这个值，你可以将它称为 new.srid

CREATE OR REPLACE FUNCTION markers_geog_tg_fn() RETURNS trigger AS
$BODY$BEGIN
  IF TG_OP = 'INSERT' AND (NEW.lat ISNULL or NEW.lng ISNULL  ) THEN
    RETURN NEW; -- no  geometry
  ELSIF TG_OP = 'UPDATE' THEN
    --Lat Long updated to null, erase geometry
    IF NEW.lat ISNULL or NEW.lng ISNULL THEN
        NEW.geography = NULL;
    END IF;

    IF NEW.lat IS NOT DISTINCT FROM OLD.lat and NEW.lng IS NOT DISTINCT FROM OLD.lng THEN
      RETURN NEW; -- same old geometry
    END IF;
  END IF;
  -- Attempt to transform a geometry
  BEGIN
    NEW.geography := ST_SetSRID(ST_MakePoint(NEW.lng::decimal, NEW.lat::decimal), 4326))
  EXCEPTION WHEN SQLSTATE 'XX000' THEN
    RAISE WARNING 'geography  not updated: %', SQLERRM;
  END;
  RETURN NEW;
END;$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER markers_geog_tg BEFORE INSERT OR UPDATE
   ON markers FOR EACH ROW
   EXECUTE PROCEDURE markers_geog_tg_fn();