Returning spatial data in GeoJSON format - Part 2

Published Mar 23 2019 02:13 PM 8,281 Views
First published on MSDN on Jan 13, 2016

In the previous post Returning spatial data in GeoJSON format - Part 1 , I have described how you can export Sql Server spatial data to GeoJSON . GeoJSON is popular JSON format used to represent spatial data that looks like:

"type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [125.6, 10.1]
"properties": {
"name": "Dinagat Islands"

However, in part 1, I have exported simple points. In this post I will show you how to export other spatial types such as lines or polygons, into equivalent GeoJSON formats.

Spatial data in SQL server can be transformed to well-known text (WKT) format that looks like MULTIPOINT ((100 100), (200 200)) . To transform spatial data to GeoJSON such as { "type": "MultiPoint", "coordinates": [ [100, 100], [200, 200] ] } , I will transform WKT to JSON.

Data preparation

First, I need to prepare spatial data that will be exported. I will use the following script:

DROP TABLE IF EXISTS dbo.SpatialTable;

( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('POINT (100 100)', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('MULTIPOINT ((100 100), (200 200))', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('MULTIPOLYGON (((5 5, 10 5, 10 10, 5 5)), ((10 10, 100 10, 200 200, 30 30, 10 10)))', 0));

In this example, I have populated one table with five different spatial objects that will be exported.

Exporting Spatial data as GeoJSON

Now, I will use SELECT query with FOR JSON PATH to select spatial data from table and format it as JSON (or GeoJSON in this case):

'Feature' as [type],
JSON_QUERY( dbo.geometry2json( GeomCol1 ) ) as [geometry]

id as [],
GeomCol1.STGeometryType() as 'properties.sqlgeotype',
GeomCol1.ToString() as 'properties.wkt'
FROM SpatialTable

First I have selected literal 'Feature' as property [type] - this is required value in GeoJSON format. Then, I'm using user defined function to convert spatial type to JSON (cod eof the function is below). Note that I need to pass generated JSON to JSON_QUERY function. Without this call, FOR JSON will output GeoJSON objects as strings instead of sub-objects.

Finally, I'm exporting some additional data such as id of the row, sql geo type, and WKT value in the properties object. These are custom additional information that I might generate if needed.

In order to produce properly formatted GeoJSON, you will just need to wrap results of this query with the following wrapper:

{ "type": "FeatureCollection",
<< query results >>

Utility function that is used formatting spatial cells as GeoJSON is shiwn below.

DROP FUNCTION IF EXISTS dbo.geometry2json
CREATE FUNCTION dbo.geometry2json( @geo geometry)
RETURNS nvarchar(MAX) AS
'{' +
(CASE @geo.STGeometryType()
'"type": "Point","coordinates":' +
REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'POINT ',''),'(','['),')',']'),' ',',')
'"type": "Polygon","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'POLYGON ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
'"type": "MultiPolygon","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'MULTIPOLYGON ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
'"type": "MultiPoint","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'MULTIPOINT ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
'"type": "LineString","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'LINESTRING ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'

In this case I'm using type and well-known text representation of spatial data and converting WKT to GeoJSON. I need few replace calls to replace parentheses and spaces in WKT to brackets and commas in JSON. Depending on type of spatial data, I would need three or four REPLACE calls. Code is modified from this stack overflow question.


FOR JSON clause enables you to export any data as JSON text, so it can be used to format spatial data as GeoJSON. With a couple of replaces you can transform WKT to GeoJSON and export any type of spatial data that is stored in SQL Server.

1 Comment
Version history
Last update:
‎Mar 23 2019 02:13 PM
Updated by: