MS-SQL 2008 Spatial experiments

...notes of some experiments from 2011 beginning

 - mixing up SQL 2008 + MS4W 3.0.1 ( MapServer 5.6.6 )

SQL Server 2008 Setup

• packages to install:
• NET 3.5 sp1
• MS SQL 2008 Express ( + Studio manager )
• tried and tried, but it didn't work at my old Windows 7 Pro
• worked fine on Windows 2003 Server - even under Virtual Box
• at installer 7th step ("Server configuration"), define "NT AUTHORITY\LOCAL SERVICE" as credentials for 'SQL Server Database Engine' service
• and please, define a good password for sa user and do not forget to enable 'mixed logins'

Some notes on SQL 2008 geometry handling

• use spatial index for optimizing speed - http://msdn.microsoft.com/en-us/library/bb934196.aspx . Sample:

CREATE SPATIAL INDEX sidx_mytable ON 

myscheme.mytable ( mygeomfield )

WITH (

BOUNDING_BOX = ( -180, -90, 180, 90 ) )

• when a table has a 'geometry' field, results will also be shown as a map at MS SQL Studio GUI 

• OGC SFS methods are invoked from geometry fields. Eg.:

SELECT v.*, v.mygeomfield.STAsText() FROM mytable AS v

Testing connection using OGR

version installed: MS4W 3.0.1 / MapServer 5.6.6

• create a system ODBC DSN
• run an ogrinfo test:

$ ogrinfo -so "MSSQL:dsn=MY_DSN;server=(local);database=DBNAME_HERE;tables=myscheme.mytable;uid=USER_HERE;pwd=PWD_HERE" mytable

• it will output something like:

INFO: Open of `MSSQL:.....'

      using driver `MSSQLSpatial' successful.

TO BE CONTINUED here!...