SQL Server Integration Services (SSIS)
Many organisations are already utilising Microsoft SQL Server to manage and maintain their corporate systems and data. It makes sense to leverage the in-built functionality available. SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and Visual Studio provides us with a suite of tools to clean, standardise and normalise data into a ‘Single Source of Truth’ within a secure SQL database, as well as providing data integration between systems and databases, while SSRS provides us with the means to easily view and make sense of the data in many different formats and using different visualisation techniques.
SSIS Benefits
‘Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. Use Integration Services to solve complex business problems by copying or downloading files, loading data warehouses, cleansing and mining data, and managing SQL Server objects and data.
Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.
Integration Services includes a rich set of built-in tasks and transformations, graphical tools for building packages, and the Integration Services Catalog database, where you store, run, and manage packages’.
Process
Organisations have been collecting, managing, sharing, and maintaining large amounts of data - since forever, and now, it’s relatively easy to incorporate spatial data into the mix to provide the additional benefit of location intelligence. Microsoft SQL Server handles spatial data just as easily as any other data type. The benefits of using SQL Server to manage your spatial data includes:
All Spatial Data are stored in the one point of truth, safe and secure within a database
Dataflow Scripting provides the ability to perform complex data transformations very simply
Dataflow Tasks to easily identify the data-source and the data-destination, SQL scripts to replace current MapBasic scripts, set-up data viewer to view the data flowing through the SQL SSIS scripts – helps with checking and de-bugging.
Package Configuration – ensure production quality packages with good deployment practices.
Control Flow Tasks – helps to define workflows
Web-service Tasks – e.g. ability for the organisation to consume publicly available data e.g. demographics data to enable us to perform spatial analysis with our corporate data e.g. determining the best area to build a playground, determining where Council requires wider footpaths to accommodate wheelchairs use, etc.
Ability to set up Checkpoints in each step of the SSIS Workflow to help maintain consistency of the data, especially useful when managing large quantities of data.
Logging – Error Monitoring – able to record and gather information about the error to enable us to easily identify the problem and fix it.
Other Benefits to the Organisation
Other benefits that will be gained through implementing this model will include the on-going improvements to the attribute and spatial data. It will become easier to identify areas where attribute data and spatial data are not matching. A SQL script providing the relevant error information is the first step to being able to fix any issue. Automated scripts can be set-up to create email alerts that will inform us immediately if a package or transformation has failed.
It is also true, that when the organisation starts using the same ‘one-source of truth’, any errors identified can be rectified at the source, and the correction will automatically flow through to other applications. By definition, this model promotes more accurate data.
What is Spatial Data in SQL Server?
Spatial data relates to the points, lines, and polygons that is a geographic representation of a feature on the Earth. In our examples, we have chosen Line (roads, footpaths, etc), Polygon (property, buffers, etc) and Point data (defects, etc). Spatial data is stored as coordinates and topology which allows the data to be mapped. SQL Server supports geometry data and geography data. I usually use geometry. The difference being:
Geometry – Stores the X and Y coordinates that represent lines, points, or polygons.
Geography – Stores the latitude and longitude coordinates that represent lines, points or polygons.
Spatial Data
If your spatial data is already being managed and maintained within a database / geodatabase, then you are half way there. You only need to take a few more steps to link your spatial data with attribute data currently being managed within your other corporate systems, to create some very valuable spatially enabled datasets.
How Do You Get Spatial Data into SQL Server?
There are a few tools out there to help you transform and import your Spatial Data into MS SQL Server. If you are using MapInfo TAB files, then MapInfo Easy Loader is the best tool for you.
Here are some more tools:
FullConvert https://www.fullconvert.com/
Easy Loader transforms the MapInfo TAB files and imports the data into the MS SQL Server database. (Actually, it doesn’t have to be MS SQL Server, Easy Loader can transform MapInfo TAB files and import into any remote database). Easy Loader stores the spatial information present in the TAB files and when it’s been uploaded into SQL, makes it available for viewing and analysing in a GIS, like MapInfo or QGIS, etc.
Querying the data in SQL
Structured Query Language (SQL) is the most popular computer language used to create, modify and query databases. SQL is the standard language for relational database management systems and big data analytics and is used for updating, deleting, and requesting information from databases. Querying spatial data is not that much different to querying non-spatial data and is used by many organisations to manage big data solutions.
This is an example of a simple select query:
It is easy to perform powerful spatial and attribute queries with Spatial SQL, such as buffer, containment, intersection, distance, nearest neighbour, routing, etc. A single Spatial SQL query can achieve as much geo-processing as a couple of pages of MapBasic scripting, and queries that need to run regularly could be incorporated into Stored Procedures that are scheduled to run automatically at specific times.
Ideally, attribute data will be managed, maintained and updated in a bespoke corporate system. For example, road data will be managed in the Councils Asset Management system. A unique Spatial ID will link the Road Asset to the corresponding spatial object in SQL. Property data will be managed within the organisation’s Property and Finance system and linked with the spatial data via the Assessment Number, or Parcel Number, or some other unique identifier that is present within both datasets.
Here are few simple SQL Server Spatial Queries I’ve found useful
Example 1: Simple Buffer query
Example 2: This query combines STBuffer and STContains
This query buffers a property parcel and provides information relating to adjoining properties that are contained within the buffer.
Example 3: Spatial Joins - This spatial query interrogates the Property and Hazard tables and returns Property data where a Trip Hazard has been reported using STContains.
Example 4: This query interrogates the Property and Hazard tables and returns Property data intersecting any reported Tree Root Trip Hazards using STIntersects.
Example 5: STTouches This STTouches query returns the values of all Properties adjacent to pcl_no 10056.
Example 6: I use this spatial query a lot when linking attribute data with property data that needs to be displayed as a Point object rather than a polygon.
Example 7: STArea() This query calculates the Area of a polygon. In this case, the query is calculating the area of each Property belonging to Council, and intersecting the 10 Year Flood plain area.
Example 8: Examples showing the difference between a simple select query, and using the STBoundary() query.
Example 9: STStartPoint() and STEndPoint(). Every line showing its Start and End Point.
Example 10: STSrid. Each spatial instance has a spatial reference identifier (SRID). The SRID corresponds to a spatial reference system based on the specific ellipsoid used for either flat-earth mapping or round-earth mapping. If your SQL Spatial Query joining two spatial tables is to work correctly, then the tables must be in the same coordinate system. To easily check the coordinate system, we can use this STSrid Query to extract the coordinate information.
Example 11: It’s easy to incorporate mathematical calculations into a Spatial Query – count, sum, avg, etc.
This query returns the total number of Tree Root Hazards that intersects with commercial properties.
Viewing / Editing MS SQL Server Spatial Tables
Viewing and editing your spatial data within a GIS is easy. Connect to the table via your GIS (MapInfo, QGIS, etc)
Editing Data in the GIS
You use the same process you would normally use to edit the geometry and / or the attribute data. Once you hit save, the edits are saved back to the SQL table.
SQL Server – Data Integration
Your data (including spatial data) is now readily accessible to allow you to join datasets, build queries, create visualisations, and easily serve out to the organisation.
Keep an eye open for our next Blog for Part 2 - where we’ll be looking at SQL Reporting Services (SSRS) and PowerBI to visualise your spatial data.
If you have enjoyed this article, please ‘like’ the page, or even better drop us a line. We’re always pleased to hear your thoughts. Thanks!
The datasets used for this article have been obtained through creative commons licensing
Other datasets such as the defect, hazard and attribute data was created solely for the purpose of demonstration for this article.
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ www.iamdata.solutions/subscribe
If you'd like to discuss how we might work together to build a Spatial Microsoft Database for your GIS data, then please email Jill at ➡️iamdata@internode.on.net
You can read all our other Newsletters and Blogs here:➡️ www.iamdata.solutions/blog
You may also be interested in our Projects Page. Here are a few ways we have helped Councils in the past:➡️ www.iamdata.solutions/past-projects
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and like to hear about it too! Please email Jill at: ➡️ iamdata@internode.on.net with your suggestions.
Opmerkingen