Last month Microsoft announced
that the next version of Microsoft's SQL Server, code-named Katmai,
will include support for spatial data. Directions Magazine
contacted Ed Katibah, spatial program manager for SQL Server, to get
further information.
Directions Magazine (DM): Please describe Ted Kummert's keynote at the Microsoft Business Intelligence Conference on May 10 which included a live demo of a Web map client displaying data layers queried from SQL Spatial. What client was used? MapPoint? VE?
Ed Katibah (EK): The demo was shown on Virtual Earth (VE) utilizing the Virtual Earth Interactive SDK. SQL Server was accessed via a Web service, essentially creating a spatial mashup.
The full demo is a typical spatial drill down. The user navigates to a region of the U.S. and then displays the ZIP Codes for that region, color-coded according to restaurant density. The user then points to a specific ZIP Code, which then selects and displays the census blocks contained by that ZIP Code region (color-coded again by restaurant density). Finally, when the user clicks in a single census block, the actual restaurant locations are shown for that census block region.
The visualization, shown below, is part of the demo. It illustrates the census blocks found within a ZIP Code region (the ZIP Code regions were shown in the previous display in the demo). Within each census block, the number of businesses with a restaurant SIC [standard industrial code, four-digit codes established by the Office of Management and Budget and used in the classification of establishments by type of activity in which they are engaged] code were computed. This count, when divided by the area of each corresponding block region, yields a density value which appears in the display as a region shaded from white (low density) to red (highest density).
Here is the SQL Server query which was used with the above visualization:
-Return Census Block regions with the count of restaurants
-contained in each region for each block group in the zipcode the user clicked on
DECLARE @clickedPoint dbo.Geometry;
SET @clickedPoint = dbo.Geometry::STPoint(@lat, @lon, 0);
SELECT c.id,
c.shape,
c.pop as [Population],
c.shape.STArea() as [Area],
(select count(*) from dbo.business b where c.shape.STIntersects(b.shape)=1
AND substring(sic,1,2)='58') as [Restaurant Count]
FROM dbo.census c, dbo.zipcodes z
WHERE c.shape.STIntersects(z.shape)=1 and z.shape.STIntersects(@clickedPoint)=1;
DM: What was the audience reaction?
EK: Some of the feedback that we heard was "wow," "this is cool," "I'm looking forward to getting my hands on it" ...
DM: Why did this take so long (after say, Terraserver, VE, etc.)? MS has been talking about it for years.
EK: The Terraserver experience (developed as a research project) followed by Virtual Earth, along with the earlier MapPoint software, paved the way at Microsoft toward elevating spatial, especially geospatial, as more than niche domain. Our corporate experience with geospatial, coupled with the growing importance of spatially enabled databases in the marketplace had raised the profile of geospatial within SQL Server from "interesting technology" to "this is important for our customers, let's go ahead and do this."
DM: Explain: two models - a "Flat Earth" planar data type and a "Round Earth" geodetic data type. What's the difference and why is this important?
EK: The "Flat Earth" (planar) data type represents geospatial data which has been projected from its native, spherical, coordinate system into a plane (we all do agree that the Earth is not flat, right?). As such, the geometry model necessary to support planar data is much different than its counterpart for supporting the "Round Earth" geodetic model. The Flat Earth data type (GEOMETRY) will feature a traditional planar model with all of the advantages and limitations. The Round Earth data type (GEOGRAPHY) will operate on an ellipsoidal model in which the Earth is defined as a single continuous entity. The geodetic GEOGRAPHY type will not suffer from the singularities (international dateline, North and South Poles, map projection zone "edges," etc.) which causes data handling problems for the planar GEOMETRY type. Without map projections to worry about, the typical user can concentrate on the spatial problem at hand. With the growing use of GPS-like devices (making room for the expected follow-on technologies to GPS…) and the growing availability of data in geodetic coordinates (i.e. WGS84-based latitude and longitude coordinates), we felt that our geospatial offering should recognize the inherent difference between the two Earth models and not try to co-mingle them.
See also discussion on this topic from Isaac Kunen of the SQL Server Spatial engineering team at his blog.
DM: Give examples of functions or methods/functions. We understand there are about 70. Geocoding? Routing? Network and/or topology models? Support for raster? 3D?
EK: The approximately 70 methods/functions which will be available when SQL Server "Katmai" ships will represent, primarily, those spatial operations which are needed to support the Open Geospatial Consortium Simple Features for SQL, Version 1.1. We do have a handful of additional methods which will be used to support our particular geospatial implementation. The first release will concentrate primarily on baseline geospatial functionality (i.e. OGC Simple Features). We are evaluating additional spatial operations which would support "advanced" functionality but have not yet made a decision on which, if any, will ship with Katmai Spatial. Microsoft has support in the spatial marketplace by existing partners who add value to our products. Some of these operations/models may best be handled through this channel.
Here is a short list of some of the methods which we will support:
EK: We have not finalized plans on a map client at this point. This is a good opportunity for our partners to use this technology with their applications.
DM: What sort of data loading options can we expect?
EK: OGC Well Known Text (WKT), Well Known Binary (WKB) and Geography Markup Language (GML) format support will be provided. We are currently looking at support for other, standard, spatial data formats. Additionally, SQL Server ships with an ETL tool called SQL Server Integration Services (SSIS) which was formerly called DTS (Data Transformation Services). This platform provides a visual method for programming ETL workflows, useful for transforming, loading and unloading data from various sources, including spatial.
DM: Is the core technology for spatial offered without an extra fee?
EK: Decisions around pricing have not been finalized at this time.
DM: Do users who were using SQL Server for spatial data support prior to this release or those who have built user-defined functions, or use blobs have to go through a translation process to the new version? What would that look like?
EK: I would expect that users who are currently using SQL Server for spatial data support will have to go through a "translation" process of some sort.* Since there are a wide variety of products and strategies for storing spatial data in current versions of SQL Server, the methods will differ. I suspect that the predominant method to migrate from spatial data stored in versions of SQL Server 2005 and earlier will be to unload the spatial data in some standard format (WKT, WKB or GML) and then import that data into Katmai.
* There are scenarios in which this may not be necessary. For instance, if only point spatial data is being stored in an X and a Y column, along with supporting metadata, updating to Katmai Spatial could be as simple as adding a new column of type GEOMETRY. The GEOMETRY column could be populated from the X and Y columns using a point constructor method, for instance.
DM: Are there plans for this to be supported by the ESRI geodatabase in a similar manner to Oracle Spatial and DB2 Spatial Extender? In other words, will there be an SQL API like the ones ESRI built for those? ESRI and MS were working on such a solution per ESRI after ArcGIS 9.2. This would allow, as I understand it, other apps to tap directly into the spatial SQL data.
EK: I'm not sure that I am following this entirely. Similar to Oracle Spatial, SQL Server "Katmai" Spatial has been developed by the parent organization. The spatial database offerings of specific ESRI partners were developed using core, underlying ESRI technology and hence have a high degree of similarity. ESRI is a Microsoft SQL Server partner and it is reasonable to expect to see ESRI Geodatabase support for SQL Server "Katmai" Spatial near its ship date (depending of course on how the respective product ship dates align).
DM: Does SQL Server Community Technology Preview 3 (CTP3) include spatial support? I understand CTP1 did not.
EK: CTP3 will not include spatial support. Spatial will begin appearing in various future CTPs. One of the final CTPs will contain the complete offering: the GEOMETRY and the GEOGRAPHIC data types, supporting methods and spatial indexes.
Directions Magazine (DM): Please describe Ted Kummert's keynote at the Microsoft Business Intelligence Conference on May 10 which included a live demo of a Web map client displaying data layers queried from SQL Spatial. What client was used? MapPoint? VE?
Ed Katibah (EK): The demo was shown on Virtual Earth (VE) utilizing the Virtual Earth Interactive SDK. SQL Server was accessed via a Web service, essentially creating a spatial mashup.
The full demo is a typical spatial drill down. The user navigates to a region of the U.S. and then displays the ZIP Codes for that region, color-coded according to restaurant density. The user then points to a specific ZIP Code, which then selects and displays the census blocks contained by that ZIP Code region (color-coded again by restaurant density). Finally, when the user clicks in a single census block, the actual restaurant locations are shown for that census block region.
The visualization, shown below, is part of the demo. It illustrates the census blocks found within a ZIP Code region (the ZIP Code regions were shown in the previous display in the demo). Within each census block, the number of businesses with a restaurant SIC [standard industrial code, four-digit codes established by the Office of Management and Budget and used in the classification of establishments by type of activity in which they are engaged] code were computed. This count, when divided by the area of each corresponding block region, yields a density value which appears in the display as a region shaded from white (low density) to red (highest density).
Here is the SQL Server query which was used with the above visualization:
-Return Census Block regions with the count of restaurants
-contained in each region for each block group in the zipcode the user clicked on
DECLARE @clickedPoint dbo.Geometry;
SET @clickedPoint = dbo.Geometry::STPoint(@lat, @lon, 0);
SELECT c.id,
c.shape,
c.pop as [Population],
c.shape.STArea() as [Area],
(select count(*) from dbo.business b where c.shape.STIntersects(b.shape)=1
AND substring(sic,1,2)='58') as [Restaurant Count]
FROM dbo.census c, dbo.zipcodes z
WHERE c.shape.STIntersects(z.shape)=1 and z.shape.STIntersects(@clickedPoint)=1;
DM: What was the audience reaction?
EK: Some of the feedback that we heard was "wow," "this is cool," "I'm looking forward to getting my hands on it" ...
DM: Why did this take so long (after say, Terraserver, VE, etc.)? MS has been talking about it for years.
EK: The Terraserver experience (developed as a research project) followed by Virtual Earth, along with the earlier MapPoint software, paved the way at Microsoft toward elevating spatial, especially geospatial, as more than niche domain. Our corporate experience with geospatial, coupled with the growing importance of spatially enabled databases in the marketplace had raised the profile of geospatial within SQL Server from "interesting technology" to "this is important for our customers, let's go ahead and do this."
DM: Explain: two models - a "Flat Earth" planar data type and a "Round Earth" geodetic data type. What's the difference and why is this important?
EK: The "Flat Earth" (planar) data type represents geospatial data which has been projected from its native, spherical, coordinate system into a plane (we all do agree that the Earth is not flat, right?). As such, the geometry model necessary to support planar data is much different than its counterpart for supporting the "Round Earth" geodetic model. The Flat Earth data type (GEOMETRY) will feature a traditional planar model with all of the advantages and limitations. The Round Earth data type (GEOGRAPHY) will operate on an ellipsoidal model in which the Earth is defined as a single continuous entity. The geodetic GEOGRAPHY type will not suffer from the singularities (international dateline, North and South Poles, map projection zone "edges," etc.) which causes data handling problems for the planar GEOMETRY type. Without map projections to worry about, the typical user can concentrate on the spatial problem at hand. With the growing use of GPS-like devices (making room for the expected follow-on technologies to GPS…) and the growing availability of data in geodetic coordinates (i.e. WGS84-based latitude and longitude coordinates), we felt that our geospatial offering should recognize the inherent difference between the two Earth models and not try to co-mingle them.
See also discussion on this topic from Isaac Kunen of the SQL Server Spatial engineering team at his blog.
DM: Give examples of functions or methods/functions. We understand there are about 70. Geocoding? Routing? Network and/or topology models? Support for raster? 3D?
EK: The approximately 70 methods/functions which will be available when SQL Server "Katmai" ships will represent, primarily, those spatial operations which are needed to support the Open Geospatial Consortium Simple Features for SQL, Version 1.1. We do have a handful of additional methods which will be used to support our particular geospatial implementation. The first release will concentrate primarily on baseline geospatial functionality (i.e. OGC Simple Features). We are evaluating additional spatial operations which would support "advanced" functionality but have not yet made a decision on which, if any, will ship with Katmai Spatial. Microsoft has support in the spatial marketplace by existing partners who add value to our products. Some of these operations/models may best be handled through this channel.
Here is a short list of some of the methods which we will support:
- STRelate
- STDisjoint
- STIntersects
- STTouches
- STCrosses
- STWithin
- STContains
- STOverlaps
- STBuffer
- STConvexHull
- STIntersection
- STUnion
- STGeomFromText
- STPointFromText
- STLineFromText
- STPolyFromText
- STGeomFromWKB
- STPointFromWKB
- STLineFromWKB
- STPolyFromWKB
EK: We have not finalized plans on a map client at this point. This is a good opportunity for our partners to use this technology with their applications.
DM: What sort of data loading options can we expect?
EK: OGC Well Known Text (WKT), Well Known Binary (WKB) and Geography Markup Language (GML) format support will be provided. We are currently looking at support for other, standard, spatial data formats. Additionally, SQL Server ships with an ETL tool called SQL Server Integration Services (SSIS) which was formerly called DTS (Data Transformation Services). This platform provides a visual method for programming ETL workflows, useful for transforming, loading and unloading data from various sources, including spatial.
DM: Is the core technology for spatial offered without an extra fee?
EK: Decisions around pricing have not been finalized at this time.
DM: Do users who were using SQL Server for spatial data support prior to this release or those who have built user-defined functions, or use blobs have to go through a translation process to the new version? What would that look like?
EK: I would expect that users who are currently using SQL Server for spatial data support will have to go through a "translation" process of some sort.* Since there are a wide variety of products and strategies for storing spatial data in current versions of SQL Server, the methods will differ. I suspect that the predominant method to migrate from spatial data stored in versions of SQL Server 2005 and earlier will be to unload the spatial data in some standard format (WKT, WKB or GML) and then import that data into Katmai.
* There are scenarios in which this may not be necessary. For instance, if only point spatial data is being stored in an X and a Y column, along with supporting metadata, updating to Katmai Spatial could be as simple as adding a new column of type GEOMETRY. The GEOMETRY column could be populated from the X and Y columns using a point constructor method, for instance.
DM: Are there plans for this to be supported by the ESRI geodatabase in a similar manner to Oracle Spatial and DB2 Spatial Extender? In other words, will there be an SQL API like the ones ESRI built for those? ESRI and MS were working on such a solution per ESRI after ArcGIS 9.2. This would allow, as I understand it, other apps to tap directly into the spatial SQL data.
EK: I'm not sure that I am following this entirely. Similar to Oracle Spatial, SQL Server "Katmai" Spatial has been developed by the parent organization. The spatial database offerings of specific ESRI partners were developed using core, underlying ESRI technology and hence have a high degree of similarity. ESRI is a Microsoft SQL Server partner and it is reasonable to expect to see ESRI Geodatabase support for SQL Server "Katmai" Spatial near its ship date (depending of course on how the respective product ship dates align).
DM: Does SQL Server Community Technology Preview 3 (CTP3) include spatial support? I understand CTP1 did not.
EK: CTP3 will not include spatial support. Spatial will begin appearing in various future CTPs. One of the final CTPs will contain the complete offering: the GEOMETRY and the GEOGRAPHIC data types, supporting methods and spatial indexes.
Postscript: On behalf of a reader Directions Magazine contacted ESRI regarding support for Microsoft's spatial data types in SDE Server. ESRI responded that it "is working closely with Microsoft and plans to support the new spatial type as a standard part of ArcGIS clients via direct connect and ArcGIS Server via the ArcSDE gateway. This capability will be included in ArcGIS 9.3."
From Our Homepage
Saying Farewell to an Amazing Journey
Communicating with Maps
Is There a GIS Career Ladder?
What does it mean to be geospatially smart? Series
Ways Real Estate and Property Developers Utilize Melissa GeoData for Data-Driven Decisions
Unlocking Value From Daily Satellite Imagery and Insights
Maximizing the Value of Your Address Data with Geo Addressing
How Indoor Mapping Enhances the Security of Smart Buildings
Look Ahead: AI, Location Intelligence and Efficiency
Collaboration Takes on Sea Level Rise & Dynamic Technology Environments
Brownies for Brownfields
Has Everything Been Mapped Already?
How Is Data Literacy Changing in an Artificial Intelligence Landscape
Portfolios for GIS Professionals: More Than Just Maps
How to Create a Distance Matrix in QGIS - A Step-by-Step Guide
7 Ideas for Bringing GIS into the K-12 Classroom
The Geography of Movement