postgis sql语句备忘

创建触发器，在更新或新增前取geom多边形中心点存入xy字段

CREATE OR REPLACE FUNCTION update_xy_on_insert_block()
RETURNS TRIGGER AS $$
BEGIN
    NEW.lat := st_y(st_centroid(NEW.geom));
    NEW.lng := st_x(st_centroid(NEW.geom));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_xy_on_block
BEFORE INSERT OR UPDATE ON block_info
FOR EACH ROW
EXECUTE