编辑PostgreSQL：实现数据库数据实时推送到应用程序

shiyonghao

网盘：https://pan.baidu.com/s/1jlyxineRQ_2n-pVHJn1_MA#list/path=%2F

提取码：2021

前言

我们可以借助FME Server 2020中的自动化功能和数据库的触发器功能来监测PostgreSQL数据库更改情况，并实时推送数据。虽然数据库自带的触发器功能也可以通过FME工作空间的Webhooks将任务直接发送到FMEServer上去。然而，使用此方法会错过Automations提供的其他功能，例如webhook触发器，多工作空间链接，电子邮件通知等等。所以通过将FMEServer的自动化功能与PostgreSQL数据库触发器相结合，再将这些过程应用于我们项目的工作流程时，就可以实现更加丰富多样的功能搭配组合。

那么，为什么我们要了解数据库触发器呢？这里您可以假设一下，有一个中央内部数据库，其中包含需要同步的外部应用程序数据集。每当中央数据库发生更改时，更新对应的外部应用程序数据集就需要大量时间和手动工作。而如果我们在FMEServer中使用数据库触发器，那么FME Server中的自动化功能将自动以高效且简化的过程为您完成所有数据同步工作。

软件要求

• FME Server2020

• FME Desktop 2020

• PostgreSQL 12

  • PostGIS扩展

  • PL / Python（Python 3）

    
    

• ArcGIS Online帐户（最低Creator许可证）。

  
  

方案概述

1、通过FME Server发送任务请求并调用FME Server上一个简单的工作空间可以将数据加载到PostGIS数据库中。

2、数据库中的设置好的触发器函数将检测到所有发生的数据库更改，并使用补充函数将JSON格式的消息发送到Automationwebhook触发器上。

3、FME Server上的自动化处理流程中包含一个消息处理工作空间，用于解析已编辑记录的ID、已执行的数据库操作以及进行编辑的日期信息。解析的结果将被写入到担任实时更改日志功能的ArcGISOnline要素服务中。

注意：本文中使用的工作空间，自动化功能和SQL脚本是相对基本的，目的是说明FMEServer针对数据库的应用潜力。可以对代码段和脚本进行自定义修改以适应您的需求和环境。本文所使用的是空间数据库进行操作，您也可以将相同的方法应用于非空间数据。

1.设置PostgreSQL数据库和Python

接下来让我们开始示例演示。第一步是安装PostgreSQL并将其用作我们的中央数据库。在此演示中，我们将使用PostgreSQL12数据库。安装PostgreSQL后，我们需要打开StackBuilder来安装PostGIS和PL /Python。

接下来选择PostGIS和EDB语言包。虽然我们也可以从其他来源使用Python，但EDB语言包是最简单，最直接的安装之一。这一步至关重要，请按照下列步骤在运行命令行里以管理员身份设置您的系统环境变量，否则PostgreSQL将无法找到Python解释器。

使用pgAdmin或类似工具，创建一个新数据库并运行CREATEEXTENSION postgis以在空间上启用该数据库。运行CREATEEXTENSION plpython3u以启用PL /Python。

CREATE EXTENSION postgis;

CREATE EXTENSION plpython3u;

注意：如果在尝试将plpython3u添加到数据库时遇到错误“找不到指定的模块”，则可能需要验证是否正确设置了环境变量和Python。

2. 在FME Server中创建一个自动化流程

接下来，我们需要在FME Server中创建一个自动化流程，它可以接收从PostgreSQL数据库触发器传入的JSON消息，并将该消息传递到我们自动化流程中的工作空间里。

打开FMEServer并创建一个新的自动化流程，将Webhook触发器添加到画布。而后保存自动化以生成WebhookURL，该URL将在数据库触发功能中使用。

3.创建数据库触发器函数和请求函数

下载并解压缩本文提供的postgis-single-trigger.zip，在文本编辑器中打开SQL脚本。使用PostgreSQL查询工具复制并运行CREATETABLE语句以创建表。该表保存了所报告的数据的编号，报告的日期，报告者的姓名，坑洞的大小和位置。请注意，位置/几何列包含在EPSG：4326（LL84）中作为坐标系。

CREATE TABLE single_pothole

(

  idserial PRIMARY KEY,

date DATE,

name VARCHAR(255),

size CHAR(10),

geom GEOMETRY(Geometry,4326)

);

在脚本的第4步中，我们将初始化将由触发函数调用的HTTP请求函数（第5步）。所述send_request函数使用PL /Python来执行以下步骤：

• 将FME服务器URL和自定义消息作为参数

• 导入解析URL并发出Web请求所需的Python库

• 解析URL并提取必要的组件

• 将自定义消息作为JSON格式的内容有效负载发送到POST请求到FME Server（标头指定有效负载类型，在我们的情况下为application / json）

• 从FME Server获取响应并将其作为字符串返回

  
  

在运行步骤5之前，您需要用上一节中的webhookURL替换第66行的URL参数。脚本的此部分使用PL / pgSQL设置触发函数，以执行以下操作：

• 声明将在函数中使用的自定义消息和熟知的文本点变量

• 如果记录的几何值被插入或更新，则将其存储在wkt_point中，并使用PostGIS ST_AsText函数将其转换为WKT格式

• 使用if / else语句和TG_OP触发过程来检查触发了哪个操作（INSERT，UPDATE或DELETE），并相应地生成自定义消息。请注意，DELETE操作将使用旧的几何值，因为不存在新的记录。

• 创建包含点ID，动作类型以及旧的或新的几何值作为WKT的自定义JSON格式的消息（取决于动作）

  
  

在查询工具中运行步骤4、5和6。该脚本的第6步用于将single_function绑定到single_pothole表。在插入，更新或删除行之后的那一刻将调用触发器。通过进入Schema> public> Trigger Functions和Schema>public> Tables> [Table Name]>触发器（绑定的触发器函数），可以在pgAdmin中查看和管理触发器和函数。

4.   设置数据加载工作空间

出于演示目的，我们将使用一个简单的FMEServer服务将任务提交到数据加载工作空间，该工作空间将在PostGIS数据库上执行各种操作。

打开postgis_single_loader.fmw工作空间如下图。

该工作空间将发布在FMEServer上，以接收和处理Server任务请求并在数据库上执行示例操作。编辑PostGIS写模块，以连接到您自己的数据库。在CSV读模块中，将路径设置为示例CSV文件（Potholes.csv）如下图。

注意：当我们使用FME工作空间加载数据时，如果您使用任何其他方法（例如SQL，ArcGIS）编辑single_pothole，数据库触发器也将运行。

准备就绪后，将工作空间发布到FME Server中名为“ PostGIS-Single”的新存储库文件夹中，作为Job Submitter服务。包括数据库连接和示例Potholes.csv文件（上传到Shared Resource文件夹> Data）。

5.   创建ArcGIS Online更改日志功能服务

在本节中，我们将使用ArcGIS Online演示一个同步的外部应用程序，该应用程序可以监视数据库中的更改而无需直接访问数据库。登录到ArcGIS Online帐户并创建一个名为PotholeChangeLog的新的点要素图层。在图层的“数据”>“字段”中，创建三个新字段：

• id     (integer)

• RowAction     (string)

• DateEdited     (date)

  
  

在这里我们创建一个简单的ArcGIS OnlineWeb应用程序服务以显示要素。

6.   创建ArcGIS Online更改日志功能工作空间

打开postgis_single_logger.fmw工作空间。

编辑ArcGIS Online要素服务写模块参数以连接到ArcGIS Online帐户。确保输出几何设定为arcgisonline_point，并与我们在第5部分中创建的功能服务层，您可能需要手动更改用户属性类型，特别是图层名称匹配的“id”到esriFieldTypeInteger（NOT esriFieldTypeOID）和“ DateEdited”到esriFieldTypeDate。

日志记录工作空间将放置在自动化中的webhook触发器之后，以执行以下操作：

• 获取Webhook触发器的输出键值（点ID，数据库操作和WKT几何）

• 用有效的WKT点几何转换并替换webhook.geom值

• 将坐标系设置为LL84

• 记录当前日期和时间，并将其转换为ArcGIS Online可以读取的兼容格式（我们使用的是ISO日期时间）

• 删除并重命名属性以匹配AGOL要素服务层

• 将具有属性的点要素写入要素服务

  
  

完成工作空间的配置后，将其作为Job Submitter服务发布到FME Server中，并与数据加载工作空间一起存储在与ArcGIS Online Web连接相同的存储库中。

7.   设置FME Server APP

要配置服务器应用程序，需要执行以下操作：

1、在FME Server中，转到“工作空间”>“管理工作空间”>“ PostGIS-Single”>，然后打开postgis_single_loader.fmw。

2、在“高级”下，单击“创建FME服务器应用程序”。

3、将应用程序命名为“     postgis-single-edit-server-app”，然后为标题输入“     PostGIS Single Edit Triggers and FME Server”。

4、完成配置应用程序后，单击“确定”。打开服务器应用程序URL。

使用下拉菜单选择要运行的示例：

1、将新的坑洞记录插入PostGIS数据库。（id = 1）

2、将三个新坑洞记录插入数据库。

3、将id = 3的记录从“中型”坑洞大小更新为“大”。

4、删除在步骤1中插入的记录（如果示例1运行多次，则删除所有重复）

8.   完成自动化流程搭建并运行示例

打开在第2部分中创建的自动化。将工作空间添加到画布，然后选择postgis_single _logger.fmw工作空间。如前所述，我们想使用输出键将自定义JSON消息从webhook触发器传递到记录器工作空间。

在webhook触发器中，打开“输出键”面板。在“ Webhook密钥”下，创建三个新密钥：id，action和geom。这些键是从PostgreSQL触发函数发送的JSON中解析出来的，并传递到记录器工作空间中。（可选）您可以将日志操作添加到webhook触发器并将其设置为记录webhook消息内容。单击“应用”，保存自动化，然后单击“启动自动化”。

打开FMEServer App并按顺序运行示例。您将被重定向到任务状态页面，该页面指示该任务何时运行以及它是否完成/失败。检查PostGIS数据库和自动化日志，以确保编辑成功完成。对于数据库的每次编辑，任务提交都将出现在日志中。如果您将消息内容日志操作添加到自动化中，则从数据库触发器发送的JSON格式的消息也将出现在日志中。

检查您的ArcGIS Online要素服务/ Shapefile，以验证是否创建了已编辑的记录。请记住，我们正在从示例CSV文件中读取相同的记录，因此要素服务中可能会有重叠的点。

经过以上的操作，相信您已经掌握了PostgreSQL数据库触发器的基础知识以及如何将它们与FME Server一起使用。接下来相信您将花更少的时间进行手动工作，而让FME Server更高效地为您完成工作！

本文转载来自CSDN作者：fmechina  版权归作者所有
原文链接：https://blog.csdn.net/fmechina/a ... 1001.2014.3001.5501

潘无缘

6666666666666

fugangxx

谢谢分享