MS-SQL 2008 Spatial experiments - last part

...and as started in another post, more (old) notes about MS-SQL 2008 Spatial.

Exporting from SQL2008 Spatial using OGR

$ ogr2ogr -f KML -s_srs "EPSG:4326" -t_srs "EPSG:4326" -where "mytable_id = 6850" myexporteditem.kml "MSSQL:dsn=MY_DSN;server=(local);database=DBNAME_HERE;tables=myscheme.mytable;uid=USER_HERE;pwd=PWD_HERE" mytable

Setup PHP driver for SQL 2008

IMPORTANT: this was drafted in 2011/03, so it could easier nowadays ;-)

• get and extract related package ( from Microsoft website)

• copy file php_sqlsrv_53_ts_vc9.dll to PHP extensions path

• enable it at php.ini and run some phpinfo() to check it out

A *downgrade* to MsSqlSpatial (SQL 2005 lib)

After some tests using native spatial functions/types of SQL2008, I just simply gave it up for trying a new approach:  MsSqlSpatial again! It's a .NET lib originally created for SQL Server 2005 and deploys OGC SFS functions and types into a SQL2005 database. Because it's .NET based, it can also be installed with SQL2008 and despite you will not get a full-featured spatial database just like PostGIS does, it will certainly run faster(very) than native 2008 spatial resources - and yes, it also lacks of enough documentation from Microsoft.

MsSqlSpatial setup

• download the .zip file and extract it to C:\Program Files (x86)\Microsoft SQL Server\100\Tools\MsSqlSpatial, for example

• using Windows cmd Prompt, enter the path above and (must be logged as machine adminstrator):

• $ msscmd -deploy -server=SERVER -db=DB_NAME

• this will deploy spatial types/functions into database scheme

• login to the master database, using 'sa' user and enable .NET libs as:

• sp_configure 'clr enabled', 1;

• GO

• RECONFIGURE;

• GO

Finally, login to your database using MS SQL Studio and then expand 'Programability' node at left treeview - you will see a lot of function names starting with "ST_"

Testing MsSqlSpatial

EXECUTE ST.AddGeometryColumn 'dbo','myspatialtable','geom_2005',4326,'GEOMETRY'

MsSqlSpatial lib will add 'geom_2005' field plus four additional metadata fiels to *myspatialtable*:

• geom_2005_Envelope_MinX

• geom_2005_Envelope_MinY

• geom_2005_Envelope_MaxX

• geom_2005_Envelope_MaxY

Then populate your new table and try to draw some map using MapServer and a virtual OGR datasource:

• CONNECTIONTYPE OGR
• CONNECTION "<OGRVRTDataSource>
•     <OGRVRTLayer name='pol_mypolygonlayer'>
• <SrcDataSource>ODBC:USER/PASSWORD@ODBC_DSN_NAME</SrcDataSource>
• <SrcLayer>dbo.myspatialtable</SrcLayer>
• <LayerSRS>WGS84</LayerSRS>
• <GeometryType>wkbMultiPolygon</GeometryType>
• <GeometryField encoding='WKB' field='geom_WGS84_2005'/>
•     </OGRVRTLayer>
• </OGRVRTDataSource>" 
• DATA "pol_mypolygonlayer"

Conclusion

Try PostGIS first, please :-)  Unless your really have to work using SQL Server spatial 2008, MsSqlSpatial open source library does the job better than manufacturer out-of-the-box option.