Business Intelligence with SQL Server 2008 Geometry Data Type.

-

I am currently working on a project which is automating a business intelligence process base upon images and image recognition. The process is as follows:

  • Take a photography
  • Put the photography through the image recognition software and record the recognition data.
  • Process the recognition data into meaningful business metrics
  • Produce a report on metrics

The part of the process, from above, that I am helping with is processing the recognition data (the hit data) into meaningful business data. This process simply takes the hit data (which is a point), then creates some square blocks, which I use to group the hits data and perform some metric operations. The metrics currently use data about the area and the density of hits within the area. To help process this data I decided to make use of the SQL Server 2008 spatial type: Geometry.

Firstly I would like to point out that I didn’t have to use the geometry type. As I am currently dealing with square areas, I can group or locate all the hits that fall within area by using the following filter clause: HitY between MinY and MaxY and HitX between MinX and MaxX. However by reading the filter alone can cause misunderstanding on what the filter is suppose to be doing. As I am using the geometry data type that filter is replaced by something which does state what the filter is doing, like so: area.STIntersects(HitPoint) = 1 with the added bonus of less code being written and read as well as less chance of a bug being created.

Another point I would to make is that by using the geometry type my code is adaptable to changes. Currently our process is working to square areas and in the future that might change. Using the standard SQL filters that would mean all the filter clauses of the data would have to be updated. Also I would need to be more sophisticated, which could take a longer development and testing cycle which could increase the risk of issues. With the use of the geometry type, only the way that the area polygon is created would need to be updated which will help in reducing the development and testing cycle which interns reduces the risk of issues.

Another benefit of having some of the data stored as the geometry spatial data type is that I was able to evaluate or debug the steps of the process far easier. This is due to the way that SQL Server Management Studio display spatial data as shown below.

SpatialGrid

Comments

Post a Comment

Popular posts from this blog

SSIS File handle leak in For Each Loop

-
I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Update: I put this on the Microsoft forums and got a intresting answer back See Here ; There seems to be a memory leak with foreach loop which should be fixed in sp1.
Read more

Azure SQL Server IaaS SSIS: A required privilege is not held by the client

-
Image
As mention in a previous post, we are using Azure SQL Server IaaS to help our transition to the cloud. The main reason for this is how some of our SSIS projects, which we inherited, were created. Some were using SSIS to manage the transactions, which uses MSDTC not supported in Azure. Use extra .Net libraries to read JSON and office files; external programs to do file operations like decompression. Migration timelines and priorities we didn't have time to refactor these projects. While ago we started to deploy our SSIS projects to our Azure SQL Server IaaS virtual machine, a Windows 2019 server with SQL Server 2019, to get the following error: SSIS Deployment Error 6522 "A required privilege is not held by the client". We able to resolve our problem by using the steps we found in the following blog posts: System.ComponentModel.Win32Exception: A required privilege is not held by the client while Deploying SSIS Project | Microsoft Docs . Fixing SQL 2012 SSIS Deployment Err
Read more

Challenges of move SQL Server data to Microsoft Azure SQL Server PaaS

-
This post covers one of the challenges we face when moving our infrastructure from on-prem to Microsoft Azure: Moving SQL Server data from on-premise to Microsoft SQL Server Paas. Moving our data was a challenge for the following reasons: Our company has tight controls and rules about what software/connectivity is allowed on our network The database that we need to migrate after compression was over 100GB in size. No information about how long other migration processes would take. As needed to ensure our migration was complete before the next Monday on the weekend of our migration. For most people migrating their data, the best option is to use Azure Database Migration Service , especially when transferring a database size that we were. We couldn't use the Azure Database Migration Service for the following reasons: Azure Database Migration Service requires software installed on a server within our network Connections to both databases
Read more