SSIS with Spatial / Geospatial Data

Mind's biggest strength is it knows no barriers / boundaries and this is also mind's biggest weakness. This sounds highly philosophical, so let's go to something technical with the same philosophy. One of the area of data which knows no boundaries is Spatial data, as any point on this earth across geographical boundaries can be plotted with pin-point precision.

Like a salesman, when it comes to something new, my mind immediately starts selling the concept of how SSIS can be sold even in this side of technology and business. Before data lands down to SQL Server in geography or geometry data types, this data can be found in different formats which different applications generate or use. For example, Well Known Text ( WKT ), Well Known Binary ( WKB ), Keyhole Markup Language ( KML ), Geography Markup Language ( GML ), Data Geocoded in the form of latitudes and longitudes are all parent compounds of geography or geometry datatypes, which we eventually can convert to store or use in SQL Server. Also there can be a need for mutual exchange of data from SQL Server to other formats.

In these cases, apart from the CLR functions that can be used with spatial data types, SSIS has the possibility of playing a major role in crafting the conversions from one data type to another. Google maps still use KML and Microsoft Virtual Earth uses GML to the best of my knowledge. Think of the possibility where one needs a conversion directly from GML to KML without staging in SQL Server. In this case SSIS and SQL XML can definitely play a big role. SSRS has also got great support with maps which also needs spatial data to fuel itself.

And those who are curious to know how interesting can be exploring and working with spatial applications, just explore the GeoSpatial Data Generator application designed by Mike Ormond.