AntholoGIS

December 3, 2007

Spatially enabling Microsoft SQL Server 2005

Filed under: GIS, Spatial Databases — Tags: , , — Eduardo Kanegae @ 8:24 pm

Before Microsoft SQL Server 2005 comes up, users of this database wanting to handle spatial data had basically 3 options:

  • ESRI ArcSDE - which means purchasing SDE licenses and probably some other ESRI product for data maintenance
  • TerraLib - an OpenSource option including TerraView as default user interface. But, just like ArcSDE, TerraLib has its own data access model and won´t permit the use of spatial SQL
  • or upgrade to SQL Server 2008 and finally enable spatial support natively from Microsoft

SQL Server 2008 Now, the opensource project named MsSqlSpatial - a C# extension created by Ricardo Stuven - can do the job using OGC SFS standards.

As a “Simple Features for SQL” compliant product, MsSqlSpatial brings to the table an advantage for other SFS users ( PostGIS, MySQL Spatial, Oracle Spatial) : a more comfortable migration process and learning curve!

By now, data stored in MsSqlSpatial databases can only be directly rendered by MapServer ( using OGR/ODBC driver), SharpMap, FWTools/OpenEV and GIS Explorer.

Installation

This guide will cover the installation of MsSqlSpatial on Microsoft SQL Server 2005 Express Edition ( a non-cost edition) but the process should be mostly the same for other editions of SQL 2005. For further information regarding system requirements please check:

.NET 2.0

If your SQL 2005 hosting machine do not have .NET 2 installed, download it and run it logged with an administrator account. It´s a “next-next” installer.

Micrsoft SQL Server 2005 Express

MS SQL 2005 Express is the new version of MSDE - the cost-free engine of MS SQL Server 2000. The advantage of SQL 2005 Express over MSDE is that it does not have that “one user connection only” limitation anymore. Instead, SQL 2005 Express limits the size of each datafile database, which may not exceed 4Gb. However, you can set various 4Gb datafiles for the same database, and then enlarge database max size.

  • if SQL Server 2000 is installed, you DON´T need to remove it
  • logged as an administrator, run the SQL Server 2005 Express installer: http://go.microsoft.com/fwlink/?LinkId=65212
  • pay attention during setup process and take care with some default options ( such as the creation of a sa user without password)
  • in a standard installation, destination folder will be something like C:\Program Files\Microsoft SQL Server\MSSQL\ and C:\Program Files\Microsoft SQL Server\90. If SQL Server 2000 is already installed, under its default folder, then the primary folder of SQL 2005 will be C:\Arquivos de Programas\Microsoft SQL Server\MSSQL.1\
  • a windows service named SQL Server(SQLEXPRESS) should appear at windows services list

Enabling MIXED MODE logins

If by any reason you forgot to change the default authentication method ( NT ), you might change it to enable MIXED MODE logins - which let you login to a database using a SQL Server account. Run regedit.exe a find the following key:

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL\MSSQLServer\Login Mode

Or, if you also have SQL 2000 installed then find the key:

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Login Mode

Change the key value to 2 and re-start SQLEXPRESS service.

Without this change, if you try a connection you might receive the following error message:

Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

Protocols

  • click at Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Configuration Manager
  • at SQL Server 2005 Network Configuration section, leave only TCP/IP and Shared Memory as active options
  • at SQL Server 2005 Network Configuration\Protocols for SQLEXPRESS\TCP/IP it´s possible to find at what port the service is currently running. Just look at TCP Dinamic Ports field. Leaving this field with 0 value, a random port will be choosen for each service start up.
  • at SQL Native Client Configuration section, click Client Protocols and enable TCP/IP and Shared Memory protocols.

Note: SQL Server 2005 uses the concept of Instance names for running services. For connections is necessary to provide HOSTNAME\INSTANCE_NAME,PORT instead of HOSTNAME,PORT only. Using the SQL Server Configuration Manager is also possible to define an alias for HOSTNAME\INSTANCE_NAME,PORT combination.

Additional settings

  • click Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration
    • click Surface Area Configuration for Services and Connections and at item Database Engine\Remote Connections, select Using TCP/IP only option. By default, the service will only accept local connections
    • click Surface Area Configuration for Features
      • at Database Engine\Ad Hoc Remote Queries section, turn on Enable OPENROWSET and OPENDATASOURCE support option. This will enable SQL 2005 to be used as a client for other databases such as SQL 2000. It´s possible to make a connection to SQL 2005 and then SELECT data from a SQL 2000 database.
      • at Database Engine\CLR Integration section, turn on Enable CLR Integration option. This feature is mandatory for MsSqlSpatial installation
  • restart the service

References

Books Online

Books Online is an off-line documentation you can install if you do not want to query documentation at MSDN website.

SQL Server Management Studio Express

MSSQL Management Studio Express
SQL Server Management Studio Express is an administration front-end for SQL Server ( for 2005 and 2000 versions).

Studio can be start with the command Start->Programs->Microsoft SQL Server 2005->SQL Server Management Studio Express.

For SQL 2000 connections, provide HOSTNAME at Server name field. For SQL 2005 services you can provide HOSTNAME\INSTANCE_NAME or HOSTNAME\INSTANCE_NAME,PORTA or an alias.

MsSqlSpatial

MsSqlSpatial C# extension will enable spatial data types and functions ( compliant to OGC SFS standard) for a given MS SQL Server 2005 database. Just like PostGIS, MsSqlSpatial is activated per database and not per server.

Creating a test database

MSSQL Management Studio Express

  • using an administrator windows account, login to your SQL 2005 server using SQL Studio with NT authentication method
  • right-click Security/Logins item and select New login option
  • provide a login name to be created and select SQL Server authentication option. Also provide a password( avoid too much simple passwords!) for this database user. This login will be the DBA for our test database.
  • turn off Enforce password expiration option and then hit OK
  • right-click Databases item and select New database option
  • provide a database name( eg.: db_gis), data and log file size policies and don´t forget to add the previously created login to Owner field
  • and finally, change at properties page of the created login the Default database property - define it using the name of the database you created.

Deploying MsSqlSpatial extension on a existing database

  • download MsSqlSpatial-0.1.1.zip file from http://www.codeplex.com/MsSqlSpatial
  • logged with a Windows administrator account extract the file in a folder such as C:\Program Files\Microsoft SQL Server\90\Tools\MsSqlSpatial. The zip file has the following files:
    • msscmd.exe.config
    • msscmd.exe
    • Npgsql.dll - PostgreSQL client( for importing data from PostGIS databases)
    • Mono.Security.dll
    • MsSqlSpatialLibrary.dll
  • enable the extension using the following command:

$ cd “C:\Program Files\Microsoft SQL Server\90\Tools\MsSqlSpatial”
$ msscmd -deploy -server=SERVER -db=DATABASENAME
# applying the necessary substitutions:
# SERVER = HOSTNAME\INSTANCE_NAME or HOSTNAME\INSTANCE_NAME,PORT or alias
# DATABASENAME = name of your database ( eg.: db_gis )

  • this command must return a message like “C:\Program Files\Microsoft SQL Server\90\Tools\MsSqlSpatial\MsSqlSpatialLibrary.dll’ deployed.”
  • now login to this database using Studio, and note that 2 meta-data table now appears: ST.GEOMETRY_COLUMNS and ST.SPATIAL_REF_SYS. The first table saves metadata regarding spatial tables and geometry fields and this table is internally managed by specific functions for creating/changing/removing geometry fields. The second one holds information and parameters of cartographic projections and it´s used by functions that handles spatial transformations.
  • also note that several functions and stored procedures now exists with ST. prefix names

Uninstalling MsSqlSpatial extension

For uninstalling MsSqlSpatial support of a given MS SQL 2005 database:

  • remove geometry fields ( please refer to Removing geometry fields)
  • remove MsSqlSpatial extension

$ cd “C:\Program Files\Microsoft SQL Server\90\Tools\MsSqlSpatial”
$ msscmd -undeploy -server=SERVER -db=DATABASENAME
# replacing… :
# SERVER = HOSTNAME\INSTANCE_NAME or HOSTNAME\INSTANCE_NAME,PORT or an alias
# DATABASENAME = name of your database ( eg.: db_gis )

Listing 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 will output 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.

Notes & References

MDAC 2.8

MDAC 2.8 is needed for old-client machines ( before .NET) to enable MS SQL 2005 connecting support - example: VB6 applications will need this driver. You don´t need to install this package at the machine you installed MS SQL 2005.

Note: this download must be approved by Windows Genuine Advantage program

Additional packages

FWTools

FWTools is a set of OpenSource GIS tools packaged by Frank Warmerdam. The kits are intended to be easy for end users to install and get going with. No fudzing with building from source, or having to collect lots of interrelated packages. The package includes:

  • OpenEV: desktop data viewer and analysis tool.
  • GDAL/OGR: these libs are currently being used by many applications to provide vector/raster data acess to many different formats. Also include a set of usefull command line tools.
  • MapServer: “the” web mapping engine.
  • PROJ.4: a cartographic projections library with commandline utilities.

In fact, FWTools is not required for handling spatial data stored on MsSqlSpatial databases. However, you can use OpenEV or MapServer to access spatial data, once OGR has ODBC support.

For installing FWTools/MapServer CGI with Microsoft IIS web server:

  • copy all .dll files and mapserv.exe from bin FWTools folder to cgi-bin IIS folder
  • access http://localhost/cgi-bin/mapserv.exe .
  • if you get a “No query information to decode. QUERY_STRING is set, but empty.” message, that´s installed. ;-)

MS4W

MS4W, MapServer for Windows acronym, is the simplest way to get MapServer & related tools running on Windows machines. Its installer has a preconfigured set of the following softwares:

Installing instructions:

  • hit http://www.maptools.org/ms4w/index.phtml?page=downloads.html and download ms4w_x.x.x.zip file from MS4W Base Package/Release Versions section
  • extract the .zip file at the root of any windows partition ( eg.: C:\ or D:\ )
  • ensure that you do not have any windows service running at 80 port
  • open ms4w folder and run apache-install.bat file. A new windows service named Apache MS4W Web Server will be created
  • for testing enter http://localhost/ . A web page starting with “MS4W - MapServer 4 Windows - version x.x.x” text must appear.

At http://www.maptools.org/ms4w/index.phtml?page=downloads.html, under Applications Packaged for MS4W section you can download and install some MS4W out-of-the-box applications. Just download the desired application package, extract it at the same folder where MS4W were installed and restart Apache.

MsSqlSpatial - an introduction

Creating spatial tables

ST.ImportFromShapefile

Using the ST.ImportFromShapefile stored procedure it´s possible to create a spatial table and also fill it with data from a shapefile.

Sintax:

EXECUTE ST.ImportFromShapefile 'your_shapefile.shp', 'schema', 'table', 'column', SRID, 'type'
  • your_shapefile.shp: shapefile path to be imported
  • schema: name of the database schema to place the new table. You can also use an empty string for using the default schema
  • table: destiny table name.
  • column: name of geometry field.
  • SRID: an integer number which identifies the spatial reference system to be assigned at geometry field. -1 SRID means “no SRS defined”) but you can run ST.UpdateGeometrySRID stored procedure to define one.
  • type: geometry type - POINT, LINESTRING, MULTILINESTRING, POLYGON, MULTIPOLYGON or GEOMETRY.

msscmd

msscmd command can also import data from shapefiles or from PostGIS tables.

Sintax:
msscmd.exe -server=SERVERNAME -db=DATABASE -table=DestinyTable -column=geom_column \
-import=shp -shp_filename=your_shapefile.shp

  • SERVERNAME: Eg.: MYSERVER\SQLEXPRESS,2104 ( server\instance_name,port)
  • DATABASE: name of target database
  • DestinyTable: name of target table
  • geom_column: name of target geometry field
  • import=shp: tells msscmd to import data using “from shapefile” method
  • your_shapefile.shp: full path of your shapefile data source

You can import data using “from PostGIS table” method:
msscmd.exe -server=SERVERNAME -db=DATABASENAME -table=DestinyTable -import=pgsql \
-pgsql_server=pg_server_name -pg_login=pg_user -pgsql_password=pg_pwd \
-pgsql_db=pg_db_name -pgsql_schema=pg_schema -pgsql_table=pg_table

Manually creating a spatial table

The advantage of manually defining spatial tables is the possibility of specifying the exact data type you wish for each table column. Per example, both previous methods probably will define string fields using varchar(4000) definitions, which is too much if your fields does not containg such bigger strings.

Note: for the samples bellow, let´s use two common objects found on a forestry system context - stands and inventory parcels.

Connect to your database and run the following scripts.
/*-- 1st step - let´s create 2 tables: Forestry stands and inventory parcels --*/
CREATE TABLE tbl_stands(
gid int IDENTITY(1,1) NOT NULL,
st_project_code char(4) NOT NULL,
st_stand_number varchar(5) NOT NULL,
CONSTRAINT tbl_stands_pkey PRIMARY KEY(gid)
) ;

CREATE TABLE tbl_parcels (
gid int IDENTITY(1,1) NOT NULL,
pc_project_code char(4) NULL,
pc_stand_number varchar(4) NULL,
pc_parcel_number varchar(3) NOT NULL,
pc_volume numeric(5,1) NOT NULL,
CONSTRAINT tbl_parcels_pkey PRIMARY KEY(gid)
) ;
Keep in mind that for each spatial table an integer field set to primary key is needed.
/*-- 2nd step - create the spatial columns --*/
EXECUTE ST.AddGeometryColumn ”,’tbl_stands,’the_geom’,4326,’MULTIPOLYGON’
EXECUTE ST.AddGeometryColumn ”,’tbl_parcels’,'the_geom’,4326,’POINT’
After running these commands you will the following messages:

Added geometry column [dbo].[tbl_stands].[the_geom] and constraints SRID=4326, GeometryType=MULTIPOLYGON.

Added geometry column [dbo].[tbl_parcels].[the_geom] and constraints SRID=4326, GeometryType=POINT.

Now, let´s take a closer look at ST.AddGeometryColumn stored procedure parameters:

  • ” : database schema name.That empty string says “use default schema, please”.
  • ‘tbl_stands’ : table which will receive the new geometry created field.
  • ‘the_geom’ : geometry field name.
  • 4326 : int number which defines a Spatial Reference System for geometry field. See Listing supported Spatial Reference Systems chapter for further information.
  • ‘POINT’ : geometry type of geom column. Most common types include POINT( for point layers), MULTILINESTRING( for line layers) and MULTIPOLYGON( for polygon layers).

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” commands! AddGeometryColumn procedure is responsible for creating the geometry field, internal objects( indexes, constraints, triggers) and it will also add a reccord to ST.GEOMETRY_COLUMNS metadata table.

Removing geometry fields

And also, when you want to remove a spatial field, the same rule is valid: use the propper procedure. DropGeometryColumn procedure will remove all objects related to desired geometry field.

EXECUTE ST.DropGeometryColumn 'schema','table','field'
  • ’schema’ : schema name. Use ” for default schema.
  • ‘table’ : target spatial table name.
  • ‘field’ : geometry field to be removed.

After running this command the following message will appear:

Column [schema].[table].[field] removed.

Running spatial queries

Listing geometries in raw format

SELECT pc_project_code, pc_stand_number, pc_parcel_number, the_geom FROM tbl_parcels

This will produce an output such as:

pc_project_code | pc_stand_number | pc_parcel_number | the_geom
----------------+-----------------+------------------+--------------------------------
P740              01                02                 0x01010000007DADA96193BF47C0...

Listing geometries using WKT ( Well-Known-Text)

WKT(Well-Known Text) is a text markup format for representing geometry objects or spatial reference systems using a human-readable data description.

SELECT pc_project_code, pc_stand_number, pc_parcel_number, ST.AsText(the_geom) wkt FROM tbl_parcels

Result:

pc_project_code | pc_stand_number | pc_parcel_number | wkt
----------------+-----------------+------------------+-------------------------------------
P740              01                02                 POINT (-47.4966852262978 -21.130131)

Computing area values per stand

Because we created our tables using WGS84 SRS ( EPSG:4326), before computing area values we need to transform target geometries using an UTM SRS. In this case, using EPSG:29183 ( UTM-SAD69 Zone 23S) will do the job:

SELECT st_project_code, st_stand_number, ST.Area(ST.Transform(the_geom,29183))/10000 AS hectares FROM tbl_stands

Note: before transforming geometries from a Geographic to an UTM SRS ensure that you already know to which UTM zone you are going to project your area of interest.

The SQL command above will output:

st_project_code | st_stand_number | hectares
----------------+-----------------+------------------
P740              01                15,7364287434625

Possibly related posts: (automatically generated)

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

You must be logged in to post a comment.

Blog at WordPress.com.