How to enable Microsoft SQL Server 2005 datasource in GeoServer

Contents

Related pages



Introduction

Microsoft SQL Server 2005 is not natively supported by sqlserver plug-in for GeoServer (http://docs.geoserver.org/2.0.x/en/user/data/sqlserver.html) due to the lack of spatial features in the database. However, I have managed to enable spatial functionality in various editions of Microsoft SQL Server 2005. The solution is based on open-source extensions and won't incur any additional costs. At the time of writing this page, proposed solution has proved its viability and has been successful for various applications:

  • Simple features configured using GeoServer GUI interface
  • Complex features without spatial properies
  • Spatially enabled complex features (tested with POINT geometry object)

Prerequisites

I have tested the solution on the following configuration:

Supported editions of Microsoft SQL Server 2005

The solutions has been tested on the following editions of Microsoft SQL Server 2005:

  • Microsoft SQL Server 2005 Express Edition (bundled with Microsoft Visual Studio 2008 or installed separately)
  • Microsoft SQL Server 2005 Developer Edition, v9.0.4035

The solution hasn't been tested on Standard Edition and Enterprise Edition of Microsoft SQL Server 2005, but it should work too since the DBMS engine is functionally the same.

Configuring Microsoft SQL Server 2005

SQL Server Management Studio

SQL Server Management Studio is a native database management environment for Microsoft SQL Server. If you have any edition of Microsoft SQL Server 2005 other than Express Edition installed locally on your machine, SQL Server Management Studio should also be installed by default. If that option has been turned off during the installation you can add it manually via the Control Panel → Add or Remove Programs. Otherwise, you can install SQL Server Management Studio separately as part of "Client Tools" component of Microsoft SQL Server 2005 installation bundle or you may choose the SQL Server Management Studio Express, which should suit you needs for most scenarios.

SQL Server Management Studio will be required for many database configuration and management tasks, so it is highly recommended to install it before proceeding further with these instructions. It may be obtained from Microsoft website directly: http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

Note:
If you already have Microsoft SQL Server 2005 Express Edition installed on your machine, if might be tricky to install client tools, which are bundled with any other edition of Microsoft SQL Server 2005. In this case it's recommended to install SQL Server Management Studio Express, which is available as a stand-alone application from Microsoft website.

Network configuration

For both cases when Microsoft SQL Server 2005 is deployed locally or remotely you have to make sure that TCP/IP protocol is enabled and properly configured for your database instance.

Open "SQL Server Configuration Manager" from Programs → Microsoft SQL Server 2005 → Configuration Tools → SQL Server Configuration Manager. Expand SQL Server Configuration Manager → SQL Server 2005 Network Configuration → Protocols for MSSQLServer branch and double-click on TCP/IP protocol in the right pane.

Recommended setting for TCP/IP protocol are:

  • Protocol tab
    • General
      • Enabled: Yes
      • Keep Alive: 30000 (default)
      • Listen All: Yes (default)
      • No Delay: No (default)
  • IP Addresses tab
    • IP1 (as many as the number of ethernet interfaces you have)
      • Active: Yes
      • Enabled: Yes
      • IP Address: 127.0.0.1
      • TCP Dynamic Ports: <leave empty>
      • TCP Port: 1433 (standard Microsoft SQL Server port)
    • Note: configure at least the localhost (127.0.0.1) and the interface, which is accessible externally.
    • IPAll
      • TCP Dynamic Ports: <leave empty>
      • TCP Port: 1433 (standard Microsoft SQL Server port)

Also make sure that "Shared Memory" protocol is enabled.

After these changes you will be required to restart MSSQLSERVER service.

Services

After you have made any changes to network configuration you are required to restart SQL Server service.

Open "SQL Server Configuration Manager" from Programs → Microsoft SQL Server 2005 → Configuration Tools → SQL Server Configuration Manager. Expand SQL Server Configuration Manager → SQL Server 2005 Services branch and restart "SQL Server (INSTANCE_NAME)" service.

Dual-mode authentication

Since GeoServer may only use SQL Server authentication you are required to enable dual-mode authentication in your SQL Server instance, which is disabled by default. Using Microsoft SQL Server Management Studio connect to the database instance using your Windows credentials, right-click on the SQL Server instance in the "Object Explorer" pane and choose "Properties". On the "Security" page of the "Server Properties" dialog make sure that "SQL Server and Windows Authentication mode" is selected.

Assuming that you have already created/restored the database on your SQL Server instance, you are required to create a login. Using Microsoft SQL Server Management Studio right-click on the Database Instance → Security → Logins branch and choose "New Login..." In the displayed dialog box enter the following properties:

  • General
    • Login name: <choose a name>
    • SQL Server authentication:
      • Password: <set a password>
      • Confirm password: <confirm password>
    • Default database: <select your database>
  • Server Roles
    • Select the following role(s):
      • public
  • User Mapping
    • Select and tick your database in the "Users mapped to this login" table and set the following properties:
      • Database: YOUR_DATABASE
      • User: LOGIN_NAME
      • Default Schema: dbo

        LoginPropertiesUserMapping.png

    • Tick the following roles in the "Database role membership for: YOUR_DATABASE" table:
      • db_owner
      • public
  • Status
    • Permission to connect to database engine: Grant
    • Login: Enabled

Note:
These security setting are provided for reference only. Configure the login as required according to your security policy.

Enabling CLR

Since Microsoft SQL Server 2005 has no native support for spatial features it is required to install a third-party extension library, which encapsulates all necessary spatial data types, functions and stored procedures. CLR option is maintained on a server level, so it has to be enabled once.

To enable CLR connect to the server with a user, which has sysadmin privileges and execute the following SQL batch:

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

MsSqlSpatial extension

Installing

The proposed solution is based on MsSqlSpatial - Spatial Extensions for SQL Server 2005, which implements OGC SFS standards. MsSqlSpatial is an open-source CLR library, which is available for free from http://www.codeplex.com/MsSqlSpatial/. I have been using MsSqlSpatial v0.1.1 Production, which worked well for all my test cases. I didn't have a chance to test the trunk version of the library, but I believe it should okay too.

If your SQL Server 2005 hosting machine doesn't have .NET 2 installed, download it and run it logged with an administrator account:

Once downloaded unzip the contents of the archive into %SQL_SERVER_HOME%\90\Tools\MsSqlSpatial\ (usually C:\Program Files\Microsoft SQL Server\90\Tools\MsSqlSpatial\). Then logged with a Windows administrator account and using "Command Prompt" utility go to MsSqlSpatial directory and run the following commands:

cd %SQL_SERVER_HOME%\90\Tools\MsSqlSpatial\
msscmd -deploy -server=SERVER -db=DATABASE_NAME

where
  • SQL_SERVER_HOME - is a path to your SQL Server 2005 installation directory;
  • SERVER - is a HOSTNAME\INSTANCE_NAME or a HOSTNAME\INSTANCE_NAME,PORT or an alias;
  • DATABASE_NAME - is name of your database.

By default, msscmd utility will use Windows authentication option with current credentials. Alternatively, you can deploy MsSqlSpatial using SQL Server authentication:

msscmd -deploy -server=SERVER -db=DATABASE_NAME -login=LOGIN -password=PASSWORD

Upon successful execution the program must return a message like 'C:\Program Files\Microsoft SQL Server\90\Tools\MsSqlSpatial\MsSqlSpatialLibrary.dll' deployed.

Now login to the database using Microsoft SQL Server Management Studio, and note that two metadata tables now appears: ST.GEOMETRY_COLUMNS and ST.SPATIAL_REF_SYS. The former table saves metadata about spatial tables and geometry fields and is internally managed by specific functions for creating/changing/removing geometry fields. The latter one holds information and parameters about cartographic projections and it is used by functions, which handle spatial transformations. Also note that several functions and stored procedures have been created with ST. prefix.

Uninstalling

For uninstalling MsSqlSpatial support of a given Microsoft SQL Server 2005 database:

cd %SQL_SERVER_HOME%\90\Tools\MsSqlSpatial\
msscmd -undeploy -server=SERVER -db=DATABASE_NAME

where
  • SQL_SERVER_HOME - is a path to your SQL Server 2005 installation directory;
  • SERVER - is a HOSTNAME\INSTANCE_NAME or a HOSTNAME\INSTANCE_NAME,PORT or an alias;
  • DATABASE_NAME - is name of your database.

By default, msscmd utility will use Windows authentication option with current credentials. Alternatively, you can undeploy MsSqlSpatial using SQL Server authentication:

msscmd -undeploy -server=SERVER -db=DATABASE_NAME -login=LOGIN -password=PASSWORD

Supported Spatial Reference Systems

To get a full list of Spatial Reference Systems supported by MsSqlSpatial just run the following SQL query:

SELECT
   ST.SPATIAL_REF_SYS.SRID,
   SUBSTRING(
      ST.SPATIAL_REF_SYS.SRTEXT,
      (SELECT CHARINDEX('"', ST.SPATIAL_REF_SYS.SRTEXT)) + 1,
      (
         (SELECT CHARINDEX('",', ST.SPATIAL_REF_SYS.SRTEXT)) -
         (SELECT CHARINDEX('"', ST.SPATIAL_REF_SYS.SRTEXT)) - 1
      )
   ) AS SRNAME
FROM ST.SPATIAL_REF_SYS

This command should display a list like:

SRID  | SRNAME
------+---------------------
...
29182 SAD69 / UTM zone 22S
29183 SAD69 / UTM zone 23S
...

Coordinate transformation operations uses source and target SRID´s numbers.

Configuring a database

Taking a typical scenario when you already have a table in the database that contains coordinates in two separate columns for longitude and latitude, and you want to replicate these fields in a single spatial column, you will need to perform the following steps to create spatial columns, configure custom triggers, which will maintain data integrity, and replicate the data from the original source fields into spatial ones.

Adding spatial columns

Execute the following SQL command to create a spatial column in the specified table:

EXECUTE ST.AddGeometryColumn 'dbo', 'TABLE', 'COLUMN_NAME', 4326, 'POINT'

where
  • dbo - is a database schema. The default schema is used if empty ('');
  • TABLE - target table;
  • COLUMN_NAME - name of the new column;
  • 4326 - integer number which defines a Spatial Reference System for geometry field. See the list of supported Spatial Reference Systems for further information;
  • POINT - geometry type of geometry column. Most common types include POINT (for point layers), MULTILINESTRING (for line layers) and MULTIPOLYGON (for polygon layers).

Upon completion you should receive the following message:

Added geometry column [dbo].[TABLE].[COLUMN_NAME] and constraints SRID=4326, GeometryType=POINT.

Keep in mind that each spatial table must have a primary key.

For any SFS compliant spatial database the use of AddGeometryColumn procedure is strongly recommended when defining geometry fields. Do not use ALTER TABLE MyTable ADD COLUMN MyColumn SQL commands! AddGeometryColumn procedure is responsible for creating the geometry field, internal objects (indexes, constraints, triggers, etc.) and it will also add a reccord to ST.GEOMETRY_COLUMNS metadata table.

Creating triggers

In a typical scenario you will have a table that contains longitude and latitude values in decimal fields, which are maintained at application level. To enable spatial features over those coordinates they need to be transformed into a spatial data type. The best way to do that is to create a trigger that will watch for any changes in longitude and latitude fields in your table and will automatically replicate those changes into a spatial field taking care of data integrity.

It is recommended that you set "Allow Nulls" property of spatial columns to the same value as set to the columns they are dependent on (e.g. longitude and latitude). By default, all spatial columns are created as nillable. So, if your source columns do not allow NULL values, disable "Allow Nulls" option for your spatial column as well after you have created all the required triggers and replicated the data into a spatial field.

To ensure that your spatial data is always consistent with the source data, create the following trigger for each spatial column you have created. If your columns allow NULL values use the following SQL script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
-- Author:      Pavel Golodoniuc
-- Create date: 2010-02-02
-- Description: Ensure data integrity between spatial column
--              and its source data.
-- =============================================================
CREATE TRIGGER [dbo].[TRIGGER_NAME]
   ON [dbo].[TARGET_TABLE]
   AFTER INSERT, UPDATE
AS 
BEGIN
   SET NOCOUNT ON;

   -- Update geometry field if longitude and latitude fields have been changed
   IF UPDATE([LONGITUDE]) OR UPDATE([LATITUDE])
   BEGIN
      -- Update geometry field when underlying data is not null
      UPDATE [dbo].[TARGET_TABLE]
      SET
         [GEOMETRY_POINT_FIELD] =
            ST.PointFromText(
               'POINT(' + CAST([dbo].[TARGET_TABLE].[LONGITUDE] AS VARCHAR) + ' ' +
                  CAST([dbo].[TARGET_TABLE].[LATITUDE] AS VARCHAR) + ')',
               4326
            )
      FROM inserted
         INNER JOIN [dbo].[TARGET_TABLE] ON [dbo].[TARGET_TABLE].[PK] = inserted.[PK]
      WHERE inserted.[LONGITUDE] IS NOT NULL AND inserted.[LATITUDE] IS NOT NULL;

      -- Reset geometry field when underlying data is null
      UPDATE [dbo].[TARGET_TABLE]
      SET [GEOMETRY_POINT_FIELD] = NULL
      FROM inserted
         INNER JOIN [dbo].[TARGET_TABLE] ON [dbo].[TARGET_TABLE].[PK] = inserted.[PK]
      WHERE inserted.[LONGITUDE] IS NULL OR inserted.[LATITUDE] IS NULL;
   END
   -- Update longitude and latitude fields if geometry field has been changed
   ELSE IF UPDATE([GEOMETRY_POINT_FIELD])
   BEGIN
      -- Update longitude and latitude fields when geometry field is not null
      UPDATE [dbo].[TARGET_TABLE]
      SET
         [LONGITUDE] = ST.X([dbo].[TARGET_TABLE].[GEOMETRY_POINT_FIELD]),
         [LATITUDE] = ST.Y([dbo].[TARGET_TABLE].[GEOMETRY_POINT_FIELD])
      FROM inserted
         INNER JOIN [dbo].[TARGET_TABLE] ON [dbo].[TARGET_TABLE].[PK] = inserted.[PK]
      WHERE inserted.[GEOMETRY_POINT_FIELD] IS NOT NULL;

      -- Reset longitude and latitude fields when geometry field is null
      UPDATE [dbo].[TARGET_TABLE]
      SET
         [LONGITUDE] = NULL,
         [LATITUDE] = NULL
      FROM inserted
         INNER JOIN [dbo].[TARGET_TABLE] ON [dbo].[TARGET_TABLE].[PK] = inserted.[PK]
      WHERE inserted.[GEOMETRY_POINT_FIELD] IS NULL;
   END
END

where
  • dbo - database schema;
  • TRIGGER_NAME - unique trigger name;
  • TARGET_TABLE - spatial table that contains a spatial field of interest;
  • LONGITUDE and LATITUDE - source coordinates, which have to be replicated into a spatial column;
  • GEOMETRY_POINT_FIELD - spatial column created with ST.AddGeometryColumn stored procedure where the LONGITUDE and LATITUDE data is to be replicated to;
  • 4326 (second argument of ST.PointFromText function) - integer number which defines a Spatial Reference System for geometry field. This value must be consistent with the value used when creating a spatial column with ST.AddGeometryColumn stored procedure. See Adding spatial columns section for further information;
  • PK - primary key defined for the target table.

In case if design of the database doesn't allow NULL values in source fields, the trigger may be simplified to the following:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================
-- Author:      Pavel Golodoniuc
-- Create date: 2010-02-02
-- Description: Ensure data integrity between spatial column
--              and its source data.
-- =============================================================
CREATE TRIGGER [dbo].[TRIGGER_NAME]
   ON [dbo].[TARGET_TABLE]
   AFTER INSERT, UPDATE
AS 
BEGIN
   SET NOCOUNT ON;

   -- Update geometry field if longitude and latitude fields have been changed
   IF UPDATE([LONGITUDE]) OR UPDATE([LATITUDE])
   BEGIN
      UPDATE [dbo].[TARGET_TABLE]
      SET
         [GEOMETRY_POINT_FIELD] =
            ST.PointFromText(
               'POINT(' + CAST([dbo].[TARGET_TABLE].[LONGITUDE] AS VARCHAR) + ' ' +
                  CAST([dbo].[TARGET_TABLE].[LATITUDE] AS VARCHAR) + ')',
               4326
            )
      FROM inserted
         INNER JOIN [dbo].[TARGET_TABLE] ON [dbo].[TARGET_TABLE].[PK] = inserted.[PK]
      WHERE inserted.[LONGITUDE] IS NOT NULL AND inserted.[LATITUDE] IS NOT NULL;
   END
   -- Update longitude and latitude fields if geometry field has been changed
   ELSE IF UPDATE([GEOMETRY_POINT_FIELD])
   BEGIN
      UPDATE [dbo].[TARGET_TABLE]
      SET
         [LONGITUDE] = ST.X([dbo].[TARGET_TABLE].[GEOMETRY_POINT_FIELD]),
         [LATITUDE] = ST.Y([dbo].[TARGET_TABLE].[GEOMETRY_POINT_FIELD])
      FROM inserted
         INNER JOIN [dbo].[TARGET_TABLE] ON [dbo].[TARGET_TABLE].[PK] = inserted.[PK]
      WHERE inserted.[GEOMETRY_POINT_FIELD] IS NOT NULL;
   END
END

These triggers will ensure data integrity in both ways - spatial column is updated automatically when any changes to the underlying non-spatial fields are encountered and vise versa.

Once the trigger is created you need to execute the following SQL command to induce data replication process:

UPDATE [dbo].[TARGET_TABLE]
SET [LONGITUDE] = [LONGITUDE]

where
  • dbo - database schema;
  • TARGET_TABLE - spatial table that contains a spatial field of interest;
  • LONGITUDE - one of the source coordinates, which will cause the trigger to fire.

Note:
This particular example uses POINT geometry. For other geometry types you will need to modify this triggers accordingly.

Dropping triggers

Before removing spatial columns you need to drop all triggers, which dependent on those columns. To drop a trigger run execute the following SQL command:

DROP TRIGGER [dbo].[TRIGGER_NAME]

where
  • dbo - database schema;
  • TRIGGER_NAME - trigger name.

Dropping spatial columns

When you want to remove a spatial field it is also highly recommended to use the propper procedure instead of manual altering of tables. DropGeometryColumn procedure will remove all objects related to desired geometry field.

EXECUTE ST.DropGeometryColumn 'dbo', 'TABLE', 'COLUMN_NAME'

where
  • dbo - is a database schema. The default schema is used if empty ('');
  • TABLE - target table;
  • COLUMN_NAME - geometry field to be removed.

Upon completion you should receive the following message:

Column [dbo].[TABLE].[COLUMN_NAME] removed.

Installing sqlserver plug-in

Microsoft SQL Server 2005 data source in GeoServer is enabled via the use of sqlserver plug-in.

  • Download the SQL Server extension from the GeoServer download page;
  • Extract the contents of the archive into the WEB-INF/lib directory of the GeoServer installation.

Interoperability between Java application and Microsoft SQL Server is provided by Java Database Connectivity (JDBC) driver. The SQL Server JDBC Driver is available to all SQL Server users at no additional charge, and provides access to SQL Server 2000, SQL Server 2005, and SQL Server 2008 from any Java application, application server, or Java-enabled applet. This solutions has been tested with different versions of Microsoft SQL Server JDBC Driver and Java. See the compatibility chart below:

  jdk1.5.0_15 jdk1.6.0_04  
sqljdbc_1.0 DONE DONE http://www.microsoft.com/downloads/details.aspx?familyid=E22BC83B-32FF-4474-A44A-22B6AE2C4E17&displaylang=en
sqljdbc_1.1 DONE DONE http://www.microsoft.com/downloads/details.aspx?FamilyId=6D483869-816A-44CB-9787-A866235EFC7C&displaylang=en
sqljdbc_1.2 DONE DONE http://www.microsoft.com/downloads/details.aspx?FamilyId=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en
sqljdbc_2.0 (sqljdbc.jar) DONE ALERT! http://www.microsoft.com/downloads/details.aspx?FamilyID=99b21b65-e98f-4a61-b811-19912601fdc9&displaylang=en
sqljdbc_2.0 (sqljdbc4.jar) ALERT! ALERT!

Choose an appropriate version of the SQL Server JDBC Driver, navigate to the download page, click "Download" button, accept the license and download the appropriate archive for your operating system. Then extract the contents of the archive and copy the JAR file to the WEB-INF/lib directory of the GeoServer instance.

See also:

As a recommended choice for Microsoft SQL Server 2005 connectivity, I would suggest to use Java 1.6 and Microsoft SQL Server JDBC Driver 1.2 with the latest build of GeoServer v2.0.1 (I have tested the solution on SVN Rev.: 13981).

Configuring connection to Microsoft SQL Server 2005

Generic connection string for SQLJDBC Driver

Once you made sqljdbc.jar library available in your CLASSPATH you may start using Microsoft SQL Server JDBC Driver from your Java application. The driver name for Microsoft SQL Server 2005 Driver for JDBC is:

com.microsoft.sqlserver.jdbc.SQLServerDriver

The template connection string for Microsoft SQL Server 2005 Driver for JDBC is:

jdbc:sqlserver://SERVER_NAME:PORT;database=DATABASE_NAME;

where
  • SERVER_NAME - server host name;
  • PORT - Microsoft SQL Server listener port (usually, 1433);
  • DATABASE_NAME - database name you want to connect to.

Configuring context and JNDI connection pool

To configure JNDI connection pool edit /etc/tomcat6/server.xml adding a Context in the Host for localhost. The Context below includes the JNDI connection pool. Note that you will have to substitute the correct values for the various connection properties:

<Context
    path="/gswa-earthresource"
    docBase="/usr/local/gswa-earthresource/geoserver"
    crossContext="false"
    reloadable="false">
    <Resource
        name="jdbc/gswa-earthresource"
        auth="Container"
        type="javax.sql.DataSource"
        url="jdbc:sqlserver://SERVER_NAME:1433;database=DATABASE_NAME;"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        username="DATABASE_USERNAME"
        password="DATABASE_PASSWORD"
        maxActive="20"
        maxIdle="10"
        minIdle="0"
        maxWait="10000"
        minEvictableIdleTimeMillis="300000"
        timeBetweenEvictionRunsMillis="300000"
        numTestsPerEvictionRun="20"
        poolPreparedStatements="true"
        maxOpenPreparedStatements="100"
        testOnBorrow="true"
        validationQuery="SELECT CURRENT_TIMESTAMP" />
</Context>

If you're using Jetty, your configuration file may look like:

<?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Mort Bay Consulting//DTD Configure//EN" "http://jetty.mortbay.org/configure.dtd">
<Configure class="org.mortbay.jetty.Server">
   <New class="org.mortbay.jetty.plus.naming.Resource">
      <Arg>jdbc/gswa-earthresource</Arg>
      <Arg>
         <New class="org.apache.commons.dbcp.BasicDataSource">
            <Set name="driverClassName">com.microsoft.sqlserver.jdbc.SQLServerDriver</Set>
            <Set name="url">jdbc:sqlserver://SERVER_NAME:1433;database=DATABASE_NAME;</Set>
            <Set name="username">DATABASE_USERNAME</Set>
            <Set name="password">DATABASE_PASSWORD</Set>
            <Set name="maxActive">20</Set>
            <Set name="maxIdle">10</Set>
            <Set name="minIdle">0</Set>
            <Set name="maxWait">10000</Set>
            <Set name="minEvictableIdleTimeMillis">300000</Set>
            <Set name="timeBetweenEvictionRunsMillis">300000</Set>
            <Set name="numTestsPerEvictionRun">20</Set>
            <Set name="poolPreparedStatements">true</Set>
            <Set name="maxOpenPreparedStatements">100</Set>
            <Set name="testOnBorrow">true</Set>
            <Set name="validationQuery">SELECT CURRENT_TIMESTAMP</Set>
         </New>
      </Arg>
   </New>
</Configure>

Firewall timeouts can silently sever idle connections to the database and cause GeoServer to hang. If there is a firewall between GeoServer and the database, a connection pool configured to shut down idle connections before the firewall can drop them will prevent GeoServer from hanging. This JNDI connection pool is configured to shut down idle connections after 5 to 10 minutes.

Configuring app-schema complex features

Microsoft SQL Server data source work absolutely the same way as any other data source. So, it should be no difference in the configuration at all.

Some things you should know about spatial fields in Microsoft SQL Server 2005

  1. Spatial properties aren't recognised as such when configuring a simple feature using GeoServer GUI.
  2. In Microsoft SQL Server 2005 all spatial data is stored in varbinary(MAX) format. This format is not automatically recognised by GeoServer. That's why when configuring a view for a complex feature in GeoServer, all spatial fields must be converted into so-called Well-Known-Text (WKT) format. WKT is a text markup format for representing geometry objects or spatial reference systems using a human-readable data description. To convert the data into WKT format call ST.AsText function:
    SELECT ST.AsText([GEOMETRY_FIELD]) AS [ALIAS]
    FROM [TABLE_NAME]
    
  3. Since all spatial data is server in WKT format GeoServer knows nothing about data SRID.
  4. Spatial filters will probably not work with this solution as all these functions belong to ST namespace that is unknown to GeoServer.

References

-- PavelGolodoniuc - 2010-01-29
Topic attachments
I Attachment Action Size Date Who Comment
LoginPropertiesUserMapping.pngpng LoginPropertiesUserMapping.png manage 32.1 K 01 Feb 2010 - 15:47 PavelGolodoniuc Login Properties - User Mapping dialog
Topic revision: r8 - 15 Oct 2010, UnknownUser
 

Current license: All material on this collaboration platform is licensed under a Creative Commons Attribution 3.0 Australia Licence (CC BY 3.0).