493: Undecipherable

SQLServer SQL to GeoJSON

2018-05-20 Dennis Guse

Export GeoJSON via SQL from SQLServer. Based on this blog post on MSDN.

For export directly to a file this powershell script might be helpful: sqlserver-cmd-to-file.ps1

NOTE: I observed (not yet solved) issues, when I tried to export more than 1600 items. Seemed like a “this string is too long” problem.

SELECT 
--GeoJSON Header
  CONCAT('{ "type": "FeatureCollection", "features":', 
--Select POINTS
  (
    SELECT
     'Feature' AS type,
     'Point' AS [geometry.type],
     JSON_QUERY ( FORMATMESSAGE('[%s,%s]',
      FORMAT(yourX, N'0.##################################################'),
      FORMAT(yourY, N'0.##################################################'))
     ) AS [geometry.coordinates]
    FROM PutYourTableHere
    FOR JSON PATH
  ), 
--Close JSON HashMap
  '}'
  )

-Blog-

-Projects-

-About me-

-RSS-

SQLServer SQL to GeoJSON