2012-09-14

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_HEREmytable

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.