|
Write text file with spatial data from SQL Server 2008
Tuesday September 15 2009 at 21:08
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
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.txt'
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.
        
|
SpatialDB Advisor
Home
Comment