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