Postgres-如何在插入上自动调用ST_SetSRID(ST_MakePoint(lng，lat)，4326)? [英] Postgres - How to automatically call ST_SetSRID(ST_MakePoint(lng, lat), 4326) on inserts?

问题描述

我正在使用postGIS，但对SQL不太熟悉.

I'm using postGIS, and I'm not very familiar with SQL.

只要执行以下操作( pseudocode！)，我就可以成功地插入到markers表中:

I can successfully insert into my markers table as long as I do something like this (pseudocode!):

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

As you can see, I am doing ST_SetSRID(ST_MakePoint($5::decimal, $4::decimal), $6) from my application code.

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

I want to avoid that, and instead put this on the database side. Since I can do things like auto time stamps on the db side, I'm wondering if I can also do ST_SetSRID(ST_MakePoint(lng, lat), 4326) on the table itself.

这样，应用程序只需要提供lat和lng，数据库就会执行ST_SetSRID(ST_MakePoint())操作.

This way, the application only needs to provide lat and lng, and the database will do the ST_SetSRID(ST_MakePoint()) operation.

谢谢

我不确定谁将答案标记为+1，因为两者都提供了非常好的帮助和建议.我希望未来的读者能读懂它们，并希望能学到一些东西！

I'm not sure whose answer to mark as +1 since both offered tremendously good help and advice. I hope future readers will read both and hopefully learn something!

推荐答案

您可以通过使用触发器来做到这一点.您的插入调用将不处理几何图形，仅处理纬度和其他非空间字段，并且触发函数将创建几何图形.让我们不要忘记在更新行时执行相同的操作. 请注意，我已经对SRID进行了硬编码，因为不可能将额外的动态参数传递给触发函数.如果需要，在表中添加一个字段来保存该值，您可以将其称为new.srid

You can do this by using a trigger. Your insert call would not deal with the geometry, only with lat-long and other non-spatial fields, and the trigger function will create the geometry. Let's not forget to do the same when updating the row. Note that I have hard-coded the SRID as it is not possible to pass extra dynamic parameters to the trigger function. If need be, add a field to your table to hold this value and you can refer to it as 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();

这篇关于Postgres-如何在插入上自动调用ST_SetSRID(ST_MakePoint(lng，lat)，4326)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！