如何在 SSIS 中使用几何数据类型进行相交查询

引言

本文将介绍如何将 SSIS 与几何图形和相交功能结合使用。我们遇到的一个场景是，需要从 LINZ（新西兰土地信息局，负责地理信息和测量职能，以及处理土地产权和管理皇家土地和财产的新西兰公共服务部门）检索地块数据。我们需要将这些数据进行相交处理，以仅检索与我们城市相关的数据。

我将向您展示我们是如何做到的。

为本示例设置数据库

我们有一个操作数据存储（或“ODS”），其中包含用于此示例的接口模式。 ODS 是一个旨在整合来自多个源的数据以进行进一步操作的数据库。与主数据存储不同，数据不会传回操作系统。它可以用于进一步操作并传递到数据仓库进行报告。

我有一个保存网格块的表。网格块既是地理单位也是分类。网格块是一个定义的地理区域，大小从城市街区的一部分到大片农村土地不等。这些数据是我们从新西兰统计局为我们城市获取的。表定义如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [interfaces].[MeshBlock](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [Space] [geometry] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

我还有一个保存我的主要地块的表。这些数据可以从 LINZ 下载。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [interfaces].[nz_primary_parcels](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [shape] [geometry] NULL,
       [appellation] [nvarchar](2048) NULL,
       [affected_surveys] [nvarchar](2048) NULL,
       [parcel_intent] [nvarchar](100) NULL,
       [topology_type] [nvarchar](100) NULL,
       [statutory_actions] [nvarchar](max) NULL,
       [land_district] [nvarchar](100) NULL,
       [titles] [nvarchar](max) NULL,
       [survey_area] [numeric](20, 4) NULL,
       [calc_area] [float] NULL,
       [_crc]  AS (checksum([id],CONVERT([varbinary](max),[shape]),[appellation],[parcel_intent],[topology_type],[land_district],[titles],[survey_area],[calc_area])),
       [stage_created_date] [datetime] NULL,
       [stage_updated_date] [timestamp] NOT NULL,
 CONSTRAINT [PK_nz_primary_parcels_2193] PRIMARY KEY CLUSTERED
(
       [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

最后，我有一个我创建的表，我需要在其中比较 LINZ 数据和我们本地应用程序中包含的地块数据。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [interfaces].[GISParcelDataRecon](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [Context_ParcelSue] [varchar](12) NULL,
       [Context_APP] [varchar](200) NULL,
       [Context_APP_Formated] [varchar](200) NULL,   
PRIMARY KEY CLUSTERED
(
       [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

最后一步是创建存储过程，用于进行数据相交处理。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:           Sunette Wessels
-- Create date:      23/09/2016
-- Description:      Get the intersect data
-- =============================================

CREATE PROCEDURE [interfaces].[Get_DataForIntersect]
@geo GEOMETRY
AS
BEGIN
SET NOCOUNT ON;

SELECT DISTINCT
  ISNULL(UPPER(RTRIM(LTRIM(ID))),'') COLLATE Latin1_General_CI_AS AS ParcelSUEGIS
, ISNULL (appellation,'') AS APPL
, ISNULL (REPLACE(appellation, ' ', ''),'') AS APPL_Formated
, ISNULL (Titles,'') AS Titles
  FROM [interfaces].[nz_primary_parcels] WITH (NOLOCK)
  WHERE 1=1
  AND shape.STIntersects(@geo) = 1
  AND parcel_intent NOT IN ('HYDRO','Railway', 'Road')

END

因此，设置完成后，让我们开始更有趣的部分。

在 SSIS 中进行有趣的操作

 

1. 设置城市网格块

这将是一个正常的导入过程。将数据从新西兰统计局获取并导入您的 LINZ 数据。

源如下所示：

并将数据映射到目标/目标表。

2. 将数据插入、更新、删除到表中

空间缓冲区网格块

获取数据如下：

SELECT CONVERT(NVARCHAR(MAX),[Space].STBuffer(100).Reduce(100))  AS [Space]
FROM  [interfaces].[MeshBlock]

 

• 我们需要为网格块添加缓冲区并进行平滑处理。如果不这样做，开销会很大，并且 SSIS 包的运行速度会非常慢。以下文章提供了一些有关此内容的要点：http://sqlblog.com/blogs/rob_farley/archive/2015/04/29/tuning-slow-spatial-queries-in-sql-server.aspx
•  对于您的城市，您可能需要更改 100 为另一个值，该值将足够充分地工作而不会丢失任何信息。
• 我们还需要将数据转换为 NVARCHAR(MAX)，因为 SSIS 与几何图形形状的兼容性不佳，并且我们将在脚本中使用此字符串进行相交操作。我将在下一步向您展示如何操作。

城市上下文数据

脚本的输入列如下所示：

我们的输入和输出列如下所示：

我们需要添加输出列。它们将具有以下属性：

 

列名	数据类型	长度
SUENumber	DT_STR	8
APPL	DT_WSTR	2048
APPL_Formated	DT_WSTR	2048
标题	DT_WSTR	4000

 

 将您的输出重命名为 MyTableOutput。

 按如下方式设置您的连接管理器：

它需要是连接到您数据库的 ADO.NET 连接。

现在让我们进入代码部分。

确保您具有以下引用：

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.SqlClient;
using Microsoft.SqlServer.Types;
using System.Data.SqlTypes;
using System.Text;

我们需要设置到我们数据库的连接。

IDTSConnectionManager100 connMgr;
SqlConnection sqlConn;
SqlDataReader sqlReader;

public override void AcquireConnections(object Transaction)
{
  connMgr = this.Connections.MyConnection ;
  sqlConn = (SqlConnection)connMgr.AcquireConnection(null);
} 

完成后，我们需要再次关闭连接。

public override void PostExecute()
{
 base.PostExecute();
 connMgr.ReleaseConnection(sqlConn);
}

棘手的部分发生在 ProcessInputRow 中。我们转换为字符串的网格块需要转换回几何图形类型，以便我们的存储过程可以使用。我发现我的网格块字符串的长度很重要。我发现如果我不指定我的 BLOB 数据的长度，我的相交效果就不太好，我返回的数据与我的城市不相交，或者不知何故是较小的地块。原因是 BLOB 数据在末尾添加了额外的空间，这些空间没有被解释为空格而是被解释为值。我还必须确保我的编码正确，否则您可能会将纬度、经度数据与横轴墨卡托（横轴墨卡托投影是标准墨卡托投影的变体。横轴版本被广泛用于世界各地的国家和国际测绘系统，包括 UTM。）格式的数据进行比较。我们的网格块也需要分配正确的墨卡托代码。

完成转换后，我们调用存储过程，将其参数传递给它，并将 SQL 数据读取器中的结果放入我们的输出中。

这是代码

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
 //get the lenght of the string of our Meshblock
 var spacelenght = Convert.ToInt32(Row.Space.Length);

 //get our blobdata for our meshblock
 var BlobData = Row.Space.GetBlobData(0, spacelenght);

 //convert it to unicode
 var StringData = Encoding.Unicode.GetString(BlobData);

 //change my string data to sqlchars. this is needed for the STGeomFromText function
 SqlChars myChar = new SqlChars(new SqlString(StringData));
 
 //convert our string to geometry type. Our Mercator id is 2193. You need to find out the code for country.
 SqlGeometry myGeo = SqlGeometry.STGeomFromText( myChar , 2193);

 //calling our stored procedure.
 SqlCommand cmd = new SqlCommand();
 cmd.CommandText = "interfaces.Get_DataForIntersect";
 cmd.CommandTimeout = 120; //You do need to set the command timeout otherwise if the query runs long it might fail on time.
 var myParam = cmd.Parameters.Add("@geo", typeof (SqlGeometry )); //this is the way to set the slqgeometry as the datatype is not recognised otherwise.
 myParam.UdtTypeName = "GEOMETRY "; //You do need to set the UdtTypeName as GEOMETRY, reason same as just above.
 myParam.Value = myGeo;
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Connection = sqlConn;

 //getting our data into a reader
 sqlReader = cmd.ExecuteReader();

 while (sqlReader.Read())
 {
  //adding a row to our table output
  this.MyTableOutputBuffer.AddRow();
  
  //suenumber row
  if (sqlReader.GetString(0) != null)
  {
   this.MyTableOutputBuffer.SUENumber = sqlReader.GetString(0);
  }
  else
  { this.MyTableOutputBuffer.SUENumber_IsNull = true; }

  //appl row
  if (sqlReader.GetString(1) != null)
  {
   this.MyTableOutputBuffer.APPL = sqlReader.GetString(1);
  }
  else
  {this.MyTableOutputBuffer.APPL_IsNull = true;}

   //applformated row
  if (sqlReader.GetString(2) != null)
  {
   this.MyTableOutputBuffer.APPLFormated = sqlReader.GetString(2);
  }
  else
  { this.MyTableOutputBuffer.APPLFormated_IsNull = true; }

  //title row
  if (sqlReader.GetString(3) != null)
  {
   this.MyTableOutputBuffer.Titles = sqlReader.GetString(3);
  }
  else
  {this.MyTableOutputBuffer.Titles_IsNull = true;}                 
 }
}

 

 最后一步是链接到您的目标数据库并映射您的列。运行包，见证奇迹发生。

抱歉。图片似乎在草稿中加载，在预览中显示，但在已发布的内容中不显示。

关注点

确保您的网格块和地块数据之间具有正确的格式。有关该功能的更多信息，请参阅https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeometry.stgeomfromtext.aspx。