How to use geometry datatype in SSIS for an intersect query

Introduction

In this article we will show how you can use SSIS with geometry and intersect. We had a scenario where we had to retrieve the parcel data from LINZ (Land Information New Zealand is the public service department of New Zealand charged with geographical information and surveying functions as well as handling land titles, and managing Crown land and property). We had to intersect this data to retrieve only data related to our city.

I’ll show you how we did this.

Setting up your database for this example:

We have an operational data store (or "ODS") with a schema of interface for this example.  ODS is a database designed to integrate data from multiple sources for additional operations on the data. Unlike a master data store, the data is not passed back to operational systems. It may be passed for further operations and to the data warehouse for reporting.

I have a table that saves the meshblock. A meshblock is both a geographic unit and a classification. A meshblock is a defined geographic area, varying in size from part of a city block to large areas of rural land. This data we retrieved from stats nz for our city. The table definition is as follows:

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

I also have a table where I save my primary parcels. This data can be downloaded from 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

Finally I have my table I created that I needed to do some comparison on between LINZ data and our local application that had data related to parcels on it.

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

The last little bit we have to do is create our stored procedure that will intersect the data:

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

So, once this is set up, let’s move on to the fun stuff.

Doing the fun stuff in SSIS

1. Setup Meshblock for City

This will be a normal import. Getting the data from nz stats and importing it into your LINZ.

The source is as follows:

And normal mapping into your target / destination table.

2. Insert, Update, delete data into tables

Space buffer meshblock:

Get the data as follows:

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

• We will need to add a buffer and smooth our meshblock. If we don’t do this, the overhead is great and your SSIS package will run very slow. The following article gave some good points on this:  http://sqlblog.com/blogs/rob_farley/archive/2015/04/29/tuning-slow-spatial-queries-in-sql-server.aspx
•  For your city you might want to change the 100 to another value that will work adequately without losing any information.
• We also need to convert the data to NVARCHAR(MAX) as SSIS don’t work well with geometry shapes and we are going to use this string for our intercept in our script. I’ll show you how to do that next.

Context data for city

The input columns for the script are as follows:

Our input and output columns look as follows:

We will need to add output columns. They will have the following properties

 Rename your output to MyTableOutput.

 Setup your connection Manager as follows:

It needs to be an ADO_net connection setup to your database.

Now let us go to the code:

Make sure you have the following references:

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;

We will need to set up a connection to our database.

IDTSConnectionManager100 connMgr;
SqlConnection sqlConn;
SqlDataReader sqlReader;

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

Once we are finished, we need to close the connection again:

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

The tricky part happens in the ProcessInputRow. Our meshblock we converted to a string will need to be converted back to a geometry type for our stored procedure.  What I did find was that the length of my meshblock string matters. I found if I didn’t specify the length of my blob data, my intersect didn’t work so well and I returned data that did not intersect with my city or was for some reason a smaller parcel. The reason being that the blobdata added exstra space to the end that was not interpreted as space but as values. I also needed to make sure my encoding was correct otherwise you might compare data that is Latitude, Longitude with data that is in the Transverse Mercator (The transverse Mercator map projection is an adaptation of the standard Mercator projection. The transverse version is widely used in national and international mapping systems around the world, including the UTM.) format. Our meshblock also needs to have the correct Mercator code assigned to it.

Once we had done the conversion, we call our stored procedure, pass our parameter to it and put the result from our sql datareader into our outputs.

Here is the code:

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;}                 
 }
}

 The last step is to link to your target database and map your columns. Run the package and see magic happen.

Apologies. Images seem to load in draft and show in preview but not in published.

Points of Interest

Make sure you have the right format between your meshblock and your parceldata.  Check out https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeometry.stgeomfromtext.aspx for some more information on the function.