FDO RFC 12 – Implement Support for a SQLServer Spatial FDO Provider

This page contains a request for comments document (RFC) for the FDO Open Source project. More FDO RFCs can be found on the RFCs page.

Status

RFC Template Version	(1.0)
Submission Date	November 23, 2007
Last Modified	Greg Boone Timestamp
Author	Greg Boone
RFC Status	Draft
Implementation Status	Under Development
Proposed Milestone	3.3.0.0
Assigned PSC guide(s)	Greg Boone
Voting History
+1
+0
-0
-1

Overview

The purpose of this RFC is to outline how an FDO Provider for SQLServer 2008 will be designed, developed and released to the OSGeo Community.

Up until the anticipated release of Microsoft SQL Server 2008, there has been no native spatial support in the Microsoft SQL Server framework. Current FDO Provider support for SQL Server, such as the SL-King FDO Provider for SQL Server, uses alternate spatial support, such as Geospatial Extensions. However, with Microsoft’s intention to natively support spatial data in SQL Server 2008, an opportunity presents itself for the OSGeo FDO community to undertake to develop and release an Open Source Provider that will take advantage of this functionality.

It is expected that most of the traditional Geospatial vendors will endorse and support this new release of Microsoft SQL Server (just like the same vendors have, during the last 6-7 years, added support for the Oracle Spatial database). For example, it is apparent that Microsoft has engaged with Safe Software for support by FME during the public beta cycle in order for customers to seamlessly move existing data into SQL Server 2008 Spatial.

The objective of the efforts outlined in this document will be the release of an FDO Provider for SQLServer Spatial in the mid-2008 timeframe when it is anticipated that Microsoft will launch their new server products.

A key objective of the efforts surround the development of the provider will be to get Microsoft’s endorsement of the FDO technology as one of the key access methods to their SQL Server 2008 Spatial capability. Getting a first movers advantage and the support of Microsoft’s marketing machine makes this a time critical activity.

Proposed Solution

A new OSGeo FDO Provider will be developed to support the upcoming SQL Server 2008 Spatial RDBMS. Microsoft is adding a spatial data type and spatial indexing to this release. This provider will be targeted to work with MapGuide Open Source 1.2.0 and 2.0.0. SQL Server 2008 will not be released until at least mid-2008, therefore delivery of an official release will have to be targeted for that timeframe. The strategy is to provide a preview version that works with SQL Server 2008 beta and then a full release version at some time after SQL Server 2008 is released.

Strategy

This provider be developed as a new FDO provider. It will not be backwards compatible with data stores created with any previously released FDO Providers for SQL Server. The new provider will be based on the FDO Generic RDBMS framework. The first phase to be completed by February 2008 so that it can demonstrated it as part of a general announcement of the intent to support Microsoft’s new SQL Server release. A secondary phase would include more testing for the final v1 release in June 2008 or later (to coincide with Microsoft’s release of SQL Server 2008).

Description

Name: OSGeo.SQLServerSpatial.3.3
Display Name: OSGeo FDO Provider for SQL Server Spatial
Description: Read/Write access to feature data in a MS SQL Server spatially enabled data store. Supports geospatial and non-geospatial data and schema.
Is Managed: False
Supported Versions: 3.2.3.0; 3.3.0.0
Feature Data Objects Version: 3.2.3.0; 3.3.0.0
Library Name: SQLServerSpatialProvider.dll

Capabilities

• Command Capabilities
  • Commands
    • Select
    • Select Aggregates
    • Insert
    • Delete
    • Update
    • Describe Schema
    • Apply Schema
    • Destroy Schema
    • Describe Schema Mapping
    • Activate Spatial Context
    • Create Spatial Context
    • Destroy Spatial Context
    • Get Spatial Contexts
    • Create DataStore
    • Destroy DataStore
    • List DataStores
    • SQL Command
  • Parameters - Unsupported
  • Select Distinct - Supported
  • Select Expressions - Supported
  • Select Functions - Supported
  • Select Grouping - Supported
  • Select Ordering - Supported
  • Timeout - Unsupported

• Connection Capabilities
  • Configuration - Unsupported
  • Flush - Unsupported
  • Locking - Unsupported
  • Long Transactions - Unsupported
  • Multiple Spatial Contexts - Supported
  • SQL - Supported
  • Timeout - Unsupported
  • Transactions - Supported

• Expression Capabilities
  • Aggregate Functions
    • Count
    • Avg
    • Max
    • Min
    • Sum
    • Median
    • Stddev
  • Conversion Functions
    • NullValue
    • ToDate
    • ToDouble
    • ToFloat
    • !ToInt32
    • !ToInt64
    • ToString
  • Date Functions
    • AddMonths
    • CurrentDate
    • Extract
    • MonthsBetween
  • Mathematical Functions
    • Abs
    • Acos
    • Asin
    • Atan
    • Atan2
    • Cos
    • Exp
    • Ln
    • Log
    • Mod
    • Power
    • Remainder
    • Sin
    • Sqrt
    • Tan
  • Numeric Functions
    • Ceil
    • Floor
    • Round
    • Sign
    • Trunc
  • String Functions
    • Concat
    • Lower
    • Upper
    • Instr
    • Length
    • Lpad
    • Ltrim
    • Rpad
    • Rtrim
    • Soundex
    • Substr
    • Translate
    • Trim
  • Geometric Functions
    • Area2d
    • Length2d

• Filter Capabilities
  • Spatial Operations
    • Envelope Intersects
    • Intersects
    • Inside
    • Equals
    • Disjoint
    • Touches
    • Overlaps
    • Crosses
    • Within
    • Contains
  • Geodesic Distance - Supported
  • Non Literal Geometric Operations- Unsupported

• Schema Capabilities
  • Class Types
    • Class
    • Feature Class
  • Data Types
    • Boolean
    • Byte
    • DateTime
    • Decimal
    • Double
    • Int16
    • Int32
    • Int64
    • Single
    • String
  • Supported AutoGenerated Types
    • Int64
  • Supported Identity Property Types
    • Boolean
    • Byte
    • DateTime
    • Decimal
    • Double
    • Int16
    • Int32
    • Int64
    • Single
    • String
  • Association Properties - Supported
  • AutoId Generation - Supported
  • Composite Id - Supported
  • Composite Unique Value Constraints - Supported
  • DataStore Scope Unique Id Generation - Supported
  • Default Value - Supported
  • Exclusive Value Range Constraints - Supported
  • Inclusive Value Range Constraints - Supported
  • Inheritance - Supported
  • Multiple Schemas - Supported
  • Network Model - Unsupported
  • Null Value Constraints - Supported
  • Object Properties - Supported
  • Schema Modification - Supported
  • Schema Overrides - Supported
  • Unique Value Constraints - Supported
  • Value Constraints List - Supported

• Geometry Capabilities
  • Dimensionalities
    • XY
  • Geometry Types
    • Point
    • Line String
    • Polygon
    • Multi Point
    • Multi Line String
    • Multi Polygon
  • Geometry Component Types
    • Linear Ring
    • Line String Segment
    • Ring

• Topology Capabilities
  • Topological Hierarchy - Unsupported
  • Topology - Unsupported

• Raster Capabilities
  • Raster - Unsupported
  • Stitching - Unsupported
  • Subsampling - Unsupported

• Read/Write.

• Geometry types as supported by SQL Server 2008. The provider will not support Z and M dimensionalities.

• Add support for the SQLServer 2008 Geography type. This type will be supported for geodetic coordinate systems. [Details to be determined.]

• Spatial operators as supported by SQL Server 2008. These are all of the OGC simple feature spec spatial operators.

• Spatial indexing based on Microsoft SQL Server 2008 geometry type. The provider will create spatial indexes automatically for geometry columns. Default spatial index parameters will be used. During implementation, we will determine reasonable defaults. Overrides will be supported via physical schema override capabilities in the API.

• No long transaction or persistent locking support.

• Add support for existing schema (a.k.a. foreign schema).

• Add support for creating and modifying schema with existing schema. This could be done in the second phase. This is new work that existing GenericRdbms based providers do not support yet, but this would be a good opportunity to add this. Users would find this very useful.

Implementation Plan

Here is a starting list of high level changes that are need to do to produce an OSGeo SQL Server 2008 provider.

• Develop a preview to be available March, with a more mature version for a June release to coincide with Microsoft’s formal release.

• Use the new spatial data type and spatial indexing scheme. It supports OGC simple feature spec geometry.

• There is no coordinate system library for non-geodetic coordinate system definitions, but the spatial data type supports saving an SRID. SQL Server doesn’t use the SRID itself, just saves and returns it. This is the same as what OSGeo MySQL Provider implements.

• Map FDO geometry to the new SQL Server spatial data type. If the FDO geometry has spatial context that includes a geodetic coordinate system, then the FDO property will map to a geography type. Otherwise, the property will map to a geometry type. Also, the providers physical schema overrides should allow the user to specify one or the other type. The Geography type may require an SRID to be specified. (TBD)

• Create a spatial index using the new spatial index type. Initially use the planar geometry type, not the geography type. The geography type is not fully implemented yet in the SQLServer CTP, but the goal is to support it as well. The geography type would be used for a 3d whole-earth model as used by Virtual Earth.

• Map spatial queries to the new spatial query functions. These are based on OGC simple feature spec as well so should be straight forward to map.

• The spatial data type has functions to set/get the geometry via OGC WKB and WKT formats. Z and M are handled with separate functions.

• Modifications to Select, Insert, Update of geometry using the new functions.

• Support existing schema. Include reverse engineering of existing schema, which previous releases of SQL Server Providers did not implement completely.

• Support Apply Schema to create FDO-enabled schema as currently done.

• Support Apply Schema to existing schema in data stores. This is new functionality and should be done in the second phase.

• Make sure the new provider is compatible with FDO 3.2.3 and 3.3.0.