Working with Spatial Data Part II – Plotting Shapes and Storing Geocoded Data
4) Edit the script of this script transform, and add the code as shown in the below screenshot in the area bordered by the red box. As we know that Script task will not allow use of user variables except post execute event, so we created a local variable at the class level. Then we create the command text for updating each row, and finally just before the end of script execution when the postexecute event is fired, we assign this command string value to the user variable we created at the package level.
STGeomFromText is a static function of Geography data type, so we have used the syntax of geography::STGeomFromText which can be perceived as “class.function()”. It expects a “Point” spatial data object with geographical latitudes and longitudes as input values, which we have used as an input parameter of STGeomFromText function. We already have the latitude and longitude, and SRID for Well Known Text (WKT) is referenced by a constant value of “4326”.
There are other static functions which can be used for the same purpose, but the above function is the most generic one that can accomodate any kind of spatial data object such as Point, Linestring, Polygon etc. For those interested in learning more on on WKT and Spatial Data Objects there is a good explanation on each of these topics available on MSDN BOL.
After adding the code, build the script.
5) Switch back to Control Flow, and add an “Execute SQL Task” after the Data Flow task. Configure this task to use its command text from the user variable we created in step 2.
6) If the modifications have been done as above, the package should execute successfully and update the values in the “Location” field of address table as shown in the below screenshot.
Open SQL Server Management Studio (SSMS), and select all the records from the “Address” table. You should see three tabs: Results, Spatial Results and Messages. Spatial Results is a new tab available in SSMS 2008, which is a part of spatial data support. Browse to this tab, and you should see a figure and a pane on your right hand side.
SSMS is intelligent enough to detect the only spatial column in our resultset, and has selected it in the spatial column selection. There are four different types of projection available for viewing data of the resultset on the projection. I have selected “Bonne” projection due to it’s resemblance to the world map. After selecting this you will see dots on the diagram which are the points we created on the geography.
Hover your mouse over any specific point to see the data in the tooltip as shown in the below screenshot. We had 12 records in our original dataset, and in the below screenshot I have marked out all these points. You can also experiment with different spatial data objects such as polygons and linestrings to see how the output could be projected differently
Continuing from the first part of this article series, we modified the Address table and the package. Firstly we modified Address table to include a field named “Location” of geography data type. Then we modified our SSIS package to create a point spatial data object using latitudes and longitudes as input values and update the location field with the same. Finally we took a quick view of spatial data support in SSMS by browsing this data visually in the spatial results tab.
After the above exercise, you should be able to answer three questions: What is the geography data type, how can spatial data objects be created and assigned to a field of the geography data type, and what kind of support is available for spatial data in SSMS 2008.
Pages: 1 2