PostgreSQL（PostGIS）触发器+坐标转换案例

需求，只录入一份坐标参考为4326的数据，但是发布的数据要求坐标必须是3857

对这种需求可以利用数据库触发器实现数据的同步

步骤：

1. 使用ArcGIS Pro创建一个名字为testfc_4326的图层，坐标参考为4326

2. 使用Pro再创建一个名字为testfc_3857,坐标参考为3857

3. 对两个图层分别开启Global ID功能

4. 图层的字段如下：

lftest_postgis=# \d testfc_4326Table "sde.testfc_4326"Column       |          Type          | Collation | Nullable |                           Default
-------------------+------------------------+-----------+----------+-------------------------------------------------------------objectid          | integer                |           | not null |gdb_geomattr_data | bytea                  |           |          |shape             | geometry               |           |          |field1            | character varying(255) |           |          |globalid          | character varying(38)  |           | not null | '{00000000-0000-0000-0000-000000000000}'::character varying
Indexes:"r387_sde_rowid_uk" UNIQUE, btree (objectid) WITH (fillfactor='75')"uuid_387" UNIQUE, btree (globalid) WITH (fillfactor='75')"a362_ix1" gist (shape)
Check constraints:"enforce_srid_shape" CHECK (st_srid(shape) = 4326)lftest_postgis=# \d testfc_3857Table "sde.testfc_3857"Column       |          Type          | Collation | Nullable |                           Default
-------------------+------------------------+-----------+----------+-------------------------------------------------------------objectid          | integer                |           | not null |gdb_geomattr_data | bytea                  |           |          |shape             | geometry               |           |          |field1            | character varying(255) |           |          |globalid          | character varying(38)  |           | not null | '{00000000-0000-0000-0000-000000000000}'::character varying
Indexes:"r388_sde_rowid_uk" UNIQUE, btree (objectid) WITH (fillfactor='75')"uuid_388" UNIQUE, btree (globalid) WITH (fillfactor='75')"a363_ix1" gist (shape)
Check constraints:"enforce_srid_shape" CHECK (st_srid(shape) = 3857)

  5. 同步触发器

CREATE OR REPLACE FUNCTION sync_4326_to_3857()RETURNS TRIGGER AS $$BEGINif pg_trigger_depth() < 2 then
IF TG_OP = 'INSERT' THENINSERT INTO testfc_3857(objectid, gdb_geomattr_data, field1,shape,globalid)VALUES (sde.next_rowid('sde','testfc_3857'), NEW.gdb_geomattr_data, NEW.field1,st_transform(NEW.shape,3857),NEW.globalid);RETURN NEW;ELSIF TG_OP = 'UPDATE' THENupdate testfc_3857 set gdb_geomattr_data=NEW.gdb_geomattr_data,field1=NEW.field1,globalid=NEW.globalid,shape=st_transform(NEW.shape,3857) where globalid=NEW.globalid;RETURN NEW;ELSIF TG_OP = 'DELETE' THENdelete from testfc_3857 where globalid=OLD.globalid;RETURN OLD;END IF;
END IF;RETURN NULL; -- 通常不会执行到这里END;$$ LANGUAGE plpgsql;
；
CREATE TRIGGER trg_sync_4326_3857AFTER INSERT OR UPDATE OR DELETE ON testfc_4326FOR EACH ROWEXECUTE FUNCTION sync_4326_to_3857();CREATE OR REPLACE FUNCTION sync_3857_to_4326()RETURNS TRIGGER AS $$BEGINif pg_trigger_depth() < 2 then
IF TG_OP = 'INSERT' THENINSERT INTO testfc_4326(objectid, gdb_geomattr_data, field1,shape,globalid)VALUES (sde.next_rowid('sde','testfc_4326'), NEW.gdb_geomattr_data, NEW.field1,st_transform(NEW.shape,4326),NEW.globalid);RETURN NEW;ELSIF TG_OP = 'UPDATE' THENupdate testfc_4326 set gdb_geomattr_data=NEW.gdb_geomattr_data,field1=NEW.field1,globalid=NEW.globalid,shape=st_transform(NEW.shape,4326) where globalid=NEW.globalid;RETURN NEW;ELSIF TG_OP = 'DELETE' THENdelete from testfc_4326 where globalid=OLD.globalid;RETURN OLD;END IF;
END IF;RETURN NULL; -- 通常不会执行到这里END;$$ LANGUAGE plpgsql;CREATE TRIGGER trg_sync_3857_4326AFTER INSERT OR UPDATE OR DELETE ON testfc_3857FOR EACH ROWEXECUTE FUNCTION sync_3857_to_4326();

6.使用Pro在testfc_4326插入一条记录后，使用sql查询如下，确实同步过来了

lftest_postgis=# select st_astext(shape) from testfc_3857;st_astext--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------POLYGON((1810836.61422326 1835666.21123957,5215209.44908767 -2293311.99894363,1593536.22052983 -6351863.96575071,-4418441.33887841 -5205160.20034392,1810836.61422326 1835666.2
1123957))
(1 row)lftest_postgis=# select st_astext(shape) from testfc_4326;st_astext
-------------------------------------------------------------------------------------------------------------------------------------------------POLYGON((16.267022076 16.267022076,46.84902358 -20.171107375,14.314979427 -49.451747112,-39.691533867 -42.294257399,16.267022076 16.267022076))

7. 使用pro编辑和删除数据后，都会同步过去。

8. 同理pro编辑testfc_3857也会同步到testfc_4326中。

8. 注意

我测试数据为4326到3857互转换，该转换为公开算法，因此可以使用postgis自带的st_transform实现，但是很多时候进行转换的都是cgcs2000到地方坐标，只有甲方有相关算法，并且再使用国产数据库的时候，st_transform的代码在数据库厂商手中，因此需要甲方和数据库厂商配合，甲方提供转换的函数或者动态库给数据库厂商来修改st_transform函数。