2011-11-21

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:
  • 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
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!...