Write text file with spatial data from SQL Server 2008

Trivial I know, but here is a method for writing the result of a query (select) to a text file where the query contains either geometry or geography data.

To do this we will use the xp_cmdshell stored procedure to call the external sqlcmd tool. To do this, first, we must allow SQL Server to use xp_cmdshell. We do this by executing the following in a Microsoft Sql Server Management Studio query window:

 sp_configure 'xp_cmdshell', '1';
 RECONFIGURE WITH OVERRIDE

Note: This is the same as for all other database systems (Oracle etc) where an internal process (eg PL/SQL or TSQL) wants to interact with the file system.

Then we can write the SQL statement result set to disk as follows (note that I have chosen to write the geography data as Well Known Text):

 USE GISDB  -- Change to your database
 GO
 EXEC master..xp_cmdshell 'sqlcmd -E -d GISDB -S SPDBA\SQLEXPRESS -Q "select gid,geog.STAsText() as wkt from dbo.MyGrid" -o C:\temp\qgc_grid.t

The written data looks like this:

 gid         wkt
 ----------- ----------------------------------------------------------------------------------------------------
           1 POLYGON ((149.911 -27.0923, 149.913 -27.0923, 149.913 -27.09, 149.911 -27.09, 149.911 -27.0923))
           2 POLYGON ((149.911 -27.09, 149.913 -27.09, 149.913 -27.0878, 149.911 -27.0878, 149.911 -27.09))
           3 POLYGON ((149.911 -27.0878, 149.913 -27.0878, 149.913 -27.0855, 149.911 -27.0855, 149.911 -27.0878)
           4 POLYGON ((149.911 -27.0855, 149.913 -27.0855, 149.913 -27.0833, 149.911 -27.0833, 149.911 -27.0855))
           5 POLYGON ((149.911 -27.0833, 149.913 -27.0833, 149.913 -27.081, 149.911 -27.081, 149.911 -27.0833))
 ...

I hope this is useful to someone.