SQL Server Spatial FDO Provider - Installation Notes

Installing SQL Server 2008

For the install, make sure that you select the option to allow for both Windows and SQL Server Authentication.

Once SQL Server 2008 is installed, run SQL Server Configuration Manager (Start->Programs->SQL 2008->Config tools). Select 'Protocols for MSSQLSERVER' on the left (under SQL Server Network Configuration) and then enable the 'TCP/IP' protocol. You will have to restart the server after this, which you can do with this tool as well. Choose SQL Server Services on the left and restart SQL Server.

Setting up the provider.

At this time, until we build an actual install tool, what we’re doing to set this up for MapGuide Open Source 2.0 and other clients is to edit the current FDO providers.xml file and add a <FeatureProvider> entry for SQL Server Spatial. The first step is to take the new SQL Server Spatial Provider dlls and drop them into the Fdo Bin directory of your client application (e.g. C:\Program Files\Autodesk\!MapGuideEnterprise2009\Server\Bin\Fdo). The necessary dlls are part of the FDO sdk binary download.

Once that is done, update the providers.xml file in those directories by adding the entry for the SQL Server Spatial provider.

Here is the <FeatureProvider> section to add for the SQL Server Spatial provider:

<FeatureProvider>
  <Name>OSGeo.SQLServerSpatial.3.3</Name> 
  <DisplayName>OSGeo FDO Provider for SQL Server Spatial</DisplayName> 
  <Description>Read/Write access to feature data in a MS SQL Server spatially enabled data store. Supports geospatial and non-geospatial data and schema.</Description> 
  <IsManaged>False</IsManaged> 
  <Version>3.3.0.0</Version> 
  <FeatureDataObjectsVersion>3.3.0.0</FeatureDataObjectsVersion> 
  <LibraryPath>SQLServerSpatialProvider.dll</LibraryPath> 
</FeatureProvider>

Now, after installing the new dll's as described above, you can run MG 2009 or other clients based on FDO 3.3. Connect to 'localhost' and use the sa user and pwd that you defined when installing SQL 2008 or other SQL Server user account that you created.

Coordinate System Handling

Once you create a data store, you create and apply a schema to it. The schema defines the table and columns into which you will put data. For FDO geometry properties, there are two possible SQL Server spatial column types: geometry and geography. The geography type is used for geodetic (lat/long) coordinate systems and the geometry type is used for non-geodetic coordinate systems. For both, a spatial index with default parameters is created automatically.

SQL Server 2008 includes a catalog of geodetic coordinate systems, but not non-geodetic coordinate systems. Both geometry and geography column types save SRID values, but only geography type columns reference an entry in the catalog, and in this case the SRID numbers are EPSG numbers. Some applications will require that the provider returns a valid WKT for the corresponding coordinate system. Since for some coordinate systems, this information is not directly available from the SQL Server 2008 catalog, a mechanism was added to by-pass this problem.

To translate the coordinate to an SRID to use in SQL Server, the provider uses a translation table in a file called ExtendedCoordSys.txt, which resides in the com sub-folder of the FDO installation directory (usually FDO\bin\com\ExtendedCoordSys.txt). If the coordinate systems are in the file, the coordinate system will be recognized and handled by the FDO Provider for SQL Server Spatial.

Note: If the coordinate system has an EPSG code, use that code as the SRID. Otherwise, use an SRID that is not an EPSG code.

See the info on ​ExtendedCoordSys.txt.